March 2011 Version of SQL Server 2005 and 2008 Diagnostic Queries

Another month, and another new version of my SQL Server Diagnostic Information queries for both SQL Server 2005 and SQL Server 2008/2008 R2. I have uploaded the scripts for each version to DropBox (see the links below).

There are two new queries in this version:

-- Get total buffer usage by database for current instance
SELECT DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4 -- system databases
AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC;

-- Tells you how much memory (in the buffer pool) is being used by each database on the instance
-- Find missing index warnings for cached plans in the current database
-- Note: This query could take some time on a busy instance
SELECT TOP(25) OBJECT_NAME(objectid) AS [ObjectName], 
               query_plan, cp.objtype, cp.usecounts
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE CAST(query_plan AS NVARCHAR(MAX)) LIKE N'%MissingIndex%'
AND dbid = DB_ID()
ORDER BY cp.usecounts DESC;

-- Helps you connect missing indexes to specific stored procedures
-- This can help you decide whether to add them or not

 

Here is the March 2011 SQL Server 2005 Diagnostic Information Query Script, and here is the March 2011 SQL Server 2008 Diagnostic Information Query Script. The second script also works on SQL Server 2008 R2.

You will need VIEW SERVER STATE permission to run many of these queries. You should read the comments and instructions for each query, and then run them one at a time (instead of running the entire batch at once). Then, you can paste the results into the matching results spreadsheet for each version of the query. These spreadsheets have labeled tabs that are in the same order as the queries.

Here is the March 2011 results spreadsheet for SQL Server 2005, and here is the March 2011 results spreadsheet for SQL Server 2008.

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

7 Responses to March 2011 Version of SQL Server 2005 and 2008 Diagnostic Queries

  1. David says:

    Thank you for your regular updates to these diagnostic queries! I love these queries!

  2. Pingback: Something for the Weekend – SQL Server Links 11/03/11 | John Sansom - SQL Server DBA in the UK

  3. Kyle says:

    Thanks so much for providing these.

  4. Pingback: Getting More Hardware Information from SQL Server Denali DMV Queries | Glenn Berry's SQL Server Performance

  5. Pingback: May 2011 SQL Server 2008 Diagnostic Information Queries | Glenn Berry's SQL Server Performance

  6. Pingback: SQL Server 2005 Diagnostic Information Queries (May 2012) | 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