Slow delete caused by many foreign keys

Question

My DB has about 90 tables. Most of the tables have an UpdatedBy and AddedBy column that have foreign keys pointing back to the user table. With even moderate amounts of data spread throughout the database, this makes for a very slow delete process when purging old user records.

Our delete query first updates all of the Updated/Added references to the current user and then deletes the user record. The execution plan shows table scans for each related table due to the foreign key. See this post as an example of what we are facing.

The pointers back to added and updated are not really useful other than for analysis during application problems. They are rarely used, and aren't a primary source of good information when they are. I'm wondering if I should remove the foreign key constraints all together or if I should possibly move the auditing of updates to a separate table that keeps a record of table name, column, value, and user id, or if there is some other generally accepted approach that the DBA community gravitates toward for this sort of situation.

Answer 1

Make sure that there are indexes on the constrained columns, since the dbms will do a lookup using those columns in the referencing tables.

You can try using this script, it generates an index creation script for any non-indexed column being referenced by a cascading constraints.

SELECT 'CREATE NONCLUSTERED INDEX IX_'+OBJECT_NAME(fk.parent_object_id)+'_'+c.name+' ON '+OBJECT_NAME(fk.parent_object_id)+'('+c.name+') WITH (ONLINE=ON)' 
        --, OBJECT_NAME(fk.referenced_object_id) AS referenced_tale,  cc.name
FROM 
    sys.foreign_keys fk
    INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id
    INNER JOIN sys.columns cc ON fkc.referenced_object_id = cc.object_id AND fkc.referenced_column_id = cc.column_id
WHERE delete_referential_action_desc IN ('CASCADE', 'SET_NULL')
    AND NOT EXISTS
        (
        SELECT  1
        FROM
            SYS.index_columns ic 
            INNER JOIN sys.indexes i ON i.object_id = ic.object_id AND ic.index_id = i.index_id
        WHERE 
            1 = 1
            AND type_DESC IN ('CLUSTERED','NONCLUSTERED')
            AND ic.OBJECT_ID = c.object_id
            AND ic.column_id = c.column_id
            AND ic.is_included_column = 0
        )