Yes, another index usage query. I adapted this a tiny bit to add a time element to it. DMVs will flush out any information when the services are restarted. I omitted system usage information to gear it more toward user created objects. Also, added the users statistics together to get a better Idea of total usage. It gives the user a good starting point to see utilization of current indexing. Conversely, Microsoft provides a “Missing indexes” DMV named sys.dm_db_missing_index_details. Don’t have blind faith in the suggestions. They are based on specific activity since last restart.
Simple query to identify Index Usage. Play around with it and make it your own.
SET NOCOUNT ON
GO
/*
Adapted from: http://www.mssqltips.com/sqlservertutorial/256/discovering-unused-indexes/
*/
DECLARE @last_startup_time AS DATETIME
SELECT @last_startup_time = [last_startup_time] FROM [master].[sys].[dm_server_services]
SELECT DISTINCT
RTRIM(DB_NAME()) [databases]
,QUOTENAME(OBJECT_SCHEMA_NAME(i.[object_id])) +'.'+QUOTENAME(Object_name(i.[object_id]))[tablename]
,i.[name] AS [index]
,s.index_id
,i.type_desc
,(S.[user_seeks] + S.[user_scans] + S.[user_lookups]) [total_usage]
,S.[user_seeks]
,S.[user_scans]
,S.[user_lookups]
,S.[user_updates]
,CONVERT(VARCHAR(20),@last_startup_time,22)[last_startup_time]
,DATEDIFF(day,@last_startup_time, GETDATE()) [up_time_days]
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
RIGHT 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 -- Exclude system tables( mostly)
AND S.database_id = DB_ID() -- Current Database
--AND i.[object_id] in (,OBJECT_ID('dbo.mytable1'),OBJECT_ID('dbo.sometable'),OBJECT_ID('dbo.anothertable') ) -- Review for specific objects
ORDER BY 2, 4
GO
Tiny URL for this post:
Comments are closed.