strange optimizer

[Carlo Paccanoni]

I tried this:

use northwind
go

SELECT OrderDate
FROM Orders WHERE OrderDate > '19950101'

see the query plan? ok

SELECT OrderDate, EmployeeId
FROM Orders WHERE OrderDate > '19950101'

see the query plan? what appened?

the only way to make an index seek instead of an index scan is to
force the
index usage ( with(index=orderdate) ), but I don't like this solution

also try this:

SELECT *
FROM Orders WHERE employeeId > 9

and

SELECT *
FROM Orders WHERE employeeId > 8

Can someone explain why this appens? and how can I overturn the
performance loss problem (well not in orders table, but in my table
there are 300K records and making a scan to retrieve 50 records is not
exactly what I want)

thanks to all

[Gert-Jan Strik]

See inline

Carlo Paccanoni wrote:

I tried this:

use northwind
go

SELECT OrderDate
FROM Orders WHERE OrderDate > '19950101'

see the query plan? ok
I see an index seek on a nonclustered index on OrderDate.

In this case, this probably the fastest access method, because the index
is smaller than the table, and can satisfy the entire query (it is a
covering index).
SELECT OrderDate, EmployeeId
FROM Orders WHERE OrderDate > '19950101'

see the query plan? what appened?
I see a clustered index scan.

Most likely, SQL-Server has estimated that it takes less time to scan
the entire table than any other strategy. One of these other strategies
would be to seek all relevant rows in the nonclustered index on
OrderDate, then for each relevant row, seek the clustered index (which
potentially is a page read for each row).

When I ran the query on my Northwind, the clustered index scan strategy
used 22 logical reads. The nonclustered index + bookmark lookup strategy
used 1740 logical reads.
the only way to make an index seek instead of an index scan is to
force the
index usage ( with(index=orderdate) ), but I don't like this solution

also try this:

SELECT *
FROM Orders WHERE employeeId > 9
I see a nonclustered index seek and a bookmark lookup for each
qualifying row.
and

SELECT *
FROM Orders WHERE employeeId > 8
I see a clustered index scan.
Can someone explain why this appens?
It all has to do with data distribution. SQL-Server has statistics on
each indexed column. When I ran "DBCC SHOW_STATISTICS (Orders,
EmployeeID)" on my Northwind, it showed that there were 277 rows
sampled, and that the values of EmployeeID range from 1 to 9. Because of
that, SQL-Server can estimate that rows with EmployeeID 9 will only be a
small portion of the table.

The second query will result in more rows. Because of this, the scale is
tipped over. It is no longer fastest to do NC Index Seeks + BM Lookups.
and how can I overturn the
performance loss problem (well not in orders table, but in my table
there are 300K records and making a scan to retrieve 50 records is not
exactly what I want)
SQL-Server will not scan the table if it has more than 50 pages (410Kb)
if it can accurately estimate that your query will only return 50 rows
(and you have an appropriate nonclustered index).

Make sure the statistics of the index are up to date.

Hope this helps,
Gert-Jan
thanks to all

--
(Please reply only to the newsgroup)

[Carlo Paccanoni]

Thankyou Gert-Jan now it's more clear