Today, I was working with one user to troubleshoot their query timeout issues. Analyzing wait stats on server, I found ‘LCK_M_X’ wait to be most prominent wait on server. This wait type occurs when a thread is waiting to acquire an Exclusive lock on a resource and there is at least one other lock in an incompatible mode granted on the resource to a different thread. This wait was clear indicator of poor indexing strategy on database.
Digging deeper I found that there were about 20 foreign key indexes which were non-trusted by SQL Server. One can use below query to find such constraints:-
SELECT '[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS keyname
from sys.foreign_keys i
INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0;
Sometimes as part of Data Migration strategy, or to carry out some ETL operation quickly, application teams disable keys and constraints in order to improve performance. After the data load finishes, enable them again, and SQL Server will check them behind the scenes. Disabling constraints is usually safer and easier than dropping and recreating them. But, it is very less known that SQL Server won’t start using your foreign key constraint just by enabling them. We have to tell SQL Server to not just enable the constraint, but to recheck all of the data that’s been loaded.
ALTER TABLE MyTableName WITH CHECK CHECK CONSTRAINT MyConstraintName;
ALTER TABLE MyTableName WITH CHECK CHECK CONSTRAINT ALL
Not doing this in right way will make foreign key constraint non-useful in terms of performance benefits. I recommend to check below video from Kendra Little for same.
For more one can also visit below urls:-