Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Find Unindexed Foreign Keys (2005) Expand / Collapse
Author
Message
Posted Sunday, September 23, 2007 6:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 3, 2011 1:01 PM
Points: 6, Visits: 38
Comments posted to this topic are about the item Find Unindexed Foreign Keys (2005)
Post #401785
Posted Thursday, October 25, 2007 2:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 3, 2011 1:01 PM
Points: 6, Visits: 38
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

Post #415120
Posted Tuesday, December 20, 2011 10:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 8:52 AM
Points: 37, Visits: 115
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

;

Post #1224618
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse