Adventures In Foreign Keys 3: Why Cascading Deletes Perform Slowly


In the last post, I looked at some issues with implementing foreign keys with cascading actions. Namely that, well, it fell apart pretty quickly just trying to set up.

I didn’t even get to the point where I wanted to test all those relationships.

But there’s an even worse surprise waiting for you if you do implement foreign keys with cascading actions.

Locking From Hell

Let’s pretend that we gave up on stuff getting deleted from other tables if we canned a post. Let’s just get things set up so that if a user bids us adieu, we’ll get rid of their badges, comments, and posts. It’s, like, GDPR, or something.

These all get added without a problem.

What if we delete some data?

Harken Back, Ye Merry Reader

I’m gonna use some code from a previous post about locks taken during indexed view creation.

When we run a delete for a single user, we log a Whole Mess® of locks.

What comes back is pretty nasty.

My Own Little Nasty World

If you’ve ever watched locks for modification queries (and who doesn’t spend weekend doing that, really?), most of these will look normal to you.

These locks change based on how many rows we need to get rid of.

For example, User Id 1 is hardly anything, but for the rows we need to delete, we take serializable locks – RANGEX-X.

Seriously Serializable

This type of lock upgrade may come as a surprise to you, but this is how SQL Server guarantees that whole referential integrity thing so that it can trust the foreign keys you’ve got on there. Lucky you!

Let’s think about a perfect storm, where…

  • You’ve got lots of cascading actions firing off (we don’t)
  • Your foreign keys aren’t indexed well (ours are, we’ll look at the query plan in a minute)
  • You need to cascade down large amounts of data (this only hit about 400 rows at most)
  • Maybe there’s a begin tran or something in there for good measure (I did that to get the locking info, ha ha ha)

You could end up with really bad locking if any one or two of those things is true.

Let’s say we need to delete Jon Skeet. Because he’s a wonderful piece of outlier data.

SQL Server is all like “object locks immediately.” And, yeah, X locks on an object (table) are a lot like serializable locks on a range, just, you know, bigger.


You Could Learn A Lot From A Query Plan

While query plans don’t show us locking per se, they will show us needing to go out to each table that references Users.

I’ll make you a believer

And we can see a delete run on all three referencing tables for both the clustered index, and the nonclustered indexes we have to support the foreign keys.

But hey, at least I got some index seeks. My Friend Forrest® didn’t have as much luck.

Lining Bottoms

Be really careful with how you set up foreign keys. If you have cascading actions on them, you could be causing yourself a whole lot of locking headaches.

We do warn about these in sp_BlitzIndex, if you’re curious about the state of your database.

Next up, we’ll look at different tactics for indexing foreign keys.

We’re going to stay far away from cascading actions, though.

Thanks for reading!

Previous Post
Adventures In Foreign Keys 2: Common Foreign Key Errors
Next Post
Adventures In Foreign Keys 4: How to Index Foreign Keys

3 Comments. Leave new

  • […] named SSISDB – uses cascading DELETEs to enforce referential integrity. In his post titled Adventures In Foreign Keys 3: Why Cascading Deletes Perform Slowly, by Erik Darling (Blog, About) aka, “Nice post, Brent!” at the Brent Ozar (@BrentO | […]

  • What is missing here is the equivalent action doing the same thing without cascading FK’s (or any FK’s) with the same data integrity enforced by the FK. (It would require begin tran before starting deleting from the bottom. ) I assume that the data integrity is important, otherwise who’d bother with FK’s or relational databases.
    It would be useful to compare two approaches.

    • Sure, if that’s something you’re interested in, by all means, go for it! There are probably other folks who would find a blog post about that topic interesting as well.


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.