optimizer decision question - 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

SQL Server Administration (2000)

optimizer decision question

Author

Topic

rubs_65
Posting Yak Master

144 Posts

Posted - 2005-02-08 : 09:37:40

Hi,

We have a query on single table like following:
select sum(isnull(colE,0)), sum(isnull(ColF,0))
from tab1
where colF is null and colA = 187 and ColB = 2515
We have a non clustered index on (ColA,ColB,ColC) and clustered index on identity column ColG.....
Somehow the optimizer is not using index while exectuting query and is taking about 7sec to execute whereas if we put index hint it only takes <1sec. We recreate the index and update statistics with fullscan on table but still optimizer is not using index...table have about 6.4M rows.....

Output from statistics time and statistics IO is following:
Without index(optimizer is choosing this) (also cost of plan is 97)
SQL Server Execution Times:
CPU time = 2094 ms, elapsed time = 7202 ms.
Table 't_acc_usage'. Scan count 2, logical reads 228120, physical reads 46, read-ahead reads 1911.

With index(force index hint)(cost of plan is 316)
Table 't_acc_usage'. Scan count 2, logical reads 228120, physical reads 46, read-ahead reads 1911.
SQL Server Execution Times:
CPU time = 1000 ms, elapsed time = 642 ms.

It is clear that logical scans and cost is less for index scan that optimizer is choosing but in fact in reality index seek response time is less.

How Optimizer calculates the cost and what can be possible reasons that optimizer is not using index seek?

Note: We run the dbcc dropcleanbuffers and dbcc freeproccahe before every run and this result is reproducible on 2-3 machines that we copy database to

Thanks
--Harvinder

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-02-08 : 13:06:17

I've always been told to put things in the where clause in the same order that my index is.


select sum(isnull(colE,0)), sum(isnull(ColF,0)) 
from tab1
where colA = 187 
and ColB = 2515
and colF is null

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

rubs_65
Posting Yak Master

144 Posts

Posted - 2005-02-08 : 13:22:40

I tried that but didn't helped. Moreover this trick might be true for oracle databases version <7.3.4 but lately optimizer for all databases look very smart to notice all this stuff....

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-02-08 : 20:54:46

Two potentially completely unrelated points...but maybe not.

1) sum(isnull(colE,0)) - There is no need to use IsNull (or Coalesce) since the aggregate function SUM will ignore Null values anyway. The use of the function could be causing the optimizer to jetison the use of the index.

2) sum(isnull(ColF,0)) <==> 0 since ColF is limited to Null in the WHERE clause

HTH

=================================================================
Egotism is the anesthetic that dulls the pain of stupidity. -Frank William Leahy, football coach (1908-1973)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources