Microsoft SQL Server: How to improve the performance of a dumb query?

I have been asked to help with performance issue of a SQL server installation. I am not a SQL Server expert, but I decided to take a look. We are using a closed source application that appears to work OK. However after a SQL Server upgrade from 2000 to 2005, application performance has reportedly suffered considerably. I ran SQL profiler and caught the following query (field names changed to protect the innocent) taking about 30 seconds to run. My first thought was that I should optimize the query. But that is not possible, given that the application is closed source and the vendor is not helpful. So I am left, trying to figure out how to make this query run fast without changing it. It is also not clear to me how this query ran faster on the older SQL server 2000 product. Perhaps there was some sort of performance tuning applied to on that instance that did not carry over or does not work on the new SQL server. DBCC PINTABLE comes to mind.

Anyway, here is the offending qu开发者_Go百科ery:

select min(row_id) from Table1 where calendar_id = 'Test1' 
and exists 
    (select id from Table1 where calendar_id = 'Test1' and
         DATEDIFF(day, '12/30/2010 09:21', start_datetime) = 0
     ) 
and exists 
    (select id from Table1 where calendar_id = 'Test1' and
         DATEDIFF(day, end_datetime, '01/17/2011 09:03') = 0
     );  

Table1 has about 6200 entries and looks like this. I have tried creating various indices to no effect.

id                 calendar_id  start_datetime  end_datetime
int, primary key   varchar(10)  datetime        datetime
1                  Test1        2005-01-01...   2005-01-01...
2                  Test1        2005-01-02...   2005-01-02...
3                  Test1        2005-01-03...   2005-01-03...
...

I would be very grateful if somebody could help resolve this mystery.

Thanks in advance.

The one thing that should help is a covering index on calendar_id:

create index <indexname> 
    on table (calendar_id, id) 
    include (start_datetime, end_datetime);

This will satisfy the calendar_id = 'Test1' predicates, the min(row_id) sort and will provide the material to evaluate the non-SARG-able DATEFIFF predicates. If there are no other columns in the table, then this is probably the clustered index you need and the id primary key should be a non-clustered one.

Make sure the indexes made the conversion. Then update statistics.

Check the differences between the execution plan on the old sql server and the new one. http://www.sql-server-performance.com/tips/query_execution_plan_analysis_p1.aspx

About the other only thing you can do beyond Remus Rusanu's index suggestion, is to upgrade to the Enterprise edition which has a more advanced scan feature (on both SQL Server 2005 and 2008 Enterprise Edition) which allows multiple tasks to share full table scans.

Beyond that, I do not think there is anything you can do if you cannot change the query. The reason is that the query is doing a comparison against a function result in the Where clause. That means it will force SQL Server to do a table scan on Table1 each time it is executed.

Reading Pages (more info about Advanced Scanning)