SQL Server Diagnostic Information Queries for October 2015

This month, I spent some time adding columns for the new SERVERPROPERTY options, along with adding more comments and links to the scripts. The SQL Server 2016 version will continue to be improved with more SQL Server 2016-specific queries and new columns in existing queries over the next few months as I discover interesting new things in SQL Server 2016. I am also continuing to add more relevant comments and links to the other active versions of these queries in order to help better interpret the results of these queries and save me time in looking up supporting documentation.

Rather than having a separate blog post for each version, I have just put the links for all six 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 that correspond to each query in the set. 

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

SQL Server 2016 Diagnostic Information Queries (October 2015)

SQL Server 2016 Blank Results

SQL Server 2014 Diagnostic Information Queries (October 2015)

SQL Server 2014 Blank Results

SQL Server 2012 Diagnostic Information Queries (October 2015)

SQL Server 2012 Blank Results

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

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 seven months ago, and so far, I have not heard any complaints.

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…

Finally, if you want to get even more information about how to run and interpret these queries, you should consider attending my half-day session Dr. DMV’s Troubleshooting Toolkit at the PASS Summit 2015 starting at 9:30AM on Friday, Oct 30, 2015 in Seattle.

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

SQL Server 2014 SP1 Cumulative Update 3 Released

On October 19, 2015, Microsoft released SQL Server 2014 Service Pack 1 Cumulative Update 3 (Build 12.0.4425.19). This CU has 36 updates in the public fix list. If you are on the Service Pack 1 branch of SQL Server 2014 (which is where you should be by now, in my opinion), then you should be planning how you will test and deploy this cumulative update on your database servers in a timely fashion.

If you are still on the RTM branch of SQL Server 2014, Microsoft has also released SQL Server 2014 RTM Cumulative Update 10 (Build 12.0.2556.4), which has 30 updates in the public fix list.

The official Microsoft SQL Server 2014 Build versions list is here, while the SQLSentry SQL Server 2014 Build List is here.

I also have a link page, that points to updated posts about the more interesting hotfixes in cumulative updates for the various active SQL Server versions and branches called SQL Server Cumulative Update Highlights.

Finally, for the CU-skeptics out there, I have noticed this verbiage from Microsoft is now included with the Knowledge Base article’s for individual hotfixes:

Recommendation: Install the latest cumulative update for SQL Server

Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. We recommend that you download and install the latest cumulative updates for SQL Server:

Building a Z170 Desktop System with a Core i7-6700K Skylake Processor

Back when I started at SQLskills in May of 2012, I built myself a pretty nice Z77 chipset system with an Intel Core i7-3770K Ivy Bridge processor and 32GB of RAM, running Windows 7 Enterprise Edition. This system uses an ASUS P8Z77-V Deluxe motherboard, with one 512GB OCZ Vertex 4 SSD. It was about the fastest mainstream system that I could build 3.5 years ago. It is mildly overclocked, to 4.3GHz, and it has been extremely reliable over the years as I have used it for much of my daily work.

Some basic information about this system is shown in Figures 1, 2 and 3 below:


Figure 1: CPU-Z CPU Tab for Z77 Core i7-3770K System



Figure 2: CPU-Z Bench Tab for Z77 Core i7-3770K System



Figure 3: Geekbench 3.3.2 Scores for Z77 Core i7-3770K System


Even though this system is still pretty fast, I felt like I could do better in some areas, with a current generation Z170 chipset system with an Intel Core i7-6700K Skylake processor and 64GB of RAM. Last Saturday, I built this new system, and got Windows 10 Professional installed.

Here is the parts list for this system:

  1. Fractal Design Define R5 case
  2. Seasonic SS-660XP2 power supply
  3. ASRock Z170 Extreme 7+ motherboard (Micro Center’s web page has it mislabeled as an Extreme 7)
  4. Intel Core i7-6700K processor
  5. Scythe Kotetsu CPU cooler
  6. (2) 32GB Corsair Vengeance LPX 2666MHz DDR4 RAM kits
  7. (2) 512 GB Samsung 850 Pro SATA III SSDs in hardware RAID 1
  8. 400GB Intel 750 PCIe NVMe storage card

Initially, I’ll be using the Intel integrated graphics, but I may end up using an EVGA Geforce GTX 960 video card. But then again, I may not, since I want to reduce my power usage and have more PCIe lanes available for storage use.

I spent a couple of hours putting this system together, doing a pretty careful job with the cable management. When I had it ready to turn on for the first time (without putting the case sides on, which is always bad luck), I was rewarded with the CPU and case fans spinning, but no visible POST or video output at all. Luckily, the ASRock motherboard has a built-in LED diagnostic display, which was showing a code 55 error. Looking this up in the motherboard manual, I discovered that this was a memory-related issue. I removed two of the 16GB DDR4 RAM modules, and powered it back up, and this time I got a POST.

Going into the UEFI BIOS setup, I discovered that my ASRock Z170 Extreme 7+ motherboard had the initial 1.4 BIOS, while the latest version was 1.7. One of the fixes listed for version 1.7 is “improve DRAM compatibility”. I was able to flash the BIOS to 1.7 using the Instant Flash utility in the UEFI BIOS setup, and then I was able to use all four 16GB DDR4 RAM modules.

Next I created a RAID 1 array with my two 512GB Samsung 850 Pro SSDs, using the Intel RAID controller that is built-in to the Z170 chipset. I made sure the Intel 750 was not installed yet, and then I used an old USB optical drive to install Windows 10 Professional on to the RAID 1 array. Windows 10 Professional installed default drivers for the dual Intel 1GB NICs, so I was able to get on the internet and download and install all of the latest Windows 10 64-bit drivers for this motherboard from the ASRock web site. Then I used Windows and Microsoft Update to get Windows 10 fully patched.

Windows 10 recognized the Intel 750 using the default Microsoft NVMe drivers. I will benchmark using those drivers, and then compare the results to the native Intel NVMe drivers. So far, I have benchmarked the new system using CPU-Z and Geekbench 3.3.2. The basic information and scores for the new system is shown in Figures 4. 5, and 6 below:


Figure 4: CPU-Z CPU Tab for Z170 Core i7-6700K System



Figure 5: CPU-Z Bench Tab for Z170 Core i7-6700K System



Figure 6: Geekbench 3.3.2 Scores for Z170 Core i7-6700K System


Keep in mind, that beyond enabling XMP 2.1, I have not overclocked the new system yet. The new system is about 10-20% faster than the old system, from a CPU and memory perspective, depending on which benchmark you choose. In some respects, this is disappointing, but the real advantage of the new system is having twice the RAM, and a lot more potential I/O bandwidth with the Z170 Express chipset. With Windows 10 Professional, I have Hyper-V support (and the Core i7-6700K supports VT-x and VT-d), so I can run more VMs simultaneously. I also have two Intel 1GB NICS, which I plan to use together with NIC teaming in Windows 10.

I plan on getting at least one of the upcoming 512GB Samsung 950 Pro M.2 NVMe cards (and this motherboard has three Ultra M.2 slots) when they are released in October/November, so I will have plenty of disk space and I/O performance for the VMs.

SystemCPU-Z Single ThreadCPU-Z Multi-ThreadGeekbench Single-Core
Core i7-3770K157359203680
Core i7-6700K171168154404