In researching how to tune up indexing, I was always switching back and forth between different screens and resultsets. There's two main sets of information that you need:
- For a particular table, how much are all the indexes being used?
- What is the structure of all the indexes on a table?
Having these two pieces of information at the same time can be really useful.
Here's a quick script that I put together to do that:
-- Get index usage
if object_id('tempdb..#IndexUsage') is not null drop table tempdb..#IndexUsage
if object_id('tempdb..#IndexStructure') is not null drop table tempdb..#IndexStructure
Declare @TableName varchar(100) = 'Users'
SELECT i.name AS IndexName
,ius.user_seeks AS Seeks
,ius.user_scans AS Scans
,ius.user_lookups AS Lookups
INTO #IndexUsage
FROM sys.indexes i
INNER JOIN sys.dm_db_index_usage_stats ius ON ius.index_id = i.index_id
AND ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN (
SELECT sch.name
,sch.schema_id
,o.OBJECT_ID
,o.create_date
FROM sys.schemas sch
INNER JOIN sys.objects o ON o.schema_id = sch.schema_id
) s ON s.OBJECT_ID = i.OBJECT_ID
LEFT JOIN (
SELECT OBJECT_ID
,index_id
,SUM(used_page_count) AS usedpages
,SUM(CASE
WHEN (index_id < 2)
THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END) AS pages
FROM sys.dm_db_partition_stats
GROUP BY object_id
,index_id
) AS ps ON i.object_id = ps.object_id
AND i.index_id = ps.index_id
WHERE ius.database_id = DB_ID()
AND OBJECT_NAME(i.OBJECT_ID) = @TableName
ORDER BY i.name
-- Get index structure with included fields
SELECT
OBJECT_NAME(i.[object_id]) TableName ,
i.[name] IndexName ,
c.[name] ColumnName ,
ic.is_included_column ,
i.index_id ,
i.type_desc ,
i.is_unique ,
i.data_space_id ,
i.ignore_dup_key ,
i.is_primary_key ,
i.is_unique_constraint
INTO #IndexStructure
FROM
sys.indexes i
JOIN sys.index_columns ic ON ic.object_id = i.object_id and i.index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
Where
OBJECT_NAME(i.[object_id]) = @TableName
ORDER BY
tableName ,
ic.index_id ,
ic.index_column_id
-- Combine into one output
;With
IndexStructure as
(Select Distinct
IndexName
, type_desc = case when type_desc = 'nonclustered' then 'no' else 'yes' end
, is_unique = case When is_unique = 0 then 'no' else 'yes' end
, is_primary_key = case When is_primary_key = 0 then 'no' else 'yes' end
From #IndexStructure
)
,IndexedFields as
(select IndexName, IndexedFields = STRING_AGG (CONVERT(NVARCHAR(max),ColumnName), ', ')
from #IndexStructure Where is_included_column = 0 Group by IndexName)
,IncludedFields as
(select IndexName, IncludedFields = STRING_AGG (CONVERT(NVARCHAR(max),ColumnName), ', ')
from #IndexStructure Where is_included_column = 1 Group by IndexName)
Select
IndexStructure.IndexName
,IndexUsage.Seeks
,IndexUsage.Scans
,IndexUsage.Lookups
,IsClustered = type_desc
,IsUnique = is_unique
,IsPrimaryKey = is_primary_key
,IndexedFields.IndexedFields
,IncludedFields = IsNull(IncludedFields.IncludedFields, '-')
From IndexStructure
JOIN #IndexUsage IndexUsage on IndexUsage.IndexName = IndexStructure .IndexName
Join IndexedFields on IndexStructure.IndexName = IndexedFields.IndexName
Left Join IncludedFields on IndexStructure.IndexName = IncludedFields.IndexName
-- Where Seeks < 500 and Scans < 500
Order by
Seeks asc
