SQL Server internal variable with DML type

  • 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

  • Checkout sys.dm_exec_requests and the command column, that will give you if its a SELECT/INSERT/UPDATE/DELETE and more

  • 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

  • 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.

  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

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

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