Index Those Foreign Keys

Today started with some quality time getting to know a deadlock that had occurred. While working through the deadlock, I noticed that there were a number of foreign key relationships that weren’t indexed on the parent side of the relationship.

I am going to skip over the why to index foreign keys and save that for a later point when I have more time to go through it with some really pretty pictures. Today though, I want to share the scripts that I put together to look for these situations and help prevent issues related to them.

Brute Force Indexing

This first script is a brute force attack on this need. If you set the output for text results in SQL Server Management Studio (SSMS) you’ll get a script with all of the indexes you’ll need to cover all of your foreign key relationships.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

;WITH cIndexes
AS (
SELECT i.object_id
,i.name
,(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 key_ordinal ASC
FOR XML PATH('')) AS indexed_compare
FROM sys.indexes i
), cForeignKeys
AS (
SELECT fk.name AS foreign_key_name
,'PARENT' as foreign_key_type
,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.constraint_column_id = 1
UNION ALL
SELECT fk.name AS foreign_key_name
,'REFERENCED' as foreign_key_type
,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.constraint_column_id = 1
), cRowCount
AS (
SELECT object_id
,SUM(row_count) AS row_count
FROM sys.dm_db_partition_stats ps
WHERE index_id IN (1,0)
GROUP BY object_id
)
SELECT
'--Missing foreign key index for '+fk.foreign_key_name+CHAR(13)+CHAR(10)+'GO'+CHAR(13)+CHAR(10)+
+'CREATE NONCLUSTERED INDEX FKIX_'+OBJECT_NAME(fk.object_id)+'_'+REPLACE(REPLACE(REPLACE(REPLACE(fk.fk_columns,',',''),'[',''),']',''),' ','')
+CHAR(13)+CHAR(10)+
+'ON [dbo].['+OBJECT_NAME(fk.object_id)+'] ('+fk.fk_columns+')'+CHAR(13)+CHAR(10)+
+'GO'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
FROM cForeignKeys fk
INNER JOIN cRowCount rc ON fk.object_id = rc.object_id
LEFT OUTER JOIN cIndexes i ON fk.object_id = i.object_id AND i.indexed_compare LIKE fk.fk_columns_compare + '%'
WHERE i.name IS NULL
ORDER BY OBJECT_NAME(fk.object_id), fk.fk_columns

Foreign Key Monitoring

This second script accommodates for those situations when you may not want to just index every foreign key that is out there. Maybe there’s a really old table in the database with a foreign key relationship that just doesn’t matter any more. Is it worth indexing along a vector that won’t lead to any performance impact – either negative or positive? Most likely not.

For this script, the results output a list of foreign keys relationships that are not fully indexed. Included in the result script is a column with XML data in it that contains a script for creating an index. You may notice that the format for this is very similar to the schema created when outputtingmmissing Indexes from execution plans.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

;WITH cIndexes
AS (
SELECT i.object_id
,i.name
,(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 key_ordinal ASC
FOR XML PATH('')) AS indexed_compare
FROM sys.indexes i
), cForeignKeys
AS (
SELECT fk.name AS foreign_key_name
,'PARENT' as foreign_key_type
,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.constraint_column_id = 1
UNION ALL
SELECT fk.name AS foreign_key_name
,'REFERENCED' as foreign_key_type
,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.constraint_column_id = 1
), cRowCount
AS (
SELECT object_id
,SUM(row_count) AS row_count
FROM sys.dm_db_partition_stats ps
WHERE index_id IN (1,0)
GROUP BY object_id
)
SELECT
fk.foreign_key_name
,OBJECT_NAME(fk.object_id) AS fk_table_name
,fk.fk_columns
,rc.row_count AS row_count
,CAST('&lt;!--dex  &#039;+CHAR(13)+CHAR(10)+&#039;Missing foreign key index for &#039;+fk.foreign_key_name+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+&#039;USE [&#039;+DB_NAME()+&#039;]&#039;<br> +CHAR(13)+CHAR(10)+'GO'+CHAR(13)+CHAR(10)+
+'CREATE NONCLUSTERED INDEX []'+CHAR(13)+CHAR(10)+
+'ON [dbo].['+OBJECT_NAME(fk.object_id)+'] ('+fk.fk_columns+')'+CHAR(13)+CHAR(10)+
+'GO'+CHAR(13)+CHAR(10)+'--?&gt;' AS xml) foreign_key_index_schema
FROM cForeignKeys fk
INNER JOIN cRowCount rc ON fk.object_id = rc.object_id
LEFT OUTER JOIN cIndexes i ON fk.object_id = i.object_id AND i.indexed_compare LIKE fk.fk_columns_compare + '%'
WHERE i.name IS NULL
ORDER BY OBJECT_NAME(fk.object_id), fk.fk_columns

Closing Up

The DDL schema output in these scripts is very basic. It doesn’t account for potentially important things like partitions and filegroups. Obviously, you’ll need to modify this for your own environment and don’t just run this on production.

I see a lot of potential in these scripts and am planning to include them as part of preparing for releases when I am clients. A good way to dot the i’s and cross the t’s.

Individual results may vary. No Legos were harmed in the writing on this post.