長時間執行的 SQL 需要進度條或即時偵錯。SQL Server DMV sys.dm_exec_requests + sys.dm_exec_sql_text 可取得當前執行語句、等待類型、阻塞鏈等關鍵資訊。
USE [master];
GO
CREATE PROC [dbo].[dba_WhatSQLIsExecuting]
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT
[Spid] = session_id,
ecid,
[Database] = DB_NAME(sp.dbid),
[User] = nt_username,
[Status] = er.status,
[Wait] = wait_type,
[Individual Query] = SUBSTRING(qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset END - er.statement_start_offset)/2),
[Parent Query] = qt.text,
Program = program_name,
Hostname,
nt_domain,
start_time
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE session_id > 50 -- 忽略系統 SPID
AND session_id <> @@SPID -- 忽略本查詢本身
ORDER BY 1, 2;
END;
GO
-- 任何資料庫均可呼叫
EXEC master.dbo.dba_WhatSQLIsExecuting;
| 欄位 | 含義 |
|---|---|
| Spid | Session ID,可用於 KILL |
| Individual Query | 當前正在執行的具體語句片段(去除批次其餘部分) |
| Parent Query | 整個批次/預存程序完整文本 |
| Wait | 等待類型(LCK_M_*, PAGEIOLATCH_*, CXPACKET 等),定位瓶頸關鍵 |
| Status | running / suspended / runnable / sleeping |
| start_time | 請求開始時間,推算執行時長 |
start_time < DATEADD(MINUTE, -10, GETDATE()) 且 status <> 'sleeping' 的記錄寫入告警表。sys.dm_os_waiting_tasks 遞迴查詢 blocking_session_id。BACKUP、INDEX REBUILD 等長操作,可讀取 percent_complete、estimated_completion_time。CREATE PROCEDURE 權限。VIEW SERVER STATE 權限(GRANT VIEW SERVER STATE TO [login])。部署 dba_WhatSQLIsExecuting 即可隨時掌握 SQL Server 實例「正在跑什麼 SQL、等什麼資源、跑多久了」。配合排程寫入歷史表,可構建輕量級 SQL 審計與效能基線系統。