菜单

一、监听某个时间段内执行的SQL

use ecology  ---声明监听的数据库
go
SELECT TOP 100 
QS.CREATION_TIME 时间,
SUBSTRING(ST.TEXT,(QS.STATEMENT_START_OFFSET/2)+1,((CASE QS.STATEMENT_END_OFFSET WHEN -1 THEN DATALENGTH(ST.TEXT) ELSE QS.STATEMENT_END_OFFSET END - QS.STATEMENT_START_OFFSET)/2)+1) AS 语句块,
ST.TEXT AS 语句,
QS.TOTAL_WORKER_TIME AS 总执行时间毫秒,
QS.LAST_WORKER_TIME AS 上次执行时间毫秒,
QS.MAX_WORKER_TIME AS 最大执行时间毫秒,
QS.MIN_WORKER_TIME AS 最小执行时间毫秒
FROM SYS.DM_EXEC_QUERY_STATS QS
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) ST
WHERE QS.CREATION_TIME BETWEEN '2024-08-10 14:00:00' AND '2024-08-10 18:00:00'
AND ST.text LIKE '%%'
ORDER BY QS.TOTAL_WORKER_TIME DESC

二、使用CDC监控表的变化

2.1查看数据库是否启用CDC

---is_cdc_enabled:0 禁用 1 启用
SELECT name,is_cdc_enabled FROM sys.databases where name='ecology'

2.2启用数据库CDC

USE ecology
GO
EXECUTE sys.sp_cdc_enable_db;
GO

2.3检查启用是否成功

----开启数据库的CDC之后，分别在用户和架构上创建新的用户cdc，新的架构cdc；
USE ecology
GO
SELECT NAME,is_cdc_enabled,CASE WHEN is_cdc_enabled=0 THEN 'CDC功能禁用' ELSE 'CDC功能启用' END 描述
FROM sys.databases
WHERE NAME = 'ecology'
go

2.4创建测试表监听设置

2.4.1创建测试表Department_Demo

CREATE TABLE [dbo].[Department_Demo](
    [DepartmentID] [smallint] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](200) NULL,
    [GroupName] [nvarchar](50) NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
    [AddName] [nvarchar](120) NULL,
 CONSTRAINT [PK_Department_DepartmentID] PRIMARY KEY CLUSTERED 
(
    [DepartmentID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

2.4.2对该测试表进行捕获（对表启用捕获）

EXEC sys.sp_cdc_enable_table 
    @source_schema= 'dbo',
       @source_name = 'Department_Demo',
       @role_name = N'cdc_Admin',
       @capture_instance = DEFAULT,
       @supports_net_changes = 1,
    @index_name = NULL,
    @captured_column_list = NULL,
    @filegroup_name = DEFAULT

2.4.3查看是否开启CDC监听

SELECT name, is_tracked_by_cdc ,
    CASE WHEN is_tracked_by_cdc = 0 THEN 'CDC功能禁用' ELSE 'CDC功能启用' END 描述
FROM sys.tables
WHERE OBJECT_ID= OBJECT_ID('dbo.Department_Demo')

2.5数据表监听调试

2.5.1 DML数据操作

--测试插入数据
INSERT  INTO dbo.Department_Demo(
    Name ,
    GroupName ,
    ModifiedDate
)VALUES('Marketing','Sales and Marketing',GETDATE())

--测试更新数据
UPDATE dbo.Department_Demo SET Name = 'Marketing Group',ModifiedDate = GETDATE()
WHERE Name = 'Marketing'

--测试删除数据

DELETE FROM dbo.Department_Demo WHERE Name='Marketing Group'

2.5.2查询捕获数据

SELECT
case __$operation when 1 then '删除' when 2 then '插入' when 3  then '更新(旧值)' when 4 then '更新（新值）' else 'Other' end OptionName,
 * 
FROM cdc.dbo_Department_Demo_CT

2.6 查看某时间段所有CDC记录

/******* 查看某时间段所有CDC记录*******/
DECLARE @FromLSN binary(10) =
sys.fn_cdc_map_time_to_lsn
('smallest greater than or equal' , '2024-06-23 09:00:30')

DECLARE @ToLSN binary(10) =
sys.fn_cdc_map_time_to_lsn
('largest less than or equal' , '2024-10-26 23:59:59')

SELECT CASE [__$operation]
WHEN 1 THEN 'DELETE'
WHEN 2 THEN 'INSERT'
WHEN 3 THEN 'Before UPDATE'
WHEN 4 THEN 'After UPDATE'
END Operation,[__$operation],[__$update_mask],DepartmentId,Name,GroupName,ModifiedDate,AddName
FROM [cdc].[fn_cdc_get_all_changes_dbo_Department_Demo]
(@FromLSN, @ToLSN,  N'all update old')

打赏赞(1)