July 2011 Version of SQL Server 2008 Diagnostic Queries

Since July is only one day away, its time for the updated version of my SQL Server 2008 Diagnostic Information Queries. There is also an updated version of the results spreadsheet, with an additional tab for the latest new query in the set.

This new query lets you see whether SQL Server has any Memory Grants Pending according to the SQL Server: Memory Manager performance monitor counter. If you see any value above zero for any sustained time at all, that is a very strong indicator of internal memory pressure.

These queries will all work on SQL Server 2008 and SQL Server 2008 R2. Most of the queries will also work on SQL Server 2005, but there is a separate set of queries that I have for SQL Server 2005, which I am not updating very often, since SQL Server 2005 went out of Mainstream Support back in April of 2011.

One surprise I have in store quite soon is that I will have two new versions of this script that are split into instance level queries, and database level queries, with even more detail about each area. I am adding quite a few new queries for each new script, so they should be even more useful (at least I hope so)!

About these ads
This entry was posted in Computer Hardware, SQL Server 2008, SQL Server 2008 R2 and tagged . Bookmark the permalink.

11 Responses to July 2011 Version of SQL Server 2008 Diagnostic Queries

  1. Rob says:

    Hi Glenn,

    The link to the spreadsheet points to the .sql file, not to the excel-sheet.

  2. Jack Vamvas says:

    Great script. Coincidence you mention memory grants pending , I was working on a problem yesterday – with a high pecentage of RESOURCE_SEMAPHORE wait times. Turned out it was some very large queries with sorting\hashing , all running at the same time

  3. Pingback: Something for the Weekend – SQL Server Links 01/07/11

  4. Keith Tate says:

    Looks like the link for the spreadsheet is pointing to the T-SQL script. Thanks for all of the great scripts that you provide.

  5. 7gartner says:

    Glenn, great script. I am finding this useful to use every day.

    I would like to modify the code so I don’t have to make as many edits. For example, the sections that grab perfmon counters are dependent on the instance name. How about a change like this (maybe there is a more elegant approach than mine, but you get the point).

    –!–
    – Page Life Expectancy (PLE) value for default instance
    SELECT cntr_value AS [Page Life Expectancy]
    FROM sys.dm_os_performance_counters
    WHERE ( CASE WHEN SERVERPROPERTY(‘InstanceName’) IS NULL — Default Instance
    THEN N’SQLServer:Buffer Manager’
    ELSE N’MSSQL$’ – Named Instance
    + CAST(SERVERPROPERTY(N’InstanceName’) AS NVARCHAR(50))
    +N’:Buffer Manager’
    END ) = [object_name]
    AND counter_name = N’Page life expectancy’
    OPTION ( RECOMPILE ) ;

  6. Lee says:

    Thanks for the great scripts. Is there a link to the 2005 versions? It looks like both links go to the 2008 versions.

  7. Pingback: Wednesday Weekly #sqlserver Links for 2011-27 | sqlmashup

  8. MattK says:

    Have you considered putting this in Google Code or GitHub? Would make it easier to keep up with changes.

  9. Pingback: August 2011 SQL Server 2008 Instance Level Diagnostic Queries | Glenn Berry's SQL Server Performance

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 )

Google+ photo

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

Connecting to %s