Indexes not used - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Indexes not used

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-02-18 : 23:51:17

Richard writes "I have 2 queries, each within a single batch:

Query 1:

select *
from table1
where status = 4

Query 2:

declare @intstatus
set @intstatus = 4
select *
from table1
where status = @intstatus

There is a non-clustered index on column 'status'.
Both queries return the same results but only query 1 uses the index.

Any ideas why ?

Richard George"

Bambola
Posting Yak Master

103 Posts

Posted - 2003-02-19 : 09:34:05

Is there some other difference? Is your select statement different?
would you show us some code?

I remember encountering some strange behavor like this. 2 select statment that are almost equal (WITH is missing in the second and that's all) were using different index.

select column from table with (nolock)
select column from table (nolock)

Bambola.

ClayG
Starting Member

13 Posts

Posted - 2003-03-04 : 01:12:04

This can occur if the distribution of data against the key values if non-uniform. For example, perhaps 80% of data is status 1, 10% status 2, 9% "3", 1% "4".

When you supply the value "4" explicitly, the optimiser can look at the data distribution and see there is little data for "4", so use an index. With a variable, the optimiser compiles the whole batch before the variable is assigned, so it can only look at the average selection ratio, which with 4 values, is 25%. With an estimate of 25%, the optimiser will not use an index.

Solutions: either query hints, or make dynamic SQL. Try "OPTION (FAST n)" on the end of the query - may force index use, less problematic than "WITH (INDEX=i)".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources