SQLServer中sp_Who、sp_Who2和sp_WhoIsActive介绍和查看监视运行
SQLServer中sp_Who、sp_Who2和sp_WhoIsActive介绍和查看监视运行
使用 sp_WhoIsActive 监视活动
sp_WhoIsActive存储过程可以监视SQL Server中当前正在运行的活动。
介绍
如果服务器速度变慢时，数据库管理员需要不断检查SQL Server实例上正在运行的操作。
系统存储过程“sp_who”和“sp_who2”可以检查实例上所有正在运行的进程，但它们缺乏很多有助于性能监控和分析过程的有用信息，也显示了很多如系统进程等无用的信息。
为此，Adam Machanic（自 2004 年起成为 Microsoft MVP）开发了一个名为“sp_whoisactive”的更强大的存储过程，以填补 DBA 的实际需求与当前提供的过程（sp_who 和 sp_who2）之间的差距。
下面将会简要介绍sp_who和sp_who2存储过程，及如何使用sp_whoisactive
sp_Who和sp_Who2介绍
官方介绍，sp_who提供有关 Microsoft SQL Server 数据库引擎实例中当前用户、会话和进程的信息。可以过滤信息以仅返回那些非空闲、属于特定用户或属于特定会话的进程。
sp_who返回诸如会话进程ID (SPID-session process ID)、执行上下文ID (ECID-execution context ID)、进程状态(status)、阻塞会话ID(blk)、数据库名称(dbname)、与此进程关联的登录名(loginame)和主机名(hostname)以及命令类型(Cmd-command type)等信息。
blk为阻塞执行的会话ID
sp_Who2与sp_Who类似，但没有文档记录也没有支持，但它从当前进程返回更多信息和性能计数器，例如执行命令的程序名称(ProgramName)、磁盘IO(DiskIO-磁盘读写总次数)、CPU时间(CPUTime-占用CPU运行的总时间)、上次批处理执行时间(LastBatch-最后一次调用存储过程或者执行查询的时间)。
如截图所示，输出显示了包含不需要的所有正在运行的系统和用户进程，并且只能使用loginname和sessionid过滤结果。而实际可能需要隐藏系统进程。
并且，以上输出不包含任何有关当前运行的SQL命令的信息，如开始执行时间、执行持续时间、等待信息等。
sp_Who和sp_Who2返回活动的、某个用户或SPID的进程信息
- 返回某个用户的进程信息
如下，返回sa用户的会话进程信息
exec sp_who 'sa';
-- exec sp_who2 'sa';
-- exec sp_who @loginame='sa';
- 返回活动的进程信息
exec sp_who 'ACTIVE';
-- EXEC sp_who2 'active';
- 返回指定进程ID的信息
exec sp_who2 '1';
exec sp_who2 1;
-- exec sp_who '1';
-- exec sp_who 1;
保存sp_Who2过滤结果
如下，有个示例可以保存sp_Who2的结果到表或表变量或临时表中。用于过滤显示
DECLARE @Table TABLE(
SPID INT,
Status VARCHAR(MAX),
LOGIN VARCHAR(MAX),
HostName VARCHAR(MAX),
BlkBy VARCHAR(MAX),
DBName VARCHAR(MAX),
Command VARCHAR(MAX),
CPUTime INT,
DiskIO INT,
LastBatch VARCHAR(MAX),
ProgramName VARCHAR(MAX),
SPID_1 INT,
REQUESTID INT
)
INSERT INTO @Table EXEC sp_who2
SELECT *
FROM @Table
WHERE ....
借助sysprocesses获取类似信息：
SELECT spid,
sp.[status],
loginame [Login],
hostname,
blocked BlkBy,
sd.name DBName,
cmd Command,
cpu CPUTime,
physical_io DiskIO,
last_batch LastBatch,
[program_name] ProgramName
FROM master.dbo.sysprocesses sp
JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid
ORDER BY spid
下载安装whoisactive存储过程
可以直接从whoisactive下载页下载该存储过程，或者GitHub repository。
最新版是2018发布的，适用于SQL Server2005+所有的版本。
下载后解压缩，并打开who_is_active.sql文件，在SSMS中执行（who_is_active_v11_32.sql）
使用sp_Whoisactive
exec sp_Whoisactive;
执行后，可以看到，默认仅返回正在运行的用户进程，并提供每个进程下面的信息：
sp_Whoisactive信息和sp_who、sp_who2对比：
sp_Whoisactive输出列信息的介绍
上面的对比已经对列进行了简要的介绍，下面再多做些说明。
- dd hh:mm:ss:mss：显示命令的持续时间（执行时间）。可以使用此列来识别长时间运行的事务。例如，可以识别运行时间超过 1 小时的 SQL Server 会话。 它显示一个活动请求的查询运行时间。 在睡眠会话（sleeping session）的情况下，它显示自上次完成批以来的时间
- session_id：用户会话的 SP ID。
- Sql_text：正在运行的会话的SQL文本。它是一个超链接，点击它并获得完整的t-SQL。
- Login name：是连接到SQL Server并执行会话中指定的SQL的登录名
- Wait_info：它是一个有用的列，用于识别 SPID 的当前等待，例如 CXPACKET、ASYNC_NETWORK_IO 以及等待时间。请参阅有关 SQL Server等待类型的文章。
- Tempdb_allocations 和 tempdb_current：如果查询正在使用 TempDB 数据库，可以使用此列获取有关 tempdb 分配的信息。例如，如果我们使用一个表变量或临时表，它会在 TempDB 中创建，我们可以使用这两个列来跟踪信息。
- CPU：从中得到查询消耗的总 CPU 时间。
- Blocking_session_id：在阻塞的情况下，可以从这个列中获取阻塞的session-id。
- Reads和writes：给出当前查询的读写次数。
- Open_tran_count：当前会话打开的事务数。
- Percent complete：通常可以使用DMV sys.dm_exec_requests 检查少数命令完成状态的百分比，例如备份和恢复数据库命令。Sp_WhoIsActive使用该DMV信息并将其显示输出。
- Program name：给出了用户连接的应用程序名称，例如 Microsoft SQL Server Management Studio – Query、Azure Data Studio、SQL Server Agent 等。
更多的详细参数介绍推荐查看SQL Server - Using SP WhoIsActive to identify Locks, Blocks, Slow Queries, Running Queries, and more中的介绍
sp_whoisactive显示系统进程
Exec sp_whoisactive @show_system_spids = 1;
还可以使用@get_additional_info、@get_locks、@get_avg_time等参数查看更多信息。
sp_Whoisactive的@help参数
sp_whoisactive比较吸引人的一个功能是@help参数。所有相关信息都可以通过执行以下命令获得：
Exec sp_whoisactive @help = 1;
help命令包含三个部分：
- 一般信息：提供版本、版权、网站、反馈邮箱、URL等一般信息
- 参数说明：其中提供了所有可用参数及其说明和默认信息的列表
- 输出列描述：所有可用输出列的完整列表及其描述
sp_WhoIsActive过程的几个可选参数和用法
@find_block_leaders查看导致阻塞和被阻塞的会话
可以将 @find_block_leaders 参数的值设置为 1 并对 blocks_session_count 列的结果进行排序，用以检查导致阻塞和被阻塞的会话-sessions。
EXEC sp_WhoIsActive
@find_block_leaders = 1,
@sort_order = '[blocked_session_count] DESC';
从输出结果可以看到，session_id为55的会话导致了阻塞，后面是它阻塞的2个会话
@get_plans获取正在查询的执行计划
假设我们正在调查 SQL Server 中的性能问题，并且发现了有问题的查询。如果能得到对应的执行计划来查看查询执行中涉及的成本高的操作，那就太好了。
此时，可以指定参数@get_plans=1，它会在输出中附加一个带 XML 执行计划的列。
EXEC sp_WhoIsActive @get_plans = 1;
query_plan列输出执行计划，必须为当前正在执行的查询语句。如果已经执行完，或没有正在执行的SQL语句，将会返回NULL
点击查询计划（query_plan），可以看到该查询的执行计划。
@get_locks获取详细的锁信息
可以使用这个参数来获取一个 XML 片段，里面是有关 SQL Server 会话中持有的锁的详细信息。在输出中，会多出一个额外的locks列。如下所示，单击locks，将看到 XML 格式的详细锁信息。
EXEC sp_WhoIsActive @get_locks = 1;
@get_additional_info获取额外的会话参数信息
我们可以设置几个可能影响查询性能的会话参数。 Sp_WhoIsActive提供的参数 @get_additional_info ，会显示有关这些参数的信息。同样会额外输出一列，列为additional_info。
EXEC sp_WhoIsActive @get_additional_info = 1;
保存sp_whoisactive历史数据
获取sp_whoisactive结果表的创建脚本
sp_WhoIsActive提供了它的结果表的创建脚本，直接使用输出参数@schema即可获得。
declare @HistTableName VARCHAR(MAX)= 'HistWhoIsActive';
declare @schema VARCHAR(MAX);
EXEC sp_WhoIsActive @get_transaction_info = 1,
@get_outer_command = 1,
@get_plans = 1,
@return_schema = 1,
@schema = @schema OUTPUT;
-- 输出<table_name>占位组成的创建表的脚本
select @schema;
-- 生成创建表的脚本并执行创建
EXEC (REPLACE(@schema, '<table_name>', @destination_table));
在代理作业中计划执行sp_whoisactive
如果想要定期保存正在运行的进程信息以供进一步分析，则可以将sp_whoisactive放在代理作业中执行保存到表中。
创建一个新job并将以下代码放入作业步骤（job step·），根据需要修改前3个变量。如果不存在，代码将创建日志记录表，如果不存在则创建聚集索引，记录当前的活动并根据@retention变量清除旧数据。
应该多久收集一次活动？原作者认为每30-60秒收集一次 sp_WhoIsActive 数据，这是一个在记录足够的活动以解决生产问题和非常繁忙的环境中保持所需的数据存储之间的良好平衡。
如下，也可以将其改为一个存储过程来处理：
SET NOCOUNT ON;
DECLARE @retention INT = 7,
@destination_table VARCHAR(500) = 'WhoIsActive',
@destination_database sysname = 'Crap',
@schema VARCHAR(MAX),
@SQL NVARCHAR(4000),
@parameters NVARCHAR(500),
@exists BIT;
SET @destination_table = @destination_database + '.dbo.' + @destination_table;
--create the logging table
IF OBJECT_ID(@destination_table) IS NULL
BEGIN;
EXEC dbo.sp_WhoIsActive @get_transaction_info = 1,
@get_outer_command = 1,
@get_plans = 1,
@return_schema = 1,
@schema = @schema OUTPUT;
SET @schema = REPLACE(@schema, '<table_name>', @destination_table);
EXEC ( @schema );
END;
--create index on collection_time
SET @SQL
= 'USE ' + QUOTENAME(@destination_database)
+ '; IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(@destination_table) AND name = N''cx_collection_time'') SET @exists = 0';
SET @parameters = N'@destination_table varchar(500), @exists bit OUTPUT';
EXEC sys.sp_executesql @SQL, @parameters, @destination_table = @destination_table, @exists = @exists OUTPUT;
IF @exists = 0
BEGIN;
SET @SQL = 'CREATE CLUSTERED INDEX cx_collection_time ON ' + @destination_table + '(collection_time ASC)';
EXEC ( @SQL );
END;
--collect activity into logging table
EXEC dbo.sp_WhoIsActive @get_transaction_info = 1,
@get_outer_command = 1,
@get_plans = 1,
@destination_table = @destination_table;
--purge older data
SET @SQL
= 'DELETE FROM ' + @destination_table + ' WHERE collection_time < DATEADD(day, -' + CAST(@retention AS VARCHAR(10))
+ ', GETDATE());';
EXEC ( @SQL );
收集sp_whoisactive的信息记录，也可以使用Insert Into命令，Insert Into <hist_table_name> Exec sp_whoisactive;。仍然推荐上面的方法。
如何查询sp_whoisactive的结果
下面只是一个入门查询。可以根据早些时候用户抱怨查询非常缓慢，来查看特定时间段的信息。比如，我（原作者）曾经使用 WhoIsActive 日志表来确定是谁导致 tempdb 变得异常大。当我找到它时，tempdb 的大小为 500GB！我将查询切换为包括 tempdb_allocations 和 tempdb_current 以及 ORDER BY tempdb_allocations DESC，这样就会很明显知道业务用户正在查询的所有事情。
SELECT TOP 1000 CPU, reads, collection_time, start_time, [dd hh:mm:ss.mss] AS 'run duration', [program_name], login_name, database_name, session_id, blocking_session_id, wait_info, sql_text, *
FROM WhoIsActive
WHERE collection_time BETWEEN '2016-07-20 07:55:00.000' AND '2016-07-20 09:00:00.000'
AND login_name NOT IN ('DomainName\sqlservice')
--AND CAST(sql_text AS varchar(max)) LIKE '%some query%'
ORDER BY 1 DESC
如何使用 sp_WhoIsActive 查找 SQL Server 慢查询
本部分建议直接参考How to Use sp_WhoIsActive to Find Slow SQL Server Queries原文。原文提供了脚本工具下载（下载的脚本有些长，可能无法介绍…），且有一个视频教程（需qiang）
DBA需要可以快速找到哪个查询和存储过程运行得比较慢。
sp_WhoIsActive有各种参数，但要知道，传入的参数越多，sp_WhoIsActive需要做的工作就越多，以便从 SQL Server 的动态管理视图 (DMV) 中获取想要的数据，运行速度也会越慢。
下面是几个很有用的参数：
- @get_plans = 1 – 提供运行查询的执行计划。
- @get_locks = 1 – 提供一个 XML 片段，可以单击以查看每个查询拥有哪些表、行、对象等锁。当试图找出某个查询阻止其他查询执行的原因时很有用。
- @get_task_info = 2 – 如果一个查询并行进行，且你正在对 CXPACKET等待 (CXPACKET waits)进行故障排除，则可以找出查询中的每个任务正在等待什么。
Transact-SQL参考：sp_who
Transact-SQL 参考
sp_who
提供关于当前 Microsoft® SQL Server™ 用户和进程的信息。可以筛选返回的信息，以便只返回那些不是空闲的进程。
语法
sp_who [[@login_name =] 'login']
参数
[@login_name =] 'login'
是 SQL Server 上的用户登录名。login 的数据类型为 sysname，默认值是 NULL。如果没有指定名称，过程将报告全部活动的 SQL Server 用户。login 也可以是特定进程标识号码 (SPID)。若要返回有关活动进程的信息，请指定 ACTIVE。ACTIVE 从报告中排除等待用户下一个命令的进程。
返回代码值
0（成功）或 1（失败）
结果集
sp_who 返回包含以下信息的结果集。
| 列 | 数据类型 | 描述 |
|---|---|---|
| spid | smallint | 系统进程 ID。 |
| ecid | smallint | 与指定 SPID 相关联的给定线程的执行上下文 ID。
ECID = {0, 1, 2, 3, ...n}，其中 0 始终表示主或父线程，并且 {1, 2, 3, ...n} 表示子线程。 |
| status | nchar(30) | 进程状态。 |
| loginame | nchar(128) | 与特定进程相关联的登录名。 |
| hostname | nchar(128) | 每个进程的主机或计算机名。 |
| blk | char(5) | 如果存在阻塞进程，则是该阻塞进程的系统进程 ID。否则该列为零。
当与给定的 spid 相关联的事务受到孤立分布式事务的阻塞时，该列将对阻塞孤立事务返回 '-2'。 |
| dbname | nchar(128) | 进程使用的数据库。 |
| cmd | nchar(16) | 为此进程执行的 SQL Server 命令（Transact-SQL 语句、SQL Server 内部引擎处理等）。 |
sp_who 结果集将根据 spid 值按升序排序。如果是并行处理，则会为特定的 spid 创建子线程。主线程表示为 spid =xxx，并且 ecid =0。其余的子线程同样也是 spid = xxx，但 ecid > 0。这样，将会为该 spid 编号返回多行 — 它们在整个列表中放置该spid 的位置组合在一起。子线程将随机排列，但父线程除外 (ecid = 0)；在该 spid 中，父线程被首先列出。
注释
阻塞进程（可能含有排它锁）是控制其它进程所需要的资源的进程。
在 SQL Server 2000 中，所有孤立 DTC 事务的 SPID 都被赋予值 '-2'。孤立 DTC 事务就是不与任何 SPID 相关联的分布式事务。这样，当某一孤立事务阻塞其它进程时，可由其特殊的 '-2' SPID 值来识别该孤立分布式事务。有关更多信息，请参见 KILL。
SQL Server 2000 保留从 1 到 50 的 SPID 值以便内部使用，而 51 或更大的 SPID 值则代表用户会话。
权限
执行权限默认授予 public 角色。
示例
A. 列出全部当前进程
此示例使用没有参数的 sp_who 报告所有当前用户。
USE master
EXEC sp_who
下面是结果集：
spid ecid status loginame hostname blk dbname cmd
---- ---- ------ ------------ -------- --- ------ -----
1 0 background sa 0 pubs LAZY WRITER
2 0 sleeping sa 0 pubs LOG WRITER
3 0 background sa 0 master SIGNAL HANDLER
4 0 background sa 0 pubs RA MANAGER
5 0 background sa 0 master TASK MANAGER
6 0 sleeping sa 0 pubs CHECKPOINT SLEEP
7 0 background sa 0 master TASK MANAGER
8 0 background sa 0 master TASK MANAGER
9 0 background sa 0 master TASK MANAGER
10 0 background sa 0 master TASK MANAGER
11 0 background sa 0 master TASK MANAGER
51 0 runnable DOMAIN
loginX serverX 0 Nwind BACKUP DATABASE
51 2 runnable DOMAIN
loginX serverX 0 Nwind BACKUP DATABASE
51 1 runnable DOMAIN
loginX serverX 0 Nwind BACKUP DATABASE
52 0 sleeping DOMAIN
loginX serverX 0 master AWAITING COMMAND
53 0 runnable DOMAIN
loginX serverX 0 pubs SELECT
(16 row(s) affected)
B. 列出特定用户的进程
此示例显示如何通过登录名查看有关单个当前用户的信息。
USE master
EXEC sp_who 'janetl'
C. 显示所有活动进程
USE master
EXEC sp_who 'active'
D. 通过进程 ID 显示特定进程
USE master
EXEC sp_who '10' --specifies the process_id
请参见
sp_who使用
[SQL Server] sp_who, sp_who2和sp_who3
Spid (系统进程ID)
status (进程状态)
loginame (用户登录名)
hostname(用户主机名)
blk (阻塞进程的SPID)
dbname (进程正在使用的数据库名)
Cmd (当前正在执行的命令类型)
sp_who2除了显示上面sp_who的输出信息外，还显示下面的信息： (可选参数LoginName, 或active代表活动会话数)
CPUTime (进程占用的总CPU时间)
DiskIO (进程对磁盘读的总次数)
LastBatch (客户最后一次调用存储过程或者执行查询的时间)
ProgramName (用来初始化连接的应用程序名称，或者主机名)
USE master GO SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 and cpu > 1000 and status = 'suspended'--AND DB_NAME([dbid])='gposdb' SELECT COUNT(*) FROM [sys].[dm_exec_sessions] WHERE [session_id]>50
CREATE PROCEDURE sp_who3
( @SessionID int = NULL )
AS
BEGIN
SELECT
SPID = er.session_id
,Status = ses.status
,[Login] = ses.login_name
,Host = ses.host_name
,BlkBy = er.blocking_session_id
,DBName = DB_Name(er.database_id)
,CommandType = er.command
,SQLStatement = st.text
,ObjectName = OBJECT_NAME(st.objectid)
,ElapsedMS = er.total_elapsed_time
,CPUTime = er.cpu_time
,IOReads = er.logical_reads + er.reads
,IOWrites = er.writes
,LastWaitType = er.last_wait_type
,StartTime = er.start_time
,Protocol = con.net_transport
,ConnectionWrites = con.num_writes
,ConnectionReads = con.num_reads
,ClientAddress = con.client_net_address
,Authentication = con.auth_scheme
FROM sys.dm_exec_requests er
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
WHERE er.session_id > 50
AND @SessionID IS NULL OR er.session_id = @SessionID
ORDER BY
er.blocking_session_id DESC
,er.session_id
END
go
