Post reply

Session blocking performance

  • August 20, 2010 at 6:46 am

    #228246

    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.

  • August 20, 2010 at 8:33 am

    #1209700

    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, 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

  • August 20, 2010 at 11:41 am

    #1209836

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

  • August 20, 2010 at 11:55 am

    #1209843

    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, 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

Viewing 4 posts - 1 through 3 (of 3 total)

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