September 23, 2007 at 6:58 pm
Comments posted to this topic are about the item Find Unindexed Foreign Keys (2005)
October 25, 2007 at 2:37 pm
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
December 20, 2011 at 10:11 am
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
JOINsys.foreign_key_columns AS foreign_key_columns
ONforeign_keys.[object_id] = foreign_key_columns.constraint_object_id
WHERENOT EXISTS (
SELECT'An index with same columns and column order'
FROM sys.indexes AS indexes
JOINsys.index_columnsAS index_columns
ONindexes.[object_id] = index_columns.[object_id]
WHEREforeign_keys.parent_object_id = indexes.[object_id]
ANDindexes.index_id = index_columns.index_id
ANDforeign_key_columns.constraint_column_id = index_columns.key_ordinal
ANDforeign_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
JOINsys.foreign_key_columns AS foreign_key_columns
ONforeign_keys.[object_id] = foreign_key_columns.constraint_object_id
WHERENOT EXISTS (
SELECT'An index with same columns and [POSSIBLY DIFFERENT] column order'
FROM sys.indexes AS indexes
JOINsys.index_columnsAS index_columns
ONindexes.[object_id] = index_columns.[object_id]
WHEREforeign_keys.parent_object_id = indexes.[object_id]
ANDindexes.index_id = index_columns.index_id
ANDforeign_key_columns.constraint_column_id = index_columns.index_column_id
ANDforeign_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
;
May 11, 2016 at 11:43 am
Thanks for the script.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply