If you update a row without actually changing its contents, does it still hurt?
Paul White wrote in detail about the impact of non-updating updates, proving that SQL Server works hard to avoid doing extra work where it can. That’s a great post, and you should read it.
But foreign keys add another level of complexity. If you have foreign keys, and you tell SQL Server that you’re updating the contents of those related columns, SQL Server will check ’em even when the data isn’t changing.
To illustrate it, I’ll take the Stack Overflow database and add a foreign key between Posts and Users:
|
1 2 3 4 |
ALTER TABLE dbo.Posts WITH NOCHECK ADD CONSTRAINT FK_Posts_OwnerUserId_Users_Id FOREIGN KEY (OwnerUserId) REFERENCES dbo.Users(Id); |
I’m saying that every Post’s OwnerUserId column must map up to a valid Users.Id. I’m using NOCHECK to get the implementation done faster because I’m not worried about the quality of the existing data in this case – I’m not trying to write a demo about foreign key join elimination.
Let’s grab a Posts row and see who the current OwnerUserId is:
Then set the Posts.OwnerUserId to the same value, and look at the actual execution plan:
The plan shows a clustered index seek against the Users table even though the data isn’t changing. Why? Because SQL Server has to go check that the Users row is still there.
The same thing happens even if I don’t specify a value for OwnerUserId, like if I just set it to itself. Here’s the actual execution plan for that, and it also hits the Users table just to check:
But if I update an unrelated column, like Score, then SQL Server doesn’t have to check the Users foreign key, and the actual plan avoids touching the Users table:
What this means for query authors
In a perfect world:
- Tables all have foreign keys that lay out their relationships
- You only update columns that are actually changing
- Therefore, you only incur the overhead of foreign keys when the related columns change
In an imperfect world:
- Your queries update every column, every time, regardless of whether they’re changing or not – maybe due to an ORM, or maybe due to a quick/easy/reusable stored procedure design
- SQL Server thinks you’re changing foreign key values every time
- You incur the overhead of foreign keys on every single update statement
This is especially problematic for transactions that update several tables in a row, each in their own update statement. As your concurrency needs grow, and you do thousands (or tens or hundreds of thousands) of updates per second, this can lead to blocking problems. At that point, you’re faced with a tough choice: either fix the T-SQL to only update columns that are changing, or…remove the foreign keys. In that case, removing the foreign keys is probably the easier quick fix.
DBAs: I’ll write your comment
to save you some time.
“Brent – this is completely unacceptable. We need foreign keys in the database to keep the bad data out. Those idiotic developers need to fix their T-SQL! Why would you update columns that aren’t changing?!? It’s their problem. Stupid ORM. I told them not to use it, and to craft every query by hand instead. I’m not changing our perfect database to lower its standards. Signed, Determined DBA”
And here’s my response.
Might as well get this over with now.
“Dear DBA: If we drop the foreign keys, our web site can sell more products right now. If we fix the code, it’ll take months, and in the meantime, our customers’ money will go to our competitors. Are you sure this is the hill you want to die on? Signed, The Consultant”
25 Comments. Leave new
I loved your response because 1) As sad as it is, it’s so very true and 2) It’s yet another proof that’s it’s better to do it right the first time.
Of course, if things really start to slow down, you can always take the time to simply rewrite all your code to work with MPP in the cloud, right? Why, you can get up to 30X performance, right? I’m sure that’ll be a huge savings, right? 😀 😀 😀
“Just shard it all, how hard can it be?” 🙂
BWAAAA-HAAAA! “Shard” wasn’t quite the word I was thinking of. 😀
It’s a bit odd how timely your post is… I’ve just been informed that we are “moving to the cloud” and that Redshift MPP will be a large component in the “joy” of all that. The project apparently started 2 days ago and today was the first time I was provided with an SOW.
Ok, fine but 20% goes to buy me better storage and more Ram. So I can a least do my job and keep up with more customers.
Deitrich, I like your negotiation techniques so much that I would high-five you if I didn’t have this hazmat suit on.
Hi Brent,
Does the behaviour of setting the foreign key to itself differ depending on the value of is_not_trusted in the sys.foreign_keys table which would be true because of the NOCHECK?
Presumably, you would expect the key to be tested when setting any value if it couldn’t guarantee the initial consistency of the data. However, if the foreign key can be trusted and you set the value to itself there is no way the User record could have been deleted because that would have violated the constraint in doing so.
Thanks,
Dan
Hey Dan,
I had that same thought. Tested it on a table not does not have the FK created with NO CHECK and it still does the lookup. I was surprised that it did.
Yep!
Brent, I hope you are just trying to get our attention today by playing devil’s advocate?
If so, please remember there are very likely a LOT of practitioners reading your blog who do not have the training and experience to understand the consequences of “turning off” the database’s internal data integrity engine, and they are often under non-technical managers who love to brow beat them into cutting corners to save a few bucks in the short term and are woefully ignorant of the long term consequnces of the loss of data integrity for sales transactions (or just don’t plan to be around when the chickens come home to roost).
Ken – I can’t teach every concept in every blog post. Start with the title, and that’s the main concept of the post.
Sometimes readers think I’m required to start every post at the 100 level, and take it all the way through 400 level. I just can’t do that, and I hope that’s fair. Your best bet is to read the title, think about it really seriously, and that’s the concept of the post – nothing else.
Brent,
So, my Determined DBA response to the Consultant’s response would be. “Well I can drop the foreign keys, but that will mean that the developers are going to have to add some defensive coding in their queries now, to no longer pull orphaned child data.” Sure hate to “accidentally” report on Posts that were not tied to an actual user in the users table. But, hey, it was not my recommendation to drop the foreign key. 🙂
Stephen – and as the consultant, I would respond with, “Yes, inaccurate queries have never been your responsibility. It’s great that you’re looking out for others, though.”
Love this…. reminds me of a time when a P1 incident kicked off because someone decided to remove the RI and supporting indexes to try and speed-up data loading and declared that the App would provide application enfored data integrity instead. Ended up with a bunch of duplicate data and blocked sessions all over the place with App session time-outs. I’m not complaining as I was the consultant who was called in to fix.
I’ve worked as a consultant for a large public company that had no foreign keys. Their “DBA” didn’t understand them, so every join was a left outer because they routinely removed the data from the primary tables when it was no longer needed.
The first thing I like to do when working for a new company is map out the database so I can see the relationships. I remember thinking “what the h***” when my diagram came out blank. After clearing my eyes and getting a dose of ice cream, I noticed faint dots on the screen and zoomed in to see all 200+ tables in a straight line spanning from here to infinity. As a developer who also has DBA responsibilities now, that’s not the picture I want of my database. This was eye-opening, and I’ll start to rethink how I write the “U”s in my CRUD stored procedures from now on.
Dennis – yeah, that’s the main takeaway: don’t update fields that aren’t changing. I get it, it’s hard – it makes writing T-SQL a lot more challenging – but if you don’t, that’s where the foreign key problems come in.
Of course, that’s the part that DBAs obsess over: “I MUST HAVE FOREIGN KEYS!” but they don’t realize how hard it is to write the appropriate T-SQL when FKs are present.
One would think it would be much easier for the database engine, once it grabs the row it’s going to update to check 1) are there any fields with foreign keys and 2) did the data change in any of those fields, then if the answer is “no” ignore checking. There must be an underlying reason to check all the time. Are you saying that the engine was designed by DBAs who took the approach, “we must preserve data integrity at all costs” and demanded that the developers writing the code put it in, or are the developers just “lazy” and decided to check all the time without thinking of the cost.
I think it’s more along the lines of this:
Microsoft developer: “Okay, I’ve got a proof of concept working for foreign keys. I’m going to need another six months to get it to perform well, starting with checking each field to see if it actually changed, and then – ”
Microsoft manager: “Don’t worry, good enough. We’ll ship it as-is. Next, I need – ”
Microsoft developer: “No wait, seriously, it’s not ready for – ”
Microsoft manager: “Nope, it’s done. You gotta start work on PowerAzure Enterprise for Teams.”
I forgot about the management factor. I was the victim of that enough times as a consultant. I was just thinking along the lines of the Change Tracking and Change Data Capture features that are available now, so checking to see what actually changed is already built into that logic. You know us developers, we love to reuse code. We even allows kangaroos to launch stinger missiles at stealth helicopters.
[…] Brent Ozar has a warning for us: […]
And it’s not just the FKs, is it? Unless I’m mistaken, the non-updating update will also lock the resources with stronger locking than needed (IX or U instead of S, can’t remember which right now). Can provide hours of fun for the whole family with deadlocks.
Yeah, and if you do cascading deletes, it gets even uglier: https://www.sqlpassion.at/archive/2014/01/21/myths-and-misconceptions-about-transaction-isolation-levels/
The chills down my spine at the words “cascading deletes”… brrrr
!!! @Alex – can you give me any hint where this is explained? I would need to understand it just because of the “fun with deadlocks”. I also left a question here: https://serverfault.com/questions/1019443/sql-server-are-shared-locks-used-for-foreign-key-constraint-enforcement-and-ho
H.M.
Well, any kind of stronger locking could lead to a higher possibility of deadlocks in a busy system, just because more stuff gets stuck waiting. As for which locks are taken in your scenario, I’d suggest to reproduce your test case and trace the locks to see what happens.
Thanks for the general reply – of course we do that. But what happens is the following: With small test cases, I see that FK checking acquires short-lived S row (key) locks – to be expected, and correct, and no problem, and no deadlocks. In our somewhat (not very) heavy production runs, however, we get S-locks on pages. Now, this contradicts all documentation by Microsoft and books by people like White, Machanik, Tripp, etc. as far as I know: All state that lock escalation goes from key to table in SQL server; so this cannot be the result of lock escalation. But where else does it come from?? Therefore I ask everyone I can hunt down to explain to me how locking in the presence of FKs actually works, down to the gory detail.
(And yes, I have about 3…5 and a half ideas what we can change to stump out the effects – but I’m not asking with my “solve the problem as cheaply as possible” hat, but with my “I actually want to understand what’s going on” hat – sometimes we need that, I’d say).
H.M.