This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a SQL script to find different operational stats of Indexes.
This script is handy for DBA because sometimes it requires concluding that total how many time indexes are updated.
Using below script, you can find the total number of INSERT, UPDATE and DELETE stats for indexes.
1 2 3 4 5 6 7 8 9 10 11 |
SELECT OBJECT_NAME(DDIOS.OBJECT_ID) AS ObjectName ,I.[NAME] AS IndexName ,DDIOS.LEAF_INSERT_COUNT AS TotalInsertCount ,DDIOS.LEAF_UPDATE_COUNT AS TotalUpdateCount ,DDIOS.LEAF_DELETE_COUNT AS TotalDeleteCount FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) AS DDIOS INNER JOIN SYS.INDEXES AS I ON I.OBJECT_ID = DDIOS.OBJECT_ID AND I.INDEX_ID = DDIOS.INDEX_ID WHERE OBJECTPROPERTY(DDIOS.OBJECT_ID,'IsUserTable') = 1 |