Here is the latest version of my Diagnostic Information Queries for SQL Server 2008 and SQL Server 2008 R2. These are very useful for determining the configuration, health, and performance of a SQL Server instance and individual database.

I have added a couple of new queries, and added some more interpretation information (as comments) to the queries. The queries start out at the instance level, and then they switch to the database level (so make sure you are pointing at the database you are concerned with). Most of these queries require VIEW SERVER STATE permission.

-- Things to look at:-- How many databases are on the instance?-- What recovery models are they using?-- What is the log reuse wait description?-- How full are the transaction logs ?-- What compatibility level are they on?

-- *** Locking Waits ***-- LCK_M_IX Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock-- LCK_M_IU Occurs when a task is waiting to acquire an Intent Update (IU) lock-- LCK_M_S Occurs when a task is waiting to acquire a Shared lock

-- *** I/O Related Waits ***-- ASYNC_IO_COMPLETION Occurs when a task is waiting for I/Os to finish-- IO_COMPLETION Occurs while waiting for I/O operations to complete. -- This wait type generally represents non-data page I/Os. Data page I/O completion waits appear -- as PAGEIOLATCH_* waits-- PAGEIOLATCH_SH Occurs when a task is waiting on a latch for a buffer that is in an I/O request. -- The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.-- PAGEIOLATCH_EX Occurs when a task is waiting on a latch for a buffer that is in an I/O request. -- The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem.-- WRITELOG Occurs while waiting for a log flush to complete. -- Common operations that cause log flushes are checkpoints and transaction commits.-- PAGELATCH_EX Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. -- The latch request is in Exclusive mode.-- BACKUPIO Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data

-- *** CPU Related Waits ***-- SOS_SCHEDULER_YIELD Occurs when a task voluntarily yields the scheduler for other tasks to execute. -- During this wait the task is waiting for its quantum to be renewed.

-- THREADPOOL Occurs when a task is waiting for a worker to run on. -- This can indicate that the maximum worker setting is too low, or that batch executions are taking -- unusually long, thus reducing the number of workers available to satisfy other batches.-- CX_PACKET Occurs when trying to synchronize the query processor exchange iterator -- You may consider lowering the degree of parallelism if contention on this wait type becomes a problem

-- Gives you the text and size of single-use ad-hoc queries that waste space in the plan cache-- Enabling 'optimize for ad hoc workloads' for the instance can help (SQL Server 2008 and 2008 R2 only)-- Enabling forced parameterization for the database can help, but test first!

-- Individual File Sizes and space available for current databaseSELECT name AS [File Name] , physical_name AS [Physical Name], size/128.0 AS [Total Size in MB],size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB], [file_id]FROM sys.database_files;

-- Look at how large and how full the files are and where they are located-- Make sure the transaction log is not full!!

-- This helps you find the most expensive cached stored procedures from a read I/O perspective-- You should look at this if you see signs of I/O pressure or of memory pressure-- Top Cached SPs By Total Logical Writes (SQL 2008). -- Logical writes relate to both memory and disk I/O pressure SELECT TOP(25) p.name AS [SP Name], qs.total_logical_writes AS [TotalLogicalWrites], qs.total_logical_writes/qs.execution_count AS [AvgLogicalWrites], qs.execution_count,ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.cached_timeFROM sys.procedures AS pINNER JOIN sys.dm_exec_procedure_stats AS qsON p.[object_id] = qs.[object_id]WHERE qs.database_id = DB_ID()ORDER BY qs.total_logical_writes DESC;

-- This helps you find the most expensive cached stored procedures from a write I/O perspective-- You should look at this if you see signs of I/O pressure or of memory pressure

-- Look at last user seek time, number of user seeks to help determine source and importance-- SQL Server is overly eager to add included columns, so beware-- Do not just blindly add indexes that show up from this query!!!

-- Tells you what tables and indexes are using the most memory in the buffer cache

-- Get Table names, row counts, and compression status for clustered index or heapSELECT OBJECT_NAME(object_id) AS [ObjectName], SUM(Rows) AS [RowCount], data_compression_desc AS [CompressionType]FROM sys.partitions WHERE index_id < 2 --ignore the partitions from the non-clustered index if anyAND OBJECT_NAME(object_id) NOT LIKE 'sys%'AND OBJECT_NAME(object_id) NOT LIKE 'queue_%' AND OBJECT_NAME(object_id) NOT LIKE 'filestream_tombstone%' GROUP BY object_id, data_compression_descORDER BY SUM(Rows) DESC;

-- Gives you an idea of table sizes, and possible data compression opportunities