Today’s blog post is directly inspired by the conversation I had during my Comprehensive Database Performance Health Check. During the consulting engagement, we identified the customers had too many indexes on tables. The goal was to consolidate the indexes and to do the same, we realized that we needed a script which lists all the key columns and included columns of the indexes.
Fortunately, I had done similar tasks before and I was able to quickly help my customers to consolidate the indexes. Meanwhile, here is the Query Listing All the Indexes Key Column with Included Column.
SELECT QUOTENAME(SCHEMA_NAME(t.schema_id)) AS SchemaName, QUOTENAME(t.name) AS TableName, QUOTENAME(i.name) AS IndexName, i.type_desc, i.is_primary_key, i.is_unique, i.is_unique_constraint, STUFF(REPLACE(REPLACE(( SELECT QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END AS [data()] FROM sys.index_columns AS ic INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0 ORDER BY ic.key_ordinal FOR XML PATH ), '<row>', ', '), '</row>', ''), 1, 2, '') AS KeyColumns, STUFF(REPLACE(REPLACE(( SELECT QUOTENAME(c.name) AS [data()] FROM sys.index_columns AS ic INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1 ORDER BY ic.index_column_id FOR XML PATH ), '<row>', ', '), '</row>', ''), 1, 2, '') AS IncludedColumns, u.user_seeks, u.user_scans, u.user_lookups, u.user_updates FROM sys.tables AS t INNER JOIN sys.indexes AS i ON t.object_id = i.object_id LEFT JOIN sys.dm_db_index_usage_stats AS u ON i.object_id = u.object_id AND i.index_id = u.index_id WHERE t.is_ms_shipped = 0 AND i.type <> 0
I have actually received the above script from one of my Performance Tuning Clients only. He had kindly shared the script and suggested that I can use it for my other clients who are struggling with SQL Server Performance.
Here are the additional blog posts which are associated which are related to the same.
- Why is SQL Server Consolidation Better Than Having a Scattered Environment?
- SQL Server – Update Table Statistics in Parallel with FULLSCAN
- SQL SERVER – New Parallel Operation Cannot be Started Due to Too Many Parallel Operations Executing at this Time
I hope you have found this blog post which lists key columns and included columns of the indexes useful.
Reference: Pinal Dave (https://blog.sqlauthority.com)