欢迎大家来到IT世界,在知识的湖畔探索吧!

使用 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';

欢迎大家来到IT世界,在知识的湖畔探索吧!

• 返回活动的进程信息

欢迎大家来到IT世界,在知识的湖畔探索吧!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的结果到表或表变量或临时表中。用于过滤显示

欢迎大家来到IT世界,在知识的湖畔探索吧!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命令包含三个部分：

1. 一般信息：提供版本、版权、网站、反馈邮箱、URL等一般信息
2. 参数说明：其中提供了所有可用参数及其说明和默认信息的列表
3. 输出列描述：所有可用输出列的完整列表及其描述

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)进行故障排除，则可以找出查询中的每个任务正在等待什么。