Sql-server – Find all the queries executed recently on a database

sql serversql-server-2008-r2ssms

[I am a beginner level T-SQL Programmer]
[..and hopefully I am on the right stack exchange site]

I would like to get a list of all the queries I executed (in the least, those I executed today since morning). I need to make a report on the execution times of the queries.

Online search didn't yield me much useful info. The only query that I found online which seemed pretty close is 

SELECT
    deqs.last_execution_time AS [Time], 
    dest.TEXT AS [Query]
 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

This query returned some odd results (most of which were a bunch of sprocs). Moreover, all these results show queries executed since today afternoon (I need queries from morning).

I couldn't find anything in the previous questions (if similar question has already been asked, please point me to it).

I saw some suggestions regarding SQL Profiler, but I guess profiler would help me only if I have already started the trace (correct me if I am wrong).

Can someone suggest me how I should go about to get a list of all queries that have been executed on the database since morning (including the query execution times)..

[It would be helpful (not a requirement) if I can also somehow get the username of the user who executed the query]

Best Answer

This query returned some odd results (most of which were a bunch of sprocs). Moreover, all these results show queries executed since today afternoon (I need queries from morning).

That's because you are looking in the procedure cache, and the plans that were used for the morning might no longer live there (due to memory pressure, server/instance restart, manually clearing the proc cache, etc.).

The real way to get the queries executed against an instance (or more specifically a database) would be to create a SQL Trace or an Extended Events session. Properly created, either of these will give you the information you are looking for.

If you are looking for execution statistics of this morning and only this morning (i.e. setting up the aforementioned monitoring implementations as a proactive and to-do-next-time task isn't sufficient), then unless that was already created there will be no way natively to get this information.

For future reference, start with a SQL Trace that captures the SQL:StmtCompleted event. In XE it would be the sql_statement_completed event.

In my opinion, instead of continuing your search for remnants of query execution statistics I would start spending time on figuring out how to re-execute the workload that you are trying to measure. But this time prior to that, set up the proper tracing/monitoring.

Related Question