工作中，需要对SQL Server做些日常基础指标的监控和告警
大事务监控
代码语言：sql复制 SELECT top 10
b.session_id 'Session ID',
CAST(Db_name(a.database_id) AS VARCHAR(20)) 'Database Name',
c.command,
Substring(st.TEXT, ( c.statement_start_offset / 2 ) 1,
( (
CASE c.statement_end_offset
WHEN -1 THEN Datalength(st.TEXT)
ELSE c.statement_end_offset
END
-
c.statement_start_offset ) / 2 ) 1)
statement_text,
Coalesce(Quotename(Db_name(st.dbid)) N'.' Quotename(
Object_schema_name(st.objectid,
st.dbid))
N'.' Quotename(Object_name(st.objectid, st.dbid)), '')
command_text,
c.wait_type,
c.wait_time,
a.database_transaction_log_bytes_used / 1024.0 / 1024.0 'MB used',
a.database_transaction_log_bytes_used_system / 1024.0 / 1024.0 'MB used system',
a.database_transaction_log_bytes_reserved / 1024.0 / 1024.0 'MB reserved',
a.database_transaction_log_bytes_reserved_system / 1024.0 / 1024.0 'MB reserved system',
a.database_transaction_log_record_count
'Record count'
FROM sys.dm_tran_database_transactions a
JOIN sys.dm_tran_session_transactions b
ON a.transaction_id = b.transaction_id
JOIN sys.dm_exec_requests c
CROSS APPLY sys.Dm_exec_sql_text(c.sql_handle) AS st
ON b.session_id = c.session_id
-- where a.database_transaction_log_bytes_used / 1024.0 / 1024.0 > 5 -- 一般建议只监控超过5MB的事务
ORDER BY 'MB used' DESC ;
大事务慢查询
代码语言：sql复制-- 下面的sql里面限定的阈值5秒
SELECT
r.session_id,
r.start_time,
TotalElapsedTime_ms = r.total_elapsed_time,
r.[status],
r.command,
DatabaseName = DB_Name(r.database_id),
r.wait_type,
r.last_wait_type,
r.wait_resource,
r.cpu_time,
r.reads,
r.writes,
r.logical_reads,
t.[text] AS [executing batch],
SUBSTRING(
t.[text],
r.statement_start_offset / 2,
( CASE
WHEN r.statement_end_offset = -1 THEN DATALENGTH (t.[text])
ELSE r.statement_end_offset
END - r.statement_start_offset
) / 2
) AS [executing statement],
p.query_plan
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS p
where r.total_elapsed_time >5000
ORDER BY r.total_elapsed_time DESC;参考：
https://www.sqlservercentral.com/articles/monitoring-longest-running-transaction-using-sql-server-agent-alerts
https://success.blueyonder.com/s/article/How-to-see-long-running-open-transactions-in-a-SQL-Server-database?language=en_US
