How to Find Recent Executed Queries in SQL Server? – Interview Question of the Week #087

Interviews are fun. Most of the time the interviewer has all the interesting questions, but I often come across a few individuals who stomps the interviewer. I was recently called by one of the organizations where I regularly help with their hiring process. They were stumped by interviewing candidates with one of the questions related to recent executed queries. Let us see the question and its answer.

Interviewer: How to find all the queries in SQL Server? Can you find it with the help of DMV?

How to Find Recent Executed Queries in SQL Server? - Interview Question of the Week #087 recentexecutedqueries-800x508

Solarwinds

Candidate: I do know if there is any DVM which stores all the queries executed in SQL Server.

Interviewer: Oh yes, there is one way. Here is the script:

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

Candidate: Yes, I am aware of this script, but this script will only give you recent queries from the cached plan. There is no guarantee that it will be accurate. It is just an indication and helps to do a basic diagnosis of the system. If you remove the plan from the cache, you will not see that row as a result of your query.

This is where the interviewer got confused and gave me a quick call. I totally agree with the candidate that this query is a good start to do basic diagnosis, but it depends on cached plan and only displays recently executed query from cache query plans.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
How to Get Started with SQL Server 2016? – Interview Question of the Week #086
Next Post
How to Hide Stored Procedure’s Code? – WITH ENCRYPTION – Interview Question of the Week #088

Related Posts

4 Comments. Leave new

  • How to find out recently executed queries with login user and system name (Client Name) ?

    Reply
  • Is there a way to do this on sql server 8.0.2039 // Ms Sql Server Standard // on MS Win NT 5.2 (those are the stats of the sql server I’m running on) That server does not like Cross Apply pretty much EVER and it also does not like and MAX when using other types of these “get last executed query” / “find all the queries I ran in the last 24 hours” code snippets.

    The other sql server (where i do NOT need to run this / where it works is) is 11.0.5388 // ms sql server standard // windows nt 6.3 – but like i said these types of queries WORK there but that is NOT where I’m needing this.

    Thanks!

    Reply
    • have you considered upgrading? I think 8 is 2000 the cross apply was introduced later than that its already too late to be an early adopter of sql 2017, but if you at least upgraded to at least 2014, you would be able to run the query and benefit from the new cardinality estimator and more.

      Reply
  • Hi
    Please let me know how we add user name in the below query,I want user name as well in the below query.
    (which user run by which query),am not able to find the user name.

    select db_name(qp.dbid)as databasename,
    sql_text.text as query
    ,st.last_execution_time
    from sys.dm_exec_query_stats st
    cross apply sys.dm_exec_sql_text(st.sql_handle)as sql_text
    inner join sys.dm_exec_cached_plans cp
    on cp.plan_handle=st.plan_handle
    cross apply sys.dm_exec_query_plan(cp.plan_handle) as qp
    where st.last_execution_time >=dateadd(month,-1,getdate())
    order by st.last_execution_time desc;

    Thanks,
    Suma

    Reply

Leave a Reply

Menu