SQL Server gives the option of creating indexes to fetch data quickly from the database tables. More the number of relevant indexes faster is the data retrieval. However the downside is storage space they occupy and the index maintenance overhead.
After your SQL Server has been used for a while I would suggest you to find any unused indexes and make a decision depending on your environment.
Here is the process I followed:
Things to consider:
- Our SQL Servers have scheduled monthly windows patching cycle and hence are restarted once a month.
- There are few indexes which gets used once a month when the month-end reporting queries run.
- Run the Unused Indexes script below to get all the list of indexes which are not used for the last 30 days.
- This will give the details of indexes in all the databases of an instance. The columns returned are DBName, TableName, Index_Name, Row_Count, IndexSize(MB), index_Type
- I saved the results in a spreadsheet.
- I waited for the next windows patch cycle and a day before the server restart, i had executed the same script again.
- Compared both the results and got a list indexes which are not being used but are part of index maintenance plan.
If you just need all the unused indexes, run the below script without the date filter in the WHERE clause
We made a decision based on the indexes that were occupying more storage space and are not being used for a month. We had also setup a discussion with the development teams for a final confirmation about dropping the indexes.
/****Find Duplicate Indexes ***/ --Create Temp Table CREATE TABLE #UnusedIndexes (DBName nvarchar(200), ObjectName nvarchar(200), Index_Name nvarchar(200), Row_Count int, indexsize int, Index_Type nvarchar(50)) --Insert values INSERT INTO #UnusedIndexes EXEC sp_msforeachdb 'USE [?]; SELECT ''?'' AS DBName, object_name(i.object_id) AS ObjectName, i.name AS [Unused Index], MAX(p.rows) Rows, 8 * SUM(a.used_pages) AS ''Indexsize(KB)'', CASE WHEN i.type = 0 THEN''Heap'' WHEN i.type= 1 THEN ''clustered'' WHEN i.type=2 THEN ''Non-clustered'' WHEN i.type=3 THEN ''XML'' WHEN i.type=4 THEN ''Spatial'' WHEN i.type=5 THEN ''Clustered xVelocity memory optimized columnstore index'' WHEN i.type=6 THEN ''Nonclustered columnstore index'' END index_type FROM sys.indexes i LEFT JOIN sys.dm_db_index_usage_stats s ON s.object_id = i.object_id AND i.index_id = s.index_id AND s.database_id = db_id() JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id JOIN sys.allocation_units AS a ON a.container_id = p.partition_id WHERE objectproperty(i.object_id, ''IsIndexable'') = 1 AND objectproperty(i.object_id, ''IsIndexed'') = 1 AND objectproperty(i.object_id, ''IsUserTable'') = 1 AND s.index_id is null -- dm_db_index_usage_stats has no reference to this index OR (s.user_updates > 0 AND s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0)-- index is being updated, but not used by seeks/scans/lookups --Below line can be removed if you need all unused indexes AND ( ISNULL(s.last_user_seek, ''19000101'') < datediff(month, -1, getdate()) AND ISNULL(s.last_user_scan, ''19000101'') < datediff(month, -1, getdate()) AND ISNULL(s.last_user_lookup, ''19000101'') < datediff(month, -1, getdate()) ) GROUP BY object_name(i.object_id) ,i.name,i.type ORDER BY object_name(i.object_id) ASC' --Select from temp table SELECT DBName, Index_Type, SUM(indexsize) FROM #UnusedIndexes GROUP BY DBName, Index_Type ORDER BY 1 --Drop temp table DROP TABLE #UnusedIndexes
Leave a comment if it has helped you or if you need help with it.
Note: As with any scripts you find online, test it in your test environments and confirm it work for you before deploying in production.