http://www.brentozar.com/archive/2011/09/indexing-for-deletes/
http://michaeljswart.com/2009/12/finding-your-missing-keys/
However, I wanted to take it a step further. The following query not only finds the Foreign Keys without an associated Index, it also provides the command needed to create each index in both XML and text formats.
If you click on the XML result, SSMS will open a new window containing the command already nicely formatted for you to copy into your source control system of preference, or into a new SSMS window so you can execute it. Of course, the query as shown uses my preferred naming and formatting standards for the indexes, but it should be easy enough to modify it to use your own.
/* What indexes are needed for my foreign keys.sql
Purpose: Find foreign keys without an index and the command needed to create the index.
Note that if these aren't showing up in the missing_index* views you might not
think that you need them, but not having them will cause some deletes from
referenced tables to perform full table scans. For example, without the
appropriate foreign key index, deleting a customer would result in a full scan
of the invoice table to ensure that the customer PK does not exist in any
invoice record.
Author: Mark Freeman (@m60freeman)
Changes: 2011-09-01 Created
2011-11-20 Eliminated matches to index where the referenced column is not the
first column in the index, added comment and brackets in Potential
DDL, and added handling of objects with the same name in multiple
schemas
*/SELECT rc.Constraint_Schema AS Base_Schema
,ccu.Table_Name AS Base_Table
,ccu.Column_Name AS Base_Column
,ccu.Constraint_Name AS Foreign_Key
,ccu2.Constraint_Schema AS Referenced_Schema
,ccu2.Table_Name AS Referenced_Table
,ccu2.Column_Name AS Referenced_Column
,'IX_' + ccu.Table_Name + '_' + ccu.Column_Name AS Potential_Index_Name
,CAST('<?query --' + CHAR(13) +
'CREATE INDEX [IX_' +
ccu.Table_Name + '_' + ccu.Column_Name +
']' + CHAR(13) +
CHAR(9) + '-- Needed to support ' +
ccu.Constraint_Name + CHAR(13) +
CHAR(9) + 'ON [' + rc.Constraint_Schema +
'].[' + ccu.Table_Name + ']' + CHAR(13) +
CHAR(9) + '(' + CHAR(13) +
CHAR(9) + CHAR(9) + '[' + ccu.Column_Name +
']' + CHAR(13) + CHAR(9) + ');'
+ CHAR(13) + ' --?>' AS XML) AS Potential_DDL_XML
,'CREATE INDEX [IX_' + ccu.Table_Name + '_' +
ccu.Column_Name + ']' + ' ON [' +
rc.Constraint_Schema + '].[' +
ccu.Table_Name + ']' + '([' +
ccu.Column_Name +
']); -- Needed to support '
+ ccu.Constraint_Name AS Potential_DDLFROM information_schema.referential_constraints rc
JOIN information_schema.constraint_column_usage ccu
ON rc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
AND rc.CONSTRAINT_SCHEMA = ccu.CONSTRAINT_SCHEMA
JOIN information_schema.constraint_column_usage ccu2
ON rc.UNIQUE_CONSTRAINT_NAME = ccu2.CONSTRAINT_NAME
AND rc.CONSTRAINT_SCHEMA = ccu2.CONSTRAINT_SCHEMAWHERE NOT EXISTS (
SELECT 1
FROM sys.indexes i
JOIN sys.index_columns ic
ON ic.[object_id] = i.[object_id]
AND ic.index_Id = i.index_Id
JOIN sys.columns c
ON c.[object_id] = ic.[object_id]
AND c.column_id = ic.column_id
JOIN sys.objects o
ON o.[object_id] = c.[object_id]
JOIN sys.schemas s
ON s.[schema_id] = o.[schema_id]
WHERE c.name = ccu.COLUMN_NAME
AND OBJECT_NAME(c.[object_id]) = ccu.TABLE_NAME
AND s.name = ccu.TABLE_SCHEMA
AND ic.key_ordinal = 1
)
AND ccu.Table_Name NOT LIKE 'aspnet_%'ORDER BY ccu2.Constraint_Schema,
ccu2.Table_Name,
rc.Constraint_Schema,
ccu.Table_Name,
ccu.Column_Name;
