8

How can I get all the queries executed on a SQL Server in last week.

1

1 Answer 1

17

I don't expect you to be on 2016 yet as it's been released only very recently (at the time of writing) but if you have a 2016 server and you have Query Store enabled for your database, you can review your queries there.

Have a look at the documentation for Query Store which lists the following example use cases:

  • Determine the number of times a query was executed in a given time window, assisting a DBA in troubleshooting performance resource problems.

  • Identify top n queries (by execution time, memory consumption, etc.) in the past x hours.

If you want to have a log of your queries for future review you could look into Extended Events or server side traces, depending on your server version.

If you are running SQL 2005+ you can log queries using this article explaining server side trace.

If you are running a version more recent than 2008 you can use Extended Events which would be recommended over server side traces.

Look at the query posted here by Paul Randal showing you how to log expensive queries using Extended events. You could modify the WHERE from that query to log all queries.

That being said I don't think it's a good idea to log ALL of your queries. If you have a fairly active production system that would really slow your system down. Have at least a few filters, and log as few data as possible.

If you don't have any logging set up there is no way you can retroactively and reliably get that information.

The closest you can get is querying the plan cache with something like:

SELECT 
sql_text.text, 
st.last_execution_time,
DB_NAME(qp.dbid) as databasename
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(week, -1, getdate())
ORDER BY last_execution_time DESC;

That would list you all queries executed in the last week that still have the plan in cache along with the last execution time and database name (unless it's prepared statement).

But you need to realize this is no guaranteed way to get you ALL of your queries as there are multiple reasons a query could have been executed but never or no longer have a plan in cache.

For example there are ways to drop the entire plan cache or evict a single plan

If optimize for ad hoc workloads is active, a query which has only been executed once will not be retrieved by this query and plans that were once stored in cache can be evicted from the cache by anything that cause a plan recompile such as updated statistics, DDL statements, index rebuilds etc. Read through this whitepaper listing all circumstances that cause plan recompilation.

Not the answer you're looking for? Browse other questions tagged or ask your own question.