SELECT TOP
-
neprosto (2/17/2012)
Result of order depend on method to get result1. 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.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Hugo Kornelis (2/17/2012)
Since this is undocumented, there is no way to guarantee this even if it is true today.It isn't true and there are many ways to disprove it. This is a conveniently simple example:
USE tempdb
GO
CREATE TABLE dbo.Example
(
data integer NOT NULL,
padding character(8000) NOT NULL DEFAULT ''
);
GO
SET NOCOUNT ON;
GO
INSERT dbo.Example
(data)
SELECT v.number
FROM master.dbo.spt_values AS v
WHERE v.[type] = N'P'
AND v.number BETWEEN 1 AND 50;
CREATE UNIQUE INDEX c
ON dbo.Example (data);
GO
CHECKPOINT; DBCC DROPCLEANBUFFERS;
GO
SELECT TOP (10)
e.data, e.padding
FROM dbo.Example AS e WITH (FORCESEEK)
WHERE
e.data > 0;
GO
DROP TABLE dbo.Example;
Sample results:
Query plan:
-
SQL Kiwi (2/17/2012)
Hugo Kornelis (2/17/2012)
Since this is undocumented, there is no way to guarantee this even if it is true today.It isn't true and there are many ways to disprove it. This is a conveniently simple example:
Wow! Thanks.
Can you explain what's going on? I played around a bit with the code. Adding an ORDER BY changes the results, of course - but the execution plan remains the same (or, if there are differences, they are hidden in a place where I haven't looked).
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Hugo Kornelis (2/17/2012)
Can you explain what's going on? I played around a bit with the code. Adding an ORDER BY changes the results, of course - but the execution plan remains the same (or, if there are differences, they are hidden in a place where I haven't looked).Without an ORDER BY clause, the nested loops join has a WithUnorderedPrefetch attribute: pages needed for the RID lookup are fetched asynchronously (similar to read-ahead) and processed in whatever order the I/Os happen to complete (loosely speaking). With an ORDER BY clause, the loops join is required to preserve the order of its outer input, so the prefetch hint is WithOrderedPrefetch.
I should add that no-one should infer any guarantees about row ordering from the above information. The optimizer and query processor co-operate under different circumstances to ensure that any required guarantees are in fact honoured at run time. These guarantees are deep internal details, and are not visible to us in show plan (even the xml form). My comment about the loops join 'preserving' sort order on its outer input does *not* mean the 'seek result' is *always* ordered.
-
SQL Kiwi (2/17/2012)
Without an ORDER BY clause, the nested loops join has a WithUnorderedPrefetch attribute: pages needed for the RID lookup are fetched asynchronously (similar to read-ahead) and processed in whatever order the I/Os happen to complete (loosely speaking). With an ORDER BY clause, the loops join is required to preserve the order of its outer input, so the prefetch hint is WithOrderedPrefetch.I should add that no-one should infer any guarantees about row ordering from the above information. The optimizer and query processor co-operate under different circumstances to ensure that any required guarantees are in fact honoured at run time. These guarantees are deep internal details, and are not visible to us in show plan (even the xml form). My comment about the loops join 'preserving' sort order on its outer input does *not* mean the 'seek result' is *always* ordered.
Truly fascinating stuff. Thanks, Paul!
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Hugo Kornelis (2/17/2012)
neprosto (2/17/2012)
Result of order depend on method to get result1. 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 😉
MS SQL 2008 MCITP x 3
MS SQL 2012 MCSE x 2
Viewing 6 posts - 46 through 50 (of 50 total)
You must be logged in to reply to this topic. Login to reply