SQL Server Diagnostic Information Queries for February 2014

I improved the file-level latency query this month and made some other small improvements to a few other queries. Rather than having a separate blog post for each version, I’ll 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.

There is an initial query in each version that tries to confirm that you are using the correct version of the script for your version of SQL Server. Please let me know what you think of these queries, and whether you have any suggestions for improvements. Thanks!

If you want to see me demonstrate and explain how to interpret these queries, you should consider attending the SQLintersection Conference in April.

SQL SP14 300x250 v2 Spring SQLintersection Conference in Orlando

Spring SQLintersection Conference in Orlando

This year the Spring 2014 SQLintersection Conference will be in Orlando, FL at the JW Marriott Grand Lakes from April 12th to April 17, (including the pre-con and post-con workshops). There will be an informal “Ask the Experts” area with many of the speakers, including two Microsoft Premier Field Engineers, – David Pless and Tim Chapman. The idea is that you can bring your own problems and network with other attendees and speakers in smaller, more relaxed setting, where you can actually spend some quality time with the experts.

There are a number of separate, practical tracks on each day for the conference, including things like Performance, Query Tuning, SQL Server 2014, BI Development, Platform Development, Statistics, Workload Analysis, Database Development, Programming, Troubleshooting, Hadoop & Riak, Indexing, and Core Administration. There will also be a number of sessions from Microsoft on In-Memory OLTP in SQL Server 2014, aka “Hekaton”, which I think will be very interesting for a lot of people.

Who are the speakers?

Honestly, there is really a great list of speakers for this conference, with nothing but top-notch content. You can see the complete list of sessions here. Here is the complete speaker list:

  • Aaron Bertrand, Sr. Consultant, SQL Sentry, Inc. (blog | twitter)
  • Andrew J. Kelly, Mentor, SolidQ (blog | twitter)
  • Bob Beauchemin, Development Partner, SQLskills.com (blog | twitter)
  • Bob Ward, Principal Architect Escalation Engineer, Microsoft (blog | twitter)
  • Brent Ozar, Brent Ozar Unlimited (blog | twitter)
  • David Pless, Senior Premier Field Engineer, Microsoft (blog | twitter)
  • Erin Stellato, Principal Consultant, SQLskills.com (blog | twitter)
  • Glenn Berry, Principal Consultant, SQLskills.com (blog | twitter)
  • Grant Fritchey, Product Evangelist, Red Gate Software (blog | twitter)
  • Jeremiah Peschka, Brent Ozar Unlimited (blog | twitter)
  • Jonathan Kehayias, Principal Consultant, SQLskills.com (blog | twitter)
  • Jos de Bruin, Senior Program Manager, Microsoft
  • Kendra Little, Managing Director, Brent Ozar Unlimited (blog | twitter)
  • Kevin Kline, Director of Engineering Services, SQL Sentry (blog | twitter)
  • Kimberly L. Tripp, President/Founder, SQLskills.com (blog | twitter)
  • Mike Zwilling, Principal Architect, Microsoft
  • Paul S. Randal, CEO / Owner, SQLskills.com (blog | twitter)
  • Steve Jones, Editor, SQLServerCentral.com (blog | twitter)
  • Tim Chapman, Premier Field Engineer, Microsoft (blog | twitter)

There are also five pre-con workshops and two post-con workshops:

  • What’s New in SQL Server 2014 with Bob Beauchemin on Saturday, April 12
  • 50 Things All SQL Server Developers Need To Know! with Kevin Kline and Aaron Bertrand on Saturday, April 12
  • Practical Disaster Recovery Techniques with Paul S. Randal on Sunday, April 13
  • Queries Gone Wild: Real-world Solutions  with Kimberly L. Tripp on Sunday, April 13
  • Developer’s Guide to SQL Server Operations with Jeremiah Peschka and Kendra Little on Sunday, April 13
  • Make SQL Server Apps Go Faster with Brent Ozar, Jeremiah Peschka, and Kendra Little on Thursday, April 17
  • Windows Azure SQL Database from A to Z with Bob Beauchemin on Thursday, April 17

You also get to pick and choose from any sessions from the five co-located conferences on SharePoint, ASP.NET, Azure, Visual Studio, and AngleBrackets while you are at this conference.  Early Bird Bonus: If you register for a Show Package (which is the full conference plus one pre or post-conference session), by February 24, you will receive your choice of a Microsoft Surface 2, Microsoft XBox One, or a $300.00 gift card.

Finally, if you use the discount code of SQLskills when you register, you will get $50.00 off of your registration.

SQLintersection