T-SQL Tuesday: The Last Ticket I Closed

Foreign Keys Suck

---

For this month’s T-SQL Tuesday, Brent Ozar (blog) wants to see your receipts.

I didn’t have a chance to write this post yesterday, because I was in the midst of closing the ticket.

Here’s a short synopsis from the client emergency:

• Third party vendor
• Merge statement compiled in a DLL file
• Called frequently
• Uses a Table-Valued Parameter
• Merges into one small table…
• Which has an indexed foreign key to a huge table
• Constantly deadlocking with other calls to the same merge query

To evaluate the foreign key, SQL Server was choosing this execution plan:

I hate the optimizer, some days. I really do.

Whatever year it’s stuck in is an unfortunate one.

 

Eager Index Spools Suck

---

Why in the name of Babe the blue-balled Ox would SQL Server’s advanced, intelligent, hyper-awesome, $7k a core query optimizer choose to build an index spool here, on 7 million rows?

Here are some things we tried to get rid of it:

• Add a clustered index to the target table
• Add a single-column index on the already-indexed clustered index key
• Add primary keys to the Table Types

If I had access to the merge statement, I would have torn it to shreds separate insert, update, and delete statements.

But would that have helped with SQL Server’s dumb execution plan choice in evaluating the foreign key? Would a FORCESEEK hint even be followed into this portion of the execution plan?

RCSI wouldn’t help here, because foreign key evaluation is done under Read Committed Locking isolation.

I don’t know. We can’t just recompile DLLs. All I know is that building the eager index spool is slowing this query down just enough to cause it to deadlock.

So, I took a page out of the Ugly Pragmatism handbook. I disabled the foreign key, and set up a job to look for rogue rows periodically.

Under non-Merge circumstances, I may have written a trigger to replace the foreign key. In that very moment, I had some doubts about writing a trigger quickly that would have worked correctly with:

• All of Merge’s weirdness
• Under concurrency

In reality, the foreign key wasn’t contributing much. The application only ever allows users to put rows in the parent table, and additional information only gets added to the child table by a system process after the original “document” is processed.

So, goodbye foreign key, goodbye eager index spool, goodbye deadlocks.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.