February 3, 2011 at 11:45 pm
hi,
My SP is suspended itself and running itself after some time.
When i use SP_WHO2 no one is connected to server except me.
Any body help why the SP is suspending and running and again suspended ..
any way to find the Cause that Y this behaviour?
the following is the status info when i ran sp_who2
60SUSPENDED Dev_psPC2 .Test_projSELECT INTO 214748364702/3/2011 21:52
60SUSPENDED Dev_psPC2 .Test_projSELECT INTO 214748364702/3/2011 21:52
60RUNNABLE Dev_psPC2 .Test_projSELECT INTO 102068328372/3/2011 21:52
60SUSPENDED Dev_psPC2 .Test_projSELECT INTO 214748364702/3/2011 21:52
60SUSPENDED Dev_psPC2 .Test_projSELECT INTO 214748364702/3/2011 21:52
60SUSPENDED Dev_psPC2 .Test_projSELECT INTO 214748364702/3/2011 21:52
60RUNNABLE Dev_psPC2 .Test_projSELECT INTO 100419533742/3/2011 21:52
60SUSPENDED Dev_psPC2 .Test_projSELECT INTO 27176572/3/2011 21:52
why the sp status change ditself during the execution?
Plz help me how to solve this....
February 4, 2011 at 1:47 am
r u sure there is no dts/ssis or job scheduled to run every x min of interval or retry after failure?
Also you can run profiler with specific database and SP to check which account is executing it.
----------
Ashish
February 4, 2011 at 2:00 am
The query is running in parallel (over multiple threads), that's why there are multiple entries for it.
You'll notice that all but two are suspended, means that some of the parallel threads are waiting for others to finish before something like a parallel exchange or merge.
If you want more info, try a google search for SQL parallelism or CXPacket waits.
This is not necessarily something to be concerned about.
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
February 4, 2011 at 2:17 am
Usually, when the optimizer decides to pick a parallel plan it's because it could not find something "smarter" to do.
Operators that fit well in a parallel plan are hash joins, that usually happen to be the best choice when the inputs are huge or there is no suitable index or sargable predicate to perform a seek.
I'm not suggesting that a parallel plan is bad, I'm just saying that maybe there's a way to turn that plan into something quicker, twicking the query or the indexes. Whenever I see "parallel" on my plans I try to investigate if there's a way to tune it further or, at least, I try to discover the reasons behind that plan choice.
-- Gianluca Sartori
February 4, 2011 at 2:30 am
Gianluca Sartori (2/4/2011)
Usually, when the optimizer decides to pick a parallel plan it's because it could not find something "smarter" to do.
Or because the data volumes are large enough that it makes sense to do so.
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
February 6, 2011 at 11:47 pm
Thanks for response...
The following solved my issue...MAx Degree of Parellellism..
OPTION(MAXDOP 1);
February 7, 2011 at 1:16 am
That's not necessarily the best approach. Yes, it will stop parallelism, but it may be hiding a real problem (inefficient query and/or poor indexing) or it may cause a degradation in performance if the parallelism was useful.
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 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply