July 25, 2012 at 2:18 am
I have this scenario where I have one procedure which runs parallely inserting data into one table on th basis of joining on two tables. The inserts are done based on few parameters which are involved in the where condition of the joins.
Now I am getting performance issue in rare case where some of my thread will get stuck indefinitely. While others keep finishing within 1-2 minutes without any issues. Now when I see the statistics on the tables using "DBCC SHOW_STATISTICS", it shows various range of RANGE_ROWS(0-8000), EQ_ROWS(4-280000), DISTINCT_RANGE_ROWS(0-30) and AVG_RANGE_ROWS(1-8000). I don't understand if the issue can arise because of variance in values. The stored procedure is created with "WITH RECOMPILE" option. So I think the plan is regenerated for every execution and for every parameter. Now what can be the probable issue. How should I proceed. Main thing is I am not able to reproduce this issue.
July 25, 2012 at 6:07 am
Can someone please help on this ?
July 25, 2012 at 6:49 am
Hi mate,
What values, if any, are you getting in the wait_type or blocking_session_id columns from sys.dm_exec_requests for the session the proc is running in?
Cheers
July 26, 2012 at 3:46 am
There were no blockings reported at that point of time by the monitoring people. They killed the process after it kept running for around 30-45 minutes (which usually takes 1-2 minutes to complete) and then reran. After that the same process got completed withing 1 minute.
July 26, 2012 at 9:53 am
OK, sounds like it's waiting for something rather than being blocked.
Did the monitoring people give you any details on the wait type field?
You say the proc runs parallely - just to clarify, do you mean the proc is run multiple times simultaeneously, or the proc has a parallel execution plan?
Cheers
July 26, 2012 at 12:52 pm
If you can provide us the DDL for the 2 tables, the stored proc, and some sample data we can look at the stored proc and many times identify the problem right away.Without these things, we can only guess to what is going on.
Jared
CE - Microsoft
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply