This article is half-done without your Comment! *** Please share your thoughts via Comment ***
As I am preparing important scripts for SQL Server DBA, so here I am also sharing one more vital script for finding the unused indexes of SQL Server Database.
The management and maintenance of database index is a day to day exercise for a Database Administrator. The wrong or unused index can create performance issues for a frequently used table.
At every insert and update, the data of an index are also changing, and it requires some IO operations. Better to find the unused index and delete it.
I also added “DropStatement” column in script for dropping unused Indexes.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
SELECT OBJECT_NAME(i.OBJECT_ID) AS ObjectName ,i.name AS UnusedIndexName ,8 * SUM(au.used_pages) AS IndexSizeInKB ,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 columnstore index' WHEN i.type=6 THEN 'Nonclustered columnstore index' WHEN i.type=7 THEN 'Nonclustered hash index.' END index_type ,'DROP INDEX ' + i.name + ' ON ' + OBJECT_NAME(i.OBJECT_ID) AS DropStatement FROM sys.indexes AS i LEFT JOIN sys.dm_db_index_usage_stats AS dius ON dius.OBJECT_ID = i.OBJECT_ID AND i.index_id = dius.index_id AND dius.database_id = DB_ID() INNER JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id INNER JOIN sys.allocation_units AS au ON au.container_id = p.partition_id WHERE OBJECTPROPERTY(i.OBJECT_ID, 'IsIndexable') = 1 AND OBJECTPROPERTY(i.OBJECT_ID, 'IsIndexed') = 1 AND dius.index_id IS NULL OR (dius.user_updates > 0 AND dius.user_seeks = 0 AND dius.user_scans = 0 AND dius.user_lookups = 0) GROUP BY OBJECT_NAME(i.OBJECT_ID), i.name, i.type ORDER BY OBJECT_NAME(i.OBJECT_ID) |
Other SQL Server Index related DBA Scripts:
SQL Server: Script to find Index Average Fragmentation in Percentage
