Non – Trusted Foreign Keys in SQL Server

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:-

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.

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:-

  • https://www.brentozar.com/blitz/foreign-key-trusted/
  • http://sqlblog.com/blogs/hugo%5Fkornelis/archive/2007/03/29/can-you-trust-your-constraints.aspx

on

Leave a Reply