德瑞克：SQL Server 學習筆記: [SQL Server] sp_whoisactive: find out long running queries (找出費時、耗資源的查詢)

2017-07-09

[SQL Server] sp_whoisactive: find out long running queries (找出費時、耗資源的查詢)

使用 sp_whoisactive，快速找出費時、耗資源的查詢。

作者： Adam Machanic - Microsoft MVP
http://sqlblog.com/blogs/adam_machanic/default.aspx

示範版本：
sp_whoisactive Version 11.17 - October 18, 2016 (Box versions 2005-2016 only. NOT for Azure.)

適用環境：
SQL Server 2005 ~ 2016，不適用於 Azure。

若是 Azure SQL Database，請改用此版本：
sp_whoisactive for Azure SQL Database

個人常用的範例

-- EX01_sp_WhoIsActive：增加顯示 執行計畫、交易資訊、封鎖資訊等
USE master
GO
EXEC sp_WhoIsActive @get_plans=1, @get_transaction_info=1, @find_block_leaders=1
GO

-- EX02_sp_WhoIsActive：增加顯示 執行計畫、交易資訊、封鎖資訊、系統資訊等
USE master
GO
EXEC sp_WhoIsActive @get_plans=1, @get_transaction_info=1, @find_block_leaders=1, @get_task_info=2
GO

補充包：
[SQL Server] sp_whoisactive: find out long running queries (找出費時、耗資源的查詢) (2)
http://sharedderrick.blogspot.tw/2017/08/sql-server-spwhoisactive-find-out-long.html

sp_whoisactive 參數說明

參數	說明
@get_plans = 1	增加顯示：執行計畫 If @get_plans = 1, gets the plan based on the request's statement offset If @get_plans = 2, gets the entire plan based on the request's plan_handle
@get_locks = 1	增加顯示：XML 格式的執行計畫 Gets associated locks for each request, aggregated in an XML format
@get_transaction_info=1	增加顯示：交易紀錄檔寫入資訊與交易持續時間 Enables pulling transaction log write info and transaction duration
@find_block_leaders = 1	增加顯示：封鎖鏈結、封鎖計數 Walk the blocking chain and count the number of total SPIDs blocked all the way down by a given session Also enables task_info Level 1, if @get_task_info is set to 0
@get_task_info = 2	增加顯示：active tasks, current wait stats, physical I/O, context switches, blocker, 檢視 CXPACKET wait 等 Get information on active tasks, based on three interest levels Level 0 does not pull any task-related information Level 1 is a lightweight mode that pulls the top non-CXPACKET wait, giving preference to blockers Level 2 pulls all available task-based metrics, including: number of active tasks, current wait stats, physical I/O, context switches, and blocker information

sp_whoisactive 使用範例

範例 1：sp_WhoIsActive

-- 01_sp_WhoIsActive without any parameters
USE master
GO
EXEC sp_WhoIsActive
GO

-- 02_sp_WhoIsActive SPID
EXEC sp_WhoIsActive 64
GO

-- 03_sp_WhoIsActive SPID
EXEC sp_WhoIsActive 66
GO

-- 010_sp_WhoIsActive without any parameters

-- 011_sp_WhoIsActive without any parameters_2

-- 012_sp_WhoIsActive without any parameters_3

-- 013_sp_WhoIsActive without any parameters_4

-- 014_sp_WhoIsActive_SPID

-- 015_sp_WhoIsActive_SPID_2

-- 016_sp_WhoIsActive_SPID_3

-- 017_檢視_sql_text

範例 2：@get_plans：增加顯示執行計畫

-- sp_WhoIsActive，@get_plans：增加顯示 執行計畫
USE master
GO
EXEC sp_WhoIsActive @get_plans = 1
GO

-- 018_增加顯示：執行計畫_get_plans

-- 019_增加顯示：執行計畫_get_plans_2

範例 3：@get_locks：增加顯示 XML 格式的執行計畫

-- sp_WhoIsActive，@get_locks：增加顯示 XML 格式的執行計畫
USE master
GO
EXEC sp_WhoIsActive @get_locks = 1 
GO

-- 020_增加顯示：執行計畫_get_locks

範例 4：@find_block_leaders：增加顯示封鎖鏈結、封鎖計數

-- sp_WhoIsActive，@find_block_leaders：增加顯示 封鎖鏈結、封鎖計數
USE master
GO
EXEC sp_WhoIsActive @find_block_leaders = 1 
GO

-- 021_增加顯示封鎖鏈結_封鎖計數_find_block_leaders

範例 5：@get_task_info：增加顯示 active tasks, current wait stats, physical I/O, context switches, blocker, CXPACKET wait

-- sp_WhoIsActive，@get_task_info：增加顯示 active tasks, current wait stats, physical I/O, context switches, blocker, CXPACKET wait
USE master
GO
EXEC sp_WhoIsActive @get_task_info = 2
GO

-- 022_增加顯示active tasks_current wait stats_physical IO_get_task_info