HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Seeing 2 very different execution plans for an UPDATE between test & prod environments

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
seeingprodupdatedifferentexecutionbetweenfortestveryplans

Problem

I am seeing 2 very different query plans between test/prod for an UPDATE statement that is part of a stored procedure. Obviously table size differs (about 10 million rows difference). Basically the difference is in Prod I am seeing sets of an expensive Sort followed by an Index Update (nc index)

Whereas in Test, I am not seeing these sets of operators at all! Only the Clustered Index Update. I've verified the NC indexes exist etc. etc. I can't figure out what is going on?! I've verified indexes, the sp, I've tried RECOMPILE, different values for the param's etc. There's got to be something I'm missing, I've even checked constraints, everything matches.

Any thoughts ideas? I've never seen this before. Did SQL just grab a bad execution plan!?

The UPDATE is structured in a way: UPDATE [tablename] SET [column]=123 FROM ...

The extra operators in the plan from Prod below (minus Clustered Index Update):

Solution

Hover over the clustered index update, you'll see a list of the other indexes involved.

If you use Plan Explorer instead, you'll see the number of nonclustered indexes noted.

See Wide vs. Narrow Plans by Bart Duncan.

Context

StackExchange Database Administrators Q#237326, answer score: 9

Revisions (0)

No revisions yet.