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
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
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.
Next Steps
- To get a better understanding of your index usage, add these dynamic management views/functions to your list of tools
- Look at the additional options and data that are returned from these DMVs
- Take a look at these other tips about indexing
