如何将sp_whoisactive存储过程结果插入Azure SQL托管实例的表中

我们的Azure SQL托管实例数据库存在问题，正尝试收集统计数据以协助排查。已创建名为[dbo].[WhoIsActive]的表，其架构如下：

CREATE TABLE [dbo].[WhoIsActive](
    [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,
    --[tran_log_writes] [nvarchar](4000) 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,
    --[query_plan] [xml] NULL,
    [status] [varchar](30) NOT NULL,
    --[tran_start_time] [datetime] 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
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** Object:  Index [cx_collection_time]    Script Date: 2/28/2023 7:17:59 AM ******/
CREATE CLUSTERED INDEX [cx_collection_time] ON [dbo].[WhoIsActive]
(
    [collection_time] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

随后安装了最新版本的存储过程sp_whoisactive，下一步是定期将该存储过程的结果插入表中，但尝试两种方案均报错：

尝试方案1 #

执行语句：

Insert Into [dbo].[WhoIsActive]
 Exec sp_whoisactive 

报错：

Msg 8164, Level 16, State 1, Procedure sp_whoisactive, Line 3737 [Batch Start Line 44]

An INSERT EXEC statement cannot be nested.

尝试方案2 #

执行脚本：

SET NOCOUNT ON;

DECLARE @retention INT = 60,
        @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 = [WhoIsActive];
                        @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 );

报错：

Msg 213, Level 16, State 1, Line 1

Column name or number of supplied values does not match table definition.

请问将该数据存储到表中的最佳方式是什么？或者我哪里操作有误？

错误原因解析 #

1. 方案1报错原因：sp_whoisactive内部本身已使用INSERT EXEC逻辑，SQL Server不允许嵌套INSERT EXEC语句，因此外部再套一层会触发该错误。

2. 方案2报错原因：你手动创建的WhoIsActive表，与sp_whoisactive启用@get_transaction_info=1、@get_outer_command=1、@get_plans=1参数时返回的结果集列数/列类型不匹配。比如你注释掉了tran_log_writes、query_plan、tran_start_time列，但启用上述参数后，存储过程会返回这些列的数据，插入时就会出现列不匹配问题。

最佳实现方式 #

方式一：让存储过程自动生成匹配的表（推荐） #

删除手动创建的WhoIsActive表，直接利用sp_whoisactive的内置功能生成表并插入数据，确保表结构与结果集完全匹配：

SET NOCOUNT ON;

DECLARE @retention INT = 60,
        @destination_table VARCHAR(500) = 'WhoIsActive',
        @destination_database sysname = 'DBATools',
        @SQL NVARCHAR(4000);

SET @destination_table = @destination_database + '.dbo.' + @destination_table;

-- 调用存储过程插入数据，表不存在时会自动创建
EXEC dbo.sp_WhoIsActive 
    @get_transaction_info = 1,
    @get_outer_command = 1,
    @get_plans = 1,
    @destination_table = @destination_table;

-- 清理过期数据
SET @SQL = 'DELETE FROM ' + @destination_table + ' WHERE collection_time < DATEADD(day, -' + CAST(@retention AS VARCHAR(10)) + ', GETDATE());';
EXEC sp_executesql @SQL;

如果必须保留手动创建的表结构，需确保调用参数与表结构一致：

• 去掉@get_transaction_info=1、@get_outer_command=1、@get_plans=1参数，避免返回表中没有的列
• 或在手动表中添加缺失的列：tran_log_writes、query_plan、tran_start_time、outer_command（@get_outer_command=1会返回该列）

方式二：使用临时表中转（避免嵌套INSERT EXEC） #

若需保留现有表结构，可通过临时表中转数据后插入目标表：

SET NOCOUNT ON;

-- 创建临时表，结构与sp_whoisactive无额外参数时的结果集匹配
CREATE TABLE #WhoIsActiveTemp (
    [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
);

-- 插入临时表，无额外参数避免列不匹配
INSERT INTO #WhoIsActiveTemp
EXEC dbo.sp_whoisactive;

-- 插入目标表
INSERT INTO [dbo].[WhoIsActive]
SELECT * FROM #WhoIsActiveTemp;

-- 清理临时表
DROP TABLE #WhoIsActiveTemp;

定期执行建议 #

创建SQL Server代理作业，按固定间隔（如1分钟）执行上述插入脚本，实现持续的活动数据收集。

内容的提问来源于stack exchange，提问作者Immortal