Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

[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]

share|improve this question

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer
    
You can also use certain 3rd party tools to do this for you. :-) –  Aaron Bertrand Feb 28 '13 at 21:15
    
@AaronBertrand You mean looking through the log file? :-) I'm not familiar with a suite of tools that can scrub that information for you. Do you have a list? –  Thomas Stringer Feb 28 '13 at 21:16
    
Uh, yep. Totally. :-) –  Aaron Bertrand Feb 28 '13 at 21:17
    
@AaronBertrand Post away, my friend :-D –  Thomas Stringer Feb 28 '13 at 21:18
1  
You mean about SQL Sentry Performance Advisor, which - among many other things - captures statements and plans for any query that runs over a certain duration (or read count) that you can specify, and keeps them in history for as long as you want? –  Aaron Bertrand Feb 28 '13 at 21:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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