Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Session blocking performance Expand / Collapse
Author
Message
Posted Friday, August 20, 2010 6:46 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 5:09 PM
Points: 94, Visits: 376
We have SQL Server 2005. We have several scheduled jobs using SSIS with numerous stored procedures running both in line and parallel. Some of these jobs have single steps (not SSIS) that execute stored procedures.

Using sys.dm_exec requests, I can identify the SPid that is blocking. The login is a service account and not identified to a particular user, but mostly used for the scheduled jobs and some other processes not for a particular user.

Is there a way to identify the actual query/Stored Procedure/SSIS procedure that is causing the block?

Thanks in advance.
Post #972481
Posted Friday, August 20, 2010 8:33 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 9:44 AM
Points: 42,822, Visits: 35,953
Yes. Query sys.dm_exec_requests and CROSS APPLY sys.dm_exec_sql_text. The column text will give you the batch or proc that the session is running.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #972574
Posted Friday, August 20, 2010 11:41 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 5:09 PM
Points: 94, Visits: 376
Thanks. I have never used the CROSS APPLY, but will learn.
Post #972722
Posted Friday, August 20, 2010 11:55 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 9:44 AM
Points: 42,822, Visits: 35,953
Books Online does have sample code under either sys.dm_exec_requests or sys.dm_exec_sql_text (or maybe both)


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #972729
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse