TSQL tip on how much is the usage of database indexes?
Recently there hasn't been much of SQL Server tasks for me, as most of the time I was involved on designing the solutions for larger installations - a 8 node cluster or VLDB configuration etc. Ok I admit that still it can be classified as SQL Server task but not directly relating to what I used to enjoy the good old ''DBA' days.
Enough blurb, let me come back to the subjective description on why I want to write this post. At the time of designing a SQL installation configuration for a VLDB project one of the DBA asked me how quickly we can have a information on how much usage a database index have on number of indexes that any database can handle. One way this is a 2 fold question that how many indexes are available on that database(s) and another one is the usage of that index on resource-intensive queries.
Here is the script that I have modified as per the usage and obtained from one of the Microsoft resource (thanks):
SELECT DB_NAME(DATABASE_ID) AS DATABASENAME,
OBJECT_NAME(B.OBJECT_ID) AS TABLENAME,
INDEX_NAME = (SELECT NAME
FROM SYS.INDEXES A
WHERE A.OBJECT_ID = B.OBJECT_ID
AND A.INDEX_ID = B.INDEX_ID),
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES
FROM SYS.DM_DB_INDEX_USAGE_STATS B
INNER JOIN SYS.OBJECTS C
ON B.OBJECT_ID = C.OBJECT_ID
WHERE DATABASE_ID = DB_ID(DB_NAME())
AND C.TYPE <> 'S'