Determine cause of a Bad Execution Plan

I had a server with some issues the other day (SQL 2005 SP3). I tracked down a job that was running every 5 minutes that was taking 3 minutes to run. Looking at the history it was previously running in under a second.

After looking at the plan it was doing a clustered index scan on a large table. I run sp_updatestats every morning at 3:00 AM and when the job ran at 7:40 AM it took under a second; when it ran at 7:45 AM it started taking 3 minutes. No major changes or data loads occurred from what I can tell between that time. When I ran sp_updatestats again, the plan changed to an index seek and execution time reduced to under a second again.

I am trying to determine what may have caused the query to start using the "Bad Plan" at that time especially since stats were just updated and no major changes to the data occurred. Any ideas?

more ▼

asked Oct 22, 2009 at 03:50 PM in Default

avatar image

Ken Simmons
466 2 3 6

What kind of Page Life Expectancy are you getting on the server?

Oct 22, 2009 at 04:34 PM Blackhawk-17

The server is due for a refresh and the PLE is averaging 191. Just trying to do a root cause now for the disruption. Short of adding a USE PLAN hint, I can't guarantee that the query will not resort to the bad plan again.

Oct 22, 2009 at 05:30 PM Ken Simmons
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Does the job have IF ...ELSE statements?

Is it using a proc?

I belive that sp_updatestats clears the proc cache. So that would resolve the slow performace as new stats are used.

more ▼

answered Oct 22, 2009 at 04:37 PM

avatar image

10.9k 27 37 37

(comments are locked)
10|1200 characters needed characters left

Based on the various comments, this may not be an answer for your situation, but I'd like to describe a similar situation we had with a stored procedure and how we resolved it.

A certain procedure was running fine until one day it kept timing out for one of our customers. Upon investigation, we learned that their dataset was roughly twice the average size. That said, the query still should have executed in less than 30 seconds. We ran sp_recompile for that sproc and tried again. This time, it worked fine. Unfortunately, the same problem occurred again 2 days later when the same customer had another large dataset.

We determined the problem was caused by a nightly reindex job that was running. (This was added by a Microsoft consultant and was written to only reindex if fragmentation was over a certain threshold.) We tried adding sp_recompile for the problem stored procedure after the reindex, but it didn't help. Then the consultant came up with a plan that worked. We ran sp_recompile and then immediately ran the procedure for a large dataset. This forced SQL Server to cache the plan for the large dataset. That change was made months ago, and we haven't had a single complaint since.

It turns out the cached execution plan generated for a large dataset was much better for large ones and only very slightly slower for small datasets. On the other hand, the plan generated for a small dataset was optimized for that but terrible for a large one.

I don't know if this will help for the original question, but it may help others with similar issues.

more ▼

answered Oct 23, 2009 at 01:19 AM

avatar image

Tom Staab ♦
14.5k 7 15 21

(comments are locked)
10|1200 characters needed characters left

From the sounds of it, it's probable that you have some parameters passed to your query that cause a bad plan and some that cause a good plan, independent of the statistics. You could run a server side trace to try to capture recompile events or take a look at the sys.dm_exec_query_stats to see when the plan was compiled. To fix it you would want to address it like it was a parameter sniffing problem (which it sort of is). Either use the OPTIMIZE FOR query hint and supply a good parameter, or use local parameters to get a more generic plan. You might even consider creating the query WITH RECOMPILE to get a new plan each time. It really depends on if you can afford the recompile cost.

more ▼

answered Oct 23, 2009 at 09:00 AM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 22, 2009 at 03:50 PM

Seen: 6645 times

Last Updated: Oct 23, 2009 at 05:03 AM

Copyright 2018 Redgate Software. Privacy Policy