Using the publicly available Stack Overflow database (the 100GB 2016/03 version), here’s an execution plan for an update statement:
It’s a pretty simple query – it just scans the Posts table looking for any post with PostTypeId = 8, and then updates a single field for the first 250 rows it finds.
And here’s another execution plan for the exact same query – except it updates 251 rows instead of 250:
Whoa – that’s totally different!
I’m not changing the table, the number of rows in it, or the number of indexes each time.
The problem is that we’re updating the AcceptedAnswerId field, and we’ve got a whole bunch of indexes with that field as part of their key or their includes. (For the repro script, check out Erik’s Github issue. More on that in a second.)
SQL Server has to update these indexes, and it’s easy to see the work involved in the wide plan. In the narrow plan, the indexes are still there – but you have to hover your mouse over the Clustered Index Update operator to see them. (Yes, that actually works in PasteThePlan – go see.)
When you’re doing performance tuning, you even have to pay attention to delete, update, and insert (DUI) operations to understand how many indexes are affected and how much work is required.
But in the timeless words of the philosophers, ain’t nobody got time for that.
So that’s why Erik came up with the idea to add an sp_BlitzCache warning about the number of indexes affected by an operation. The differences between the narrow and the wide plans also mean it was harder to code than you might think at first glance, and you can learn a lot about SQL Server plans just by reading that Github issue.
End result – check out the Warnings column:
Ta-da! That’ll be in the January release of the First Responder Kit, or if you’re ambitious, you can go visit the dev branch (zip file direct link) now.
