In this post, I provide one SQL script to find different operational stats of Indexes.
This script is very useful for DBA because sometime it requires to find that total how many time indexes is updated.
Using this script you can find the total number of INSERT, UPDATE and DELETE stats for indexes.
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
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.