Proc Optimization

Question

Proc Optimization

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

sqlnaive (5/30/2012)
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.

i seem to remember seeing in the profiler traces that you can capture an XML plan - if you set the XML showplan event on the trace you can capture it

MVDBA

sqlnaive SSCoach Points: 17437 More actions · Answer 2

I'm sure because at that moment, system DBAs have checked that there is no blocking. Only blocking is caused because of that stucked proc instance for other instances.

Secondly we delete the data form the tables on daily basis. Also there is weekly maintenance job which does this rebuilding indexes and updating stats on all tables.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

try running sp_whoisactive (freebie from sqlblog.com) while the stuck one is hung. Actually I would run it while you have all the concurrent runs going on. I wonder if they are blocking each other or at least overloading one or more of the precious resources on the box (RAM, CPU, IO).

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

sqlnaive SSCoach Points: 17437 More actions · Answer 4

Can you please suggest what steps should i take or what data should i capture which might help in finding the root cause during the time when that proc is stuck.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Execution plan and the query's waits as a minimum. I'd suggest an extended events session for the waits. Google Jonathan Kehayias extended events waits. He has a post somewhere.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 6

sqlnaive (6/1/2012)
Can you please suggest what steps should i take or what data should i capture which might help in finding the root cause during the time when that proc is stuck.

as we've both said - without further info we can't help.

if you want to capture the query plan then set a profiler trace running and include the SHOWPLAN event along withe PROC START and PROC END events.

this will grab the query plan for the bad query for you and you can post it here for us. (as per my previous post)

MVDBA

sqlnaive SSCoach Points: 17437 More actions · Answer 7

I'll try to get the execution plan if get a chance to grab it when such incidence occurs.