Session blocking performance

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Session blocking performance

Session blocking performance

Rate Topic

Display Mode

Topic Options

Author

Message

Howard C. BAchtel-438731

Posted Friday, August 20, 2010 6:46 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 12:54 PM
Points: 77, Visits: 301

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

GilaMonster

Posted Friday, August 20, 2010 8:33 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 37,741, Visits: 30,019

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

Howard C. BAchtel-438731

Posted Friday, August 20, 2010 11:41 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 12:54 PM
Points: 77, Visits: 301

Thanks. I have never used the CROSS APPLY, but will learn.

Post #972722

GilaMonster

Posted Friday, August 20, 2010 11:55 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 37,741, Visits: 30,019

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 »

Permissions