查询历史是否存储在一些日志文件中?如果有，你能告诉我怎么找到他们的位置吗?如果没有，你能给我一些建议吗?
当前回答
一个稍微开箱即用的方法是在AutoHotKey中编写一个解决方案。我用了这个，它不是完美的，但是有效而且免费。从本质上讲，这个脚本为CTRL+SHIFT+R分配了一个热键，它将复制SSMS (CTRL+C)中所选的SQL，保存一个日期戳SQL文件，然后执行突出显示的查询(F5)。如果您不习惯AHK脚本，则前导分号是注释。
;CTRL+SHIFT+R to run a query that is first saved off
^+r::
;Copy
Send, ^c
; Set variables
EnvGet, HomeDir, USERPROFILE
FormatTime, DateString,,yyyyMMdd
FormatTime, TimeString,,hhmmss
; Make a spot to save the clipboard
FileCreateDir %HomeDir%\Documents\sqlhist\%DateString%
FileAppend, %Clipboard%, %HomeDir%\Documents\sqlhist\%DateString%\%TimeString%.sql
; execute the query
Send, {f5}
Return
最大的限制是，如果你点击“执行”而不是使用键盘快捷键，这个脚本将无法工作，并且这个脚本不会保存整个文件-只保存选中的文本。但是，您总是可以修改脚本以执行查询，然后在复制/保存之前选择所有(CTRL+A)。
使用具有“在文件中查找”功能的现代编辑器可以让您搜索SQL历史记录。您甚至可以把您的文件刮到SQLite3数据库中以查询您的查询。
其他回答
查询历史可以通过系统视图查看:
sys.dm_exec_query_stats sys.dm_exec_sql_text sys.dm_exec_query_plan
例如，使用以下查询:
select top(100)
creation_time,
last_execution_time,
execution_count,
total_worker_time/1000 as CPU,
convert(money, (total_worker_time))/(execution_count*1000)as [AvgCPUTime],
qs.total_elapsed_time/1000 as TotDuration,
convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur],
total_logical_reads as [Reads],
total_logical_writes as [Writes],
total_logical_reads+total_logical_writes as [AggIO],
convert(money, (total_logical_reads+total_logical_writes)/(execution_count + 0.0)) as [AvgIO],
[sql_handle],
plan_handle,
statement_start_offset,
statement_end_offset,
plan_generation_num,
total_physical_reads,
convert(money, total_physical_reads/(execution_count + 0.0)) as [AvgIOPhysicalReads],
convert(money, total_logical_reads/(execution_count + 0.0)) as [AvgIOLogicalReads],
convert(money, total_logical_writes/(execution_count + 0.0)) as [AvgIOLogicalWrites],
query_hash,
query_plan_hash,
total_rows,
convert(money, total_rows/(execution_count + 0.0)) as [AvgRows],
total_dop,
convert(money, total_dop/(execution_count + 0.0)) as [AvgDop],
total_grant_kb,
convert(money, total_grant_kb/(execution_count + 0.0)) as [AvgGrantKb],
total_used_grant_kb,
convert(money, total_used_grant_kb/(execution_count + 0.0)) as [AvgUsedGrantKb],
total_ideal_grant_kb,
convert(money, total_ideal_grant_kb/(execution_count + 0.0)) as [AvgIdealGrantKb],
total_reserved_threads,
convert(money, total_reserved_threads/(execution_count + 0.0)) as [AvgReservedThreads],
total_used_threads,
convert(money, total_used_threads/(execution_count + 0.0)) as [AvgUsedThreads],
case
when sql_handle IS NULL then ' '
else(substring(st.text,(qs.statement_start_offset+2)/2,(
case
when qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2
else qs.statement_end_offset
end - qs.statement_start_offset)/2 ))
end as query_text,
db_name(st.dbid) as database_name,
object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as [object_name],
sp.[query_plan]
from sys.dm_exec_query_stats as qs with(readuncommitted)
cross apply sys.dm_exec_sql_text(qs.[sql_handle]) as st
cross apply sys.dm_exec_query_plan(qs.[plan_handle]) as sp
WHERE st.[text] LIKE '%query%'
使用下面的脚本可以看到当前运行的查询:
select ES.[session_id]
,ER.[blocking_session_id]
,ER.[request_id]
,ER.[start_time]
,DateDiff(second, ER.[start_time], GetDate()) as [date_diffSec]
, COALESCE(
CAST(NULLIF(ER.[total_elapsed_time] / 1000, 0) as BIGINT)
,CASE WHEN (ES.[status] <> 'running' and isnull(ER.[status], '') <> 'running')
THEN DATEDIFF(ss,0,getdate() - nullif(ES.[last_request_end_time], '1900-01-01T00:00:00.000'))
END
) as [total_time, sec]
, CAST(NULLIF((CAST(ER.[total_elapsed_time] as BIGINT) - CAST(ER.[wait_time] AS BIGINT)) / 1000, 0 ) as bigint) as [work_time, sec]
, CASE WHEN (ER.[status] <> 'running' AND ISNULL(ER.[status],'') <> 'running')
THEN DATEDIFF(ss,0,getdate() - nullif(ES.[last_request_end_time], '1900-01-01T00:00:00.000'))
END as [sleep_time, sec] --Время сна в сек
, NULLIF( CAST((ER.[logical_reads] + ER.[writes]) * 8 / 1024 as numeric(38,2)), 0) as [IO, MB]
, CASE ER.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommited'
WHEN 2 THEN 'ReadCommited'
WHEN 3 THEN 'Repetable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END as [transaction_isolation_level_desc]
,ER.[status]
,ES.[status] as [status_session]
,ER.[command]
,ER.[percent_complete]
,DB_Name(coalesce(ER.[database_id], ES.[database_id])) as [DBName]
, SUBSTRING(
(select top(1) [text] from sys.dm_exec_sql_text(ER.[sql_handle]))
, ER.[statement_start_offset]/2+1
, (
CASE WHEN ((ER.[statement_start_offset]<0) OR (ER.[statement_end_offset]<0))
THEN DATALENGTH ((select top(1) [text] from sys.dm_exec_sql_text(ER.[sql_handle])))
ELSE ER.[statement_end_offset]
END
- ER.[statement_start_offset]
)/2 +1
) as [CURRENT_REQUEST]
,(select top(1) [text] from sys.dm_exec_sql_text(ER.[sql_handle])) as [TSQL]
,(select top(1) [objectid] from sys.dm_exec_sql_text(ER.[sql_handle])) as [objectid]
,(select top(1) [query_plan] from sys.dm_exec_query_plan(ER.[plan_handle])) as [QueryPlan]
,NULL as [event_info]--(select top(1) [event_info] from sys.dm_exec_input_buffer(ES.[session_id], ER.[request_id])) as [event_info]
,ER.[wait_type]
,ES.[login_time]
,ES.[host_name]
,ES.[program_name]
,cast(ER.[wait_time]/1000 as decimal(18,3)) as [wait_timeSec]
,ER.[wait_time]
,ER.[last_wait_type]
,ER.[wait_resource]
,ER.[open_transaction_count]
,ER.[open_resultset_count]
,ER.[transaction_id]
,ER.[context_info]
,ER.[estimated_completion_time]
,ER.[cpu_time]
,ER.[total_elapsed_time]
,ER.[scheduler_id]
,ER.[task_address]
,ER.[reads]
,ER.[writes]
,ER.[logical_reads]
,ER.[text_size]
,ER.[language]
,ER.[date_format]
,ER.[date_first]
,ER.[quoted_identifier]
,ER.[arithabort]
,ER.[ansi_null_dflt_on]
,ER.[ansi_defaults]
,ER.[ansi_warnings]
,ER.[ansi_padding]
,ER.[ansi_nulls]
,ER.[concat_null_yields_null]
,ER.[transaction_isolation_level]
,ER.[lock_timeout]
,ER.[deadlock_priority]
,ER.[row_count]
,ER.[prev_error]
,ER.[nest_level]
,ER.[granted_query_memory]
,ER.[executing_managed_code]
,ER.[group_id]
,ER.[query_hash]
,ER.[query_plan_hash]
,EC.[most_recent_session_id]
,EC.[connect_time]
,EC.[net_transport]
,EC.[protocol_type]
,EC.[protocol_version]
,EC.[endpoint_id]
,EC.[encrypt_option]
,EC.[auth_scheme]
,EC.[node_affinity]
,EC.[num_reads]
,EC.[num_writes]
,EC.[last_read]
,EC.[last_write]
,EC.[net_packet_size]
,EC.[client_net_address]
,EC.[client_tcp_port]
,EC.[local_net_address]
,EC.[local_tcp_port]
,EC.[parent_connection_id]
,EC.[most_recent_sql_handle]
,ES.[host_process_id]
,ES.[client_version]
,ES.[client_interface_name]
,ES.[security_id]
,ES.[login_name]
,ES.[nt_domain]
,ES.[nt_user_name]
,ES.[memory_usage]
,ES.[total_scheduled_time]
,ES.[last_request_start_time]
,ES.[last_request_end_time]
,ES.[is_user_process]
,ES.[original_security_id]
,ES.[original_login_name]
,ES.[last_successful_logon]
,ES.[last_unsuccessful_logon]
,ES.[unsuccessful_logons]
,ES.[authenticating_database_id]
,ER.[sql_handle]
,ER.[statement_start_offset]
,ER.[statement_end_offset]
,ER.[plan_handle]
,NULL as [dop]--ER.[dop]
,coalesce(ER.[database_id], ES.[database_id]) as [database_id]
,ER.[user_id]
,ER.[connection_id]
from sys.dm_exec_requests ER with(readuncommitted)
right join sys.dm_exec_sessions ES with(readuncommitted)
on ES.session_id = ER.session_id
left join sys.dm_exec_connections EC with(readuncommitted)
on EC.session_id = ES.session_id
where ER.[status] in ('suspended', 'running', 'runnable')
or exists (select top(1) 1 from sys.dm_exec_requests as ER0 where ER0.[blocking_session_id]=ES.[session_id])
该请求显示所有活动请求和显式阻止活动请求的所有请求。
所有这些和其他有用的脚本都作为SRV数据库中的表示形式实现，该数据库是免费分发的。 例如，第一个脚本来自视图[inf]。[vBigQuery]，第二个来自视图[inf].[vRequests]。
对于查询历史，也有各种第三方解决方案。 我使用Dbeaver的查询管理器: 和SSMS内嵌的SQL Tools中的查询执行历史:
你可以使用“每次保存自动生成脚本”，如果你使用管理工作室。 这当然不是日志。 检查一下是否对你有用。；）
我使用下面的查询来跟踪未启用跟踪分析器的SQL服务器上的应用程序活动。 该方法使用查询存储(SQL Server 2016+)而不是DMV的。这提供了更好的查看历史数据的能力，以及更快的查找。 捕获sp_who/sp_whoisactive无法捕获的短时间运行的查询是非常有效的。
/* Adjust script to your needs.
Run full script (F5) -> Interact with UI -> Run full script again (F5)
Output will contain the queries completed in that timeframe.
*/
/* Requires Query Store to be enabled:
ALTER DATABASE <db> SET QUERY_STORE = ON
ALTER DATABASE <db> SET QUERY_STORE (OPERATION_MODE = READ_WRITE, MAX_STORAGE_SIZE_MB = 100000)
*/
USE <db> /* Select your DB */
IF OBJECT_ID('tempdb..#lastendtime') IS NULL
SELECT GETUTCDATE() AS dt INTO #lastendtime
ELSE IF NOT EXISTS (SELECT * FROM #lastendtime)
INSERT INTO #lastendtime VALUES (GETUTCDATE())
;WITH T AS (
SELECT
DB_NAME() AS DBName
, s.name + '.' + o.name AS ObjectName
, qt.query_sql_text
, rs.runtime_stats_id
, p.query_id
, p.plan_id
, CAST(p.last_execution_time AS DATETIME) AS last_execution_time
, CASE WHEN p.last_execution_time > #lastendtime.dt THEN 'X' ELSE '' END AS New
, CAST(rs.last_duration / 1.0e6 AS DECIMAL(9,3)) last_duration_s
, rs.count_executions
, rs.last_rowcount
, rs.last_logical_io_reads
, rs.last_physical_io_reads
, q.query_parameterization_type_desc
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY plan_id, runtime_stats_id ORDER BY runtime_stats_id DESC) AS recent_stats_in_current_priod
FROM sys.query_store_runtime_stats
) AS rs
INNER JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
INNER JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id
INNER JOIN sys.query_store_query AS q ON q.query_id = p.query_id
INNER JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
LEFT OUTER JOIN sys.objects AS o ON o.object_id = q.object_id
LEFT OUTER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
CROSS APPLY #lastendtime
WHERE rsi.start_time <= GETUTCDATE() AND GETUTCDATE() < rsi.end_time
AND recent_stats_in_current_priod = 1
/* Adjust your filters: */
-- AND (s.name IN ('<myschema>') OR s.name IS NULL)
UNION
SELECT NULL,NULL,NULL,NULL,NULL,NULL,dt,NULL,NULL,NULL,NULL,NULL,NULL, NULL
FROM #lastendtime
)
SELECT * FROM T
WHERE T.query_sql_text IS NULL OR T.query_sql_text NOT LIKE '%#lastendtime%' -- do not show myself
ORDER BY last_execution_time DESC
TRUNCATE TABLE #lastendtime
INSERT INTO #lastendtime VALUES (GETUTCDATE())
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 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)
我唯一要谨慎说的是——我不知道这会给你的系统带来多少负载;它将添加一些，但“一些”的大小可能取决于您的服务器有多忙。
推荐文章
- 如何在Ruby On Rails中使用NuoDB手动执行SQL命令
- 查询JSON类型内的数组元素
- 确定记录是否存在的最快方法
- 如何配置slf4j-simple
- 获得PostgreSQL数据库中当前连接数的正确查询
- 在SQL选择语句Order By 1的目的是什么?
- MySQL数据库表中的最大记录数
- 如何在Python中记录源文件名称和行号
- 从现有模式生成表关系图(SQL Server)
- 我如何循环通过一组记录在SQL Server?
- MongoDB记录所有查询
- HyperLogLog算法是如何工作的?
- 数据库和模式的区别
- 如何在SQL Server中一次更改多个列
- 如何禁用标准错误流的日志记录?
