April 29, 2009 at 4:07 am
I've run into an interesting situation where it appears that a query plan that is normally very efficient, gets replaced with an inefficient one.
The timing seems to coincide with the executing of a job that does some pretty heavy inserts & updates to a large (300 million row) table that is utilized in the query. I'm assuming (requesting a trace from profiler is a pain in the neck and something I'm hoping to avoid) these updates are causing the statistics for the table to be updated and therefore causing the efficient plan to be invalidated and marked for recompile.
At any rate, I grabbed the xml query plan before and after this suspected recompile and noticed that there was indeed a seek that had mysteriously been converted to a scan on the index hitting the really large table. However, I also noticed that the StatementOptmEarlyAbortReason attribute was set to timeout whereas the efficient plan was GoodEnoughPlanFound.
I've never really looked into this before, but a little research has shown that the optimizer can sometimes timeout before searching through all plans, however, I can't imagine that it would be a good idea to cache a plan that may not be optimal....
Just wondering if anyone else had encountered this before or if I'm overlooking something more obvious. I hate to use query plan hints, but it seems like my options are limited to that or doing an sp_recompile one more time after the updates to the giant table are complete and there is less load on the db allowing the optimizer to fully search the plan space.
April 29, 2009 at 4:58 am
Yeah, that sounds familiar. I'd suggest recompiling after the data load and avoid using query hints. Since you know it's going to happen, take care of it that way. Also, I'd suggest, unless the load job rebuilds the indexes, that you update the statistics prior to issuing the recompile.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 1, 2009 at 5:59 am
Alright well, thanks for the input. I think we may play with the scheduling of the job/tuning the job to see if we can free up enough resources to avoid the optimizer timeout. Failing that, I'll resort to a much less elegant sp_recompile 😉
Thanks again!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply