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)!
Glenn Berry's SQL Server Performance 
Hi Glenn,
The link to the spreadsheet points to the .sql file, not to the excel-sheet.
Thanks, I got that fixed now.
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
Pingback: Something for the Weekend – SQL Server Links 01/07/11
Looks like the link for the spreadsheet is pointing to the T-SQL script. Thanks for all of the great scripts that you provide.
Thanks for letting me know. That is fixed now.
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 ) ;
Thanks for the great scripts. Is there a link to the 2005 versions? It looks like both links go to the 2008 versions.
Pingback: Wednesday Weekly #sqlserver Links for 2011-27 | sqlmashup
Have you considered putting this in Google Code or GitHub? Would make it easier to keep up with changes.
Pingback: August 2011 SQL Server 2008 Instance Level Diagnostic Queries | Glenn Berry's SQL Server Performance