September 28, 2021 at 8:43 am
Can you advice how to retrieve the DML type a query is running? Does an internal variable exist for this?
I mean, after running a query DELETE FROM .... the DML type would be DELETE, after INSERT INTO TABLE, the DML type would be INSERT INTO.
Thanks
September 28, 2021 at 9:00 am
Checkout sys.dm_exec_requests and the command column, that will give you if its a SELECT/INSERT/UPDATE/DELETE and more
September 28, 2021 at 9:18 am
ok 🙂
but how can I know how to identify my query?
in the SP i might have:
DELETE FROM.....
(after this step I want to know that is a DELETE)
SELECT * FROM
(after this step I want to know that is a SELECT)
etc etc etc
thanks
September 28, 2021 at 9:25 am
Then you need to look at the text it is running and hope you catch it at the right times.
From sys.dm_exec_requests CROSS APPLY to sys.dm_exec_query_text using the SQL_HANDLE or the PLAN_HANDLE, then you will need to do some string work to get the first 6 characters of the command.
September 28, 2021 at 12:49 pm
Or, look to Extended Events to capture each batch call or each procedure call. It's also possible to capture each statement for either of these, but that results in LOTS of data. However, you can see each statement as it starts and completes if you want to, including metrics about the performance as it goes. Here's a Microsoft article on the topic. Here's a great video by Kathi Kellenberger. If you follow the link below to my blog, I have a ton of posts on how to use Extended Events for query tracking.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply