我们经常使用sp_whoisactive来排查性能问题，但是有时候我们运行sp_whoisactive知道性能问题后，这个问题已经消失了。所以我们需要一种方法可以自动收集sp_whoisactive执行的结果，并且以后可以对收集到的信息进行分析。
假设我们已经创建了sp_whoisactive，我们可以通过执行带参数@help=1来了解sp_whoisactive的更多信息。
例如：
EXEC sp_whoisactive @help = 1;
执行结果如下：
我们可以看到三部分：
- 有关版本、版权和联系信息的头信息。
- 具体的默认值的参数和每个参数的简要描述。
- 可以返回的列、列数据类型和简要描述。
1、生成创建表的脚本
以下命令将生产创建存储数据所需表的代码。
DECLARE @table_creation_script VARCHAR(MAX);
EXEC sp_WhoIsActive @schema = @table_creation_script OUTPUT,
@return_schema = 1;
PRINT @table_creation_script;
复制脚本生成的结果如下：
CREATE TABLE <table_name>
(
[dd hh:mm:ss.mss] VARCHAR(8000) NULL,
[session_id] SMALLINT NOT NULL,
[sql_text] XML NULL,
[login_name] NVARCHAR(128) NOT NULL,
[wait_info] NVARCHAR(4000) NULL,
[CPU] VARCHAR(30) NULL,
[tempdb_allocations] VARCHAR(30) NULL,
[tempdb_current] VARCHAR(30) NULL,
[blocking_session_id] SMALLINT NULL,
[reads] VARCHAR(30) NULL,
[writes] VARCHAR(30) NULL,
[physical_reads] VARCHAR(30) NULL,
[used_memory] VARCHAR(30) NULL,
[status] VARCHAR(30) NOT NULL,
[open_tran_count] VARCHAR(30) NULL,
[percent_complete] VARCHAR(30) NULL,
[host_name] NVARCHAR(128) NULL,
[database_name] NVARCHAR(128) NULL,
[program_name] NVARCHAR(128) NULL,
[start_time] DATETIME NOT NULL,
[login_time] DATETIME NULL,
[request_id] INT NULL,
[collection_time] DATETIME NOT NULL
);
我们只要把脚本中的字符串<table_name>替换为我们要创建的表名即可，这里我们使用info_whoisactive。
执行下面语句就可以收集我们的执行结果了。
EXEC sp_WhoIsActive @destination_table = 'dbo.info_whoisactive';
但是，此表的创建脚本包含许多列，这些列都是默认列，当在没有任何参数的情况下运行sp_WhoIsActive ，但我们可能不一定需要所有信息。另一方面，默认列可能不包含我们需要的内容，例如，我可能不关心 [request_id] 或 [open_tran_count]，但我确实需要一个列，可以判断哪个会话是多个会话阻止方案中的阻止链的源头。
因此，我定义了我想要的列表，然后重新生成创建表的脚本。
-- generate a script to create a table that can save sp_whoisactive result
DECLARE @table_creation_script VARCHAR(MAX);
EXEC sp_WhoIsActive @get_outer_command = 1,
@output_column_list = '[dd%][session_id][sql_command][sql_text][login_name][host_name][database_name][wait_info][blocking_session_id][blocked_session_count][percent_complete][cpu][used_memory][reads][writes][program_name][collection_time]',
@find_block_leaders = 1,
@schema = @table_creation_script OUTPUT,
@return_schema = 1;
PRINT @table_creation_script;
运行后会得到下面的脚本：
CREATE TABLE <table_name>
(
[dd hh:mm:ss.mss] VARCHAR(8000) NULL,
[session_id] SMALLINT NOT NULL,
[sql_command] XML NULL,
[sql_text] XML NULL,
[login_name] NVARCHAR(128) NOT NULL,
[host_name] NVARCHAR(128) NULL,
[database_name] NVARCHAR(128) NULL,
[wait_info] NVARCHAR(4000) NULL,
[blocking_session_id] SMALLINT NULL,
[blocked_session_count] VARCHAR(30) NULL,
[percent_complete] VARCHAR(30) NULL,
[CPU] VARCHAR(30) NULL,
[used_memory] VARCHAR(30) NULL,
[reads] VARCHAR(30) NULL,
[writes] VARCHAR(30) NULL,
[program_name] NVARCHAR(128) NULL,
[collection_time] DATETIME NOT NULL
);
同样的我们只要把脚本中的字符串<table_name>替换为我们要创建的表名即可，这里我们使用info_whoisactive。
执行下面语句就可以收集我们的执行结果了。
EXEC sp_WhoIsActive @get_outer_command = 1,
@output_column_list = '[dd%][session_id][sql_command][sql_text][login_name][host_name][database_name][wait_info][blocking_session_id][blocked_session_count][percent_complete][cpu][used_memory][reads][writes][program_name][collection_time]',
@find_block_leaders = 1,
@destination_table = 'dbo.info_whoisactive';
2、配置作业
我们可以创建一个作业来收集每次执行的结果，便于以后分析。作业可以每多少分钟或者每多少秒执行一次。
