Engineering

SQL 2014 Service Pack 2 was recently released by Microsoft and there is a ton of great new features and enhancements in this release.This isn’t just a collection of bug fixes…there’s some serious value in this Service Pack. Check out the full list here. One of the key things added in this Service Pack is an enhancement of the Extended Events for AlwaysOn Availability Group replication.

Why are the new Availability Group Extended Event interesting?

If you’ve used Availability Groups in production systems with high transaction volumes you know that replication latency can impact your availability. If you want to brush up on that check out our blog posts on AG Replication Latency, Monitoring for replication latency, and issues with the DMVs when monitoring. These new extended events add insight at nearly every point inside your Availability Group’s replication. More importantly they also include duration. So using these Extended Events we can pinpoint latency inside our Availability Group replication.

Paradigm Shift!

What do I mean by that? Every once in a while a technology comes along and changes the way things are done, moves the bar…well last week Microsoft released a Channel 9 video on persistent memory using NVDIMMs and DAX on Windows 2016…then combining it with SQL Server! This is one of those technologies that moves the bar! Check it out here.

Why is this important?

Relational databases like SQL Server use a transaction log to ensure the durability of the transactional operations to the database. This is so it can ensure its data is consistent in the event of a system failure. SQL Server uses a logging protocol called write ahead logging (WAL). This means that the data change operations must be written to stable, persistent storage before the process can change data in the database file…so this means our database throughput is at the mercy of the performance of the device the changes (log records) are written to.

In this post we’re going to introduce the basics of CPU scheduling.

In a computer system, only one thing can happen at a time. More specifically, only one task can be on a processor at a point in time. This can expand to several tasks if the system has multiple processors or a processor with multiple cores, which most modern systems have. For example, a four core system can potentially execute four tasks concurrently.

In this post we’re going discuss how to implement load testing of your storage subsystem with DiskSpd. We’re going to craft tests to measure bandwidth and latency for specific access patterns and IO sizes. In the last post “Load Testing Your Storage Subsystem with Diskspd”  we looked closely at access patterns and I/O size and discussed the impact each has on key performance attributes.

Diskspd command options

Let’s start with some common command options, don’t get caught up on the syntax. Diskspd’s documentation is fantastic. It’s included with the program download here. Here I’m going to tell you why I set these settings this way, so you can adjust them as needed for your environments.

Recently,I have been working with the Debugger Symbols for SQL Server to generate call stacks and learn more about the internals of SQL Server.

I approached one of our clients about doing this on a non-production system and they thought it be great. They would get better insight on their workload, I would get access to a real workload. win..win, right? Even in their stage environment they have a pretty heavy workload so it would be a good candidate for generating call stack data.

Where to get the debugger tools

To generate the needed symbols you will need the “Windows Software Development Kit (SDK) for Windows 10” download here: https://dev.windows.com/en-us/downloads/windows-10-sdk and click “Download the standalone SDK”

• I like to select the Download option and pick “Debugging Tools for Windows” this will download all of the installation files

How to install the debugger tools

The installation of this software is very straight forward, Then find in the downloaded files \Windows Kits\10\StandaloneSDK\Installers\X64 Debuggers And Tools-x64_en-us.msi and install the debugger and install the tools with the default settings.

At this fall’s SQLIntersection conference in Las Vegas I attended, Paul Randal’s (t|b)“Performance Troubleshooting Using Latches and Waits” precon. Where at he asked for some assistance compiling data for a project he’s working on. The project that would require installing the “Debugging Tools for Windows” and generating debug symbols for the SQL binaries. I have always intended to work with the debug symbols to find the call stack traces and experiment with what SQL Server does during certain events, like creating a database, inserting a row and such. These are topics that interest me as a computer scientist and a SQL Server professional and also can help our clients in understand conditions when trying to get a handle on obscure performance anomalies.