Index Those Foreign Keys

Posted by & filed under , , , , , , , .

Lego DeadlockToday 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('<!--dex  '+CHAR(13)+CHAR(10)+'Missing foreign key index for '+fk.foreign_key_name+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+'USE ['+DB_NAME()+']'<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)+'--?>' 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.

  • http://michaeljswart.com Michael J Swart

    Very awesome, I had a similar post a while ago ( http://michaeljswart.com/?p=46 ) which didn't output any ddl for the indexes, but did indicate what kind of impact a "delete" operation would have in terms of child rows scanned.

    • http://feeds2.feedburner.com/StrateSql jstrate

      Sweet. I like how you presented the information back – makes it easy for the user to determine what the impact would be.

  • Pingback: Tweets that mention Index Those Foreign Keys | Strate SQL -- Topsy.com

  • WIDBA

    Cool – I used to work with someone who indexed every FK regardless of if the table had 2 rows in it or not. In our case, we utilize many tables to support self documenting constraints with FKs to lookup tables. I seldom index them because the possible values are well below what could be useful for an index. Certainly something a person could work around should it be necessary with some convention restrictions or determining the size of the table with the key. Thanks for the good stuff.

  • George Mastros

    I really like this blog. It's very helpful. I noticed a security problem with certain users trying to run the query. I'm reasonably certain the problem was a result of the users not having permissions to run the dynamic management view (sys.dm_db_partition_stats). To correct this problem, I needed to grant them permissions with:

    GRANT VIEW DATABASE STATE TO LoginName

  • Pingback: Wrapping Up 2010 | Strate SQL

  • Pingback: SQL Server Central