November 13, 2011 at 1:33 pm
Hi Karl,
The plan ''QueryWithoutStatusRankInSelect" does indeed have an ANY Stream Aggregate; the "QueryWithStatusRankInSelect" does not (it has the row numbering Segment and Sequence Projects).
On the face of it, this behaviour seems like an unsafe optimizer rewrite (i.e. a bug) because optimizer transformations should not affect the semantic. If you'd like me to validate that and perhaps offer a more detailed explanation, I would need a script for the tables involved, including all the constraints (both FOREIGN KEY and CHECK) and indexes defined on them. You've already provided the view definition, so I don't need that.
The reason updating statistics resolves the problem is that it forces a recompilation of query plans that used the pre-update statistics. Why recompiling produces a plan with a different semantic is a deeper question, and one I need the information mentioned above to debug.
There have been a significant number of fixes since SQL Server 2008 RTM, so it will be interesting to see if this issue reproduces on a more current version.
Glad you found the ANY article interesting.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 13, 2011 at 1:58 pm
SQL Kiwi (11/13/2011)
Hi Karl,On the face of it, this behaviour seems like an unsafe optimizer rewrite (i.e. a bug) because optimizer transformations should not affect the semantic. If you'd like me to validate that and perhaps offer a more detailed explanation, I would need a script for the tables involved, including all the constraints (both FOREIGN KEY and CHECK) and indexes defined on them.
It will probably be a while before I can post that, but I'll make myself a note.
Why recompiling produces a plan with a different semantic is a deeper question, and one I need the information mentioned above to debug.
There have been a significant number of fixes since SQL Server 2008 RTM, so it will be interesting to see if this issue reproduces on a more current version.
I can get a the most recent build running and try it there.
Glad you found the ANY article interesting.
It seems like there is always another level to dig into in SQL Server and looking how specific optimizations are applied certainly qualifies.
Again thanks to both Paul and Ninja's_RGR'us for help.
Karl
November 13, 2011 at 2:16 pm
One more thing, Karl, you should run DBCC CHECKCONSTRAINTS on the tables involved in that query (I'm not sure if you run DBCC CHECKDB regularly - or if that includes CHECKCONSTRAINTS). If there is data in any table that violates a CHECK or FOREIGN KEY constraint, that would be a reason for the odd results: the optimizer might be relying on a constraint to ensure a transformation is safe.
edit: CHECKDB does not include CHECKCONSTRAINTS. Vote here: http://connect.microsoft.com/SQLServer/feedback/details/508837/option-to-check-constraints-in-dbcc-checkdb if you agree that it should!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply