sql-server - 扩展事件检测语句何时导致更新

Question

包含事件的字段之一sp_statement_completed是sql_batch_completedwrites 字段。我想用它来过滤事件会话，其中语句导致某些数据通过更新/插入或删除发生更改。这似乎适用于小型测试 Db，但不适用于较大的 Db。我能错过什么。如果正在更新单个记录，写入不会 > 0 吗？

有效的样本，但这个 Db 非常小。在较大的 Db 上使用时，更新记录时，不会捕获任何事件。

USE [Blowup]
GO
/****** Object:  Table [dbo].[BoxTest]    Script Date: 4/28/2021 10:54:09 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BoxTest](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [BoxName] [varchar](10) NULL,
    [BoxWidth] [decimal](10, 2) NULL,
 CONSTRAINT [PK_BoxTest] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Customer]    Script Date: 4/28/2021 10:54:09 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customer](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CustomerName] [varchar](100) NOT NULL,
    [Location] [varchar](100) NOT NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[TestTable]    Script Date: 4/28/2021 10:54:09 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [SomeName] [varchar](200) NOT NULL,
    [SomeValue] [varchar](max) NOT NULL,
 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[DoStuff]    Script Date: 4/28/2021 10:54:09 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[DoStuff] 
    
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT * FROM Customer 

    SELECT * FROM dbo.TestTable
END
GO

活动会话

CREATE EVENT SESSION [DDLcapture]
ON SERVER
    ADD EVENT sqlserver.sp_statement_completed
    (SET collect_statement = (1)
     ACTION
     (
         sqlserver.sql_text,
         sqlserver.username
     )
     WHERE (
               [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name], N'BlowUp')
               AND [sqlserver].[like_i_sql_unicode_string]([statement], N'%TestTable%')
               AND [writes] > (0)
           )
    ),
    ADD EVENT sqlserver.sql_batch_completed
    (ACTION
     (
         package0.event_sequence,
         sqlserver.client_app_name,
         sqlserver.client_pid,
         sqlserver.database_id,
         sqlserver.nt_username,
         sqlserver.query_hash,
         sqlserver.server_principal_name,
         sqlserver.session_id,
         sqlserver.sql_text,
         sqlserver.username
     )
     WHERE (
               [package0].[equal_boolean]([sqlserver].[is_system], (0))
               AND [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name], N'BlowUp')
               AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text], N'%TestTable%')
               AND [writes] > (0)
           )
    )
    ADD TARGET package0.event_file
    (SET filename = N'D:\LocalSQLExEvents\BlowUpDb_DDLExEvents.xel', max_file_size = (20))
WITH
(
    MAX_MEMORY = 8192KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = PER_CPU,
    TRACK_CAUSALITY = ON,
    STARTUP_STATE = ON
);
GO

标签： sql-serverextended-events