最近一个项目中需要监测SQLServer数据库中某些表的数据更新情况,于是做了一番POC测试和简单性能的评估.这里使用的是 SQLServer2008的更改跟踪.因为需求原因,没有考虑使用进一步的变更数据捕获.
POC过程如下:
这里我们建立一个测试环境，模拟数据在 Insert ， Update 和 Delete 情况下的跟踪效果。
1 、测试脚本的准备，下面脚本建立一个新的数据库环境，并作相应的跟踪配置后向表中添加删除更改数据。
master
go
/***
1
、建立测试环境：生成一个带主键的测试表 T_Trace
*/
if
( DB_ID
( 'db_Trace_test'
) is
not
null
) drop
database
db_Trace_test
go
Create
DataBase
db_Trace_test
go
use
db_Trace_test
go
Create
Table
T_Trace
( id
int
not
null
,
name
varchar
( 100
)
CONSTRAINT
[
PK_T_Trace
]
PRIMARY
KEY
CLUSTERED
( [
id
]
ASC
)
)
go
/***
2
、配置数据库和表的更改跟踪参数
*/
ALTER
DATABASE
db_Trace_test
SET
CHANGE_TRACKING
=
ON
(
AUTO_CLEANUP
=
ON
,
-- 打开自动清理选项
CHANGE_RETENTION
=
1
HOURS
-- 数据保存期为时
);
ALTER
TABLE
dbo
.
T_Trace
ENABLE
CHANGE_TRACKING
go
/***
3 、向表中增加修改删除数据
*/
insert
into
T_Trace
values
( 1
,
' 上海 '
),(
2
,
' 北京 '
),(
3
,
' 广州 '
)
delete
from
T_Trace
where
id
=
3
update
T_Trace
set
name
=
' 天津 '
where
id
=
1
4
、获取更改过的数据
*/
SELECT
CHG
.
Sys_Change_Version
as
序
列
,
id
as
主键
,
Sys_change_Operation
as
操
作
FROM
CHANGETABLE
( CHANGES
dbo
.
T_Trace
,
0
) CHG
order
by
CHG
.
Sys_Change_Version
/*
* 这里主键为 1 的数据标志为插入，是因为 Insert 和 Update 是在同一个跟踪事务中查询出来的。
3 、调整跟踪范围参数，我们从序列为 2 的操作开始跟踪，这样可以跟踪到测试数据的 Update 语句：
SELECT
CHG . Sys_Change_Version as 序列 , id as 主键 , Sys_change_Operation as 操作
FROM CHANGETABLE ( CHANGES dbo . T_Trace , 2) CHG
order by CHG . Sys_Change_Version
2
、软件测试环境：
Windows 2008Server
，
SQLServer2008
3
、样本数据：
/--**--/
4
、测试结果：其中判断和提取更新表示查询时间，包含了返回到
SQLServer
客户端的传输时间。
|
序列 |
源表数据 |
操作 |
判断更新 |
提取更新 |
|
1 |
1000 |
Delete |
0 |
无 |
|
2 |
0 |
Insert |
0 |
0 |
|
3 |
100 |
Insert |
0 |
0 |
|
4 |
1100 |
Insert |
0 |
0 |
|
5 |
11100 |
Insert |
3 |
4 |
|
6 |
111100 |
Insert |
6 |
7 |
|
7 |
211100 |
Insert |
7 |
11 |
|
8 |
311100 |
Delete |
0 |
无 |
|
9 |
311100 |
Update |
0 |
0 |
|
10 |
311100 |
Update |
0 |
0 |
|
11 |
311100 |
Update |
0 |
0 |
5
、测试评估：
在变更数据量万级的情况下，可以很快地响应跟踪结果并提取出所需要的数据。