This is the SQL Server 2008/2008 R2 version of the DMV Diagnostic Information Queries that I did for my Dr. DMV presentation at SQLConnections in Las Vegas today. Many of these queries will also work on SQL Server 2005, but I also have a SQL Server 2005 version of this script that I will post pretty soon.
You will need to have VIEW SERVER STATE permission to run many of these queries.
-- SQL Server 2008 and R2 Diagnostic Information Queries -- Glenn Berry -- November 2010 -- http://glennberrysqlperformance.spaces.live.com/ -- Twitter: GlennAlanBerry -- Instance level queries ******************************* -- SQL and OS Version information for current instance SELECT@@VERSIONAS[SQL Server and OS Version Info]; -- SQL Server 2008 RTM is considered an "unsupported service pack" -- as of April 13, 2010 -- SQL Server 2008 RTM Builds SQL Server 2008 SP1 Builds SQL Server 2008 SP2 Builds -- Build Description Build Description Build Description -- 1600 Gold RTM -- 1763 RTM CU1 -- 1779 RTM CU2 -- 1787 RTM CU3 --> 2531 SP1 RTM -- 1798 RTM CU4 --> 2710 SP1 CU1 -- 1806 RTM CU5 --> 2714 SP1 CU2 -- 1812 RTM CU6 --> 2723 SP1 CU3 -- 1818 RTM CU7 --> 2734 SP1 CU4 -- 1823 RTM CU8 --> 2746 SP1 CU5 -- 1828 RTM CU9 --> 2757 SP1 CU6 -- 1835 RTM CU10 --> 2766 SP1 CU7 -- RTM Branch Retired --> 2775 SP1 CU8 --> 4000 SP2 RTM -- 2789 SP1 CU9 -- 2799 SP1 CU10 -- SQL Server 2008 R2 Builds -- Build Description -- 10.50.1092 August 2009 CTP2 -- 10.50.1352 November 2009 CTP3 -- 10.50.1450 Release Candidate -- 10.50.1600 RTM -- 10.50.1702 RTM CU1 -- 10.50.1720 RTM CU2 -- 10.50.1734 RTM CU3 -- 10.50.1746 RTM CU4 -- SQL Azure Builds (most DMV queries don't work on SQL Azure) -- Build Description -- 10.25.9200 RTM Service Update 1 -- 10.25.9268 RTM Service Update 2 -- 10.25.9331 RTM Service Update 3 -- 10.25.9386 RTM Service Update 4 -- Hardware information from SQL Server 2008 -- (Cannot distinguish between HT and multi-core) SELECTcpu_count AS[Logical CPU Count],hyperthread_ratio AS[Hyperthread Ratio], cpu_count/hyperthread_ratio AS[Physical CPU Count], physical_memory_in_bytes/1048576 AS[Physical Memory (MB)],sqlserver_start_time FROMsys.dm_os_sys_info; -- Get configuration values for instance SELECTname,value,value_in_use,[description] FROMsys.configurations ORDER BYname; -- Focus on -- backup compression default -- clr enabled (only enable if it is needed) -- lightweight pooling (should be zero) -- max degree of parallelism -- max server memory (MB) (set to an appropriate value) -- optimize for ad hoc workloads (should be 1) -- priority boost (should be zero) -- File Names and Paths for TempDB and all user databases in instance SELECTDB_NAME([database_id])AS[Database Name], [file_id],name,physical_name,type_desc,state_desc, CONVERT(bigint,size/128.0)AS[Total Size in MB] FROMsys.master_files WHERE[database_id] >4 AND[database_id] <>32767 OR[database_id] =2 ORDER BYDB_NAME([database_id]); -- Things to look at: -- Are data files and log files on different drives? -- Is everything on the C: drive? -- Is TempDB on dedicated drives? -- Are there multiple data files? -- Calculates average stalls per read, per write, and per total input/output for each database file. SELECTDB_NAME(fs.database_id)AS[Database Name],mf.physical_name,io_stall_read_ms,num_of_reads, CAST(io_stall_read_ms/(1.0 +num_of_reads)AS NUMERIC(10,1))AS[avg_read_stall_ms],io_stall_write_ms, num_of_writes,CAST(io_stall_write_ms/(1.0+num_of_writes)AS NUMERIC(10,1))AS[avg_write_stall_ms], io_stall_read_ms +io_stall_write_ms AS[io_stalls],num_of_reads +num_of_writes AS[total_io], CAST((io_stall_read_ms +io_stall_write_ms)/(1.0 +num_of_reads +num_of_writes)AS NUMERIC(10,1)) AS[avg_io_stall_ms] FROMsys.dm_io_virtual_file_stats(null,null)ASfs INNER JOINsys.master_filesASmf ONfs.database_id =mf.database_id ANDfs.[file_id] =mf.[file_id] ORDER BYavg_io_stall_ms DESC; -- Helps determine which database files on the entire instance have the most I/O bottlenecks -- Recovery model, log reuse wait description, log file size, log usage size -- and compatibility level for all databases on instance SELECTdb.[name] AS[Database Name],db.recovery_model_desc AS[Recovery Model], db.log_reuse_wait_desc AS[Log Reuse Wait Description], ls.cntr_value AS[Log Size (KB)],lu.cntr_value AS[Log Used (KB)], CAST(CAST(lu.cntr_value AS FLOAT) /CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) *100 AS[Log Used %], db.[compatibility_level] AS[DB Compatibility Level], db.page_verify_option_desc AS[Page Verify Option],db.is_auto_update_stats_on, db.is_auto_update_stats_async_on,db.is_parameterization_forced, db.snapshot_isolation_state_desc,db.is_read_committed_snapshot_on FROMsys.databasesASdb INNER JOINsys.dm_os_performance_countersASlu ONdb.name =lu.instance_name INNER JOINsys.dm_os_performance_countersASls ONdb.name =ls.instance_name WHERElu.counter_name LIKEN'Log File(s) Used Size (KB)%' ANDls.counter_name LIKEN'Log File(s) Size (KB)%' ANDls.cntr_value >0; -- 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? -- Clear Wait Stats -- DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR); -- Isolate top waits for server instance since last restart or statistics clear WITHWaits AS (SELECTwait_type,wait_time_ms /1000. ASwait_time_s, 100. *wait_time_ms /SUM(wait_time_ms)OVER()ASpct, ROW_NUMBER()OVER(ORDER BYwait_time_ms DESC)ASrn FROMsys.dm_os_wait_stats WHEREwait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK' ,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR','LOGMGR_QUEUE','CHECKPOINT_QUEUE' ,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT' ,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT' ,'XE_DISPATCHER_WAIT','XE_DISPATCHER_JOIN','SQLTRACE_INCREMENTAL_FLUSH_SLEEP')) SELECTW1.wait_type, CAST(W1.wait_time_s AS DECIMAL(12,2))ASwait_time_s, CAST(W1.pct AS DECIMAL(12,2))ASpct, CAST(SUM(W2.pct)AS DECIMAL(12,2))ASrunning_pct FROMWaits ASW1 INNER JOINWaits ASW2 ONW2.rn <=W1.rn GROUP BYW1.rn,W1.wait_type,W1.wait_time_s,W1.pct HAVINGSUM(W2.pct) -W1.pct <99;-- percentage threshold -- Common Significant Wait types with BOL explanations -- *** Network Related Waits *** -- ASYNC_NETWORK_IO Occurs on network writes when the task is blocked behind the network -- *** 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 -- Signal Waits for instance SELECTCAST(100.0 *SUM(signal_wait_time_ms) /SUM(wait_time_ms)AS NUMERIC(20,2)) AS[%signal (cpu) waits], CAST(100.0 *SUM(wait_time_ms -signal_wait_time_ms) /SUM(wait_time_ms)AS NUMERIC(20,2)) AS[%resource waits] FROMsys.dm_os_wait_stats; -- Signal Waits above 10-15% is usually a sign of CPU pressure -- Get CPU Utilization History for last 144 minutes (in one minute intervals) -- This version works with SQL Server 2008 and SQL Server 2008 R2 only DECLARE@ts_now bigint = (SELECTcpu_ticks/(cpu_ticks/ms_ticks)FROMsys.dm_os_sys_info); SELECT TOP(144)SQLProcessUtilization AS[SQL Server Process CPU Utilization], SystemIdle AS[System Idle Process], 100 -SystemIdle -SQLProcessUtilization AS[Other Process CPU Utilization], DATEADD(ms, -1 * (@ts_now -[timestamp]),GETDATE())AS[Event Time] FROM( SELECTrecord.value('(./Record/@id)[1]','int')ASrecord_id, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]','int') AS[SystemIdle], record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS[SQLProcessUtilization],[timestamp] FROM( SELECT[timestamp],CONVERT(xml,record)AS[record] FROMsys.dm_os_ring_buffers WHEREring_buffer_type =N'RING_BUFFER_SCHEDULER_MONITOR' ANDrecord LIKEN'%<SystemHealth>%')ASx )ASy ORDER BYrecord_id DESC; -- Good basic information about memory amounts and state SELECTtotal_physical_memory_kb,available_physical_memory_kb, total_page_file_kb,available_page_file_kb, system_memory_state_desc FROMsys.dm_os_sys_memory; -- You want to see "Available physical memory is high" -- SQL Server Process Address space info --(shows whether locked pages is enabled, among other things) SELECTphysical_memory_in_use_kb,locked_page_allocations_kb, page_fault_count,memory_utilization_percentage, available_commit_limit_kb,process_physical_memory_low, process_virtual_memory_low FROMsys.dm_os_process_memory; -- You want to see 0 for process_physical_memory_low -- You want to see 0 for process_virtual_memory_low -- Page Life Expectancy (PLE) value for default instance SELECTcntr_value AS[Page Life Expectancy] FROMsys.dm_os_performance_counters WHEREOBJECT_NAME=N'SQLServer:Buffer Manager'-- Modify this if you have named instances ANDcounter_name =N'Page life expectancy'; -- PLE is a good measurement of memory pressure. -- Higher PLE is better. Below 300 is generally bad. -- Watch the trend, not the absolute value. -- Buffer cache hit ratio for default instance SELECT(a.cntr_value *1.0 /b.cntr_value) *100.0 AS[Buffer Cache Hit Ratio] FROMsys.dm_os_performance_countersASa INNER JOIN (SELECTcntr_value,[OBJECT_NAME],instance_name FROMsys.dm_os_performance_counters WHEREcounter_name =N'Buffer cache hit ratio base' AND[OBJECT_NAME] =N'SQLServer:Buffer Manager')ASb -- Modify this if you have named instances ONa.[OBJECT_NAME] =b.[OBJECT_NAME] ANDa.instance_name =b.instance_name WHEREa.counter_name =N'Buffer cache hit ratio' ANDa.[OBJECT_NAME] =N'SQLServer:Buffer Manager';-- Modify this if you have named instances -- Shows the percentage that SQL Server is finding requested data in memory -- A higher percentage is better than a lower percentage -- Watch the trend, not the absolute value. -- Memory Clerk Usage for instance -- Look for high value for CACHESTORE_SQLCP (Ad-hoc query plans) SELECT TOP(20)[type],[name],SUM(single_pages_kb)AS[SPA Mem, Kb] FROMsys.dm_os_memory_clerks GROUP BY[type],[name] ORDER BYSUM(single_pages_kb)DESC; -- CACHESTORE_SQLCP SQL Plans These are cached SQL statements or batches that aren't in -- stored procedures, functions and triggers -- CACHESTORE_OBJCP Object Plans These are compiled plans for stored procedures, -- functions and triggers -- CACHESTORE_PHDR Algebrizer Trees An algebrizer tree is the parsed SQL text that -- resolves the table and column names -- Find single-use, ad-hoc queries that are bloating the plan cache SELECT TOP(100)[text],cp.size_in_bytes FROMsys.dm_exec_cached_plansAScp CROSS APPLYsys.dm_exec_sql_text(plan_handle) WHEREcp.cacheobjtype =N'Compiled Plan' ANDcp.objtype =N'Adhoc' ANDcp.usecounts =1 ORDER BYcp.size_in_bytes DESC; -- 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! -- Database specific queries ***************************************************************** -- **** Switch to a user database ***** USEYourDatabaseName; GO -- Individual File Sizes and space available for current database SELECTname 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] FROMsys.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!! -- Top Cached SPs By Execution Count (SQL 2008) SELECT TOP(100)p.name AS[SP Name],qs.execution_count, ISNULL(qs.execution_count/DATEDIFF(Second,qs.cached_time,GETDATE()),0)AS[Calls/Second], qs.total_worker_time/qs.execution_count AS[AvgWorkerTime],qs.total_worker_time AS[TotalWorkerTime], qs.total_elapsed_time,qs.total_elapsed_time/qs.execution_count AS[avg_elapsed_time], qs.cached_time FROMsys.proceduresASp INNER JOINsys.dm_exec_procedure_statsASqs ONp.[object_id] =qs.[object_id] WHEREqs.database_id =DB_ID() ORDER BYqs.execution_count DESC; -- Tells you which cached stored procedures are called the most often -- This helps you characterize and baseline your workload -- Top Cached SPs By Avg Elapsed Time (SQL 2008) SELECT TOP(25)p.name AS[SP Name],qs.total_elapsed_time/qs.execution_count AS[avg_elapsed_time], qs.total_elapsed_time,qs.execution_count,ISNULL(qs.execution_count/DATEDIFF(Second,qs.cached_time, GETDATE()),0)AS[Calls/Second],qs.total_worker_time/qs.execution_count AS[AvgWorkerTime], qs.total_worker_time AS[TotalWorkerTime],qs.cached_time FROMsys.proceduresASp INNER JOINsys.dm_exec_procedure_statsASqs ONp.[object_id] =qs.[object_id] WHEREqs.database_id =DB_ID() ORDER BYavg_elapsed_time DESC; -- This helps you find long-running cached stored procedures -- Top Cached SPs By Total Worker time (SQL 2008). Worker time relates to CPU cost SELECT TOP(25)p.name AS[SP Name],qs.total_worker_time AS[TotalWorkerTime], qs.total_worker_time/qs.execution_count AS[AvgWorkerTime],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_time FROMsys.proceduresASp INNER JOINsys.dm_exec_procedure_statsASqs ONp.[object_id] =qs.[object_id] WHEREqs.database_id =DB_ID() ORDER BYqs.total_worker_time DESC; -- This helps you find the most expensive cached stored procedures from a CPU perspective -- You should look at this if you see signs of CPU pressure -- Top Cached SPs By Total Logical Reads (SQL 2008). Logical reads relate to memory pressure SELECT TOP(25)p.name AS[SP Name],qs.total_logical_reads AS[TotalLogicalReads], qs.total_logical_reads/qs.execution_count AS[AvgLogicalReads],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_time FROMsys.proceduresASp INNER JOINsys.dm_exec_procedure_statsASqs ONp.[object_id] =qs.[object_id] WHEREqs.database_id =DB_ID() ORDER BYqs.total_logical_reads DESC; -- This helps you find the most expensive cached stored procedures from a memory perspective -- You should look at this if you see signs of memory pressure -- Top Cached SPs By Total Physical Reads (SQL 2008). Physical reads relate to disk I/O pressure SELECT TOP(25)p.name AS[SP Name],qs.total_physical_reads AS[TotalPhysicalReads], qs.total_physical_reads/qs.execution_count AS[AvgPhysicalReads],qs.execution_count, qs.total_logical_reads,qs.total_elapsed_time,qs.total_elapsed_time/qs.execution_count AS[avg_elapsed_time],qs.cached_time FROMsys.proceduresASp INNER JOINsys.dm_exec_procedure_statsASqs ONp.[object_id] =qs.[object_id] WHEREqs.database_id =DB_ID() ORDER BYqs.total_physical_reads,qs.total_logical_reads DESC; -- 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_time FROMsys.proceduresASp INNER JOINsys.dm_exec_procedure_statsASqs ONp.[object_id] =qs.[object_id] WHEREqs.database_id =DB_ID() ORDER BYqs.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 -- Lists the top statements by average input/output usage for the current database SELECT TOP(50)OBJECT_NAME(qt.objectid)AS[SP Name], (qs.total_logical_reads +qs.total_logical_writes) /qs.execution_count AS[Avg IO], SUBSTRING(qt.[text],qs.statement_start_offset/2, (CASE WHENqs.statement_end_offset = -1 THENLEN(CONVERT(nvarchar(max),qt.[text])) *2 ELSEqs.statement_end_offset END-qs.statement_start_offset)/2)AS[Query Text] FROMsys.dm_exec_query_statsASqs CROSS APPLYsys.dm_exec_sql_text(qs.sql_handle)ASqt WHEREqt.[dbid] =DB_ID() ORDER BY[Avg IO] DESC; -- Helps you find the most expensive statements for I/O by SP -- Possible Bad NC Indexes (writes > reads) SELECTOBJECT_NAME(s.[object_id])AS[Table Name],i.name AS[Index Name],i.index_id, user_updates AS[Total Writes],user_seeks +user_scans +user_lookups AS[Total Reads], user_updates - (user_seeks +user_scans +user_lookups)AS[Difference] FROMsys.dm_db_index_usage_statsASs WITH(NOLOCK) INNER JOINsys.indexesASi WITH(NOLOCK) ONs.[object_id] =i.[object_id] ANDi.index_id =s.index_id WHEREOBJECTPROPERTY(s.[object_id],'IsUserTable') =1 ANDs.database_id =DB_ID() ANDuser_updates > (user_seeks +user_scans +user_lookups) ANDi.index_id >1 ORDER BY[Difference] DESC,[Total Writes] DESC,[Total Reads] ASC; -- Look for indexes with high numbers of writes and zero or very low numbers of reads -- Consider your complete workload -- Investigate further before dropping an index -- Missing Indexes current database by Index Advantage SELECTuser_seeks *avg_total_user_cost * (avg_user_impact *0.01)AS[index_advantage], migs.last_user_seek,mid.[statement] AS[Database.Schema.Table], mid.equality_columns,mid.inequality_columns,mid.included_columns, migs.unique_compiles,migs.user_seeks,migs.avg_total_user_cost,migs.avg_user_impact FROMsys.dm_db_missing_index_group_statsASmigs WITH(NOLOCK) INNER JOINsys.dm_db_missing_index_groupsASmig WITH(NOLOCK) ONmigs.group_handle =mig.index_group_handle INNER JOINsys.dm_db_missing_index_detailsASmid WITH(NOLOCK) ONmig.index_handle =mid.index_handle WHEREmid.database_id =DB_ID()-- Remove this to see for entire instance ORDER BYindex_advantage DESC; -- 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!!! -- Breaks down buffers used by current database by object (table, index) in the buffer cache SELECTOBJECT_NAME(p.[object_id])AS[ObjectName], p.index_id,COUNT(*)/128 AS[Buffer size(MB)], COUNT(*)AS[BufferCount], p.data_compression_desc AS[CompressionType] FROMsys.allocation_unitsASa INNER JOINsys.dm_os_buffer_descriptorsASb ONa.allocation_unit_id =b.allocation_unit_id INNER JOINsys.partitionsASp ONa.container_id =p.hobt_id WHEREb.database_id =CONVERT(int,DB_ID()) ANDp.[object_id] >100 GROUP BYp.[object_id],p.index_id,p.data_compression_desc ORDER BY[BufferCount] DESC; -- 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 heap SELECTOBJECT_NAME(object_id)AS[ObjectName], SUM(Rows)AS[RowCount],data_compression_desc AS[CompressionType] FROMsys.partitions WHEREindex_id <2 --ignore the partitions from the non-clustered index if any ANDOBJECT_NAME(object_id) NOT LIKE'sys%' ANDOBJECT_NAME(object_id) NOT LIKE'queue_%' ANDOBJECT_NAME(object_id) NOT LIKE'filestream_tombstone%' GROUP BYobject_id,data_compression_desc ORDER BYSUM(Rows)DESC; -- Gives you an idea of table sizes, and possible data compression opportunities -- When were Statistics last updated on all indexes? SELECTo.name,i.name AS[Index Name], STATS_DATE(i.[object_id],i.index_id)AS[Statistics Date], s.auto_created,s.no_recompute,s.user_created,st.row_count FROMsys.objectsASo WITH(NOLOCK) INNER JOINsys.indexesASi WITH(NOLOCK) ONo.[object_id] =i.[object_id] INNER JOINsys.statsASs WITH(NOLOCK) ONi.[object_id] =s.[object_id] ANDi.index_id =s.stats_id INNER JOINsys.dm_db_partition_statsASst WITH(NOLOCK) ONo.[object_id] =st.[object_id] ANDi.[index_id] =st.[index_id] WHEREo.[type] ='U' ORDER BYSTATS_DATE(i.[object_id],i.index_id)ASC; -- Helps discover possible problems with out-of-date statistics -- Also gives you an idea which indexes are most active
