May 29, 2012 at 2:21 am
We have one procedure [say P1] having multiple inserts to a table [Say Target1] resulting from join on two big tables [say Source1 and Source2]. Proc is like this
create proc P1
@Param1 varchar(20),
@Param2 int
with recompile
as
begin
insert into Target1
[col1, col2, col3, .... col50]
select
from Source1 a
inner join Source2 b on a.col1 = b.col1
and a.col2 = b.col2
where a.col1 = @Param1
and a.col2 = @Param2
end
Now I'm running it for 100 combinations of @Param1 and @Param2 with 10 parallel execution of this proc. Usually every instance gets completed between 1 mins to 5 mins depending on the number of records for this combination. Now at times, i see any one random instance of this proc getting stuck at insert and we have to get it killed even after 45-60 minutes. I am unable to understand this behaviour.
May 29, 2012 at 2:25 am
Is it getting blocked?
Is that specific combination of parameters likely to return more rows than the others?
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
May 29, 2012 at 3:05 am
It does actually look like parameter sniffing...
But, if it's not blocked or other, try to add Option (MAXDOP 1)
Are you running on SQL2008?
May 29, 2012 at 3:33 am
Is it getting blocked?
The one instance which is getting stuck is blocking other processes but not getting stuck itself. It's just in the running mode for infinity.
Is that specific combination of parameters likely to return more rows than the others?
All the instances affect different number of records ranging from 1 to somewhere near 80000. The problem does not show any specific pattern related to the combination of parameters affecting extreme high records.
Are you running on SQL2008?
Yes
May 29, 2012 at 3:43 am
Can't be parameter sniffing, cause of the with recompile.
Please don't just slam a maxdop on without investigating carefully.
Can you post estimated plan of the parameter values that run 'forever' and actual plan of one that runs fine?
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
May 29, 2012 at 5:35 am
Gail, though I know it is useful to have the execution plan but it looks difficult to get as it comes very randomly and at very unusual time (at around 3-6 am). Are there any possible things which we should check in such scenario ?
May 29, 2012 at 5:44 am
If it's a particular time of day, not a particular set of parameters, chedck what's running at that time and log what the query in question is waiting for.
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
May 29, 2012 at 7:46 am
This is no time specific. So there is no quesiton of getting blocked by any process. I guess it is because of bad query plan but not sure. How to proceed with this investigation ?
May 30, 2012 at 12:29 am
Can anyone help on this ?
May 30, 2012 at 1:10 am
Without more information, no. Check the exec plans to see if they are different. See if the long duration is due to blocking or some other wait.
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
May 30, 2012 at 1:33 am
why don't you have a look at your wait stats? - flush them before you go home and then if there is an instance then you can maybe see if it's an I/O stall
also set up a profiler trace that captures proc start and proc complete. - this might give you some other info to go on.
MVDBA
May 30, 2012 at 1:39 am
sqlnaive (5/29/2012)
Gail, though I know it is useful to have the execution plan but it looks difficult to get as it comes very randomly and at very unusual time (at around 3-6 am). Are there any possible things which we should check in such scenario ?
You can capture estimated and actual execution plans with a Profiler trace.
http://msdn.microsoft.com/en-us/library/ms190233(v=sql.105).aspx
http://sqlinthewild.co.za/index.php/2011/01/04/capturing-the-execution-plan/
You will probably want to configure the trace to run server-side (not live in Profiler).
May 30, 2012 at 1:39 am
I'll try if I can get the execution plan whenever we get this situation again. Meanwhile I am sure that there is no process blocking the stuck procedure instance.
May 30, 2012 at 1:41 am
It is hard to capture the execution plan of the proc instance which is getting stuck as it is coming very randomly. Like once in a month or two and sometimes twice in a week.
May 30, 2012 at 2:07 am
Understand that, but without the plans debugging this is going to be very difficult. Also you need to check the waits that the query gets, lock waits if it is getting blocked (how can you be sure it isn't?) and non-lock waits.
p.s. Once or twice a month may be bad stats (recompile won't help if the stats are bad, it'll just get another bad plan). Check that.
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
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply