make sure the owner remains the same (i.e. dbo.)
run update statistics on the table

otherwise:
table scan may be what the optimizer chooses, if:
is the table small?
are you retrieving more than 80% of the rows?

-----Original Message-----
From: Lee, David [mailto:David.[Email Address Removed] Thursday, April 29, 2004 9:26 AM
To: LazyDBA.com Discussion
Subject: query not using new index


I have been tasked with improving the performance of a query which is part of a Delphi program to which I do not have the source. Using profile while the program is running I identified the query and copied it to the Query Analyser to work on it.

By creating an index on a certain field I managed to reduce the time in QA from 58secs to 120ms. However, when I created the new index on the server and ran the executable exactly as before but it does not use the new index.

How do I make the query use the new index?

thanks in advance

David Lee
DBA
Carlson Marketing

MS Sql Server LazyDBA home page