How to see query history in SQL Server Management Studio

Question

Is the query history stored in some log files? If yes, can you tell me how to find their location? If not, can you give me any advice on how to see it?

Answer 1

[Since this question will likely be closed as a duplicate.]

If SQL Server hasn't been restarted (and the plan hasn't been evicted, etc.), you may be able to find the query in the plan cache.

SELECT t.[text]
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE t.[text] LIKE N'%something unique about your query%';

If you lost the file because Management Studio crashed, you might be able to find recovery files here:

C:\Users\<you>\Documents\SQL Server Management Studio\Backup Files\

Otherwise you'll need to use something else going forward to help you save your query history, like SSMS Tools Pack as mentioned in Ed Harper's answer - though it isn't free in SQL Server 2012+. Or you can set up some lightweight tracing filtered on your login or host name (but please use a server-side trace, not Profiler, for this).

---

As @Nenad-Zivkovic commented, it might be helpful to join on sys.dm_exec_query_stats and order by last_execution_time:

SELECT t.[text], s.last_execution_time
FROM sys.dm_exec_cached_plans AS p
INNER JOIN sys.dm_exec_query_stats AS s
   ON p.plan_handle = s.plan_handle
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE t.[text] LIKE N'%something unique about your query%'
ORDER BY s.last_execution_time DESC;