2023. 9. 11. 09:03
카테고리 없음
CREATE PROC usp_chk_long_run_query
AS
SET NOCOUNT ON;
SELECT COUNT(*) AS long_run_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
AND r.total_elapsed_time > (30 * 60 * 1000)
AND command NOT IN (
'BACKUP DATABASE'
,'KILLED/ROLLBACK'
)
AND DATEPART(HH, getdate()) BETWEEN 8 AND 20
AND DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
AND wait_type NOT IN('SP_SERVER_DIAGNOSTICS_SLEEP', 'OLEDB', 'TRACEWRITE')