SQL Server - index usage/utilization stats

Goal:
Find out what indexes are being used, if at all, and if the stats on seeks, scans and lookups on the indexes:
index-seek (preferred)
index-scan (worst)
index-lookup

Solution:

Run this scrip against your database

SELECT

o.name AS object_name,

i.name AS NameOfIndex,

i.type_desc as IsItClustered,

u.user_seeks as IndexSeeks, u.user_scans as IndexScans,

u.user_lookups as IndexLookups, u.user_updates as HowManyUpdates,

o.type

FROM

sys.indexes i

JOIN

sys.objects o ON i.object_id = o.object_id

LEFT JOIN

sys.dm_db_index_usage_stats u ON i.object_id = u.OBJECT_ID

AND i.index_id = u.index_id

AND u.database_id = DB_ID()

WHERE

o.type IN ('U', 'V') AND

i.name IS NOT NULL

ORDER BY

o.name, i.name

Print | posted @ Tuesday, April 17, 2012 7:50 AM

Comments on this entry:

Gravatar # re: SQL Server - index usage/utilization stats
by Dave at 5/11/2012 12:57 PM

Very handy. Thanks!
Gravatar # wholesale beads
by http://www.8seasons.com at 8/16/2013 5:18 AM

I like what you guys are up also. Such intelligent work and reporting! Keep up the excellent works guys I have incorporated you guys to my blogroll. I think it'll improve the value of my site :).
Gravatar # re: SQL Server - index usage/utilization stats
by Anvesh at 2/15/2016 8:11 AM



Nice Article !

Really this will help to people of SQL Server Community.
I have also prepared small note on this, Script to find index operational stats.

http://www.dbrnd.com/2015/10/sql-server-script-to-find-index-operational-stats/
Post A Comment
Title:
Name:
Email:
Comment:
Verification: