如何在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>\Documents\SQL Server Management Studio\Backup 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屋！