Using Dynamic Management Views to capture the executing Statement

  • Hi, I have created a trigger on a table that get's too many updates so to analyze what records and what columns get updates I have coded a trigger that inserts into a table the record key and a flag on each column to let me know what is updates. Now that I am presenting the data to the developers I have been asked to provide the update / insert or delete statement

    So on my trigger I have tried using dynamic management views but its returning the actual trigger code not the parent SQL Script that triggered it.

    Can any one help?

    This is what I am using.

    select@dDate as dDate, dest.[dbid], dest.[objectid], dest.[number], dest.[encrypted],

    case when sder.[statement_start_offset] > 0 --the start of the active command is not at the beginning of the full command text

    thencasesder.[statement_end_offset]

    --the end of the full command is also the end of the active statement

    when -1 then substring(dest.text, (sder.[statement_start_offset]/2) + 1, 2147483647)

    --the end of the active statement is not at the end of the full command

    elsesubstring(dest.text, (sder.[statement_start_offset]/2) + 1, (sder.[statement_end_offset] - sder.[statement_start_offset])/2)

    end

    else--1st part of full command is running

    casesder.[statement_end_offset]

    --the end of the full command is also the end of the active statement

    when -1 then rtrim(ltrim(dest.[text]))

    --the end of the active statement is not at the end of the full command

    else left(dest.text, (sder.[statement_end_offset]/2) +1)

    end

    endas [exec_statement],

    dest.[text] as [full_statement]

    fromsys.[dm_exec_requests] SDER

    CROSS APPLY sys.[dm_exec_sql_text](SDER.[sql_handle]) DEST

    wheresder.session_id = @@spid

  • If it's a dev environment with not too much traffic I would go with a trace filtering on the statement txt for the table name. This will as give a better view of the multiple statements, the timing and source.

  • You may take help of external tools of sql server like PA (Performance Analysis tool) by Dell

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

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