Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456

SELECT TOP Expand / Collapse
Author
Message
Posted Monday, July 16, 2012 7:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, July 12, 2014 12:18 AM
Points: 2,063, Visits: 519
Hugo Kornelis (2/17/2012)
neprosto (2/17/2012)
Result of order depend on method to get result
1. Index seek
in this case relation engine use ROOT PAGE to start search. And result will be sort
Little trick for force seek use hint (ForceSEEK) and add search predicate in WHERE clause


This is only partially true.
Since this is undocumented, there is no way to guarantee this even if it is true today. And while it is true today under most circumstances, it isn't always true. I don't have the time to repro this now, but I think that if the table is sufficiently large, then both partitioning the table and getting a parallel execution plan could change the order of rows.

2. Index scan
If index scan is used then relation engine get leaf level pages in order that is stored in Index Allocation Map This order may be any ;) and depend on free pages allocation at the moment of creation index


And this is definitely not correct. An IAM scan will only be used if the following conditions are met:
1) The query optimizer must specify an unordered scan - that is, the query optimizer is not interested in the order of the rows after the scan operator. This will not be the case if the optimizer has to guarantee some order (for an ORDER BY, GROUP BY, merge join operator, or other operator or query ingredient that requires ordered rows) and can spare a sort operator by scanning rows in order.
2) The query must be processed with either no locks at all or locks at the table level.
Since SQL Server defaults to row-level locks, IAM scans are very rare in practice; most index scans are still in index order - though, again, not guaranteed!

And also, on Enterprise Edition, Data Center Edition, and Developer Edition, the engine has the ability to do "piggy back" scans - if a scan is already in progress when a new (unordered) scan is required, it will start consuming rows from that scan already in process and then restart from the start until where it started piggy-backing.


Finally, please remember that none of the above behaviours are documented as "future proof". All of this may change - not only in a future version, but also in a service pack, CU or even in a hotfix. The only way to get results in an order you can depend upon is to use that ORDER BY clause. (And the extra benefit is that it also documents your code better). In cases where the normal access method would result in that order anyway, it won't even cost you anything extra, as the optimizer will not add an unneccessary sort step.


Thank you for comment!
First of all i agree, the index scan trough IAM page(s) occurs when table lock level is applied. But I want to add NOLOCK/Dirty read also cause the same situation

It force read leaf level page not in order that they are linked. I just check it via debug each page and links - it is still truth.

But if I use fake predicate in where clause on any column that is in index, it forces engine use scan via root page and record appear in sorted order. It still valid when table is partitioned and plan is parallel. Just check on table with 6 section and six million of rows.
I did not find any examples refute this.

I know and remember - it is not documented, and there is not guaranties, but i think it very important to understand why records my appear in another order and how it order does. The key concept - SQL uses IAM pages to begin scan, not root page.

PS Sorry for my English level ;)


MCITP x 3
MCSE x 1
Post #1330106
« Prev Topic | Next Topic »

Add to briefcase «««23456

Permissions Expand / Collapse