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.

  • We aren't using transactions. Deletions can take from 45-75 seconds per user which is a problem with 140k users to delete. We've tested in batches of 10, 50, 100, 1,000, and 10,000. We can run the whole delete in about 4 minutes if we disable constraints and then re-enable. We've had soft deletes in place, but there is Personal Health Information that we want to remove from production.
    – Josh
    Jun 3, 2014 at 18:39
  • I wonder if it would ease things a bit if you first ran an update to set these auditing columns to NULL, then ran the DELETE. Yes, this is two write operations for each row, but may turn out to be less expensive than any checks involved with the FKs when the values are present... particularly if you delete the rows in batches. It is moving swiftly toward the end of a long day, and I'm not sure what I'm suggesting actually makes sense, but it seems obvious that the constraints are having an effect on the duration of the transaction, and if the referencing value is NULL it may help. Jun 4, 2014 at 0:58
  • Could be worth a try, but there would still be many, many records with valid pointers back to the User table. I would only be able to null out the records that I'm trying to delete. Also, I don't think this would give SQL server a reason to skip a table scan.
    – Josh
    Jun 4, 2014 at 17:19

1 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
    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')
        SELECT  1
            SYS.index_columns ic 
            INNER JOIN sys.indexes i ON i.object_id = ic.object_id AND ic.index_id = i.index_id
            1 = 1
            AND ic.OBJECT_ID = c.object_id
            AND ic.column_id = c.column_id
            AND ic.is_included_column = 0
  • Forgive my 'greenness', but is there a point where having too many indexes on a table would actually hurt performance? All the linked tables have other indexes, keys, and constraints defined outside of the scope of the pointer back to the User table. Should I be cautious about introducing 2 more indexes on the 90 or so tables?
    – Josh
    Jun 4, 2014 at 17:22
  • 1
    There's really no catch-all answer for this, you have to tune your index needs based on the characteristics of your system. You might want to reference this answer here for details: dba.stackexchange.com/questions/5525/…
    – druzin
    Jun 5, 2014 at 1:56
  • 2
    I think you also need AND ic.key_ordinal = 1 since the column might be in some index somewhere, but if it's not the first column it cannot be looked up quickly.
    – Ed Avis
    Aug 14, 2015 at 17:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.