March 5, 2013 at 8:37 am
we have a stored procedure that will run just fine for a couple of hours but then somewhere during that time its execution plan seems to get confused and executing it then comes to crawl
when executing the stored procedure using the WITH RECOMPILE option we see that query begins executing at what we consider a normal amount of time again and will usually for a while, but eventually it slows down to a crawl again
currently, a simple fix for us is to leave the WITH RECOMPILE option in the stored procedure
however, we'd like to get a better understanding and come to an actual resolution if possible rather than this "workaround"
any insight is greatly appreciated, thanks in advance!
March 5, 2013 at 8:47 am
Is the stored procedure that has many different parameters that could be passed in?
Do you see a lot of changes against the tables the procedure runs against?
What you are describing sounds like it could be that the statistics for the tables related to that query might need updated. It could also be that there is a parameter issue.
The recompile of that one procedure is a good first step in troubleshooting. Understanding the data and how the proc is used can help us to provide some better answers.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 6, 2013 at 7:56 am
thank you for the quick response
the stored procedure only takes 4 parameters, so i wouldn't say that's too many
the tables the procedure runs against have a lot of data that is added on a daily basis, the structure of the tables themselves however does not change
please let me know what other information would be helpful, i'll try and work up a basic example of what this procedure is doing when i have a chance
also, i've updated my settings so i get constant updates on when this thread is replied to so hopefully i'll be more prompt to replies
thanks again!
March 6, 2013 at 10:12 am
dbx (3/6/2013)
...the stored procedure only takes 4 parameters, so i wouldn't say that's too many...
The number of parameters isn't as much an issue as if any of the parameters are ever passed in the value NULL. Is the stored proc called the same way each time? I could see a situation where param_1 is passed in a value durring execution1 which matches an index on the table, but then later execution25 passes in NULL for param_1 and a value for param_2 which doesn't match an index or matches a different index, but the old execution plan is still cached so it's tryng to use an index which won't help it resolve the query. To get more specific we'd probably have to see the query of the stored proc.
March 6, 2013 at 11:27 am
You may want to consider dummy variables to prevent parameter sniffing, that could explain what you're seeing. Here's one article about parameter sniffing:
http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx
March 10, 2013 at 8:19 am
dbx (3/6/2013)
the tables the procedure runs against have a lot of data that is added on a daily basis,...
As the OP marked, with a whole lot of changes happening in the data on a daily basis (continious maybe),
It could well be that after some amount of time the statistics do not longer represent the actual data and therefore the cached execution plan is no longer the best possible.
I would recommend executing the SP manual when things are slowing down and check the execution plan to estimated row count and actual row counts.
If these are off then update the stats, if they are not it could well be a case of parameter problem.
Wkr,
Eddy
March 10, 2013 at 7:05 pm
Try:
SELECT
is_auto_update_stats_on
,is_auto_update_stats_async_on
FROM sys.databases
If auto update stats is on and the statistics get out of date the query optimizer will always wait for the statistics to be auto-updated and then do a recompile before execution of the query. If this is a large table that has had a lot of changes that can take a long time.
If you change the auto-statistics mode to asynchronous (the default is synchronous), the query will use the existing query plan (even a sub-optimal one) without recompilation even if the statistics are out of date. Of course then you must schedule a manual statistics update periodically. But that update can be scheduled to run before the scheduled execution of your offending procedure.
To turn on the auto updates:
ALTER DATABASE dbName SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE dbName SET AUTO_UPDATE_STATISTICS_ASYNC ON
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply