I’ve stumbled across many index interrogation queries over the years and each had something unique to offer. I thought I would share the one I use. It functions at the database level so you’d need to run it once per database. I successfully used it to drop unused indexes and defragment several production servers this past quarter. It gives information on seeks, scans, lookups, updates, index type, last table stats update, page count, fragmentation, reserved and used KB, which indexes are hard coded into any procs, indexed and included columns, among other goodies. Thanks to Michelle for a decent chunk of it!
The script is quite in-depth and should be run with caution on production servers.
DECLARE @objectID INT; SET @objectID = OBJECT_ID(QUOTENAME(DB_NAME(DB_ID()))); DECLARE @Objects TABLE (HardCodedIndexes VARCHAR(1000)); INSERT INTO @Objects SELECT name FROM [sys].[objects] WHERE [object_id] IN (SELECT [object_id] FROM [sys].[sql_modules] WHERE [definition] LIKE '%INDEX = %'); IF NOT EXISTS (SELECT TOP 1 0 FROM @Objects) BEGIN; INSERT INTO @Objects SELECT 'None'; END; WITH indexCTE AS (SELECT sps.name AS 'partition_scheme_name', spf.name AS 'partition_function_name', sps.data_space_id AS 'data_space_id' FROM [sys].[partition_schemes] AS sps INNER JOIN [sys].[partition_functions] AS spf ON sps.function_id = spf.function_id), DMV_OperationalStats AS (SELECT index_id, [object_id], range_scan_count, singleton_lookup_count, -- number of times the index has been used to fetch a single row forwarded_fetch_count, -- for a heap, shows the number of forward pointers have been used to resolve a query lob_fetch_in_pages, -- Quantifies the #of large object (varchar(max),varbinary(max),text etc) retrieved using this index lob_fetch_in_bytes, row_lock_count, -- number of row locks that have been requested against this index row_lock_wait_count, -- number of times a process has waited on a row lock against this index row_lock_wait_in_ms, -- amount of time spent waiting on a row lock against this index page_latch_wait_count, -- number of waits and time waited on the physical page of the object to have the latch removed page_io_latch_wait_count FROM [sys].[dm_db_index_operational_stats](DB_ID(),NULL, NULL, NULL)), DMV_UsageStats AS (SELECT QUOTENAME(DB_NAME(u.database_id)) AS 'database_name', i.name AS 'index_name', COALESCE(u.user_seeks,NULL) AS 'user_seeks', COALESCE(u.last_user_seek, NULL) AS 'last_user_seek', COALESCE(u.last_system_seek, NULL) AS 'last_system_seek', COALESCE(u.user_scans, NULL) AS 'user_scans', COALESCE(u.last_user_scan, NULL) AS 'last_user_scan', COALESCE(u.last_system_scan, NULL) AS 'last_system_scan', COALESCE(u.user_lookups, NULL) AS 'user_lookups', COALESCE(u.last_user_lookup, NULL) AS 'last_user_lookup', COALESCE(u.last_system_lookup, NULL) AS 'last_system_lookup', COALESCE(u.user_updates, NULL) AS 'user_updates' FROM [sys].[indexes] AS i INNER JOIN [sys].[objects] AS o ON i.object_id = o.object_id LEFT OUTER JOIN [sys].[dm_db_index_usage_stats] AS u ON i.object_id = u.object_id AND i.index_id = u.index_id WHERE u.database_id = DB_ID() AND o.[type] <> 'S'), Fragmentation AS (SELECT QUOTENAME(DB_NAME(DB_ID())) AS 'databaseName', ps.[object_id] AS 'objectID', ps.index_id AS 'indexID', ps.partition_number AS 'partitionNumber', SUM(ps.avg_fragmentation_in_percent) AS 'fragmentation', SUM(ps.page_count) AS 'page_count', os.range_scan_count, GETDATE() AS 'scanDate' FROM [sys].[dm_db_index_physical_stats](DB_ID(), NULL, NULL , NULL, 'LIMITED') AS ps INNER JOIN [sys].[dm_db_index_operational_stats](DB_ID(), NULL, NULL , NULL) AS os ON ps.database_id = os.database_id AND ps.[OBJECT_ID] = os.[OBJECT_ID] AND ps.index_id = os.index_id AND ps.partition_number = os.partition_number GROUP BY QUOTENAME(DB_NAME(ps.database_id)), ps.[OBJECT_ID], ps.index_id, ps.partition_number, os.range_scan_count), SpaceUsed AS (SELECT i.[object_id], i.index_id, a.total_pages * 8 AS 'Reserved(KB)', a.used_pages * 8 AS 'Used(KB)', o.create_date AS 'TableCreateDate', o.modify_date AS 'TableLastModified' FROM [sys].[indexes] AS i INNER JOIN [sys].[partitions] AS p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id INNER JOIN [sys].[allocation_units] AS a ON p.partition_id = a.container_id LEFT OUTER JOIN [sys].[objects] AS o ON o.[object_id] = i.[object_id] WHERE o.type = 'U'), HardCodedIndexes AS (SELECT COALESCE(HardCodedIndexes + ',' + HardCodedIndexes, HardCodedIndexes) AS 'HardCodedIndexes' FROM @Objects) SELECT dus.database_name, st.name AS 'Table', ISNULL(ix.name, '') AS 'Index', dus.user_seeks AS 'User Seeks', dus.user_scans AS 'User Scans', dus.user_lookups AS 'User Lookups', dus.user_updates AS 'User Updates', CAST(CASE WHEN ix.index_id = 1 THEN 'clustered' WHEN ix.index_id = 0 THEN 'heap' ELSE 'nonclustered' END + CASE WHEN ix.ignore_dup_key <> 0 THEN ', ignore duplicate keys' ELSE '' END + CASE WHEN ix.is_unique <> 0 THEN ', unique' ELSE '' END + CASE WHEN ix.is_primary_key <> 0 THEN ', primary key' ELSE '' END AS VARCHAR(210)) AS 'Index Type', STATS_DATE(ix.[object_id], ix.index_id) AS 'Last Stats Update', fr.page_count, fr.fragmentation, dos.range_scan_count, SUM(ROWS) AS 'row_count', su.[Reserved(KB)], su.[Used(KB)], h.HardCodedIndexes AS 'Indexes Hard-Coded', ISNULL(REPLACE( REPLACE( REPLACE( ( SELECT c.name AS 'columnName' FROM [sys].[index_columns] AS sic INNER JOIN [sys].[columns] AS c ON c.column_id = sic.column_id AND c.[object_id] = sic.[object_id] WHERE sic.[object_id] = ix.[object_id] AND sic.index_id = ix.index_id AND is_included_column = 0 ORDER BY sic.index_column_id FOR XML Raw) , '"/><row columnName="', ', ') , '<row columnName="', '') , '"/>', ''), '') AS 'indexed_columns', ISNULL(REPLACE(REPLACE(REPLACE( ( SELECT c.name AS 'columnName' FROM [sys].[index_columns] AS sic INNER JOIN [sys].[columns] AS c ON c.column_id = sic.column_id AND c.[object_id] = sic.[object_id] WHERE sic.[object_id] = ix.[object_id] AND sic.index_id = ix.index_id AND is_included_column = 1 ORDER BY sic.index_column_id FOR XML Raw) , '"/><row columnName="', ', ') , '<row columnName="', '') , '"/>', ''), '') AS 'included_columns', fr.partitionNumber, dus.last_user_seek, dus.last_system_seek, dus.last_user_scan, dus.last_system_scan, dus.last_user_lookup, dus.last_system_lookup, ix.[object_id], ix.index_id, COUNT(partition_number) AS 'partition_count', ISNULL(cte.partition_scheme_name,'') AS 'partition_scheme_name', su.TableCreateDate, su.TableLastModified, dos.singleton_lookup_count, dos.forwarded_fetch_count, dos.lob_fetch_in_pages, dos.lob_fetch_in_bytes, dos.row_lock_count, dos.row_lock_wait_count, dos.row_lock_wait_in_ms, dos.page_latch_wait_count, dos.page_io_latch_wait_count FROM [sys].[indexes] AS ix INNER JOIN [sys].[partitions] AS sp ON ix.OBJECT_ID = sp.OBJECT_ID AND ix.index_id = sp.index_id INNER JOIN [sys].[tables] AS st ON ix.OBJECT_ID = st.OBJECT_ID LEFT OUTER JOIN indexCTE AS cte ON ix.data_space_id = cte.data_space_id LEFT OUTER JOIN DMV_OperationalStats AS dos ON dos.index_id = ix.index_id AND dos.object_id = ix.object_id INNER JOIN DMV_UsageStats AS dus ON ix.name = dus.index_name LEFT OUTER JOIN Fragmentation AS fr ON fr.objectID = IX.OBJECT_ID AND fr.databaseName = dus.database_name AND fr.indexID = ix.index_id LEFT OUTER JOIN SpaceUsed AS su ON fr.objectid = su.object_id AND su.index_id = fr.indexID CROSS JOIN HardCodedIndexes AS h WHERE ix.[object_id] = ISNULL(@objectID, ix.[object_id]) GROUP BY st.name, ISNULL(ix.name, ''), ix.[object_id], ix.index_id, CAST(CASE WHEN ix.index_id = 1 THEN 'clustered' WHEN ix.index_id = 0 THEN 'heap' ELSE 'nonclustered' END + CASE WHEN ix.ignore_dup_key <> 0 THEN ', ignore duplicate keys' ELSE '' END + CASE WHEN ix.is_unique <> 0 THEN ', unique' ELSE '' END + CASE WHEN ix.is_primary_key <> 0 THEN ', primary key' ELSE '' END AS VARCHAR(210)), ISNULL(cte.partition_scheme_name, ''), ISNULL(cte.partition_function_name, ''), dus.database_name, dus.user_seeks, dus.last_user_seek, dus.last_system_seek, dus.user_scans, dus.last_user_scan, dus.last_system_scan, dus.user_lookups, dus.last_user_lookup, dus.last_system_lookup, dus.user_updates, dos.range_scan_count, dos.singleton_lookup_count, dos.forwarded_fetch_count, dos.lob_fetch_in_pages, dos.lob_fetch_in_bytes, dos.row_lock_count, dos.row_lock_wait_count, dos.row_lock_wait_in_ms, dos.page_latch_wait_count, dos.page_io_latch_wait_count, fr.fragmentation, fr.partitionNumber, fr.page_count, su.[Reserved(KB)], su.[Used(KB)], su.TableCreateDate, su.TableLastModified, h.HardCodedIndexes ORDER BY dus.user_seeks ASC, dus.user_scans ASC, dus.user_lookups ASC, CAST(CASE WHEN ix.index_id = 1 THEN 'clustered' WHEN ix.index_id = 0 THEN 'heap' ELSE 'nonclustered' END + CASE WHEN ix.ignore_dup_key <> 0 THEN ', ignore duplicate keys' ELSE '' END + CASE WHEN ix.is_unique <> 0 THEN ', unique' ELSE '' END + CASE WHEN ix.is_primary_key <> 0 THEN ', primary key' ELSE '' END AS VARCHAR(210)) DESC, fr.page_count DESC OPTION (MAXDOP 1);