December 16, 2010 at 6:53 pm
Paul White NZ (12/16/2010)
CREATE TABLE dbo.Example
(
row_id INTEGER IDENTITY NOT NULL PRIMARY KEY CLUSTERED,
data AS CONVERT(VARCHAR(11), row_id) PERSISTED NOT NULL UNIQUE
);
GO
INSERT dbo.Example (data)
DEFAULT VALUES;
GO 10
SELECT E.row_id, E.data
FROM dbo.Example E;
I think the reason that your example comes out in order is because the data is not causing a page split (8K) nor a re-allocation.
try this
CREATE TABLE [dbo].[Example](
[row_id] [int] IDENTITY(1,1) NOT NULL,
[data] AS (CONVERT([varchar](11),[row_id],(0))) PERSISTED,
[MoreData] AS (newid()),
CONSTRAINT [PK__Example__6965AB572B3F6F97] PRIMARY KEY CLUSTERED
(
[row_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UQ__Example__D9DE21E12E1BDC42] UNIQUE NONCLUSTERED
(
[data] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
on the above table
run
INSERT dbo.Example (data)
DEFAULT VALUES;
GO 100-- 0000
SELECT E.row_id, E.data,E.moredata
FROM dbo.Example E;
and have a look at the ordering received now insert the order by Row_id and have a look at the difference. Clustered Index is still Row ID but the retrived order is different. The first is a non clustered index scan and the second (Order by Row_ID) is a Clustered index Scan. Clearly showing retrieve order is indepndant of the Inserted order of data using a clustered index.
Hope that helps
CodeOn
😛
December 16, 2010 at 7:16 pm
Paul White NZ (12/15/2010)
Perhaps it would be nice if SQL Server printed a warning message in the second case. My understanding is that this is technically impossible to do for every case in practice, so they decided not to bother.Paul
Then your understanding is wrong, whatever MS says about it. It's not technically impossible, just somewhat difficult.
At a naive level, one could say that if it is technically possible for the optimizer to decide that it will not do the sort, then it is technically possible to determine that the optimizer will reach that conclusion. At a less naive level, I will sat that it is possible in a high level analysis to determine whether a given 'order by' relates either to a count-constrained selection or to the outermost select in a query (at least it certainly was in 1991; I don't think anything added to SQL since then could have changed that, but I may be wrong). What is probably true is that to do it in the current syntax analyser in SQLServer would be very difficult, involving a change in the whole philosophy of the thing - the current syntax analyser isn't at all into semantics, and this is of course semantics.
Tom
December 16, 2010 at 7:40 pm
Tom.Thomson (12/16/2010)
Paul White NZ (12/15/2010)
Perhaps it would be nice if SQL Server printed a warning message in the second case. My understanding is that this is technically impossible to do for every case in practice, so they decided not to bother.Then your understanding is wrong, whatever MS says about it. It's not technically impossible, just somewhat difficult.
Ok. It was impractical to do. Better?
December 16, 2010 at 7:48 pm
Malcolm Daughtree (12/16/2010)
I think the reason that your example comes out in order is because the data is not causing a page split (8K) nor a re-allocation.
The point of the demo is the rows don't come back in clustered index order - look where row_id 10 appears in the output 😛
December 16, 2010 at 7:52 pm
Craig Farrell (12/16/2010)
I'm trying to force an IAM defrag that will cause this. I've forced some fragging, but I'm still getting logical read orders. Get back to you. 🙂
http://www.sqlmag.com/article/sql-server/quaere-verum-clustered-index-scans-part-i.aspx
http://www.sqlmag.com/article/sql-server/quaere-verum-clustered-index-scans-part-ii.aspx
http://www.sqlmag.com/article/sql-server/quaere-verum-clustered-index-scans-part-iii.aspx
http://sqlblog.com/blogs/paul_white/archive/2010/09/23/a-tale-of-two-index-hints.aspx
December 16, 2010 at 8:11 pm
Craig Farrell (12/16/2010)
Thanks Paul... a little adaptation for the 2k5 amongst us:
I hadn't noticed that 2005 objected...the fix is simple though, just remove the pointless (data) column reference:
INSERT dbo.Example (data) --> INSERT dbo.Example
For some reason the 2005 parser objects to that with DEFAULT VALUES. Never noticed that before.
December 16, 2010 at 9:07 pm
Craig Farrell (12/16/2010)
Paul White NZ (12/16/2010)
CREATE TABLE dbo.Example
(
row_id INTEGER IDENTITY NOT NULL PRIMARY KEY CLUSTERED,
data AS CONVERT(VARCHAR(11), row_id) PERSISTED NOT NULL UNIQUE
);
GO
INSERT dbo.Example (data)
DEFAULT VALUES;
GO 10
SELECT E.row_id, E.data
FROM dbo.Example E;
Thanks Paul... a little adaptation for the 2k5 amongst us:
--drop table dbo.Example
CREATE TABLE dbo.Example
(
row_id INTEGER IDENTITY NOT NULL PRIMARY KEY CLUSTERED,
data VARCHAR(40) NOT NULL UNIQUE
);
GO
INSERT dbo.Example (data) VALUES (NEWID())
GO 10
SELECT E.row_id, E.data
FROM dbo.Example E;
And that produces the unordered mess. However, it's pulling from the non-clustered index. If I swap it to this:
SELECT E.row_id, E.dataFROM dbo.Example E WITH (INDEX (1));
I get clustered indexing order, as you would expect. You wouldn't know it though unless you looked at the execution plan and figured out why... so I understand what you're showing here, Paul, thank you.
@Wayne: Yes, pretty sure that was a single core, will check tomorrow to confirm when I'm back in the office.
I'm trying to force an IAM defrag that will cause this. I've forced some fragging, but I'm still getting logical read orders. Get back to you. 🙂
One thing I noticed is that if you don't have the Unique constraint, then it produces (this limited result set) in the CI order.
@paul-2 (Mr. Knows_How_The_Optimizer_Works_In_Excruciating_Detail :-D) - is it possible that the query is doing parallelism on a CI Scan without the actual execution plan indicating it? All I see is a CI Scan and a Select.
@Craig: If you have access to a multi-core processor server, can you try that code on it also? I'd really like to know why it is in order for you, but not for MM and myself.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 16, 2010 at 9:33 pm
WayneS (12/16/2010)
One thing I noticed is that if you don't have the Unique constraint, then it produces (this limited result set) in the CI order.
That's the point. The index that enforces the unique constraint is covering for the query, since all non-clustered indexes include the cluster key. The optimizer will almost always choose a covering non-clustered index scan over a clustered scan, even where the estimated cost is the same. Of course, the fact that the rows come back in non-clustered index order isn't guaranteed, for exactly the same reasons, but it happens to do so in this case.
@Paul (Mr. Knows_How_The_Optimizer_Works_In_Excruciating_Detail :-D) - is it possible that the query is doing parallelism on a CI Scan without the actual execution plan indicating it? All I see is a CI Scan and a Select.
No, absolutely not. If SQL Server uses parallelism, you'll always see it in the actual plan. Interestingly, there are cases where an apparently parallel plan executes serially, see http://sqlblog.com/blogs/paul_white/archive/2010/11/04/myth-sql-server-caches-a-serial-plan-with-every-parallel-plan.aspx
The INDEX(1) forces an ordered scan of the clustered index, that's all. This still does not guarantee that the final output will be in clustered index order, of course. For more on INDEX(1) and ordered scans, see http://sqlblog.com/blogs/paul_white/archive/2010/09/23/a-tale-of-two-index-hints.aspx
December 17, 2010 at 12:59 am
On the original subject of this thread, this is what Books Online says:
"SQL Server 2008 Books Online (August 2008)
Sorting Rows with ORDER BY
...ORDER BY guarantees a sorted result only for the outermost SELECT statement of a query. For example, consider the following view definition:
CREATE VIEW TopView AS
SELECT TOP 50 PERCENT * FROM Person.Contact
ORDER BY LastName
Then query the view:
SELECT * FROM TopView
Although the view definition contains an ORDER BY clause, that ORDER BY clause is used only to determine the rows returned by the TOP clause. When querying the view itself, SQL Server does not guarantee the results will be ordered, unless you specify so explicitly, as shown in the following query:
SELECT * FROM TopView
ORDER BY LastName"
As for the whole discussion that I ignited about "Select * from MyTable" not guaranteed to return rows in clustered index order, I don't know of a particular source, but I am fairly sure there is one. I have been able to demo it a number of times on different versions of SQL Server, and a single case is enough to prove the point. If you want your data in a particular order, use an ORDER BY.
There are people that insist this is not true. See the link below for a discussion on this subject that turned really nasty when I pointed this out. "IncisiveOne" just didn't appreciate being told he was wrong. :crazy:
Moving away from a cursor to while loop
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=131674
Thinking back, "IncisiveOne" was a lot of fun. This thread is a classic example of someone unable to stand getting called on their BS:
December 17, 2010 at 5:50 am
Paul White NZ (12/16/2010)
Ok. It was impractical to do. Better?
yes, that's it exactly.
Tom
December 17, 2010 at 6:55 am
Paul White NZ (12/16/2010)
http://www.sqlmag.com/article/sql-server/quaere-verum-clustered-index-scans-part-i.aspxhttp://www.sqlmag.com/article/sql-server/quaere-verum-clustered-index-scans-part-ii.aspx
http://www.sqlmag.com/article/sql-server/quaere-verum-clustered-index-scans-part-iii.aspx
http://sqlblog.com/blogs/paul_white/archive/2010/09/23/a-tale-of-two-index-hints.aspx
That's a wonderful set of stuff. Thanks, Paul.
Tom
December 17, 2010 at 2:06 pm
To add to Pauls links, here is a link of links.. 😉
http://sqlserverpedia.com/blog/sql-server-bloggers/merry-go-round-scans/
December 17, 2010 at 3:21 pm
<--------------------------- This look on my face is getting to be a habit these past two weeks...
Paul White NZ (12/16/2010)
Craig Farrell (12/16/2010)
I'm trying to force an IAM defrag that will cause this. I've forced some fragging, but I'm still getting logical read orders. Get back to you. 🙂http://www.sqlmag.com/article/sql-server/quaere-verum-clustered-index-scans-part-i.aspx
http://www.sqlmag.com/article/sql-server/quaere-verum-clustered-index-scans-part-ii.aspx
http://www.sqlmag.com/article/sql-server/quaere-verum-clustered-index-scans-part-iii.aspx
http://sqlblog.com/blogs/paul_white/archive/2010/09/23/a-tale-of-two-index-hints.aspx
Okay, that explains why I couldn't get it to go to IAM order, my tables were too small (I was mucking around at around 20-30 pages, trying to keep it reasonable to track). The jawdrop was the NOLOCK item. I knew about the uncommitted rows and the like, but once again, the final implication of the IAM read for NOLOCK... gyeah! Losing the scan position?! :sick:
* Be aware of the implications of reading data with the NOLOCK hint (or in a read uncommitted isolation). It’s not just a matter of reading uncommitted changes, or data in an intermediate state of the transaction; rather you might even get the same row twice, or SQL Server might lose the scan position during the scan.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 17, 2010 at 5:13 pm
Lamprey13 (12/17/2010)
To add to Pauls links, here is a link of links.. 😉http://sqlserverpedia.com/blog/sql-server-bloggers/merry-go-round-scans/
My problem with the Advanced Scan feature is that, like Gail, I have been unable to reproduce it. Ever.
One thing that I haven't seen mentioned anywhere is that it only applies to unordered scans.
If the scan operation shows Ordered:True, this feature won't be used, for reasons which should be obvious.
December 17, 2010 at 5:25 pm
Craig Farrell (12/17/2010)
<--------------------------- This look on my face is getting to be a habit these past two weeks...
Cool avatar, much better than the last one IMVHO.
Okay, that explains why I couldn't get it to go to IAM order, my tables were too small (I was mucking around at around 20-30 pages, trying to keep it reasonable to track). The jawdrop was the NOLOCK item. I knew about the uncommitted rows and the like, but once again, the final implication of the IAM read for NOLOCK... gyeah! Losing the scan position?!
Yeah it can happen, but it is pretty rare in real life (or at least it has been for me).
Read Committed (the default) isn't much better. You won't lose scan position, but skipping records and double-reads are perfectly possible. Using a row-versioning isolation level (most usually read committed snapshot) is a huge win for this, and many other things too.
Viewing 15 posts - 46 through 60 (of 62 total)
You must be logged in to reply to this topic. Login to reply