22 May 2020 0 minutes to read Contributors
Good index management is key to success. Having too many unused indexes around slows down your system. It is not difficult to find them or defining the criteria for dropping them. Here is how you retrieve them:
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566 -- Find Unused Index Script-- Original Author: Pinal Dave (C) 2011SELECTo.name AS TableName, i.name AS IndexName, dm_ius.user_seeks AS UserSeek, dm_ius.user_scans AS UserScans, dm_ius.user_lookups AS UserLookups, dm_ius.user_updates AS UserUpdates, p.TableRows, dm_ius.last_user_scan, dm_ius.last_user_seek, dm_ius.last_user_update, 'DROP INDEX ' + QUOTENAME(i.name)+ ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement',index_create_script.Statement AS 'Create Index Statement'FROM sys.dm_db_index_usage_stats dm_iusINNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = i.OBJECT_IDINNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_IDINNER JOIN sys.schemas s ON o.schema_id = s.schema_idINNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_IDFROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) pON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_IDINNER JOIN (SELECT 'CREATE INDEX '+IndexName+' ON ' + TableName + ' ('+KeyCols+' ) '+CASE WHEN IncludeCols IS NOT NULL THEN ' INCLUDE ('+IncludeCols+' )' ELSE '' END AS Statement, IndexNameFROM (SELECT'[' + Sch.name + '].[' + Tab.[name] + ']' AS TableName,Ind.Name AS IndexName,SUBSTRING(( SELECT ', ' + AC.nameFROM sys.[tables] AS TINNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]AND I.[index_id] = IC.[index_id]INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]AND IC.[column_id] = AC.[column_id]WHERE Ind.[object_id] = I.[object_id]AND Ind.index_id = I.index_idAND IC.is_included_column = 0ORDER BY IC.key_ordinalFORXML PATH('') ), 2, 8000) AS KeyCols,SUBSTRING(( SELECT ', ' + AC.nameFROM sys.[tables] AS TINNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]AND I.[index_id] = IC.[index_id]INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]AND IC.[column_id] = AC.[column_id]WHERE Ind.[object_id] = I.[object_id]AND Ind.index_id = I.index_idAND IC.is_included_column = 1ORDER BY IC.key_ordinalFORXML PATH('') ), 2, 8000) AS IncludeColsFROM sys.[indexes] IndINNER JOIN sys.[tables] AS Tab ON Tab.[object_id] = Ind.[object_id]INNER JOIN sys.[schemas] AS Sch ON Sch.[schema_id] = Tab.[schema_id]) index_create_script) index_create_script ON i.name = index_create_script.IndexnameWHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1AND dm_ius.database_id = DB_ID()AND i.type_desc = 'nonclustered'AND i.is_primary_key = 0AND i.is_unique_constraint = 0AND i.is_unique = 0ORDER BY last_user_seek, last_user_scan
The WHERE clause contains a filter that only shows the indexes for the current databases and only those indexes that are nonclustered, are non unique, is not a constraint and not a primary key. You can adjust that for your situation.
The result is a list of DROP and CREATE statements that you can copy and execute in your database. The good thing about this is, if you save these results in an Excel file and mark the indexes that you've actually dropped, then you have managed your index maintenance. At some point in time, you want to have the original index back and that's where you can use the CREATE statement.
We especially pay attention to the data in the yellow marked area. For example If the userseek, userscan, userlookup is 0 and your sql server is already running for some time, we could drop those indexes. If the userseek, userscan, userlookup is low (<1000) and userupdates is a magnitude of that you could choose those indexes to be dropped the next time you do some index maintenance. You will always have the CREATE statement in your excel. Save your excel file with a date and time of the maintenance you have done and you will build up a nice history. Excel files older than 6 months can be discarded.
Over the years, SQLTreeo gained precious know-how which we transformed into customer support, managed database services and database solutions. In order to express our knowledge and experiences, we tend to work with anything less than the best-trained staff and that is the key to success and the biggest asset of our company. Thus, all our database management services are controlled by high level skilled and Senior database administrators. We can help you in every aspect of SQL Server.