March 19, 2015 at 1:03 am
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
March 19, 2015 at 6:08 am
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.
March 19, 2015 at 4:24 pm
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