SQL Server Index Usage Statistics, Physical Statistics & User Statistics

sqlserver2014

In a recent blog entry by Pinal Dave, he takes a look at three SQL Server reports which are available: Index Usage Statistics, Index Physical Statistics, and User Statistics.  He shows the simple query for the Index Physical report:

SELECT t4.name AS [schema_name]
,       
       t3.name AS table_name
,       
       t2.name AS index_name
,      
       t1.OBJECT_ID
,      
       t1.index_id
,      
       t1.partition_number
,      
       t1.index_type_desc
,      
       t1.index_depth
,      
       t1.avg_fragmentation_in_percent
,      
       t1.fragment_count
,      
       t1.avg_fragment_size_in_pages
,      
       t1.page_count

FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') t1
      
INNER JOIN sys.objects t3 ON (t1.OBJECT_ID = t3.OBJECT_ID)

      INNER JOIN sys.schemas t4 ON (t3.schema_id = t4.schema_id)

      INNER JOIN sys.indexes t2 ON (t1.OBJECT_ID = t2.OBJECT_ID 
      AND  t1.index_id = t2.index_id) 

WHERE index_type_desc <> 'HEAP'

ORDER BY t4.name, t3.name, t2.name, partition_number

He also helps us better understand the reports and what they mean so you can provide better support.

Advertisements

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