Categories
SQL

Identifying Missing Foreign Key Indexes

Foreign keys provide us with confidence in the integrity of the code we develop. In this post we identify indexing opportunities around foreign keys to increase performance of their usage.

Creating an index on a foreign key column (or columns) can provide additional performance in two key situations:

  • SELECT queries joining based on the foreign key, particularly filtering parent > child
  • DELETE statements needing to check for a key in related tables, avoiding a full table scan

That’s all well and good if we’re creating a new foreign key and adding our own index. How do we find existing foreign keys which aren’t yet indexed?

That’s where the query below comes into play.

We use a CTE to find foreign keys which are already indexed. From this we can then return details of other foreign keys along with scripts to create (and roll back) an index to mirror the key:

WITH IndexedKeys AS (
	/* Foreign Keys which have indexes */
	SELECT
		fk.parent_object_id,		/* Table */
		fk.[object_id]				/* Foreign Key */
	FROM
		sys.foreign_keys fk
		INNER JOIN sys.foreign_key_columns fkc
			ON fk.[object_id] = fkc.constraint_object_id
		LEFT JOIN sys.indexes i
			ON fk.parent_object_id = i.[object_id]
			/* Ignore columnstore / disabled indexes */
			AND i.[type_desc] NOT LIKE '%COLUMNSTORE%'
			AND i.is_disabled = 0
		LEFT JOIN sys.index_columns ic
			ON i.[object_id] = ic.[object_id]
			AND i.index_id = ic.index_id
			AND fkc.constraint_column_id = ic.index_column_id
			AND ic.column_id = fkc.parent_column_id
	WHERE /* Only trusted / enabled keys */
		fk.is_disabled = 0
		AND fk.is_not_trusted = 0
	GROUP BY fk.parent_object_id, fk.[object_id], i.index_id
	HAVING COUNT(DISTINCT(ic.column_id)) = COUNT(DISTINCT(fkc.parent_column_id))
)
/* Foreign Keys without indexes */
SELECT
	TableName = OBJECT_SCHEMA_NAME(fk.parent_object_id) + '.' + OBJECT_NAME(fk.parent_object_id),
	TableRecords = rc.NumRows,
	ForeignKey = fk.[name],
	KeyColumns = STRING_AGG(c.[name], ', '),
	ReferencedTable = OBJECT_SCHEMA_NAME(fk.referenced_object_id) + '.' + OBJECT_NAME(fk.referenced_object_id),
	CreateStatement = 'CREATE INDEX ' + QUOTENAME('IX_' + STRING_AGG(c.[name], '_') WITHIN GROUP (ORDER BY fkc.constraint_column_id)) +
		' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id)) +
		' (' + STRING_AGG(QUOTENAME(c.[name]), ', ') WITHIN GROUP (ORDER BY fkc.constraint_column_id) + ')',
	RollbackStatement = 'DROP INDEX ' + QUOTENAME('IX_' + STRING_AGG(c.[name], '_') WITHIN GROUP (ORDER BY fkc.constraint_column_id)) +
		' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id))
FROM sys.foreign_keys fk
	INNER JOIN sys.foreign_key_columns fkc
		ON fk.[object_id] = fkc.[constraint_object_id]
	INNER JOIN sys.columns c
		ON fkc.parent_object_id = c.[object_id]
		AND fkc.parent_column_id = c.column_id
	CROSS APPLY (
		SELECT NumRows = SUM(row_count)
		FROM sys.dm_db_partition_stats
		WHERE index_id IN (0, 1)
			AND [object_id] = fk.parent_object_id
		) rc
	LEFT JOIN IndexedKeys ik
		ON fk.parent_object_id = ik.parent_object_id
		AND fk.[object_id] = ik.[object_id]
WHERE /* Only trusted / enabled keys */
	fk.is_disabled = 0
	AND fk.is_not_trusted = 0
	AND ik.parent_object_id IS NULL
GROUP BY
	fk.parent_object_id,
	rc.NumRows,
	fk.[name],
	fk.referenced_object_id
ORDER BY
	rc.NumRows DESC;

The script should cater for a varity of situations such as:

  • Simple single-column foreign keys
  • Foreign keys with multiple key columns (composite keys)
  • Existing indexes with multiple columns, as long as they lead with the key column
  • Ignoring keys or indexes which are disabled

The results are ordered based on the number of records in the table as larger tables may see greater benefits from the indexes.

Check out some of the top results in your own systems to see if you might be missing an opportunity to gain some performance.

4 replies on “Identifying Missing Foreign Key Indexes”

Leave a comment