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 ...