August 18, 2004 at 10:13 am
Hi All,
Some of my stored procedure running times are wiered.It runs pretty fast some times & pretty slow some times.The time swing is from few minutes to many hours.
For eg. the a proc which has an update for 12 million record takes 7+ hours and sometime it completes in 50 minutes.
We tried monitoring using profiler.We could not get anything usefull.Could anyone knows what are the possible ways we can tune this.
Think Ahead, Raj.D
August 19, 2004 at 12:31 am
It sounds to me like it could be a locking issue. If there are other queries running against the table you're trying to update, your stored proc will be blocked until it can get the locks it wants.
Try running the proc and looking at the db processes
(sp_who2 or through EM)
You could also run the proc from QA and look at the execution plan. It might give you some hints.
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
August 19, 2004 at 5:38 am
In addition to locking as a likely cause, another reason for the difference can be I/O times. If there is a major difference in the amount of framgmentation between the two times you could get a big difference in the amount of I/O that occurs.
Are you defragmenting at the SQL level? Are you sure to leave free space?
Page splits, extent splicts, and file growth are slow operations. I'd look into these.
Andy
August 19, 2004 at 9:09 am
One issue I've run into that caused wild variations in sp execution times was when developers used optional parameters with queries like
select ... from ...
where UserID = @User or @User is null
When the stored procedure is compiled, only one execution plan can be generated. That execution plan may not be optimal for every combination of parameters, and I saw cases where the time varied from <20 sec to >30 min.
Queries like the above should be written:
if @User is null
select ... from ...
else
select ... from ...
where UserID = @User
If you've got 6 optional parameters then this requires 64 versions of the query, unless you use dynamic SQL.
You can use SQL Profiler to see if the longer execution times are related to specific combinations of parameters.
August 19, 2004 at 9:28 am
I had the same issue once when I had a query with a bunch of inner joins inside the SP.
I moved the query to a view and then referenced the view inside the SP.
Unbelievable SPEED difference!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply