Some Plans Are Wider Than Others

Execution Plans, SQL Server

Using the publicly available Stack Overflow database (the 100GB 2016/03 version), here’s an execution plan for an update statement:

The Narrow Plan (click to see PasteThePlan details)

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:

The Wide Plan (click to see PasteThePlan details)

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:

sp_BlitzCache index warnings for DUI operations

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.

Previous Post
Build Me A Build: What Would You Do?
Next Post
Your 10 Favorite Blog Posts of 2016

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.