September 27, 2005 at 4:34 pm
I have a SQL Server database with a table called “Data” that contains 4.7 million rows of records. I have put a non-clustered index on a column “FiscYrMo” that many queries use in the where clause. For some reasons, SQL Server does not use the index and only scans the whole table. If I put an index hint on the queries, it improves the performance significantly. Since SQL Server has the Index Tuning Wizard, I ran the Profiler with the template “SQL Profiler Tuning”, execute the queries over 3000 times; and saved the trace file for analysis. After running through the wizard, SQL Server did not recommend any index at all. My question is it is very clear that an index on the column “FiscYrMo” of the “Data” table helps the queries, how come SQL Server does not use it?
Here is one of the queries:
SELECT T00."FacilityName", SUM(T01."GrossSalesPriceExt") FROM dbo."MfgFacility" T00, dbo."Data" T01, dbo."Time" T02 WHERE T00."MfgFacility"=T01."MfgFacility" AND T01."FiscYrMo"=T02."FiscYrMo" AND T02."CalDate" BETWEEN {d '2005-02-01'} AND {d '2005-02-28'} GROUP BY T00."FacilityName" ORDER BY 1 ASC
September 27, 2005 at 6:42 pm
What other indexes are on the table? What indexes does the query use? Does the query use different indexes if you use ANSI standard joins in the FROM clause?
At first glance, noting that you haven't provided any schema information, I'd guess that this query would be helped with a clustered index on the CallDate column.
--------------------
Colt 45 - the original point and click interface
September 28, 2005 at 2:49 am
Hi, running the query through Query Analyser, with the show execution plan option on will help determine what might be the problem.
I agree that you may need a clustered index, but further than that you may need to create statistics on the table, and particularly the indexed column, and ensure that they are frequently updated.
I have however seen the odd case where it is necessary to force SQL Server to use the index, but generally the problem is that SQL Server does not have enough information, in the form of statistics, to determine the most efficient query plan.
To force an index:
SELECT some_column (index=myindex) FROM sometable
Cheers,
Iain
September 28, 2005 at 1:18 pm
The index on FiscYrMo is NON-clustered.
Fiscal Year/Month by itself is a bad candidate for a NON-clustered index - over 4.7 million rows, it is nowhere near *selective* enough for the optimizer to even consider it. As it stands at the moment, it is a waste of disk space and a performance drag to insert/update/delete.
September 28, 2005 at 3:57 pm
I finally figured out it is the sampling of data for the statistics that caused SQL Server not to use the index. There are 37 values in the FiscYrMo column and the first 200,000 rows are of the same value. After I updated the statistics with a full scan of the table, SQL Server is able to use the index.
Thank you all for your input.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply