SQL Server 2008 Diagnostic Information Queries (August 2012)

Here is the August 2012 version of my SQL Server 2008 Diagnostic Information Queries, with some minor tweaks and improvements to a number of the existing queries, and one new query. To go with it is an updated, blank 2008 results spreadsheet, where the tabs in the spreadsheet are labeled and in the same order as the queries. The idea is that you can run these queries one by one, click on the top left square of the results grid, and then right-click and select “Copy with Headers” to select and copy the results of the query and then paste them into the matching tab of the results spreadsheet.

These queries will work on both SQL Server 2008 and SQL Server 2008 R2 (although some of them will only work on SQL Server 2008 R2 SP1 or later). This is indicated in the instructions for each query.

This entry was posted in SQL Server 2008, SQL Server 2008 R2 and tagged , . Bookmark the permalink.

8 Responses to SQL Server 2008 Diagnostic Information Queries (August 2012)

  1. What would be considered a ‘HIGH’ VLF Count – eg ‘High VLF counts can affect write performance .’


  2. Pingback: Something for the Weekend - SQL Server Links 10/08/12

  3. Jeff Bennett says:

    In my environment, all SQL instances are on virtual servers, and this seems to really cause an issue if the ad-hoc query plans are using a lot of memory. One of the most useful queries I have is your query that get thes Memory Clerk usage numbers , and I use it very regularly to see if CACHESTORE_SQLCP is high. We have been making sure that the instance is optimized for ad-hoc processing, and we also create a job to regularly flush this cachestore. Do you see this or hear about this practice a lot? It really seems to help us to keep this cachestore on the small side, and I never read anything recommending an automated solution (job) to keep it that way.

    • Glenn Berry says:


      Yes, that is a pretty common practice that we recommend. I have actually added that recommendation to the SQL Server 2012 Diagnostic Information script, and I will add it to the 2008 version also.

      I use this text: “Running DBCC FREESYSTEMCACHE (‘SQL Plans’) periodically may be required to better control this.”

  4. Michael says:

    What are your thoughts to adding i.is_disabled = 0 to Query 45 for the possible bad NC indexes to exclude disabled indexes?

  5. Adam Tappis says:

    Hi Glenn, great set of queries. I quite like the idea of pulling the data into excel, but hate the thought of having to copy paste each result set individualy. If you stored each query in a seperate .sql file then the spreadsheet could be set up to connect to SQL Server and populate the tabs automatically. For anyone who wants a single script to run directly a simple batch file could concatenate all the individual SQL queries into 1 script. Just a thought about making the excel portion at little bit more user friendly.

  6. Venkata says:

    Hi Glenn, Wonderful work. Thanks for all the efforts. This is great

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s