October 25, 2004 at 2:54 pm
I have two tables which I have partitioned by quarterly dates, when queried separately results are returned ridiculously fast compared to the source table. The first time I ran a query against the joined views the top 10 records were again returned extremely quickly. However the next day when running the same query, no results were returned after 30 seconds. I checked out the execution plan, it seemed that every member table was missing statistics as well as every member table being scanned. After updating statistics the query would still hang. I then re-created the same query in EM/Views and results were returned instantly. No new data had been imported, no changes had been made to the table structure.
Any idea why this would happen and what I could do to prevent it?
January 12, 2005 at 3:56 am
Without statistics SQL Server cannot make accurate guesses over which execution plan is best. Unfortunately, sometimes updating the statistics after a query/stored proc has already performed poorly doesn't always help. This is because execution plans are cached and there is quite a complex algorithm that SQL Server uses to determine whether to recalculate the execution plans.
To force the SQL to recalculate the execution plan, which would have solved the problem after updating the stats, you can use sp_recompile. Dropping and re-creating the procedure helped, because it forced the query plan to be re-calculated.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply