Find Unindexed Foreign Keys (2005)

  • Michael L. Smith

    SSC Enthusiast

    Points: 102

    Comments posted to this topic are about the item Find Unindexed Foreign Keys (2005)

  • Michael L. Smith

    SSC Enthusiast

    Points: 102

    Time after time, we hear the recommendation, "index your foreign keys." This is why this script was written. Now, the column or columns being referenced must be either a primary key or a unique constraint/index. That leaves referencing column(s) to be indexed...

    This script omits statistics and system objects. I want to take this opportunity to address a limitation of the script. The script assumes the order of columns in an appropriate index will be the same order as in the constraint definition, per this join condition:

    foreign_key_columns.constraint_column_id = index_columns.key_ordinal

    This is not always true! For composite indexes (or indices), we are free to manipulate the key column order. A few guiding principles are:

    1. SARGs go leftmost

    2. Most selective column goes lefmost

    3. Equality columns go leftmost

    For more detailed discussion of these points, I recommend these articles: "The Best Indexes for Joins," SQL Server Magazine, May 2002; and "The Big Cover-Up," SQL Server Magazine, September 2001 both by Kalen Delaney.

    Thus, you may have a foreign key appropriately indexed -- giving you better performance than an index in FK-definition order -- but still reported by this script...

    Is there a reader out there that can formulate another script to allow for different but appropriate order? I'm also looking for a non-cursor based solution to identify potential foreign keys...

    Thanks,

    Mike

  • janis.l.murphy

    SSC Veteran

    Points: 239

    How about this:

    WITH CTE_missingIndexKeyOrdinal AS (

    SELECT DISTINCT /* remove dups caused by composite constraints */

    DB_NAME() AS [database_name],

    OBJECT_NAME(foreign_keys.parent_object_id) AS [table_name],

    foreign_keys.[name] AS [fk_name]

    FROM sys.foreign_keys AS foreign_keys

    JOIN sys.foreign_key_columns AS foreign_key_columns

    ON foreign_keys.[object_id] = foreign_key_columns.constraint_object_id

    WHERE NOT EXISTS (

    SELECT 'An index with same columns and column order'

    FROM sys.indexes AS indexes

    JOIN sys.index_columns AS index_columns

    ON indexes.[object_id] = index_columns.[object_id]

    WHERE foreign_keys.parent_object_id = indexes.[object_id]

    AND indexes.index_id = index_columns.index_id

    AND foreign_key_columns.constraint_column_id = index_columns.key_ordinal

    AND foreign_key_columns.parent_column_id = index_columns.column_id

    AND OBJECTPROPERTYEX(indexes.[object_id],'IsMSShipped') = 0

    AND indexes.is_hypothetical = 0

    )

    AND foreign_keys.is_ms_shipped = 0

    )

    , CTE_missingIndexColumniId AS (

    SELECT DISTINCT /* remove dups caused by composite constraints */

    DB_NAME() AS [database_name],

    OBJECT_NAME(foreign_keys.parent_object_id) AS [table_name],

    foreign_keys.[name] AS [fk_name]

    FROM sys.foreign_keys AS foreign_keys

    JOIN sys.foreign_key_columns AS foreign_key_columns

    ON foreign_keys.[object_id] = foreign_key_columns.constraint_object_id

    WHERE NOT EXISTS (

    SELECT 'An index with same columns and [POSSIBLY DIFFERENT] column order'

    FROM sys.indexes AS indexes

    JOIN sys.index_columns AS index_columns

    ON indexes.[object_id] = index_columns.[object_id]

    WHERE foreign_keys.parent_object_id = indexes.[object_id]

    AND indexes.index_id = index_columns.index_id

    AND foreign_key_columns.constraint_column_id = index_columns.index_column_id

    AND foreign_key_columns.parent_column_id = index_columns.column_id

    AND OBJECTPROPERTYEX(indexes.[object_id],'IsMSShipped') = 0

    AND indexes.is_hypothetical = 0

    )

    AND foreign_keys.is_ms_shipped = 0

    )

    SELECT [database_name],

    [table_name],

    [fk_name]

    FROM CTE_missingIndexKeyOrdinal

    EXCEPT

    SELECT [database_name],

    [table_name],

    [fk_name]

    FROM CTE_missingIndexColumniId

    UNION

    SELECT [database_name],

    [table_name],

    [fk_name]

    FROM CTE_missingIndexColumniId

    EXCEPT

    SELECT [database_name],

    [table_name],

    [fk_name]

    FROM CTE_missingIndexKeyOrdinal

    ;

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply