March 9, 2012 at 8:53 am
I am looking for some assistance. We are see degrading performance on the SQL calls. We are having to run DBCC FREEPROCCACHE every 3 hours to fix the problem (in most cases). This was not needed to do in the past but only run a rare occasions.
Example of a why it is needed is that a Stored Proc that was finishing under a second would take over a minute causing application timeout errors.
We are working on rewriting some of the identified procs that reference some SQL views that may be the root cause. But the changes will not be implemented in the short term on production.
Obviously reviewing and optimizing the query execution plans are in the works as well.
Unfortunately we are only on SP1 for SQL 2008 and been unable to afford the down time to get SP3 installed so Microsoft is unwilling to provide assistance.
Any suggestions on help diagnosing the underlying issues.
Thanks,
Daniel
March 9, 2012 at 12:17 pm
Based on the brief description it sounds like parameter sniffing. You might want to check Gails blog series[/url] for details and solutions.
If FREEPROCCACHE solves the issue and there are just a few sprocs being affected by the slowness, you might benefit from using the
Recompile option (example F). This would allow to create a new query plan for the sproc each time it is called.
Another option would be to schedule a recompile for a specific sproc or a set of sprocs:
EXEC sp_recompile N'yourSchema.YourSproc';
March 9, 2012 at 2:33 pm
Thank you for your reply. On one of the identified poor performing procs, With Recompile' was add to see if proc performance would improve. It still returned slowly.
A work around, was to make a change to a SQL View that the stored procedure was referencing to perform less joins (that weren't needed) for this process.
I may disable the job that is running every 3 hours to clear cache and see with the code changes, if we are still being impacted.
March 9, 2012 at 2:43 pm
- is this a "one sproc serves all" kind of situation ? If so, Gail also posted suggestions on how to handle such cases.
My personal preference goes to a starter proc calling other procs for typical query scenarios. Why: Because that may have your DBA help out better to support your cases.
- did you capture the sqlplan(s) of the bad performing sprocs before and after the free of the proc cache.
( you can post in reply ( add the saved .sqlplan or plan xml as attachment ! )
- are you sure the plan is the issue ? How about (b)locking situations at your istance ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply