4

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): enter image description here

10

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

NUTS

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

NUTS

See Wide vs. Narrow Plans by Bart Duncan.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.