Dr. DMV Queries From Fall 2010 SQLConnections Conference

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