I would like to monitor index usage for an sql database, in order to find unused indexes and then drop them. How can I monitor index usage most efficiently? And which scripts could be useful?
(I'm aware of this question about identifying unused objects, but this applies only to the current run of the sql server. I would like to monitor index usage over a period of time...)
In Oracle SQL Developer, when you have SQL in the worksheet, there is a button "Explain Plan", you can also hit F10. After you execute Explain plan, it will show in the bottom view of SQL Developer. There is a column "OBJECT_NAME", it will tell you what index is being used.
To determine which missing index groups a particular missing index is part of, you can query the sys. dm_db_missing_index_groups dynamic management view by equijoining it with sys. dm_db_missing_index_details based on the index_handle column.
SQL Server actually keeps up with how often an index is used. It does this behind the scenes, and you can find this information by querying the built in sys. indexes and sys. dm_db_index_usage_stats DMVs.
To see indexes for all tables within a specific schema you can use the STATISTICS table from INFORMATION_SCHEMA: SELECT DISTINCT TABLE_NAME, INDEX_NAME FROM INFORMATION_SCHEMA. STATISTICS WHERE TABLE_SCHEMA = 'your_schema'; Removing the where clause will show you all indexes in all schemas.
This is an interesting question. I've been working on this same question over the past week. There is a system table called dm_db_index_usage_stats that contains usage statistics on indexes.
Indexes That Never Appear in the Usage Statistics Table
However, many indexes never appear in this table at all. The query David Andres posted lists all indexes for this case. I've updated it a little bit to ignore primary keys, which probably shouldn't be deleted, even if they aren't ever used. I also joined on the dm_db_index_physical_stats table to get other information, including Page Count, Total Index Size, and the Fragmentation Percentage. An interesting note is that indexes that are returned by this query don't seem to show up in the SQL Report for Index Usage Statistics.
DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())
SELECT Databases.Name AS [Database],
Objects.NAME AS [Table],
Indexes.NAME AS [Index],
Indexes.INDEX_ID,
PhysicalStats.page_count as [Page Count],
CONVERT(decimal(18,2), PhysicalStats.page_count * 8 / 1024.0) AS [Total Index Size (MB)],
CONVERT(decimal(18,2), PhysicalStats.avg_fragmentation_in_percent) AS [Fragmentation (%)]
FROM SYS.INDEXES Indexes
INNER JOIN SYS.OBJECTS Objects ON Indexes.OBJECT_ID = Objects.OBJECT_ID
LEFT JOIN sys.dm_db_index_physical_stats(@dbid, null, null, null, null) PhysicalStats
on PhysicalStats.object_id = Indexes.object_id and PhysicalStats.index_id = indexes.index_id
INNER JOIN sys.databases Databases
ON Databases.database_id = PhysicalStats.database_id
WHERE OBJECTPROPERTY(Objects.OBJECT_ID,'IsUserTable') = 1
AND Indexes.type = 2 -- Nonclustered indexes
AND Indexes.INDEX_ID NOT IN (
SELECT UsageStats.INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS UsageStats
WHERE UsageStats.OBJECT_ID = Indexes.OBJECT_ID
AND Indexes.INDEX_ID = UsageStats.INDEX_ID
AND DATABASE_ID = @dbid)
ORDER BY PhysicalStats.page_count DESC,
Objects.NAME,
Indexes.INDEX_ID,
Indexes.NAME ASC
Indexes That Do Appear in the Usage Statistics Table, But Are Never Used
There are other indexes that do appear in the dm_db_index_usage_stats table, but which have never been used for user seeks, scans, or lookups. This query will identify indexes that fall into this category. Incidentally, unlike the indexes returned from the other query, the indexes returned in this query can be verified on the SQL Report by Index Usage Statistics.
I added a Minimum Page Count that allows me to initially focus on and remove unused indexes that are taking up a lot of storage.
DECLARE @MinimumPageCount int
SET @MinimumPageCount = 500
SELECT Databases.name AS [Database],
Indexes.name AS [Index],
Objects.Name AS [Table],
PhysicalStats.page_count as [Page Count],
CONVERT(decimal(18,2), PhysicalStats.page_count * 8 / 1024.0) AS [Total Index Size (MB)],
CONVERT(decimal(18,2), PhysicalStats.avg_fragmentation_in_percent) AS [Fragmentation (%)],
ParititionStats.row_count AS [Row Count],
CONVERT(decimal(18,2), (PhysicalStats.page_count * 8.0 * 1024) / ParititionStats.row_count) AS [Index Size/Row (Bytes)]
FROM sys.dm_db_index_usage_stats UsageStats
INNER JOIN sys.indexes Indexes
ON Indexes.index_id = UsageStats.index_id
AND Indexes.object_id = UsageStats.object_id
INNER JOIN sys.objects Objects
ON Objects.object_id = UsageStats.object_id
INNER JOIN SYS.databases Databases
ON Databases.database_id = UsageStats.database_id
INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS PhysicalStats
ON PhysicalStats.index_id = UsageStats.Index_id
and PhysicalStats.object_id = UsageStats.object_id
INNER JOIN SYS.dm_db_partition_stats ParititionStats
ON ParititionStats.index_id = UsageStats.index_id
and ParititionStats.object_id = UsageStats.object_id
WHERE UsageStats.user_scans = 0
AND UsageStats.user_seeks = 0
AND UsageStats.user_lookups = 0
AND PhysicalStats.page_count > @MinimumPageCount -- ignore indexes with less than 500 pages of memory
AND Indexes.type_desc != 'CLUSTERED' -- Exclude primary keys, which should not be removed
ORDER BY [Page Count] DESC
I hope this helps.
Final Thought
Of course, once indexes are identified as candidates for removal, careful consideration should still be employed to make sure it's a good decision to do so.
For more information, see Identifying Unused Indexes in a SQL Server Database
Currently (as of SQL Server 2005 - 2008) the SQL index stats information is only kept in memory and so you have to do some of the work yourself if you would like to have that persisted across restarts and database detaches.
What I usually do, is I create a job that runs every day and takes a snapshot of the information found in the sys.dm_db_index_usage_stats
table, into a custom table that I create for the database in question.
This seems to work pretty well until a future version of SQL which will support persistent index usage stats.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With