Index usage statistics in SQL Server

How to check index usage statistics in sql server ?

There are two ways to check the index usage statistics.
  • Canned Reports
  • DMV Query

Canned Report to get index usage

Login to SQL Server 
Right Click on the database on which you table and index present.
Now navigate to standard report and click on the Index usage statistics report as shown below.

After click on Index usage statistics your report will load.

DMV query to get index usage statistics.

Select
(dense_rank() over (order by s.name,t.name))%2 as l1
,       (dense_rank() over (order by s.name,t.name,i.name))%2 as l2
, s.name as [schema_name]
,       t.name as [table_name]
,       i.name as [index_name]
,       i.type_desc
,       iu.object_id 
,       iu.index_id 
,       iu.user_seeks  as seek_user
,       iu.user_scans as scan_user
from sys.dm_db_index_usage_stats iu
inner join sys.indexes i on  ((iu.index_id = i.index_id) and (iu.object_id = i.object_id))
inner join sys.tables t on ( i.object_id = t.object_id )
inner join sys.schemas s on (s.schema_id = t.schema_id)
where iu.database_id = db_id() and i.type <> 0
order by s.name,t.name,i.name


No comments:

Post a Comment

Write a python program to find factorial of a number ?

Factorial  When we recursively multiply number incrementing from 1 to a given number then its called factorial of that number. We use the no...