August 23, 2012 at 9:27 pm
GSquared (8/22/2012)
Based on that, I'll just pipe in with, "How dare you speak about those people that way! They've suffered in ways you can't possibly imagine! You are an evil, evil person!"
Last argument of MVP's
:hehe:
_____________
Code for TallyGenerator
August 23, 2012 at 11:39 pm
Sergiy (8/23/2012)
OK, I'm really curious what's going on behind the scene.
Let me explain. In an update query, the optimizer considers sorting the incoming rows into index order. Sorting into key order tends to result in more sequential access to the index, but there may be a cost involved in sorting. In deciding whether to sort of not, the optimizer weighs up the pros and cons and makes a cost-based decision
You can tell if the optimizer decided a sort was worthwhile or not by checking the properties of the update operator. If you see [DMLRequestSort] = True, the optimizer decided sorting was worth doing. Of course, you may or may not see an *explicit* sort in the plan. If the optimizer finds a plan that happens to produce rows in the desired order naturally, it can use that plan without a sort.
So, whether [DMLRequestSort] appears or not depends on a detailed calculation that I won't go into, expect to say that you are generally more likely to see it appear as the number of rows to be updated becomes larger.
Second point is that before SQL Server 2012, the optimizer always produced a plan as if an identity column existed. As Gail mentioned, ORDER BY in this context has only ever guaranteed that the order of assignment of identity values would match the ORDER BY clause. SQL Server 2012 contains an improvement so that the plan only has the shape required by the identity-assignment guarantee if the table actually does contain a column with the identity property.
Viewing 2 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply