Upcoming Speaking Engagements

I found out on Friday that I will have two speaking slots at the PASS Summit 2015 in Seattle, WA. The annual PASS Summit is always a lot of fun and a great learning and networking opportunity.


I'm Speaking Graphic_Large


The first session will be a half-day session, which will let me go into much more detail about this subject:

Dr. DMV’s Troubleshooting Toolkit

Dynamic management views (DMVs) and functions allow you to easily see exactly what is happening inside your SQL Server instances and databases, with a high level of detail. You can discover your top wait types, identify the most CPU-intensive stored procedures, find missing indexes, and identify unused indexes, to name just a few examples. This session presents, demonstrates, and explains a complete set of diagnostic DMV queries that you can easily use to detect and diagnose configuration and performance issues in your SQL Server instances and databases. This session goes into exhaustive detail about how to interpret the results of each diagnostic query, including relevant background information about how to properly configure your hardware, storage subsystem, operating system, SQL Server instance, and databases to avoid many performance and scalability issues.

I will also be doing a regular 75 minute session:

High Availability/Disaster Recovery 101

How do you design a SQL Server 2014 infrastructure to meet specific Recovery Time Objective (RTO) and Recovery Point Objective (RPO) service levels? There are many aspects to consider, from technology choices and licensing to policies and procedures. This session outlines and compares the various high availability and disaster recovery technologies available in SQL Server 2014, showing how you can combine them to design and build a solution to help meet your goals. This session also teaches you how to formulate policies that enable effective use of technology in your organization.

The entire SQLskills team will be speaking at PASS Summit 2015, including Paul Randal, Kimberly Tripp, Jonathan Kehayias, Erin Stellato, and Tim Radney.

Before then, I will be speaking at a number of other events, some of which will be remote and some in person.

Pittsburg SQL Server User’s Group on Jun 30 at 5PM Eastern Time                    Dr. DMV’s Troubleshooting Toolkit

Denver WIT on July 8 at 5:30PM Mountain Time                                               Dr. DMV’s Troubleshooting Toolkit

Johannesburg SQL Server User Group on July 14 at 11AM Mountain Time           Dr. DMV’s Troubleshooting Toolkit

BIG PASS User’s Group on August 11 at 7PM Pacific Time                                 Dr. DMV’s Troubleshooting Toolkit


On Friday, September 18, 2015 I will be doing a full day pre-con for SQLSaturday #441 in Denver, CO. This should be very interesting and a lot of fun.

Analyzing and Improving I/O Subsystem Performance    

SQL Server is often I/O bound, but proving it to your storage or SAN administrator can be challenging! You will learn about the different types of storage that are available for SQL Server, and how to decide what type of storage to use for different SQL Server workload and file types. You will also learn useful tips and techniques for configuring your storage for the best performance and reliability for your workload. There will be extensive coverage on how to use disk benchmark tools like CrystalDiskMark 4.0, SQLIO and Microsoft DiskSpd, so you can confidently understand the performance that your I/O subsystem can deliver. We’ll also cover methods to effectively measure and monitor your storage performance from an OS and SQL Server perspective so that you will have valuable information and evidence available the next time you have to discuss I/O performance with your storage administrator. You will also learn a number of valuable OS and SQL Server configuration settings that will help you get the best I/O performance possible from your storage subsystem. 

Finally, I’ll be speaking at the Fall 2015 SQLintersection event in Las Vegas.

SQL Server 2014 RTM CU8 Available

Microsoft has released SQL Server 2014 RTM CU8, which is Build 12.0.2546. This cumulative update has 38 public hotfixes by my count. This CU may be less attractive now that Microsoft has also released SQL Server 2014 SP1 CU1. I say that because many people may be wanting to get on the SP1 branch, now that the first CU on top of SP1 has been released, and the branches are back in synch.

The official Microsoft build list for SQL Server 2014 is here.

SQL Server Diagnostic Information Queries for June 2015

This month, I have added a new, separate version of these queries for SQL Server 2016. This version will be improved with more SQL Server 2016-specific  queries and columns in existing queries in the near future.

Since SQL Server 2008 R2 and older are out of Mainstream support from Microsoft (and because fewer of my customers are using these old versions of SQL Server), I am not going to be updating the scripts for these older versions of SQL Server every single month going forward.  I started this policy two months ago, and so far, I have not heard any complaints.

Rather than having a separate blog post for each version, I have just put the links for all five major versions in this single post. There are two separate links for each version. The first one on the top left is the actual diagnostic query script, and the one below on the right is the matching blank results spreadsheet, with labeled tabs. 

Here are links to the latest versions of these queries for SQL Server 2016, 2014 and 2012:

SQL Server 2016 Diagnostic Information Queries (June 2015)

SQL Server 2016 Blank Results

SQL Server 2014 Diagnostic Information Queries (June 2015)

SQL Server 2014 Blank Results

SQL Server 2012 Diagnostic Information Queries (June 2015)

SQL Server 2012 Blank Results

Here are links to the most recent versions of these scripts for SQL Server 2008 R2 and older:

SQL Server 2008 R2 Diagnostic Information Queries

SQL Server 2008 R2 Blank Results

SQL Server 2008 Diagnostic Information Queries

SQL Server 2008 Blank Results

SQL Server 2005 Diagnostic Information Queries

SQL Server 2005 Blank Results

The basic instructions for using these queries is that you should run each query in the set, one at a time (after reading the directions for that query). It is not really a good idea to simply run the entire batch in one shot, especially the first time you run these queries on a particular server, since some of these queries can take some time to run, depending on your workload and hardware. I also think it is very helpful to run each query, look at the results (and my comments on how to interpret the results) and think about the emerging picture of what is happening on your server as you go through the complete set. I have some comments in the script on how to interpret the results after each query.

You need to click on the top left square of the results grid in SQL Server Management Studio (SSMS) to select all of the results, and then right-click and select “Copy with Headers” to copy all of the results, including the column headers to the Windows clipboard. Then you paste the results into the matching tab in the blank results spreadsheet.

About half of the queries are instance specific and about half are database specific, so you will want to make sure you are connected to a database that you are concerned about instead of the master system database. Running the database-specific queries while being connected to the master database is a very common mistake that I see people making when they run these queries.

Note: These queries are stored on Dropbox. I occasionally get reports that the links to the queries and blank results spreadsheets do not work, which is most likely because Dropbox is blocked wherever people are trying to connect.

I also occasionally get reports that some of the queries simply don’t work. This usually turns out to be an issue where people have some of their user databases in 80 compatibility mode, which breaks many DMV queries, or that someone is running an incorrect version of the script for their version of SQL Server.

It is very important that you are running the correct version of the script that matches the major version of SQL Server that you are running. There is an initial query in each script that tries to confirm that you are using the correct version of the script for your version of SQL Server. If you are not using the correct version of these queries for your version of SQL Server, some of the queries are not going to work correctly.

If you want to understand how to better run and interpret these queries, you should consider listening to my three latest Pluralsight courses, which are SQL Server 2014 DMV Diagnostic Queries – Part 1SQL Server 2014 DMV Diagnostic Queries – Part 2 and SQL Server 2014 DMV Diagnostic Queries – Part 3. All three of these courses are pretty short and to the point, at 67, 77, and 68 minutes respectively. Listening to these three courses is really the best way to thank me for maintaining and improving these scripts…

Please let me know what you think of these queries, and whether you have any suggestions for improvements. Thanks!