March 3, 2010 at 5:33 am
Hi all,
I've got a little problem and I was wondering if anyone could offer me some new avenues to pursue. I have a procedure, lets call it PROCA, and when I run it on our production server it runs in about 1 second, which acceptable, however recently, last few weeks it has begun to slow down dramtically, but only in certain instances.
I've been profiling it to see the durations, and most run at around a second, but then there is say 1 in 3 that runs at around 30 seconds, but when I take the code and re-run it (again on the production server) it runs in about 1 second. I've gone through the execution plan to see if anything funny was happening and there doesnt seem to be, the correct indexes are being used, I've checked the stats and indexes used, and re-organized one or two of the indexes to remove fragmentation.
Now I've identified a couple of tweaks I can make in the procedure to get some performance out of it, but my concern is where this is coming from, and why all of a sudden when no changes have been made to it. I've been examining for cache misses but with no luck as yet.
Any one any idea's on what else I can check, or what may cause this? Oh it's on SQL 2005 Standard SP3, I've also double checked and we dont have any obvious memory, cpu or i/o bottlenecks, I've also checked out locks, the procedure is dirty reads so it shouldnt be waiting for any locks on the tables.
Any hep would be appreciated, if you need more info then please ask.
Thanks,
Nic
March 3, 2010 at 5:58 am
Sounds like parameter sniffing.
Can you post the store proc and the actual execution plan (not estimated, so not retrieved from cache) for both a fast and a slow query if possible.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 3, 2010 at 6:03 am
Hi Gail,
Thanks for the quick reply, I think your probably right as it is one of them 'jack of all trades' procedure which has some parameters which are optional.
I'll dig out the procedure now and the actual execution plan and get them over to you asap.
Thanks,
Nic
March 3, 2010 at 6:17 am
NicHopper (3/3/2010)
Thanks for the quick reply, I think your probably right as it is one of them 'jack of all trades' procedure which has some parameters which are optional.
In that case, you really want to read this http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/ and this http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
I'll dig out the procedure now and the actual execution plan and get them over to you asap.
Post them here please, don't PM or mail me.
Some background on parameter sniffing (3 parts): http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 3, 2010 at 6:56 am
Hi,
Thanks for the links on parameter sniffing, having a read through them now, I'm fairly sure that this is our issue, although I wonder why the issue has just raised it's head now? Is this one of those things that could of worked fine for a while, or would you expect this issue to have been there all along?
As for the file, in order that I can delete the procedure (and the map of the underlying schmea), I've had to post the
files up to (https://www.sugarsync.com/pf/D690398_196575_786021). This is just as the powers that be dont want the data leaving up on a public forum, the files will be removed later on today. Sorry for any inconvience this may cause.
@StartDate DATETIME,
@EndDate DATETIME,
@Location INT,
@LanguageID INT = 1,
@GetUnavail BIT = 0,
@HostelID INT = NULL,
@Beds INT = 1,
@strCurrencyCode VARCHAR(3) = NULL
However, in most instances, the execution will not pass in @HostelID, and @GetUnavail is pretty much always 0. Which are used in the WHERE clause, so a good example of a parameter sniffing issue I beleive.
Assuming this is a parameter sniffing issue, I assume the best solution would be to logically break up the procedure so rather than being a 'catch all' we end up with a couple of procedures, which correctly meet the requirements.
One interesting thing, I noticed that looking at the execution plan on the production db there are no missing indexes suggested by SQL, however when viewing the files locally, an index is selected, I'm wondering if this is a display issue on production, or if it's just my local environment is missing an index that we have on production?
Thanks for all your help with this, it's greatly appreciated.
Nic
March 3, 2010 at 8:24 am
NicHopper (3/3/2010)
This is just as the powers that be dont want the data leaving up on a public forum, the files will be removed later on today.
I'm not going to have chance to look at this today (it's past 5pm where I am), probably not even before the weekend. Hopefully someone else on the forums will have some time today.
Assuming this is a parameter sniffing issue, I assume the best solution would be to logically break up the procedure so rather than being a 'catch all' we end up with a couple of procedures, which correctly meet the requirements.
Probably. Test it out on a dev server. See if it helps.
One interesting thing, I noticed that looking at the execution plan on the production db there are no missing indexes suggested by SQL, however when viewing the files locally, an index is selected
Is that looking at the same exec plan? If so, on the prod server it's 2005 management studio and you're using 2008. The missing index info's always been in the exec plan, 2005 management studio didn't display it, 2008's does.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 3, 2010 at 8:44 am
Could it be something like another process having a lock on a common table used by the SP ? So there's a conflict when they are run at the same time ?
March 3, 2010 at 9:10 am
I noticed that you are selecting from the CTE three times, once in the main query and twice in subqueries. You may want to consider populating one or two temp tables and pre-aggregate the data for the those subqueries.
March 3, 2010 at 9:23 am
Unless the procedure is called very frequently, try adding WITH RECOMPILE to its definition. That will re-optimize the procedure for the current parameter values at the cost of, er, a recompilation. Better still, add OPTION (RECOMPILE) to the problem statement inside the procedure. Better still, re-write the thing using dynamic SQL.
Seriously though, adding a recompile will at least tell you if parameter sniffing is the probable cause.
Side note, those user-defined functions don't do any data access do they? Even if they don't, T-SQL scalar functions are a performance nightmare. Replace them with in-line table-valued functions if possible, or CLR scalar functions if not. General advice.
Paul
March 5, 2010 at 1:54 am
Hi all,
Thanks for the feedback, and sorry for not getting back sooner, I'm going to give the recompile a try, just to prove if it's a sniffing issue or not.
Also on the CTE, I will have a try with a table variable and see what I can get out of it.
Keep you posted on how I get on.
Thanks again,
Nic
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply