If the queries you are interested in are dynamic queries that fail intermittently, you could log the SQL and the datetime and user in a table at the time the dynamic statement is created. It would be done on a case-by case basis though as it requires specific programming to happen and it takes a littel extra processing time, so do it only for those few queries you are most concerned about. But having a log of the specific statements executed can really help when you are trying to find out why it fails once a month only. Dynamic queries are hard to thoroughly test and sometimes you get one specific input value that just won't work and doing this logging at the time the SQL is created is often the best way to see what specifically wasn in the sql that was built.

你可以使用“每次保存自动生成脚本”，如果你使用管理工作室。 这当然不是日志。 检查一下是否对你有用。；）

[因为这个问题可能会作为一个重复的问题结束。]

如果SQL Server还没有重新启动(并且计划还没有被清除等等)，您可能能够在计划缓存中找到该查询。

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崩溃而丢失了文件，你可以在这里找到恢复文件:

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

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

正如@Nenad-Zivkovic评论的那样，加入sys可能会有帮助。Dm_exec_query_stats和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的最慢的20个查询

select top 20 dest.text, deqs.execution_count, deqs.total_elapsed_time, deqs.total_worker_time,
(deqs.total_elapsed_time / deqs.execution_count) as 'avg_elapse_time',
(deqs.total_worker_time / deqs.execution_count) as 'avg_worker_time'
from sys.dm_exec_query_stats as deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) as dest
where deqs. last_execution_time >= '2021-09-27 16:00' /* YOUR DATE AND TIME HERE*/
order by 'avg_elapse_time' desc

正如其他人所指出的，您可以使用SQL Profiler，但也可以通过sp_trace_*系统存储过程来利用它的功能。例如，这个SQL片段将(至少在2000年;我认为这对SQL 2008是一样的，但你必须仔细检查)捕获RPC:Completed和SQL:BatchCompleted事件的所有查询需要超过10秒运行，并将输出保存到一个跟踪文件，您可以在稍后的日期在SQL分析器中打开:

DECLARE @TraceID INT
DECLARE @ON BIT
DECLARE @RetVal INT
SET @ON = 1

exec @RetVal = sp_trace_create @TraceID OUTPUT, 2, N'Y:\TraceFile.trc'
print 'This trace is Trace ID = ' + CAST(@TraceID AS NVARCHAR)
print 'Return value = ' + CAST(@RetVal AS NVARCHAR)
-- 10 = RPC:Completed
exec sp_trace_setevent @TraceID, 10, 1, @ON     -- Textdata
exec sp_trace_setevent @TraceID, 10, 3, @ON     -- DatabaseID
exec sp_trace_setevent @TraceID, 10, 12, @ON        -- SPID
exec sp_trace_setevent @TraceID, 10, 13, @ON        -- Duration
exec sp_trace_setevent @TraceID, 10, 14, @ON        -- StartTime
exec sp_trace_setevent @TraceID, 10, 15, @ON        -- EndTime

-- 12 = SQL:BatchCompleted
exec sp_trace_setevent @TraceID, 12, 1, @ON     -- Textdata
exec sp_trace_setevent @TraceID, 12, 3, @ON     -- DatabaseID
exec sp_trace_setevent @TraceID, 12, 12, @ON        -- SPID
exec sp_trace_setevent @TraceID, 12, 13, @ON        -- Duration
exec sp_trace_setevent @TraceID, 12, 14, @ON        -- StartTime
exec sp_trace_setevent @TraceID, 12, 15, @ON        -- EndTime

-- Filter for duration [column 13] greater than [operation 2] 10 seconds (= 10,000ms)
declare @duration bigint
set @duration = 10000
exec sp_trace_setfilter @TraceID, 13, 0, 2, @duration

您可以从Books Online中找到每个跟踪事件、列等的ID;只需搜索sp_trace_create、sp_trace_setevent和sp_trace_setfiler sppros即可。然后，您可以像下面这样控制跟踪:

exec sp_trace_setstatus 15, 0       -- Stop the trace
exec sp_trace_setstatus 15, 1       -- Start the trace
exec sp_trace_setstatus 15, 2       -- Close the trace file and delete the trace settings

.．.其中'15'是跟踪ID(如上面的第一个脚本所删除的sp_trace_create所报告的)。

您可以查看哪些跟踪正在运行:

select * from ::fn_trace_getinfo(default)

我唯一要谨慎说的是——我不知道这会给你的系统带来多少负载;它将添加一些，但“一些”的大小可能取决于您的服务器有多忙。

如果需要，可以通过SQL Profiler监视SQL查询