Find Important Information on All Indexes on your Database Server!
In this article, I share queries with comments, to find Important Information on All Indexes on your Database Server.
--How many indexes does each table have, with a list of their names
--If there are too many indexes then further analysis can be done to drop any unused/duplicate indexes.
--index_id <> 0 - To ignore Heaps.
--is_hypothetical = 0 - To ignore hypothetical indexes created by DTA.
SELECT? DB_Name() AS DBName, convert(CHAR(100),object_schema_name(t.object_ID)+'.'
+object_name(t.object_ID)) AS 'The_Table',
sum(CASE WHEN i.object_ID IS NULL THEN 0 ELSE 1 END) AS The_Count,
coalesce(stuff((?
SELECT ', '+i2.name
? ?FROM sys.indexes i2
? ?WHERE t.object_ID = i2.object_ID
? ?ORDER BY i2.name
FOR XML PATH(''), TYPE).value(N'(./text())[1]',N'varchar(8000)'),1,2,''),'') AS Index_List
FROM sys.tables AS t
LEFT OUTER JOIN sys.indexes i
ON t.object_id=i.object_id
AND is_hypothetical = 0 AND i.index_id > 0?
GROUP BY t.Object_ID
ORDER BY The_Count DESC
GO
-- List all tables by name that have no clustered Index, along with their rowcounts.
--If there are big tables with no clustered index then further analysis can be done to add CI to those.
SELECT object_schema_name(sys.tables.object_id)+'.'
+object_name(sys.tables.object_id) AS 'Heaps',
sum(rows) AS row_count
FROM sys.indexes /* see whether the table is a heap */? ? ??
INNER JOIN sys.tables ON sys.tables.object_ID=sys.indexes.object_ID
INNER JOIN sys.partitions ON sys.partitions.object_ID=sys.tables.object_ID?
WHERE sys.indexes.type = 0
GROUP BY object_schema_name(sys.tables.object_id)+'.'
+object_name(sys.tables.object_id)
ORDER BY row_count DESC
GO
--Indexes updated but not read.
--sp_helpdb 'TempDB'
--check last server restart date by chec king tempdb createdate.
--make sure usage stats data is at least 60-90 days before making decisions to drop unused indexes.
SELECT
object_schema_name(i.Object_ID) + '.'+ object_name(i.Object_ID) as TheTable,
i.name AS 'Index',
Coalesce(
? ?(SELECT SUM(s.rows) FROM sys.partitions s WHERE s.object_id = i.object_id
AND s.index_id = i.index_ID
), 0) 'Rows'
FROM sys.indexes i
left outer join sys.dm_db_index_usage_stats s?
ON s.object_id = i.object_id
AND s.index_id = i.index_id
AND s.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
AND i.index_id > 0? --Exclude heaps.
AND i.is_primary_key = 0 --and Exclude primary keys.
AND i.is_unique = 0--and Exclude unique constraints.
AND coalesce(s.user_lookups + s.user_scans + s.user_seeks,0) = 0 --No user reads.
AND coalesce(s.user_updates,0) > 0 --Index is being updated.
ORDER BY 'Rows' DESC
GO
--How much space are the indexes taking?
--If the indexes are too large, you can decide to make them smaller by compressing them or
--remove them altogether if they are unused or reduce unwanted columns from the index.
SELECT?
? object_schema_name(i.Object_ID) + '.'+ object_name(i.Object_ID) AS Thetable,
? coalesce(i.name,'heap IAM')AS 'Index',
? convert(DECIMAL(9,2),(sum(a.total_pages) * 8.00) / (1024.00*1024.00))AS 'Index_GB'
FROM sys.indexes i
INNER JOIN sys.partitions p?
? ON i.object_id = p.object_id
AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a?
? ON p.partition_id = a.container_id
? WHERE objectproperty(i.object_id, 'IsUserTable') = 1
GROUP BY i.object_id, i.index_id, i.name
ORDER BY 'Index_GB' DESC
GO
--List all tables with disabled indexes
/* don't leave these lying around */
SELECT object_schema_name(object_id)+'.'+object_name(object_id) as Has_Disabled_indexes,
? sys.indexes.name as IndexName
? FROM sys.indexes? WHERE is_disabled=1
GO
--List all compressed indexes on the database.
--If there are none, explore possibility for compression of large indexes.
SELECT DISTINCT
SCHEMA_NAME(o.schema_id)? + '.' + OBJECT_NAME(o.object_id) AS TableName,
i.name AS IndexName,
p.data_compression_desc AS CompressionType,
i.type_desc AS StorageType
FROM sys.partitions? p?
INNER JOIN sys.objects o?
ON p.object_id = o.object_id?
JOIN sys.indexes i?
ON p.object_id = i.object_id
AND i.index_id = p.index_id
WHERE p.data_compression > 0?
AND SCHEMA_NAME(o.schema_id) <> 'SYS'?
GO
--List all filtered indexes on the DB.
--If there are none, explore possibility for filtering a few large indexes.
SELECT [SchemaName] = s.[Name]
,[TableName] = t.[Name]
,[IndexName] = i.[Name]
,[IndexType] = i.[type_desc]
,[Filter] = i.filter_definition
FROM sys.indexes i
INNER JOIN sys.tables t ON t.object_id = i.object_id
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE t.type_desc = N'USER_TABLE'
AND i.has_filter = 1
GO
--List all fragmented indexes on the server.
--This can be a long running query on big databases, run on low peak hour and monitor closely.
--Once fragmented indexes are found work on analysing and rebuilding/reorganising required indexes.
SELECT DB_NAME(ips.database_id) AS DatabaseName,
? ? ? ?SCHEMA_NAME(ob.[schema_id]) SchemaNames,
? ? ? ?ob.[name] AS ObjectName,
? ? ? ?ix.[name] AS IndexName,
? ? ? ?ob.type_desc AS ObjectType,
? ? ? ?ix.type_desc AS IndexType,
? ? ? ?-- ips.partition_number AS PartitionNumber,
? ? ? ?ips.page_count AS [PageCount], -- Only Available in DETAILED Mode
? ? ? ?ips.record_count AS [RecordCount],
? ? ? ?ips.avg_fragmentation_in_percent AS AvgFragmentationInPercent
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'SAMPLED') ips -- QuickResult
INNER JOIN sys.indexes ix ON ips.[object_id] = ix.[object_id]?
AND ips.index_id = ix.index_id
INNER JOIN sys.objects ob ON ix.[object_id] = ob.[object_id]
WHERE ob.[type] IN('U','V')
AND ob.is_ms_shipped = 0
AND ix.[type] IN(1,2,3,4)
AND ix.is_disabled = 0
AND ix.is_hypothetical = 0
AND ips.alloc_unit_type_desc = 'IN_ROW_DATA'
AND ips.index_level = 0
AND ips.page_count >= 1000 -- Filter to check only table with over 1000 pages
AND ips.record_count >= 100 -- Filter to check only table with over 1000 rows
AND ips.database_id = DB_ID() -- Filter to check only current database
AND ips.avg_fragmentation_in_percent > 50 -- Filter to check over 50% fragmented indexes
ORDER BY DatabaseName
GO