搜尋本站文章

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






下載與安裝 sp_whoisactive

-- 200_sp_whoisactive_official_website



-- 201_who_is_active_v11_17.zip



-- 202_who_is_active_v11_17.sql



-- 203_Create_sp_WhoIsActive



-- 204_Create_sp_WhoIsActive






參考資料

sp_whoisactive
http://whoisactive.com/

sp_whoisactive: The Big Fixes!
http://sqlblog.com/blogs/adam_machanic/archive/2016/10/18/sp-whoisactive-the-big-fixes.aspx

How to Use sp_WhoIsActive to Find Slow SQL Server Queries
https://www.brentozar.com/archive/2010/09/sql-server-dba-scripts-how-to-find-slow-sql-server-queries/

sp_WhoIsActive utility for SQL Server troubleshooting
http://www.sqlserver-dba.com/2015/09/sp_whoisactive-utility-for-sql-server-troubleshooting.html

How to monitor Blocked Processes with SQL Alert and email sp_whoisActive report
http://www.sqlserver-dba.com/2017/01/how-to-monitor-blocked-processes-with-sql-alert-and-email-sp_whoisactive-report.html

How to Log Activity Using sp_whoisactive in a Loop
https://www.brentozar.com/responder/log-sp_whoisactive-to-a-table/

INF: Understanding and resolving SQL Server blocking problems
https://support.microsoft.com/en-us/help/224453/inf-understanding-and-resolving-sql-server-blocking-problems

sp_whoisactive for Azure SQL Database
http://sqlblog.com/blogs/adam_machanic/archive/2016/04/14/sp-whoisactive-for-azure-sql-database-attempt-2.aspx

下載  20170709_sp_WhoIsActive
https://drive.google.com/drive/folders/0B9PQZW3M2F40RU43aFNZUWtJc0k?usp=sharing