In this blog, we learn about the index usage information (SYS.DM_DB_INDEX_USAGE_STATS) and analyze the index usage data (USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES) and will identify the unused index.
Indexes help us in covering and enhancing the performance of a large number of queries that try to retrieve data from the database table and SQL Server allows us to create up to 999 Non-clustered indexes and one Clustered indexes per each table and there is a Meta data table SYS.INDEXESwhich contains one row per each index in table or view.
SQL server stores the index usage information in the system table and it provides SYS.DM_DB_INDEX_USAGE_STATSview to access the index usage statistics information so with help of index usage information we can identify the un-used index and will remove them easily.
there are few important columns (USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES) in SYS.DM_DB_INDEX_USAGE_STATSthat help us to identify the bad or un-used index in table which are not being used in any query but in each update /insert operations , index data is getting updated.
· USER_SEEKS - it stores the number of times the index is used to find a specific row.
· USER_SCANS - it stores the number of times the leaf pages of the index are scanned.
· USER_LOOKUPS - it stores the number of times a Clustered index is used by the Non-clustered index to fetch the full row .
· USER_UPDATES - it stores the number of times the index data is modified
Here is a SQL query to pull the above information about the index.
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
AND S.database_id=DB_ID()
AND I.name='OrderOrderStatusDate_IDX'
Output:
As per output, the sql index “OrderOrderStatusDate_IDX” is not being used but index data is modified by 28046 times.
