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..
col ID Int Primary Key clustered.
PK ID List.