Post reply

Wait type per Statement

  • May 28, 2019 at 11:35 am

    #3647768

    The SQL statement below is a part of my query to monitor how each statement of a proc is behaving.

    How would I have to modify this query in order to get any potential Wait Types per statement?

    WITH cteProcStats AS (
      SELECT deps.object_id, deps.sql_handle
      FROM sys.dm_exec_procedure_stats AS deps
      WHERE deps.database_id = DB_ID()
        AND deps.object_id = OBJECT_ID(N'SchemaName.ProcName', N'P')
    )
    SELECT 
        ObjectName = OBJECT_NAME(ps.object_id)
      , Statement_Plan = CAST(qp.query_plan AS XML)
      , Full_Plan = CAST(qpf.query_plan AS XML)
      , StatementText = SUBSTRING(  st.[text]
                            , qs.statement_start_offset/2+1
                            , (ISNULL(NULLIF(qs.statement_end_offset, -1), DATALENGTH(st.[text])) - qs.statement_start_offset)/2 + 1
                           )
      , ShortStatementText = LEFT(REPLACE(REPLACE(SUBSTRING(  st.[text]
                            , qs.statement_start_offset/2+1
                            , (ISNULL(NULLIF(qs.statement_end_offset, -1), DATALENGTH(st.[text])) - qs.statement_start_offset)/2 + 1
                           ), CHAR(13), ' '), CHAR(10), ' '), 100)
      , qs.execution_count
      , qs.total_logical_reads
      , qs.total_worker_time
      , qs.total_elapsed_time
      --, WaitType   = -- Looking to find any possible WaitTypes experiernced here
    FROM cteProcStats AS ps
    INNER JOIN sys.dm_exec_query_stats AS qs ON qs.sql_handle = ps.sql_handle
    OUTER APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
    OUTER APPLY sys.dm_exec_text_query_plan(qs.plan_handle, 0, -1) AS qpf
    OUTER APPLY sys.dm_exec_sql_text(qs.plan_handle) st 
    ORDER BY ps.object_id, qs.statement_start_offset;

    How to post data/code on a forum to get the best help.

  • May 29, 2019 at 12:10 pm

    #3648193

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 1 (of 1 total)

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