Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lazerath
Constraint Violating Yak Guru
343 Posts |
 Posted - 2007-02-13 : 14:46:54
|
| Basically, I have a web-based application that queries aggregates from several big tables that grow rather quickly. I feel I am fairly accomplished at tuning individual queries and have optimized these particular stored procedures as much as I can and often see great results for a period of time. However, after time passes, it seems the stored procedures begins performing really badly. What's frustrating is that I have traced the server, located a stored procedure that consistently executes with an abnormally high duration, but when I run the exact same text directly against the sql server it returns instantly. If I recompile the stored procedure using my direct connection by including the WITH RECOMPILE clause to the EXEC statement, the problem remains for the web-app. However, if I add the WITH RECOMPILE to the actual stored procedure, it is instantly resolved. I can then remove the WITH RECOMPILE from the stored procedure text and things will remain speedy for a period of time before they again slip back.I'm curious as to what I can do from a SQL standpoint to correct for this. For now, the app team is setting a counter for each execution and then issuing a WITH RECOMPILE when the counter limit is exceeded.I apologize if this question has most been asked before and I have attempted a review of the forums but was unable to find any material. Any suggestions, advice, links or general assistance would be most appreciated. |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2007-02-13 : 15:00:48
|
| Easiest would be to create the procedure with recompile, instead of calling it with recompile. It would cause a compilation on each call to the stored procedure, but it sounds like you are willing to live with that. |
 |
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2007-02-13 : 16:18:52
|
| Thank you for the response, mcrowley.I have definately added the WITH RECOMPILE option to the body of the stored procedure(s) and it solves for the inconsistency, but I lose the benefit of execution plan caching which decreases my overall performance. Since I know a recompile fixes performance for a while, I'd rather have the application recompile the stored procedure every 500 executions instead of on each execution (which is what the WITH RECOMPILE option does when placed in body of the stored procedure).I guess I was approaching this from a somewhat different angle. I'm mostly interested in understanding exactly why my procedure cache is causing slow performance so that I can treat the problem at it's root instead of applying the bandaid to the wound (WITH RECOMPILE). In essence, I'd like to maximize the performance of my hardware as much as possible and I'm not convinced that applying WITH RECOMPILE to the innards of the stored procedure does that.Maybe someone knows some good reading material with this information? |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2007-02-13 : 16:54:13
|
| This article should get you started pretty well: http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspxHave you quantified how much the recompiles of this stored procedure are hurting you? As for the base of the problem, I would bet that there is a set of parameters that will give you a query plan that is poor for a number (maybe a majority) of cases that this stored procedure will run for. This can be caused by parameters that lead to range scans, or odd/skewed distributions of data.A second alternative may be to set up a job that runs sp_recompile on one of the tables this procedure is based on to run say every 2 hours? |
|
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2007-02-13 : 17:19:13
|
| I've been doing some research and came to the same conclusion about sp_recompile and parameter sniffing, but this white paper is good stuff. Thanks for the info! |
|
|
|
|
|
|
|
|
Inconsistent Application Performance