20 Aug 2019 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:
-- Find Unused Index Script -- Original Author: Pinal Dave (C) 2011 SELECT o.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_ius INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = i.OBJECT_ID INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID INNER JOIN sys.schemas s ON o.schema_id = s.schema_id INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID INNER JOIN ( SELECT 'CREATE INDEX '+IndexName+' ON ' + TableName + ' ('+KeyCols+' ) '+CASE WHEN IncludeCols IS NOT NULL THEN ' INCLUDE ('+IncludeCols+' )' ELSE '' END AS Statement, IndexName FROM ( SELECT '[' + Sch.name + '].[' + Tab.[name] + ']' AS TableName, Ind.Name AS IndexName, SUBSTRING(( SELECT ', ' + AC.name FROM sys.[tables] AS T INNER 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_id AND IC.is_included_column = 0 ORDER BY IC.key_ordinal FOR XML PATH('') ), 2, 8000) AS KeyCols, SUBSTRING(( SELECT ', ' + AC.name FROM sys.[tables] AS T INNER 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_id AND IC.is_included_column = 1 ORDER BY IC.key_ordinal FOR XML PATH('') ), 2, 8000) AS IncludeCols FROM sys.[indexes] Ind INNER 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.Indexname WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1 AND dm_ius.database_id = DB_ID() AND i.type_desc = 'nonclustered' AND i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND i.is_unique = 0 ORDER 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.