SQLTeam.com | Weblogs | Forums

FK Constraints - Causing Timeouts


I have a stored proc, that performs and insert via insert... values (...), that results in timeouts (application log) when the CHECK option is on for 2 FK constraints. The table with the PK, has 10 rows, with the below ids (PK). There are 2 cols in the table with the FKs (CreatedBy, ModifiedBy) that have CHECK constraints to the id col in the users table. At first, I turned off CHECK (set to NOCHECK), and problem went away, but I then simply dropped and recreated the FK constraints with the CHECK option ON, and no timeouts for 4 hours now (was occurring every 10 minutes). There are no msgs in the SQL ErrorLog (blocks or otherwise).
Would the gap iun value for the min(id) =1 and the next ID value = 116256 be a suspect ? 116251 value difference ? I cannot find anything, there is no locking, just randomly occurs with insert to table that has the 2 FK constraints to the below ID column (PK). The FK cols are NOT INDEXED. The FK table has ~7100 rows and 8 other FK Check constraints (to other tables PK, not the below). All tables re-indexed < 2% fragmentation, all stats updated, the only other odd thing is UDT for FK column that has same underlying type (INT) as the PK Column, as well as INS/DEL/UPD triggers on the table with the FK..

Users table
col ID Int Primary Key clustered.
PK ID List.


Significant number of changes happening on the various tables involved? or indexes not recently rebuilt? I would check the fragmentation %age of the indexes. However, this does not sound likely in the "Drop the FKey and recreate and its fine for 4 hours", that sounds more like some sort of caching problem.

STATs getting out of date?

Perhaps more likely: FK is triggering a Stats Refresh and Stats are set to ASYNC (which is the default). I always change that setting to SYNC (the query that triggers the refresh uses the old stats, rather than WAITING for the new stats) (However, that doesn't sound right either: this would only effect the query that triggered the Stats Refresh, the very next one should run quickly)