SQL Server Diagnostic Information Queries for December 2016

This month, I have added a separate set of queries for SQL Server vNext. Right now, they are almost identical to the SQL Server 2016 queries, but I will make changes to surface new functionality in SQL Server vNext over time. I have also added support for new functionality in SQL Server 2016 Service Pack 1.

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 vNext, 2016, 2014 and 2012:

SQL Server vNext Diagnostic Information Queries (December 2016)

SQL Server nNext Blank Results Spreadsheet

SQL Server 2016 Diagnostic Information Queries (December 2016)

SQL Server 2016 Blank Results Spreadsheet

SQL Server 2014 Diagnostic Information Queries (December 2016)

SQL Server 2014 Blank Results Spreadsheet

SQL Server 2012 Diagnostic Information Queries (December 2016)

SQL Server 2012 Blank Results Spreadsheet

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 a while ago, and so far, I have not heard any complaints. I did update these queries slightly in January 2016 though.

SQL Server 2008 R2 Diagnostic Information Queries (CY 2016)

SQL Server 2008 R2 Blank Results

SQL Server 2008 Diagnostic Information Queries (CY 2016)

SQL Server 2008 Blank Results

SQL Server 2005 Diagnostic Information Queries (CY 2016)

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 quite a few comments and links in the script on how to interpret the results after each query.

After running 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 related 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!

Intel Xeon E7 Processor Generational Performance Comparison

Intel has a fairly recent document titled Accelerated Operations for Telecom and Financial Services which is also listed under Accelerate OLTP Database Performance with Intel TSX. It describes the “performance” increases seen with the AsiaInfo ADB from moving from 2.8GHz Intel Xeon E7-4890 v2 (Ivy Bridge-EX), to 2.5GHz Intel Xeon E7-8890 v3 (Haswell-EX), and finally to 2.2GHz Intel Xeon E7-8890 v4 (Broadwell-EX) processors, as shown in Figure 1.

 

Figure 1: Speedup from Successive Processor Generations

 

This workload is described as “AsiaInfo ADB Database OCS k-tpmC”, while the AsiaInfo ADB is described as “a scalable OLTP database that targets high performance and mission critical businesses such as online charge service (OCS) in the telecom industry”, that runs on Linux.

The reason I have performance in quotes above is because what they are really measuring is closer to what I would call capacity or scalability. Their topline result is “Thousands of Transactions per Minute” as measured with these different hardware and storage configurations.

The key point to keep in mind with these types of benchmarks is whether they are actually comparing relatively comparable systems or not. In this case, the systems are quite similar, except for the core counts of the successive processor models (and the DD3 vs. DDR4 memory support). Here are the system components, as listed in the footnotes of the document:

Baseline: Four-sockets, 15-core Intel Xeon E7-4890 v2, 256GB DDR3/1333 DIMM, Intel DC S3700 SATA for OS, (2) 2TB Intel DC P3700 PCIe NVMe for storage, 10GbE Intel X540-AT2 NIC

Next Generation: Four-sockets, 18-core Intel Xeon E7-8890 v3, 256GB DDR4/1600 LVDIMM, Intel DC S3700 SATA for OS, (2) 2TB Intel DC P3700 PCIe NVMe for storage, 10GbE Intel X540-AT2 NIC

New: Four-sockets, 24-core Intel Xeon E7-8890 v4, 256GB DDR4/1600 LVDIMM, Intel DC S3700 SATA for OS, (2) 2TB Intel DC P3700 PCIe NVMe for storage, 10GbE Intel X540-AT2 NIC

The baseline system has a total of 60 physical cores, running at 2.8GHz, using the older Ivy Bridge-EX microarchitecture. The next generation system has a total of 72 physical cores, running at 2.5GHz, using the slightly newer Haswell-EX microarchitecture. Finally, the new system has a total of 96 physical cores, running at 2.2GHz, using the current Broadwell-EX microarchitecture. These differences in core counts, base clock speeds, and microarchitecture make it a little harder to fully understand their benchmark results in a realistic manner.

Table 1 shows some relevant metrics for these three system configurations. The older generation processors have fewer cores, but run at a higher base clock speed. The newer generation processors would be faster than the older generation processors at the same clock speed, but the base clock speed is lower as the core counts have increased with each successive generation flagship processor. The improvements in IPC and single-threaded performance are obscured by lower base clock speeds as the core counts increase, which makes the final score increase less impressive.

 

Processor Base Clock Total System Cores Raw Score Score/Core
Xeon E7-4890 v2 2.8GHz 60 725 12.08
Xeon E7-8890 v3 2.5GHz 72 1021 14.18
Xeon E7-8890 v4 2.2GHz 96 1294 13.48

Table 1: Analysis of ADB Benchmark Results

 

Table 2 shows some metrics from an analysis of some actual and estimated TPC-E benchmark results for those same three system configurations, plus an additional processor choice that I added. The results are pretty similar, which supports the idea that both of these benchmarks are CPU-limited. From a SQL Server 2016 perspective, you are going to be better off from a performance/license cost perspective if you purposely choose a lower core count “frequency-optimized” processor (at the cost of less total system capacity per host).

This is somewhat harder to do with the Intel Xeon E7 v4 family, because of your limited SKU choices. A good processor choice for many workloads would be the 10-core Intel Xeon E7-8891 v4 processor, which has a base clock speed of 2.8GHz and a 60MB L3 cache that is shared by only 10 cores.

If you could spread your workload across two database servers, you would be much better off with two, four-socket servers with the 10-core Xeon E7-8891 v4 rather than one four-socket server with the 24-core Xeon E7-8890 v4. You would have more total system processor capacity, roughly 27% better single-threaded CPU performance, twice the total system memory capacity, and twice the total number of PCIe 3.0 expansion slots. You would also only need 80 SQL Server 2016 Enterprise Edition core licenses rather than 96 core licenses, which would save you about $114K in license costs. That license savings would probably pay for both database servers, depending on their exact configuration.

 

Processor Base Clock Total System Cores Est TPC-E Score Score/Core
Xeon E7-4890 v2 2.8GHz 60 5576.27 92.94
Xeon E7-8890 v3 2.5GHz 72 6964.75 96.73
Xeon E7-8890 v4 2.2GHz 96 9068.00 94.46
Xeon E7-8891 v4 2.8GHz 40 4808.79 120.22

Table 2: Analysis of Estimated TPC-E Benchmark Results

 

The Intel document also discusses the “performance” increases seen from moving from Intel DC S3700 SATA drives to Intel DC P3700 PCIe NVMe drives. This is going to be primarily influenced by the advantages of being connected directly to the PCIe bus and the lower latency and overhead of the NVMe protocol compared to the older AHCI protocol.

Finally, they talk about the “performance” increases they measured from enabling the Intel Transactional Synchronization Extensions (TSX) instruction set and the Intel AVX 2.0 instruction set on current generation Intel E7-8800 v4 series processors.

SQL Server 2016 already has hardware support for older SSE/AVX instructions as discussed here and here. I really hope that Microsoft decides to add even more support for newer instruction sets (such as TSX) in SQL Server vNext.

 

 

CPU-Z 1.78 is Available

On November 21, CPU-Z 1.78 was released. This is a great tool for getting all the technical details about your processors and checking on their current clock speed.

The main improvement in this version is support for Intel Kaby Lake processors, which are already available in the mobile space. It looks like the desktop version of Kaby Lake will be released at CES in January. Tom’s Hardware did some benchmarking of an early sample of a Core i7-7700K that someone supplied to them, as detailed here.

 

image

Figure 1: CPU-Z 1.78 CPU Tab

 

Recent versions of CPU-Z have added a quick CPU benchmarking function that is very useful for running a brief CPU benchmark that measures single-threaded CPU performance and multi-threaded CPU performance. Each test only takes about 7-8 seconds, and is useful for a number of reasons.

 

image

Figure 2: CPU-Z 1.78 Bench Tab For Intel Core i7-6700K System

 

First, you can get a quick gauge of your single-threaded CPU performance (which equates to the “speed” of the processor), and your multi-threaded CPU performance (which equates to the CPU capacity of the entire system). This is useful for comparing different processors and systems, whether they are physical or virtual. You can measure the performance of a VM versus running bare metal on the host, or you can measure different VM configurations. You can also compare your numbers to the built-in reference processors, or submit your results and compare them to other systems results that are stored online.

Second, you can use the Bench CPU button to briefly stress your processors, and then quickly switch to the main CPU tab while the test is running, to see what happens to your CPU core clock speeds, in order to understand whether you have power management configured correctly to get the performance benefits of Intel Turbo Boost.

SQL Server 2016 Service Pack 1 and SQL Server 2016 RTM CU3

Microsoft has released SQL Server 2016 RTM CU3 (Build 13.0.2186.6), with 31 fixes in the public fix list. They have also released SQL Server 2016 Service Pack 1 (Build 13.0.4001.0).

As they previously did for SQL Server 2014 SP2, Microsoft has managed to include all of the hotfixes from the latest SQL Server 2016 RTM CU3 in SQL Server 2016 SP1 RTM, including the very recent security fixes in Security Update MS16-136, so there is no need to wait for a subsequent SP1 CU to get caught up with the latest fixes from the RTM branch.

The Microsoft SQL Server Release Services Blog has a lot more detail about SQL Server 2016 SP1 here. The Data Platform blog has more detail about SP1 feature improvements here.

Given all of the feature changes and other useful improvements in SQL Server 2016 Service Pack 1 (on top of all of the hotfixes), it is almost a no-brainer to move to SQL Server 2016 Service Pack 1 as soon as you can do your testing, and plan and implement your deployment.

This is also a good time for organizations that are running down-level versions of SQL Server Standard Edition to seriously consider upgrading to SQL Server 2016 Standard Edition (preferably on new hardware, running Windows Server 2016).

Major Changes for SQL Server 2016 Standard Edition

At today’s Connect() event, Microsoft announced some pretty major changes for SQL Server 2016 Standard Edition, which are implemented in SQL Server 2016 Service Pack 1 (which was also released today). Many very useful SQL Server features that were previously only available in Enterprise Edition will now be available in Standard Edition (and also in Web Edition and even Express Edition).

These include Row-level security, Dynamic Data Masking, Change Data Capture(*), Database Snapsnots, Columnstore indexes, Table Partitioning, Data Compression, Multiple Filestream containers, In-Memory OLTP, Always Encrypted, Distributed Partitioned Views, Polybase, and Fine grained Auditing.

There are some scalability limits for some of these features running on Standard or Web/Express Edition. For example, In-Memory OLTP is limited to 1/4 of the Edition buffer pool memory limit. Columnstore is also limited to 1/4 of the Edition buffer pool memory limit. These feature memory limits are in addition to the buffer pool limit for each edition.

In Standard Edition, Columnstore is limited to 2 DOP, and in Web/Express is limited to 1 DOP. Polybase worker compute nodes can be deployed on Standard, Web, and Express Editions, but still requires Enterprise Edition for the head node to scale out with (multiple worker compute nodes).

Keep in mind that you will need SQL Server 2016 Service Pack 1, to get these new features (and many other nice improvements, as detailed here).

The primary goal here is to provide application developers with an easy way to program an application in the same way using all of the application features of SQL Server 2016, regardless of which edition of SQL Server 2016 that the application may eventually be deployed on.

This is going to make it much easier for ISVs to use these features without requiring their customers to use Enterprise Edition. They will be able to simplify their deployment scripts by not having to check the Edition of SQL Server before they run DDL statements to create database objects. This is a very welcome development that should encourage many more organizations to move to SQL Server 2016.

Once you have an application using SQL Server 2016 Standard Edition, you can just do an Edition Upgrade to Enterprise Edition to get even more scalability and performance, taking advantage of the higher license limits in Enterprise Edition. You will also get the intrinsic performance benefits that are present in Enterprise Edition. Microsoft should consider publicizing some of these performance differences between Standard Edition and Enterprise Edition.

Note: Change Data Capture won’t be available in Express Edition, since it does not have SQL Server Agent.

 

 

SQL Server Diagnostic Information Queries for November 2016

This month, I have added several new queries to SQL Server 2016 set, along with adding additional comments and documentation in the SQL Server 2012, 2014 and 2016 sets. I have gotten quite a bit of interest about making a special version of these queries for SQL Database in Microsoft Azure. So, I will be doing that later in November, (this time I really mean it).

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 (November 2016)

SQL Server 2016 Blank Results

SQL Server 2014 Diagnostic Information Queries (November 2016)

SQL Server 2014 Blank Results

SQL Server 2012 Diagnostic Information Queries (November 2016)

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 a while ago, and so far, I have not heard any complaints. I did update these queries slightly in January 2016 though.

SQL Server 2008 R2 Diagnostic Information Queries (CY 2016)

SQL Server 2008 R2 Blank Results

SQL Server 2008 Diagnostic Information Queries (CY 2016)

SQL Server 2008 Blank Results

SQL Server 2005 Diagnostic Information Queries (CY 2016)

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 quite a few comments and links in the script on how to interpret the results after each query.

After running 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 related 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!

SQL Server Management Studio Changes and Servicing

For the SQL Server 2016 release cycle, Microsoft has separated SQL Server Management Studio (SSMS) from the rest of the SQL Server 2016 product. This new standalone version of SSMS (which is completely free) is designed to work with SQL Server 2008 through SQL Server 2016. It may also work with SQL Server 2000 and SQL Server 2005, although Microsoft does not officially support that scenario. Finally, it is designed to work with Azure SQL Database.

The setup program for SSMS is not included with the rest of product on the SQL Server 2016 DVD or .iso file. Instead, there is a link that takes you to the Download SQL Server Management Studio (SSMS) page where you can download the most current version of the SSMS setup program.

If you are trying to install SSMS on a machine that does not have internet access (which is pretty common), then you will need to download the SSMS setup program in advance, and make it available on the server where you want to install it. The SSMS setup program is typically about 800-900MB in size, so this should not be a huge burden for most people.

If you have internet access on a machine where SSMS is installed, it will periodically check for a newer version and will notify you that there is one via a “toast” popup in your system tray. You can also go to the Tools menu in SSMS, and select “Check for Updates…” to force SSMS to check for any updated components that may be available. The latest SSMS release notes are available from the link below:

 

SQL Server Management Studio – Release Notes

 

Microsoft has been pretty reliable about releasing new versions of SSMS every month, which often have many bug fixes and feature improvements. Figure 1 shows an example dialog with details about components that have available updates.

 

image

Figure 1: SSMS Updates are Available Example

 

Figure 2 shows an example dialog where SSMS is up to date.

 

image

Figure 2: SSMS Is Up to Date Example

 

If the machine where SSMS is installed does not have internet connectivity, you will have to be a little more proactive about checking for new versions of SSMS. You can pull up the About dialog for SSMS, as shown in Figure 3, and compare the build number for Microsoft SQL Server Management Studio (in the top row) to the latest available build number shown in the changelog page in the link below, using some other machine that does have internet connectivity.

SQL Server Management Studio – Changelog (SSMS)

 

image

Figure 3: About Microsoft SQL Server Management Studio Dialog

 

The Microsoft SQL Server Release Services Blog usually has a new post when a new version of SSMS is released. The latest post is here.

 

You should also be able to use Windows Server Update Services (WSUS) to check for and obtain SSMS updates, if your organization is using WSUS.

 

 

Special Discount Code for PASS Summit and Precon

On Monday, October 24, 2016, I will be doing an all-day, Pre-Conference session on how to interpret my SQL Server diagnostic information queries. I have done many shorter versions of this session (such as 60 minutes, 75 minutes, or even a half-day) before, but I have always felt a little rushed as I went through the complete set of diagnostic queries, explaining how to interpret the results of each one, and also talking about related background information and guidance that is relevant to each query.

Now, I will have a full day to go into much more detail, without having to hurry to cover everything. I will be using the SQL Server 2016 version of the diagnostic queries, which have even more useful information, including information about many new SQL Server 2016 features. If you are on an older version of SQL Server, most of the queries will still be relevant (depending on how old of a version of SQL Server you are using).

Based on past experience and feedback, Dr. DMV has always been a very popular session that people really seem to enjoy. This all-day, expanded version is going to be really fun and useful, and I hope to see you there!

Here is the abstract for the session:

Dr. DMV: How to Use DMVs to Diagnose Performance Problems

SQL Server 2005 introduced Dynamic Management Views (DMVs) that allow you to see exactly what is happening inside your SQL Server instances and databases with much more detail than ever before. SQL Server 2016 adds even more capability in this area. You can discover your top wait types, most CPU intensive stored procedures, find missing indexes, and identify unused indexes, to name just a few examples. This session (which is applicable to SQL 2005-2016), presents and explains over seventy DMV queries that you can quickly and easily use to detect and diagnose performance issues in your environment. If you have ever been responsible for a mission critical database, you have probably been faced with a high stress, emergency situation where a database issue is causing unacceptable application performance, resulting in angry users and hovering managers and executives. If this hasn’t happened to you yet, thank your lucky stars, but start getting prepared for your time in the hot seat. This session will show you how to use DMV queries to quickly detect and diagnose the problem, starting at the server and instance level, and then progressing down to the database and object level. This session will show you how to properly analyze and interpret the results of every single query in the set, along with lots of information on how to properly configure your instance and databases.

I wanted to share a unique discount code: PRE250GB for $250.00 off of a full, three day Summit registration and a pre-conference session. This is a pretty good deal for this late in the process! This code expires at midnight, Thursday, October 13, 2016. You can register here.

The PASS Summit is always a fun and very useful and educational event. It is a great way to get to know more people in the SQL Server community and to connect with people that you may only know online. I am looking forward to seeing as many of you as possible in Seattle for PASS Summit 2016!

 

 

 

SQL Server Diagnostic Information Queries for October 2016

This month, I have spent quite a bit of time trueing up the queries for SQL Server 2012, 2014 and 2016 sets, along with adding additional comments and documentation in the SQL Server 2012, 2014 and 2016 sets. 

I have gotten quite a bit of interest about making a special version of these queries for SQL Database in Microsoft Azure. So, I will be doing that during October (this time I really mean it).

The best way to learn how to interpret the results of all of these queries is to attend my all-day PASS Summit 2016 Pre-Conference Session on Monday, October 24, 2016.

Dr. DMV: How to Use DMVs to Diagnose Performance Problems

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 2016)

SQL Server 2016 Blank Results

SQL Server 2014 Diagnostic Information Queries (October 2016)

SQL Server 2014 Blank Results

SQL Server 2012 Diagnostic Information Queries (October 2016)

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 a while ago, and so far, I have not heard any complaints. I did update these queries slightly in January 2016 though.

SQL Server 2008 R2 Diagnostic Information Queries (CY 2016)

SQL Server 2008 R2 Blank Results

SQL Server 2008 Diagnostic Information Queries (CY 2016)

SQL Server 2008 Blank Results

SQL Server 2005 Diagnostic Information Queries (CY 2016)

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 quite a few comments and links in the script on how to interpret the results after each query.

After running 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 related 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!

SQL Server Diagnostic Information Queries for September 2016

This month, I have a couple of new queries in the SQL Server 2012, 2014 and 2016 sets, along with additional comments and documentation in the SQL Server 2012, 2014 and 2016 sets.  I have gotten quite a bit of interest about making a special version of these queries for SQL Database in Microsoft Azure. So, I will be doing that during September (this time for sure).

The best way to learn how to interpret the results of all of these queries is to attend my all-day PASS Summit 2016 Pre-Conference Session on Monday, October 24, 2016.

Dr. DMV: How to Use DMVs to Diagnose Performance Problems

 

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 (September 2016)

SQL Server 2016 Blank Results

SQL Server 2014 Diagnostic Information Queries (September 2016)

SQL Server 2014 Blank Results

SQL Server 2012 Diagnostic Information Queries (September 2016)

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 a while ago, and so far, I have not heard any complaints. I did update these queries slightly in January 2016 though.

SQL Server 2008 R2 Diagnostic Information Queries (CY 2016)

SQL Server 2008 R2 Blank Results

SQL Server 2008 Diagnostic Information Queries (CY 2016)

SQL Server 2008 Blank Results

SQL Server 2005 Diagnostic Information Queries (CY 2016)

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 quite a few comments and links in the script on how to interpret the results after each query.

After running 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!