【SQLServer】使用sp_whoisactive记录sqlserver数据库活动

1.下载

https://github.com/amachanic/sp_whoisactive

 

2.执行who_is_active.sql脚本
下载完成后，应使用SQL Server Management Studio打开who_is_active.sql文件并执行脚本。

 

3.简单使用

exec sp_WhoIsActive

默认只是返回正在运行的用户进程

 

4.查询系统进程

Exec sp_whoisactive @show_system_spids = 1

 

5.你还可以使用其他参数进行查询
比如：@get_additional_info, @get_locks, @get_avg_time

 

6.查看帮助

Exec sp_whoisactive @help = 1

 

7.保存历史记录
保存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));

 

8.在代理作业中计划执行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 = 'DBAtools',
@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;。仍然推荐上面的方法。

 

9.查询结果
下面只是一个入门查询。可以根据早些时候用户抱怨查询非常缓慢，来查看特定时间段的信息。比如，我（原作者）曾经使用 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

 

10.如何使用sp_WhoIsActive查找SQL Server慢查询
本部分建议直接参考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/

DBA需要可以快速找到哪个查询和存储过程运行得比较慢。
sp_WhoIsActive有各种参数，但要知道，传入的参数越多，sp_WhoIsActive需要做的工作就越多，以便从SQL Server 的动态管理视图 (DMV) 中获取想要的数据，运行速度也会越慢。

下面是几个很有用的参数：
@get_plans = 1 – 提供运行查询的执行计划。
@get_locks = 1 – 提供一个 XML 片段，可以单击以查看每个查询拥有哪些表、行、对象等锁。当试图找出某个查询阻止其他查询执行的原因时很有用。
@get_task_info = 2 – 如果一个查询并行进行，且你正在对 CXPACKET等待 (CXPACKET waits)进行故障排除，则可以找出查询中的每个任务正在等待什么。