April 21, 2009 at 12:26 am
I have one unique problem when tuning a query. In one of my main transactions I am using a particular SP means one sp will get executed at the time of one particular transaction in Our product. That particular task will be run very often. My problem is when a user a tries to do the same transaction, the SP executed so quickly consider within 2 seconds, but when another time its taking 10 seconds. There was no parrall process for the particular SP at the time, Only the CPU utilization went high and I am sure that that was not the first time plan has been generated in the cache.
Can anybody tell me that what are all the ways I can find the solution.
Thanks in advance
April 21, 2009 at 12:59 am
Can you post the code please Kavin?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 21, 2009 at 1:03 am
I am 100% sure that this is not a code level problem as I havent find any locks or any code level blockings there. More over as its online transaction environment there are so many SubSPs in that paticular stored procedure. I can only find that the CPU utilization time is high at some particular point of time.
April 21, 2009 at 8:48 am
It sure sounds like blocking, but since you say it's not, I'd step back a bit, not look at that procedure directly and instead examine the waits and queues on the system. See what's is causing things to slow down during the execution period you're concerned with. This will tell you if you have a coding issue or something else.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply