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.
Process:
-
- 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
Decision:
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.
Script:
/****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.