Hi Itzik,

Further to my earlier reply - here are some timings (T1 w/ 1,000,000 rows, col2 'pseudorandomizedish' - SQL Server w/ 768MB RAM, testdb and tempdb on separate disks).

Original query: 29s.
Query with index hint: 29s.
FASTFIRSTROW query: 230s. (!)

The problem here, I believe, is that unless you've shelled out for SSD disks, there'll be a lot of random I/O as SQL Server loads in the pages from the clustered index in the order dictated by the non-clustered index on col2. The first two queries result in more-or-less sequential disk access. This is borne out by the thrashing sound my PC makes when the third query is being run!

I guess in the future (if it doesn't already), SQL Server will need to be able to tune itself to different disk types (conventional vs. SSD) - as the performance characteristics of the different technologies make query optimization choices dependant on the hardware.

[Reply:
Hi Will. Many thanks for your comments! Please see my response in the following blog entry: http://www.sqlmag.com/article/articleid/101784/sql_server_blog_101784.html.

Cheers,
Itzik]

William3/26/2009 5:12:11 PM

The assumption here (and correct me if I'm wrong) is that the values in col2 are 'more or less ordered' to start with - I took a look at the values and they're (col1 + 1). If col2 were a timestamp column, and there are many less updates that inserts then this'd be fine - but try e.g.:

UPDATE dbo.T1
SET Col2 = (CAST(Col2 AS BIGINT) * Col2) % 1000000

After this (and some judicious use of DBCC DROPCLEANBUFFERS), the 'SQL Server optimal' plan does outperform the alternatives presented.

I guess that if SQL Server 'knew' that the values in a column were more or less sorted to begin with, it might to switch to a different plan.

William3/26/2009 2:53:32 AM

Hi Itzik,

I tried all your examples and notice something interesting. I have a 2-processor machine. When I tried to run the original queries I got parallelized query plans. Then, I forced the queries to run in 1 processor and some queries ran faster than using the default parallel approach. That is the first time I noticed that parallelism is not always good in SQL Server. This could be related to the cost of the "Parallelism (gather streams)" operator. Here are the queries I ran in order to test what I said:

DBCC DROPCLEANBUFFERS;
-- Parallel = 19 secs
SELECT * FROM dbo.T1
ORDER BY col2;
-- 1-processor = 11 secs
SELECT * FROM dbo.T1
ORDER BY col2 OPTION(MAXDOP 1);

DBCC DROPCLEANBUFFERS;
-- Parallel = 16 secs
SELECT * FROM dbo.T1 WITH
(index = idx_nc_col2)
ORDER BY col2;

-- 1-processor = 18 secs
SELECT * FROM dbo.T1 WITH
(index = idx_nc_col2)
ORDER BY col2 OPTION(MAXDOP 1);


DBCC DROPCLEANBUFFERS;
-- This query always runs in 1-processor ignoring the query hint = 6 secs
SELECT * FROM dbo.T1 WITH
(FASTFIRSTROW)
ORDER BY col2 ;

[Itzik: Keep in mind that the cost based optimizer is based on cost estimations. Naturally there’s a level of inaccuracy that should be expected from estimations; not to speak of the fact that on different hardware there are different ratios between the strengths of the various hardware components. I’d relate your observations from your tests to this. We can’t expect the optimizer to always come up with the most optimal possible plan in 100% accuracy; today’s available resources would require too complex and too long of an optimization process.]

John Alexander4/12/2007 7:26:10 AM

the CTE and function to generate sequential numbers was very helpful. The rest of the article was helpful as well:)

john2/4/2007 2:42:15 PM