How to find executed query

  • Hi,

    I have a requirement where I have to find what was the last recent executed query on certain user database.

    Is there way to check what was the last query run and was the transaction committed?

    Please let me know your thoughts.

     

  • If you've set nothing up for monitoring queries, then your only option is to use the Dynamic Management Views (DMV). There are a few directly related to queries:

    sys.dm_exec_query_stats

    sys.dm_exec_procedure_stats

    sys.dm_exec_function_stats

    They all have the database_id as a column, so you can filter on that. These are cache dependent though, so if something has aged out of cache, your cache has been cleared, or the query never went into cache, no data will be visible. The query stats DMV has everything, functions & procedures. The functions & procedures stats are a way to focus only on the call to those objects.

    You can combine these with other DMVs, such as sys.dm_exec_sql_text or sys.dm_exec_query_plan, to get additional information. An easy way to do all this is to get a copy of sp_whoisactive. Another approach is to check out Glen Barry's scripts. Otherwise, I frequently just write quick queries to find what I want from cache.

    If you need more than what querying the cache provides, you'll need to set something up such as Query Store or Extended Events (don't listen to anyone who suggests Trace/Profiler, it's almost 2020, time for new technology).

    "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

  • sizal0234 wrote:

    Hi,

    I have a requirement where I have to find what was the last recent executed query on certain user database.

    Is there way to check what was the last query run and was the transaction committed?

    Please let me know your thoughts.

    Just to emphasize what Grant has stated but with a bit of a different  spin on it...

    Even if the requirement was to find the last query executed by a user within 1 second after the user caused the execution, the DMVs cited will work only as long as the query is still in the plan cache, which is extremely volatile and can be totally cleared out at any time especially on a machine that doesn't have much memory for such things or on extremely active machines.

    To wit, I wouldn't even bother with such an attempt.

    So I agree with Grant's final paragraph.  Someone is going to have to hit the books on the items he mentioned.  I'll also add that I'm not an expert in any of the things Grant mentioned (Query Store/Extended Events) but rumor has it that Extended Events can't return queries for such things.  You'll need to check on that and if that rumor turns out to be true, then listening to someone about how to set up a Profiler run in the short term (to get you out of the woods until you can figure out a much better method) might actually be the way to go but ONLY if you have an individual that you're trying to track.  Even if you're using something like Extended Events, trying to track this for EVERY login will likely bring the server to its knees.

    And with that being said, WHY are you trying to do this?  Is it for just one individual or what?  What is the reason why anyone would want to do this?  What is the actual business reason?  The answer may help us and you define a method without killing your server.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    ... but rumor has it that Extended Events can't return queries for such things.

    What do you mean on this one Jeff? I'm not aware of anything that Trace/Profiler does (with a single exception, marrying Perfmon counters to query metrics) that we can't do in Extended Events. I'd love to know if I'm missing something or if there's some method I need to promote better.

    "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

  • I was told (but have not tested) that Extended Events cannot return the query that is causing an event.  If that's not true, then my apologies and I'll go back and remove my comments.  If EE can return such a query, then I'd love a link for how to do it so I can stuff the guy that told me you couldn't.

     

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    I was told (but have not tested) that Extended Events cannot return the query that is causing an event.  If that's not true, then my apologies and I'll go back and remove my comments.  If EE can return such a query, then I'd love a link for how to do it so I can stuff the guy that told me you couldn't.

    Ah, that was in the initial release in 2008. ExEvents sucked in 2008 & R2.

    2012 or better, the answer is, it depends. Most events, especially the ones around query behavior, statement start/end, rpc start/end, batch start/end, you can get the query with the event and, in the case of procs, the values of the parameters passed. All exactly the same as with Trace. Some events have the query as part of the package. Other events, not the straight "what's that query doing" ones, you can use what's called an Action to add query text. Some events, usually unrelated to queries, may not return the query, even with the Action. However, there's a thing called causality tracking that you can enable. Capture the standard query event, which is what we're talking about here anyway, like sql_batch_completed, add in whatever other events you want, recompile, stats updates, pretty much anything, add causality tracking. Then, all the events associated with the query get a GUID and an order assignment. All very neat and cool, and something you can't do in Trace.

    As to links, tons of stuff on my blog, but for something official, let's just go with rpc_completed. If you look, in addition to object_id and object_name, there's a textdata column. That is the actual rpc call, the full EXEC dbo.MyProc @Id = 42;. If we look at sql_batch_completed, the batch text is again in the textdata column. Now, those two links are to the internal definitions. The actual events look a little different, but the behavior is identical.

    You absolutely can get the query text back, a whole bunch of different ways.

    No need to delete or edit stuff, I was just curious where you were coming from on that.

    "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

  • SELECT dest.TEXT AS [Query],
    deqs.execution_count [Count],
    deqs.last_execution_time AS [Time]
    FROM sys.dm_exec_query_stats AS deqs
    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
    ORDER BY deqs.last_execution_time DESC

    https://blog.sqlauthority.com/2016/09/04/find-recent-executed-queries-sql-server-interview-question-week-086/

     

     

  • Grant Fritchey wrote:

    Jeff Moden wrote:

    I was told (but have not tested) that Extended Events cannot return the query that is causing an event.  If that's not true, then my apologies and I'll go back and remove my comments.  If EE can return such a query, then I'd love a link for how to do it so I can stuff the guy that told me you couldn't.

    Ah, that was in the initial release in 2008. ExEvents sucked in 2008 & R2.

    2012 or better, the answer is, it depends. Most events, especially the ones around query behavior, statement start/end, rpc start/end, batch start/end, you can get the query with the event and, in the case of procs, the values of the parameters passed. All exactly the same as with Trace. Some events have the query as part of the package. Other events, not the straight "what's that query doing" ones, you can use what's called an Action to add query text. Some events, usually unrelated to queries, may not return the query, even with the Action. However, there's a thing called causality tracking that you can enable. Capture the standard query event, which is what we're talking about here anyway, like sql_batch_completed, add in whatever other events you want, recompile, stats updates, pretty much anything, add causality tracking. Then, all the events associated with the query get a GUID and an order assignment. All very neat and cool, and something you can't do in Trace.

    As to links, tons of stuff on my blog, but for something official, let's just go with rpc_completed. If you look, in addition to object_id and object_name, there's a textdata column. That is the actual rpc call, the full EXEC dbo.MyProc @Id = 42;. If we look at sql_batch_completed, the batch text is again in the textdata column. Now, those two links are to the internal definitions. The actual events look a little different, but the behavior is identical.

    You absolutely can get the query text back, a whole bunch of different ways.

    No need to delete or edit stuff, I was just curious where you were coming from on that.

    Awesome, Grant.  You may have finally sold me on EE.  I'll give it a shot.  Thank you.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jonathan AC Roberts wrote:

    SELECT dest.TEXT AS [Query],
    deqs.execution_count [Count],
    deqs.last_execution_time AS [Time]
    FROM sys.dm_exec_query_stats AS deqs
    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
    ORDER BY deqs.last_execution_time DESC

    https://blog.sqlauthority.com/2016/09/04/find-recent-executed-queries-sql-server-interview-question-week-086/

    Just to warn again, you have to understand the unpredictable time limits on the availability of data in sys.dm_exec_query_stats.  The data may have a lifetime of days or milliseconds.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  •  

    Thank you All! for the information and help. I really do appreciate your time and admire the intention to help.

    I will test out the suggestions and methods recommend. Learning every day 🙂

     

Viewing 10 posts - 1 through 9 (of 9 total)

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