Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL Server 2005 - "Select" cannot use optimum index

Posted on 2008-10-29
8
864 Views
Last Modified: 2008-11-04
I am using Microsoft SQL Server 2005.
There is a datetime column, sales_date, in the table sales.
I have created an index in the table: -
create index idx_sales on sales(sales_date)

When I issue the follow SQL: -
select * from sales where sales_date = '20081030'
The result prompt out immediately.  I figure out from the Execution Plan that it has used the correct index, idx_sales.

When I issue the follow SQL: -
select * from sales where sales_date between '20080801' and '20081030'
The result prompt out immediately, too.  The correct index, idx_sales is used.

However, when I issue the follow SQL: -
select * from sales where sales_date between '20080501' and '20081030'
(Any date range  more than two months)
The result come out very very slowly.  From the Execution Plan, the index of the primary key has been used.  (It means that "table scan" has been performed).

The table has been set as "IsAutoCreateStatistics" and "IsAutoUpdateStatistics".
So, why the correct index cannot be used?

Thx!

0
Comment
Question by:roland_lei
8 Comments
 
LVL 18

Expert Comment

by:jmoss111
ID: 22838244
How is the index ordered? I've found  that ordering by date descending makes a bit of difference on large tables.

Are all the columns really necessary?
0
 

Author Comment

by:roland_lei
ID: 22838442
The order of the index is decending.

All other columns are necessary.  Do other columns affect the correct selecting of index?
0
 
LVL 32

Expert Comment

by:bhess1
ID: 22838457
You have two options:

1)  Force the index (using index hints)

2)  Try alternate forms of the query, such as:

select * from sales where sales_date >= '20080501' and sales_date <= '20081030'

There can be quirks in the query optimizer that incorrectly affects the query plan.  Try 2 first, but if it does not work, try 1.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22838468
please try to rebuild the index, or defrag the index, or update the stats on the index...
0
 

Author Comment

by:roland_lei
ID: 22838495
Rewriting as
select * from sales where sales_date >= '20080501' and sales_date <= '20081030
will have no change.

And I would not like to force the index because couple of programs should be amended.

I have dropped the index and recreate it and then update the statistics.  There is no change, too.
0
 
LVL 32

Expert Comment

by:bhess1
ID: 22841244
Sometimes the query analyzer simply fails to optimize correctly.  Do not assume that rewriting the query to use a different format will do nothing, as I have shown in my own queries that it can (but will not always, or even frequently) correct issues with bad query plans.

What is happening in this case is that the query analyzer is deciding that, with the number of records you are selecting, the end speed *should* be faster using a pk / clustered index (is the primary key clustered?) scan than it would be by using the index.

If you test by forcing the index, does the resulting query return more quickly than without in the poorly=performing case?  Or does it simply return the first rows faster, thus seeming more responsive, but take longer or more resources to complete the full query?
0
 

Author Comment

by:roland_lei
ID: 22850550
I have done two tests with different date ranges.

1st (shorter date range)
The time when running the full query with forcing index is 19 seconds.
The time when running the full query without forcing index is 39 seconds.

2nd (longer date range)
The time when running the full query with forcing index is 25 seconds.
The time when running the full query without forcing index is 44 seconds.

It means that the index is useful.  However, the query optimizer cannot use the appropriate index.
0
 
LVL 32

Accepted Solution

by:
bhess1 earned 500 total points
ID: 22869607
Yes, this would be a failure inside the optimizer code in SQL Server itself.   The statistics and assumptions used by the optimizer apparently do not handle this particular table and index structure as a completely optimized process.

The reasons can vary from excessive clustered index fragmentation in the table itself (i.e. if the file was completely defragmented, the clustered index would actually be faster, but since it is not, the end result is slower response time) to unusual data distribution (i.e. the optimizer looks at the statistics and decides that the data is not sufficiently selective for the period covered - a problem that may fix itself as more data is added to the table) to beyond threshold resource usage (i.e. the optimizer compares the resource usage of the queries - even though query 1 is faster, it may use more resources, and so the optimizer uses option 2) to simply a case of the assumptions and rules built into the optimizer code messing up on this specific example.

The index hints are available to handle the cases where the optimizer chooses a non-optimal method.  I think that's what you will need to do unless patching to a new SP or hotfix corrects the issue - and there is no guarantee that it will.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to check data in sql table 11 51
Truncate vs Delete 63 107
Help with stripping out character in SQL LEFT/RIGHT/REPLACE 2 47
Unable to save view in SSMS 21 73
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question