How Can I Retrieve and Track Query History in SQL Server Management Studio?

Tracking Down Past Queries in SQL Server Management Studio (SSMS)

Sometimes, you need to review previously run queries in SSMS. While SSMS doesn't have a built-in query history tracker, there are workarounds.

Finding Query History Data

If the SQL Server instance hasn't been restarted since running the query, the query plan might still be in the plan cache. Try this query:

<code class="language-sql">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'%<unique_query_identifier>%';</code>

Replace <unique_query_identifier> with a portion of the query you remember.

If SSMS crashed and you lost your query history, check these recovery files:

<code>C:\Users\<username>\Documents\SQL Server Management Studio\Backup Files\</code>

Using Third-Party Tools and Tracing

For ongoing query history monitoring, explore tools like the SSMS Tools Pack. Alternatively, you can set up a lightweight SQL Server trace filtered by your login or hostname.

Detailed Query History with sys.dm_exec_query_stats

As Nenad-Zivkovic pointed out, using sys.dm_exec_query_stats provides more comprehensive query history details. This query joins it with other system views to order results by last execution time:

<code class="language-sql">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'%<unique_query_identifier>%'
ORDER BY s.last_execution_time DESC;</code>

Remember to replace <unique_query_identifier> with a unique part of your query.

The above is the detailed content of How Can I Retrieve and Track Query History in SQL Server Management Studio?. For more information, please follow other related articles on the PHP Chinese website!