How to get index usage information in SQL Server
Problem
Databases have two primary storage needs; data pages and
index pages. Understanding and viewing the actual data in your tables is
pretty straightforward by running some sample queries to get an idea of what
columns are being used as well as what type of data is actually being
stored. On the flip side of this, it is often difficult to know exactly
what indexes are being used and how they are being used. So how can you
get a better understanding of how your indexes are being used and what
operations are occurring (inserts, updates, deletes, selects)?
Solution
In SQL Server, many new dynamic management objects have been
created that allow you insight into a lot of data that was not accessible or just
difficult to get in previous versions of SQL Server. One new function and
one new view that provide data about index usage are sys.dm_db_index_operational_stats and sys.dm_db_index_usage_stats.
sys.dm_db_index_operational_stats
This function gives you information about insert, update and
delete operations that occur on a particular index. In addition, this
view also offers data about locking, latching and access methods. There
are several columns that are returned from this view, but these are some of the
more interesting columns:
- leaf_insert_count
- total count of leaf level inserts
- leaf_delete_count
- total count of leaf level deletes
- leaf_update_count
- total count of leaf level updates
Here is a sample query that provides some of the key columns
that are helpful to determine insert, update and delete operations.
SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
A.LEAF_INSERT_COUNT,
A.LEAF_UPDATE_COUNT,
A.LEAF_DELETE_COUNT
FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A
INNER JOIN SYS.INDEXES AS I
ON I.[OBJECT_ID] = A.[OBJECT_ID]
AND I.INDEX_ID = A.INDEX_ID
WHERE OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1
I.[NAME] AS [INDEX NAME],
A.LEAF_INSERT_COUNT,
A.LEAF_UPDATE_COUNT,
A.LEAF_DELETE_COUNT
FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A
INNER JOIN SYS.INDEXES AS I
ON I.[OBJECT_ID] = A.[OBJECT_ID]
AND I.INDEX_ID = A.INDEX_ID
WHERE OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1
Here is the output from the above query. From this
function we can get an idea of how many inserts, updates and delete operations
were performed on each table and index.
sys.dm_db_index_usage_stats
This view gives you information about overall access methods
to your indexes. There are several columns that are returned from this
DMV, but here are some helpful columns about index usage:
- user_seeks
- number of index seeks
- user_scans-
number of index scans
- user_lookups
- number of index lookups
- user_updates
- number of insert, update or delete operations
SELECT OBJECT_NAME(S.[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 S
INNER JOIN SYS.INDEXES AS I
ON I.[OBJECT_ID] = S.[OBJECT_ID]
AND I.INDEX_ID = S.INDEX_ID
WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
I.[NAME] AS [INDEX NAME],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
INNER JOIN SYS.INDEXES AS I
ON I.[OBJECT_ID] = S.[OBJECT_ID]
AND I.INDEX_ID = S.INDEX_ID
WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
Here is the output from the above query. From this
view we can get an idea of how many seeks, scans, lookups and overall updates
(insert, update and delete) occurred.
Notes
The values for these counters get reset each time you
restart SQL Server. In addition, the values for the data returned by
sys.dm_db_index_operational_stats exists only as long as the metadata cache
object that represents the heap or index is available.
Indexes
That Do Appear in the Usage Statistics Table, But Are Never Used
DECLARE
@MinimumPageCount int
SET
@MinimumPageCount = 500
SELECT Databases.name AS [Database],
Indexes.name AS [Index],
Objects.Name AS [Table],
PhysicalStats.page_count
as [Page Count],
CONVERT(decimal(18,2), PhysicalStats.page_count
* 8 / 1024.0) AS [Total Index
Size (MB)],
CONVERT(decimal(18,2), PhysicalStats.avg_fragmentation_in_percent) AS [Fragmentation
(%)],
ParititionStats.row_count
AS [Row Count],
CONVERT(decimal(18,2), (PhysicalStats.page_count
* 8.0 * 1024) / ParititionStats.row_count) AS [Index Size/Row (Bytes)]
FROM sys.dm_db_index_usage_stats UsageStats
INNER JOIN sys.indexes Indexes
ON Indexes.index_id = UsageStats.index_id
AND
Indexes.object_id =
UsageStats.object_id
INNER JOIN sys.objects Objects
ON Objects.object_id =
UsageStats.object_id
INNER JOIN SYS.databases Databases
ON Databases.database_id
= UsageStats.database_id
INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS PhysicalStats
ON
PhysicalStats.index_id =
UsageStats.Index_id
and
PhysicalStats.object_id
= UsageStats.object_id
INNER JOIN SYS.dm_db_partition_stats
ParititionStats
ON
ParititionStats.index_id = UsageStats.index_id
and
ParititionStats.object_id
= UsageStats.object_id
WHERE UsageStats.user_scans = 0
AND
UsageStats.user_seeks =
0
AND
UsageStats.user_lookups = 0
AND
PhysicalStats.page_count > @MinimumPageCount -- ignore indexes
with less than 500 pages of memory
AND Indexes.type_desc != 'CLUSTERED' --
Exclude primary keys, which should not be removed
ORDER BY [Page Count] DESC
