Friday, September 2, 2011

Finding Foreign Keys without Indexes

I have seen a number of blog posts on this topic that go into detail as to why it is important to have such indexes and sometimes provide helpful queries. Examples would include:
    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_DDL

FROM        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_SCHEMA

WHERE       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;

4 comments:

  1. This is brilliant! And I hope to hear more from you.

    I don't know if you remember me, I was sitting in Aaron Bertrand's "What's New In Denali" at SQLSat60 and I noticed you were too. I regret not introducing myself then.

    But any way, I noticed one case that isn't quite covered: The case where a foreign key column is indexed, but not indexed in a way that can be used. For example:

    use tempdb
    CREATE TABLE A (a int primary key);
    CREATE TABLE B (b int not null, a int not null references A(a), primary key (b,a));

    The column B(a) is part of an index, but not one that's useful when deleting from A.

    It would be good if your script picked up this case as well! If you can solve that, this script would definitely go into my arsenal.

    Cheers!

    ReplyDelete
  2. Michael,

    I updated the script in this post to address the issue that you raised as well as others I noticed myself. Thanks for your feedback!

    ReplyDelete
  3. Hi Mark

    i saved a heck of a time in using your script, your a genius - hope to be like you one day !
    all the best and thanks very much.

    Ali.

    ReplyDelete
  4. One useful enhancement I want to make to this script at some point would be for it to ignore key column order so it would recognize an index on Column2, Column1 as a match for a foreign key on Column1, Column2, while not being fooled by an index on Column2, Column3, Column1.

    I found that circumstance (with just the two columns in a different order) in a production database recently. The inconsistency was insufficient justification to make a change because everything was functioning as intended. But it bothers me that my script keeps reporting it as a problem.

    ReplyDelete