Slow query due to missing statistic - help on whether to rebuild stats
Posted on 2004-04-06
Situation: 8 million row table1 with 3 column concatentated clustered index (non-unique, not primary key):
table1 (c1, c2, c3, ...)
select count(*) from table1 where c1=1234
500,000 rows in 1 second
select count(*) from table1 where c1=2345
600,000 rows in 60 seconds
WHY? Plans were identical, but query analyzer indicated that the top query would take 98% of the time even though it was 60 times faster. Really weird, I know (though not for SQL Server).
Anyway, there was no statistic on C1, so I created one, and the problem went away - performance to sub-second for both queries and query optimizer showing 50% of time for both queries.
This 8 million row table is a small version of a table with 150 million rows. We are thinking of building the stat on the big table.
Can anyone suggest a reason this statistic helped? Do you need a statistic on the lead column of a clustered index? Any reason the performance was so slow on one but not the other?
Not a critical problem but a big puzzle, though the decision to add the statistic has to be made. One more thing - is it possible that the statistics were out of date but were not showing "out of date" in the query analyzer (all indexes are set to auto update statistics by the way).
NOTE: I WILL AWARD THE POINTS ON THIS QUESTION BEFORE 1 PM TODAY AS A THANKS FOR ANY DECENT ADVICE I CAN GET!