如何在 SQL Server Management Studio 中查看查询历史记录 [英] How to see query history in SQL Server Management Studio

问题描述

查询历史是否存储在某些日志文件中?如果是，你能告诉我如何找到他们的位置吗?如果没有，你能给我一些关于如何看到它的建议吗?

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?

推荐答案

[自从 这个问题 可能会作为重复问题关闭.]

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

如果 SQL Server 没有重新启动(并且计划没有被驱逐等)，您可能能够在计划缓存中找到查询.

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%';

如果您因为 Management Studio 崩溃而丢失了文件，您可以在此处找到恢复文件:

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

C:Users<you>DocumentsSQL Server Management StudioBackup Files

否则，您将需要使用其他东西来帮助您保存查询历史记录，例如 Ed 中提到的 SSMS 工具包Harper 的回答 - 尽管它在 SQL Server 2012+ 中不是免费的.或者，您可以设置一些基于登录名或主机名过滤的轻量级跟踪(但请为此使用服务器端跟踪，而不是 Profiler).

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).

正如@Nenad-Zivkovic 所评论的，加入sys.dm_exec_query_stats 并按last_execution_time 排序可能会有所帮助:

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;

这篇关于如何在 SQL Server Management Studio 中查看查询历史记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！