SQL Server 查询当前正在执行的 SQL

Note：可以根据 session_id，执行 KILL { SESSION_ID } 来强制停止某个 SQL。

SELECT      CASE req.status
                 WHEN 'background' THEN N'后台'
                 WHEN 'rollback' THEN N'回滚'
                 WHEN 'running' THEN N'运行'
                 WHEN 'runnable' THEN N'就绪'
                 WHEN 'sleeping' THEN N'休眠'
                 WHEN 'suspended' THEN N'挂起'
                 ELSE CONCAT_WS(N': ', N'unknown', req.status)
            END status
          , CONCAT('kill ', req.session_id) kill_session_id
          , CONCAT(CASE WHEN req.blocking_session_id <> 0 THEN 'kill ' END, req.blocking_session_id) kill_blocking_session_id
          , DB_NAME(req.database_id) db_name
          , OBJECT_NAME(st.objectid, st.dbid) obj_name
          , REPLACE(
              REPLACE(
                SUBSTRING(
                  st.text
                , (req.statement_start_offset / 2) + 1
                , ((CASE req.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)ELSE req.statement_end_offset END - req.statement_start_offset) / 2) + 1
                )
              , CHAR(10)
              , '\n'
              )
            , CHAR(13)
            , '\r'
            ) AS statement_text
          , req.cpu_time
          , req.percent_complete
          , req.start_time
          , CASE WHEN req.total_elapsed_time < 1000 THEN FORMAT(req.total_elapsed_time, '#,0.## ms')
                 WHEN req.total_elapsed_time < 60000 THEN FORMAT(req.total_elapsed_time / 1000.0, '#,0.## s')
                 WHEN req.total_elapsed_time < 3600000 THEN FORMAT(req.total_elapsed_time / 60000.0, '#,0.## m')
                 ELSE FORMAT(req.total_elapsed_time / 3600000.0, '#,0.## h')
            END elapsed_time
          , req.estimated_completion_time
          , req.wait_time
          , req.wait_type
          , req.wait_resource
          , req.request_id
          , req.transaction_id
          , req.transaction_isolation_level
FROM        sys.dm_exec_requests req
OUTER APPLY (
  SELECT sqlTxt.dbid, sqlTxt.objectid, sqlTxt.number, sqlTxt.encrypted, sqlTxt.text
  FROM   sys.dm_exec_sql_text(req.sql_handle) sqlTxt
) st
WHERE       req.session_id <> @@SPID AND req.sql_handle <> ''
ORDER BY    req.total_elapsed_time DESC;