Secret Squirrel
When you modify a table with multiple indexes, SQL Server may choose either a narrow plan, if it doesn’t think all that many rows are going to change, or a wide plan if it thinks many will.
In narrow plans, the work SQL Server has to do to modify many indexes is hidden from you. However, these plan choices are prone to the same issues with estimates that any other plan choices are. During a conversation about when temp tables or table variables are appropriate, it came up that table variables are better for modification queries, because not all the indexes had to be updated at once.
When we looked at the plan together, we all had a good laugh and no one wept into their lumbar supports.
Pantsburner
I created some nonclustered indexes on the Posts table that all had the Score column in them, somewhere. Without them, there wouldn’t be much of a story.
When we use this query to update…
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
BEGIN TRAN DECLARE @bad_idea TABLE (id INT NOT NULL) INSERT @bad_idea ( id ) SELECT TOP 1 u.Id FROM dbo.Users AS u ORDER BY u.Reputation DESC UPDATE p SET p.Score += 1000 FROM dbo.Posts AS p JOIN @bad_idea AS bi ON bi.id = p.OwnerUserId --ROLLBACK |
We get this plan…
If you’re playing along at home, the single row estimate that comes out of the Hash Match persists along the plan path right up to the Clustered Index Update.
Since a one row modification likely won’t qualify for a per-index update, all of the updated objects are stashed away behind the Clustered Index Update.
It’s a cover up, Scully. This one goes all the way up the plan tree.
Crackdown
Swapping our table variable out, and running this query…
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
BEGIN TRAN CREATE TABLE #better_idea (id INT NOT NULL) INSERT #better_idea ( id ) SELECT TOP 1 u.Id FROM dbo.Users AS u ORDER BY u.Reputation DESC UPDATE p SET p.Score += 1000 FROM dbo.Posts AS p JOIN #better_idea AS bi ON bi.id = p.OwnerUserId --ROLLBACK |
We get a much more honest plan…
The estimates are accurate, so the optimizer chooses the wide plan.
I can see why this would scare some people, and they’d want to use the table variable.
The thing is, they both have to do the same amount of work.
Warnings
If you use our First Responder Kit, you may see warnings from sp_BlitzCache about plans that modify > 5 indexes. In sp_BlitzIndex, we warn about this in a bunch of different ways. Aggressive locking, unused indexes, indexes with a poor read to write ratio, tables with > 7 indexes, etc.
You can validate locking issues by sp_BlitzFirst and looking at your wait stats. If you see lots of LCK_ waits piling up, you’ve got some work to do, and I don’t mean adding NOLOCK to all your queries.
1 Comment. Leave new
Nice article, but I have some open questions:
– Will the wider plan spool only once or n times (once for every affected index as the plan seems to say)?
– Why has it to filter every spool table (I understand the sort to allow a “MERGE INDEX UPDATE”, but as long not every index has a WHERE condition…)
– since it uses a HASH JOIN in the first query (@bad_idea) too, should it not be the same costas (regardless of the wrong estimates)? And why does it a HASH JOIN / CLUSTERED INDEX SCAN instead of Nested Lookup when it “guess” that there will be only one row affected?
– what is the reason for doing / showing us narrow / wider plans. When they both do the same work, why exists two different plans (and is the wider plan faster than the narrow)