Tall Tales From Table Variables

Execution Plans, Indexing
1 Comment

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…

We get this plan…

itsy bitsy teenie weenie LIAR

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…

We get a much more honest plan…

Best Policy

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.

Previous Post
It’s Time to Improve DBCC CHECKDB.
Next Post
DBAs Need a Jump Box or Jump Server.

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)

    Reply

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.

Menu