I added several new queries this month to all five versions of the script. I also spent quite a bit of time getting all five versions back in synch as much as possible, as far as my comments and other minor differences that had cropped up over the last few months.
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 query script, and the one below on the right is the matching blank results spreadsheet.
SQL Server 2005 Diagnostic Information Queries
SQL Server 2005 Blank Results
SQL Server 2008 Diagnostic Information Queries
SQL Server 2008 Blank Results
SQL Server 2008 R2 Diagnostic Information Queries
SQL Server 2008 R2 Blank Results
SQL Server 2012 Diagnostic Information Queries
SQL Server 2012 Blank Results
SQL Server 2014 Diagnostic Information Queries
SQL Server 2014 Blank Results
The basic idea is that you should run each query in the set, one at a time (after reading the directions). You need to click on the top left square of the results grid in 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. There are also some comments on how to interpret the results after each query.
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.
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.
It is also 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 want to understand how to better run and interpret these queries, you should consider listening to my latest Pluralsight course, which is SQL Server 2014 DMV Diagnostic Queries – Part 1. This course is short and to the point (only 67 minutes), and I think you will enjoy it!
Please let me know what you think of these queries, and whether you have any suggestions for improvements. Thanks!
7 thoughts on “SQL Server Diagnostic Information Queries for June 2014”
Hi Glenn,
Thanks for sharing these very informative queries! It’s very handy to have something at hand to get so much information at once.
I’ve found a small typo/error in the script for SQL 2008 R2. On line 704 you call the column [pages_in_bytes] from [dm_os_memory_objects]. This column doesn’t exist and must be changed to [page_size_in_bytes]. I’ve executed it on a SQL2008 R2 SP2 (10.50.4000) instance.
Best regards
Thanks for the heads up. I have fixed that in the 2008 R2 version of the queries. Thanks!
Hi Glenn,
For the CPU utilization history for the last 256 minute, Is the value for SQL Server utilization MAX or AVG and if i need to get the max value is there a way ?.
Please let me know
Thanks
The ring buffers DMV CPU query gets the actual CPU utilization by the SQL Server process in one minute increments. Looking at that output over time will give you a good idea of your average CPU utilization and the normal range of values at different times of the day. Each value that it stores is the actual value when the measurement was taken that minute.
If you really want to know the maximum value for CPU utilization by SQL Server, you would need to run PerfMon session to a file, tracking the CPU utilization by the SQL Server process in small enough increments so that you don’t miss any peak values. Honestly, I am not really sure why you want to do this though.