go backsqlschool blogs list

Find missing indexes on foreign key constraints

by Antonios Chatzipavlis

Τα foreign keys όπως είναι γνωστό δεν φτιάχνουν κάποιον index στο reference table. Επίσης είναι γνωστό ότι καλό (αλλά όχι πάντα απαραίτητο) να φτιάχνουμε nonclustered index στο reference table στo field(s) που εμπλέκονται στο FK constraint.



Με το παρακάτω script βρίσκουμε αυτές τις περιπτώσεις τις αξιολογούμε και αν χρειαστεί να φτιάξουμε αυτούς η τελευταία κολώνα περιέχει και το create index statement.



Enjoy

//antonch


with fcc as
(
    select 
            sch1.name                                as parent_schema_name,
            object_name(fkc.parent_object_id)        as parent_table_name,
            object_name(fkc.constraint_object_id)    as constraint_name,
            sch2.name                                as referenced_schema,
            object_name(fkc.referenced_object_id)    as referenced_table_name,
            substring(
                        (    select ',' 
                                + rtrim(col_name(fc.parent_object_id,parent_column_id)) as [data()]
                            from sys.foreign_key_columns as fc with (nolock)
                            inner join sys.foreign_keys as fk with (nolock) on fc.constraint_object_id = fk.[object_id]
                            and fc.constraint_object_id = fkc.constraint_object_id
                            order by fc.constraint_column_id
                            for xml path('')
                        ), 2, 8000)                    as parent_columns,
            substring(
                        (    select ',' 
                                + rtrim(col_name(fc.referenced_object_id,referenced_column_id)) as [data()]
                            from sys.foreign_key_columns as fc with (nolock)
                            inner join sys.foreign_keys as fk with (nolock) on fc.constraint_object_id = fk.[object_id]
                            and fc.constraint_object_id = fkc.constraint_object_id
                            order by constraint_column_id
                            for xml path('')
                        ), 2, 8000)                    as referenced_columns
    from        sys.foreign_key_columns        as fkc    with (nolock)
    inner join    sys.objects                    as obj1 with (nolock) on fkc.parent_object_id        =    obj1.[object_id]
    inner join    sys.tables                    as tbl1 with (nolock) on tbl1.[object_id]            =    obj1.[object_id]
    inner join    sys.schemas                    as sch1 with (nolock) on sch1.[schema_id]            =    tbl1.[schema_id]
    inner join    sys.objects                    as obj2 with (nolock) on fkc.referenced_object_id    =    obj2.[object_id]
    inner join    sys.tables                    as tbl2    with (nolock) on tbl2.[object_id]            =    obj2.[object_id]
    inner join    sys.schemas                    as sch2 with (nolock) on sch2.[schema_id]            =    tbl2.[schema_id]
    where        obj1.type = 'U' 
                and 
                obj2.type = 'U'
    group by    obj1.[schema_id],
                obj2.[schema_id],
                fkc.parent_object_id,
                constraint_object_id,
                referenced_object_id,
                sch1.name,
                sch2.name
),
idxcols as
(
    select 
            s.name                        as schemaname,
            object_name(t.[object_id])    as objectname,
            substring(
                        ( 
                            select ',' 
                                + rtrim(ac.name) 
                            from sys.tables                    as st
                            inner join sys.indexes            as ix on st.[object_id] = ix.[object_id]
                            inner join sys.index_columns    as ic on ix.[object_id] = ic.[object_id] and ix.[index_id]    = ic.[index_id] 
                            inner join sys.all_columns        as ac on st.[object_id] = ac.[object_id] and ic.[column_id] = ac.[column_id]
                            where    i.[object_id] = ix.[object_id] 
                                    and 
                                    i.index_id = ix.index_id 
                                    and 
                                    ic.is_included_column = 0
                            order by ac.column_id
                            for xml path('')
                        ), 2, 8000 ) as keycols
    from        sys.indexes        as i
    inner join    sys.tables        as t    on    t.[object_id]    =    i.[object_id]
    inner join    sys.schemas        as s    on    s.[schema_id]    =    t.[schema_id]
    where    i.[type] in (1,2,5,6) 
            and 
            i.is_unique_constraint = 0
            and 
            t.is_ms_shipped = 0
)
select    fcc.constraint_name,
        fcc.parent_schema_name +'.' + fcc.parent_table_name as parent_table,
        fcc.referenced_schema + '.' + fcc.referenced_table_name as reference_table,
        fcc.parent_columns,
        fcc.referenced_columns,
        N'CREATE NONCLUSTERED INDEX idx_'    +
            fcc.referenced_table_name        +    
            '_'                                +
            fcc.constraint_name                +
            N' ON '                            +
            fcc.parent_schema_name            +
            '.'                                + 
            fcc.parent_table_name            +
            N'('                            +
            fcc.referenced_columns            +
            N');'    as ddl_create
from fcc
where not exists ( SELECT 1 FROM idxcols 
                    WHERE fcc.parent_schema_name = idxcols.schemaName
                        AND fcc.parent_table_name = idxcols.objectName 
                        AND REPLACE(fcc.parent_columns,'' ,'') = idxcols.KeyCols)
Ημερομηνία: 16 October 2017 15:54
Αξιολόγηση:
Κατηγορίες:
Share it:

Αφήστε το σχόλιο σας - Leave your comment

Τα σχόλια έχουν κλείσει.
Επιτρέπονται μόνο τα σχόλια από τα μέλη του SqlSchool.gr.


newsletter subscription

Εάν επιθυμείτε να λαμβάνετε ενημέρωση από εμάς, δώστε μας το e-mail σας.
PASS chapter logo
Official Professional Association for SQL Server (PASS) chapter for Greece
Join to PASS