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
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