September 6, 2011 at 6:47 am
Hi
I have an issue that one of my SP is taking different timings in executing with similar data.
If I execute an SP at some time then sometimes it takes 1 minute and same SP with the same input starts taking more than1/2 an hour even some times an hour. I am not finding any clue for the same.
I have set the ISOLATION LEVEL AS READ UNCOMMITTED but still problems are there.
I checked execution plan in both the times but there is no change in the execution plan.
Is there any other process which is blocking the same.
Please help me.
Thanks in advance
Amit
September 6, 2011 at 6:55 am
READ UNCOMMITTED is not going to help in this. This is most probably because you are getting different execution plan at different times. There are lots of reasons why this could happen.
What you can do is to check the execution plan at different times and see what is the difference in the plan.
-Roy
September 6, 2011 at 7:05 am
All the reasons not to use nolock (readuncommited)
http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
I'd still like to see the actual execution plan (especially when slow).
When's the last time you reindexed / updated the stats?
Do you filter / join on a date column?
September 7, 2011 at 3:40 am
Also, you're saying "similar" data sets, not identical data sets. Similar can be pretty radically different depending on the data distribution.
Also, no mention of any measure for contention. Are you running into resource issues, blocking, that sort of thing, and again, NOLOCK won't prevent all contention, it just avoids locking (and introduces the potential for error).
"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
September 7, 2011 at 4:14 am
there might be huge usage of tempdb. when that query is executing more than 1 hour.
can u post that sp?
September 8, 2011 at 11:22 am
The runtime duration of a query isn't always indicative of how much work it's doing at the moment. Sometimes it's blocked or waiting in the queue. When diagnosing a problem like this, the first thing you should look at is a high level view of wait states. Here are a couple of articles with scripts for troubleshooting wait states and blocking.
Paul Randal: Survey what is the highest wait on your system
http://www.sqlskills.com/BLOGS/PAUL/post/Survey-what-is-the-highest-wait-on-your-system.aspx
Adam Machanic: Who is Active v11.00
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 8, 2011 at 2:25 pm
...you could always capture a trace..
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply