Skip to content

Instantly share code, notes, and snippets.

@jeremybeavon

jeremybeavon/all_index_usages.sql

Created May 1, 2018 01:17
Show Gist options
  • Save jeremybeavon/d5eb5bee3ccfb24399722aed47789b79 to your computer and use it in GitHub Desktop.
Save jeremybeavon/d5eb5bee3ccfb24399722aed47789b79 to your computer and use it in GitHub Desktop.
Download ZIP
Find the usages of all indexes in a database
Raw
all_index_usages.sql
select OBJECT_NAME(ius.object_id) as "Object Name",
i.name as "Index Name",
user_seeks,
user_scans,
user_lookups,
user_updates
from sys.dm_db_index_usage_stats as ius
inner join sys.indexes as i on i.object_id = ius.object_id
and i.index_id = ius.index_id
where OBJECTPROPERTY(ius.object_id,'IsUserTable') = 1
and ius.database_id = db_id()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment