Analyze This – Analyze Your Indexes – Part 3

This is the third post in which I am walking through a query that I use to analyze indexes. Thus far I have added to the following to the query that I use:

An error in the WHERE clause for the missing index portion of the query was pointed out in the last post and is fixed in the current version. The SQL code read:

WHERE database_id = db_id()
AND mid.object_id = @ObjectID OR @ObjectID IS NULL

The correct code should be:

WHERE database_id = db_id()
AND (mid.object_id = @ObjectID OR @ObjectID IS NULL)

Today I am looking at foreign keys and adding in functionality to determine indexes that are related to foreign keys and possibly missing from the indexing strategy. A while back I read Greg Lowe’s post on whether foreign keys should be indexed automatically. I’m not a fan of that approach even though in a lot of situations that I’ve been in working on performance tuning this would have been a good idea.

Instead, I’d like to know which foreign keys are related to existing indexes through either exact or covering scenarios. The new version of this query has two new CTEs added to it. The first new CTE is ForeignKeys which summarizes meta-data on the existing foreign keys. The second new CTE is IndexAnalysis which UNIONS the previous result set with a list of foreign keys that are not indexed.

Version 3 of the query is as follows:

DECLARE @ObjectID int
SELECT @ObjectID = OBJECT_ID('Production.BillOfMaterials')

;WITH preIndexAnalysis
AS (
SELECT
OBJECT_SCHEMA_NAME(t.object_id) as schema_name
,t.name as table_name
,COALESCE(i.name, 'N/A') as index_name
,CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END + i.type_desc as type_desc
,NULL as impact
,ROW_NUMBER()
OVER (PARTITION BY i.object_id ORDER BY i.is_primary_key desc, ius.user_seeks + ius.user_scans + ius.user_lookups desc) as ranking
,ius.user_seeks + ius.user_scans + ius.user_lookups as user_total
,COALESCE(CAST(100 * (ius.user_seeks + ius.user_scans + ius.user_lookups)
/(NULLIF(SUM(ius.user_seeks + ius.user_scans + ius.user_lookups)
OVER(PARTITION BY i.object_id), 0) * 1.) as decimal(6,2)),0) as user_total_pct
,ius.user_seeks
,ius.user_scans
,ius.user_lookups
,STUFF((SELECT ', ' + QUOTENAME(c.name)
FROM sys.index_columns ic
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND is_included_column = 0
ORDER BY index_column_id ASC
FOR XML PATH('')), 1, 2, '') AS indexed_columns
,STUFF((SELECT ', ' + QUOTENAME(c.name)
FROM sys.index_columns ic
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND is_included_column = 1
ORDER BY index_column_id ASC
FOR XML PATH('')), 1, 2, '') AS included_columns
,i.object_id
,i.index_id
,(SELECT QUOTENAME(ic.column_id,'(')
FROM sys.index_columns ic
WHERE i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND is_included_column = 0
ORDER BY index_column_id ASC
FOR XML PATH('')) AS indexed_columns_compare
,COALESCE((SELECT QUOTENAME(ic.column_id, '(')
FROM sys.index_columns ic
WHERE i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND is_included_column = 1
ORDER BY index_column_id ASC
FOR XML PATH('')), SPACE(0)) AS included_columns_compare
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id AND ius.database_id = db_id()
WHERE t.object_id = @ObjectID OR @ObjectID IS NULL
UNION ALL
SELECT
OBJECT_SCHEMA_NAME(mid.object_id) as schema_name
,OBJECT_NAME(mid.object_id) as table_name
,'--MISSING--'
,'--NONCLUSTERED--'
,(migs.user_seeks + migs.user_scans) * migs.avg_user_impact as impact
,0 as ranking
,migs.user_seeks + migs.user_scans as user_total
,NULL as user_total_pct
,migs.user_seeks
,migs.user_scans
,0 as user_lookups
,COALESCE(equality_columns + ', ', SPACE(0)) + COALESCE(inequality_columns, SPACE(0)) as indexed_columns
,included_columns
,mid.object_id
,NULL
,NULL
,NULL
FROM sys.dm_db_missing_index_details mid
INNER JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
WHERE database_id = db_id()
AND (mid.object_id = @ObjectID OR @ObjectID IS NULL)
)
,ForeignKeys
AS (
SELECT fk.name + '|PARENT' AS name
,fkc.parent_object_id AS object_id
,STUFF((SELECT ', ' + QUOTENAME(c.name)
FROM sys.foreign_key_columns ifkc
INNER JOIN sys.columns c ON ifkc.parent_object_id = c.object_id AND ifkc.parent_column_id = c.column_id
WHERE fk.object_id = ifkc.constraint_object_id
ORDER BY ifkc.constraint_column_id
FOR XML PATH('')), 1, 2, '') AS fk_columns
,(SELECT QUOTENAME(ifkc.parent_column_id,'(')
FROM sys.foreign_key_columns ifkc
WHERE fk.object_id = ifkc.constraint_object_id
ORDER BY ifkc.constraint_column_id
FOR XML PATH('')) AS fk_columns_compare
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
WHERE fkc.parent_object_id = @ObjectID OR @ObjectID IS NULL
UNION ALL
SELECT fk.name + '|REFERENCED' as name
,fkc.referenced_object_id AS object_id
,STUFF((SELECT ', ' + QUOTENAME(c.name)
FROM sys.foreign_key_columns ifkc
INNER JOIN sys.columns c ON ifkc.referenced_object_id = c.object_id AND ifkc.referenced_column_id = c.column_id
WHERE fk.object_id = ifkc.constraint_object_id
ORDER BY ifkc.constraint_column_id
FOR XML PATH('')), 1, 2, '') AS fk_columns
,(SELECT QUOTENAME(ifkc.referenced_column_id,'(')
FROM sys.foreign_key_columns ifkc
WHERE fk.object_id = ifkc.constraint_object_id
ORDER BY ifkc.constraint_column_id
FOR XML PATH('')) AS fk_columns_compare
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
WHERE fkc.referenced_object_id = @ObjectID OR @ObjectID IS NULL
), IndexAnalysis
AS (
SELECT schema_name
,table_name
,index_name
,type_desc
,impact
,ranking
,user_total
,user_total_pct
,CAST(100 * (user_seeks + user_scans + user_lookups)
/(NULLIF(SUM(user_seeks + user_scans + user_lookups)
OVER(PARTITION BY schema_name, table_name), 0) * 1.) as decimal(6,2)) as estimated_percent
,user_seeks
,user_scans
,user_lookups
,indexed_columns
,included_columns
,STUFF((SELECT ', ' + index_name AS [data()]
FROM preIndexAnalysis iia
WHERE ia.object_id = iia.object_id
AND ia.index_id  iia.index_id
AND ia.indexed_columns_compare = iia.indexed_columns_compare
AND ia.included_columns_compare = iia.included_columns_compare
FOR XML PATH('')), 1, 2, '') AS duplicate_indexes
,STUFF((SELECT ', ' + index_name AS [data()]
FROM preIndexAnalysis iia
WHERE ia.object_id = iia.object_id
AND ia.index_id  iia.index_id
AND (ia.indexed_columns_compare LIKE iia.indexed_columns_compare + '%'
OR iia.indexed_columns_compare LIKE ia.indexed_columns_compare + '%')
AND ia.indexed_columns_compare  iia.indexed_columns_compare
FOR XML PATH('')), 1, 2, '') AS overlapping_indexes
,STUFF((SELECT ', ' + name AS [data()]
FROM ForeignKeys ifk
WHERE ifk.object_id = ia.object_id
AND ia.indexed_columns_compare LIKE ifk.fk_columns_compare + '%'
FOR XML PATH('')), 1, 2, '') AS related_foreign_keys
,CAST((SELECT name
FROM ForeignKeys
WHERE ForeignKeys.object_id = ia.object_id
AND ia.indexed_columns_compare LIKE ForeignKeys.fk_columns_compare + '%'
FOR XML AUTO) as xml) AS related_foreign_keys_xml
FROM preIndexAnalysis ia
UNION ALL
SELECT OBJECT_SCHEMA_NAME(fk.object_id) AS schema_name
,OBJECT_NAME(fk.object_id) AS table_name
,fk.name AS index_name
,'--MISSING FOREIGN KEY--'
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,fk.fk_columns
,NULL
,NULL
,NULL
,NULL
,NULL
FROM ForeignKeys fk
LEFT OUTER JOIN preIndexAnalysis ia
ON fk.object_id = ia.object_id
AND ia.indexed_columns_compare LIKE fk.fk_columns_compare + '%'
WHERE ia.index_name IS NULL
)
SELECT schema_name
,table_name
,index_name
,type_desc
,impact
,user_total
,user_total_pct
,estimated_percent
,user_seeks
,user_scans
,user_lookups
,indexed_columns
,included_columns
,duplicate_indexes
,overlapping_indexes
,related_foreign_keys
FROM IndexAnalysis
ORDER BY 1, 2, 3

NOTE: Populating the table name in the function for the @ObjectID variable is optional. If the value of @ObjectID is NULL then information on all tables is returned. And it actually works properly now.

This version of the DMV query returns the following columns:

schema_name: Schema name
table_name: Table name
index_name: Index Name
type_desc: Type of index; either clustered or non-clustered.
impact: Calculation of impact of a potential index. This is based on the seeks and scans that the index could have utilized multiplied by average improvement the index would have provided. This is included only for missing indexes.
user_total: Total number of seek, scan, and lookup operations for the index
user_total_pct: Percentage of total number of seek, scan, and lookup operations for this index compared to all seek, scan, and lookup operations for existing indexes for the table.
estimated_percent: Percentage of total number of seek, scan, and lookup operations for this index compared to all seek, scan, and lookup operations for existing and potential indexes for the table. This number is naturally skewed because a seek for potential Index A resulted in another operation on an existing index and both of these operations would be counted.
user_seeks: Number of seek operations on the index.
user_scans: Number of scan operations on the index.
user_lookups: Number of lookup operations on the index.
indexed_columns: Columns that are part of the index, missing index or foreign key.
included_columns: Columns that are included in the index or missing index.
duplicate_indexes: List of Indexes that exist on the table that are identical to the index on this row.
overlapping_indexes: List of Indexes that exist on the table that overlap the index on this row.
related_foreign_keys: List of foreign keys that are related to the index either as an exact match or covering index.
related_foreign_keys_xml: XML document listing foreign keys that are related to the index either as an exact match or covering index.

The last column was added to the query to allow the results to be clicked and opened in a new window. I’ve found that it is easier to do this with list information from time to time.

With this version of the query there isn’t any need to run and SQL statements to get results, the query is set to look at Production.BillOfMaterials and that table provides a useful example. As with previous version’s example, the image below of the query results will only include a limited list of the columns. Only the following columns are included; schema_name, table_name, index_name, type_desc indexed_columns, and related_foreign_keys.

Reviewing these results, the following can be determined:

The foreign key FK_BillOfMaterials_Product_ProductAssemblyID is able to utilize the index AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate.
The foreign key FK_BillOfMaterials_UnitMeasure_UnitMeasureCode|PARENT is able to utilize the index IX_BillOfMaterials_UnitMeasureCode.
The foreign key FK_BillOfMaterials_Product_ComponentID is not able to utilize any index on the table and could benefit from an index on the column [ComponentID].

Now whether you should or should not index all foreign keys is a matter for another post. It really depends on the table, the foreign key, and how the database is being used. In some cases there will a performance gain on the indexing and in others the opposite. This version of the query provides a method to analyze indexes in relationship to foreign keys.

CAUTION: I should note that I haven’t extensively tested the foreign key portion of this query. I had planned to add it in a couple months ago and didn’t get it in until this post. There are other components that I plan to add to this query that have been tested, but haven’t been added yet. It just made sense to add foreign key analysis in at this point.

Share this:

Related

9 thoughts on “Analyze This – Analyze Your Indexes – Part 3”