最近一個(gè)項(xiàng)目中需要監(jiān)測SQLServer數(shù)據(jù)庫中某些表的數(shù)據(jù)更新情況,于是做了一番POC測試和簡單性能的評(píng)估.這里使用的是 SQLServer2008的更改跟蹤.因?yàn)樾枨笤?沒有考慮使用進(jìn)一步的變更數(shù)據(jù)捕獲.
POC過程如下:
這里我們建立一個(gè)測試環(huán)境，模擬數(shù)據(jù)在 Insert ， Update 和 Delete 情況下的跟蹤效果。
1 、測試腳本的準(zhǔn)備，下面腳本建立一個(gè)新的數(shù)據(jù)庫環(huán)境，并作相應(yīng)的跟蹤配置后向表中添加刪除更改數(shù)據(jù)。
Use master
go
/***
1 、建立測試環(huán)境：生成一個(gè)帶主鍵的測試表 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 、配置數(shù)據(jù)庫和表的更改跟蹤參數(shù)
*/
ALTER DATABASE db_Trace_test SET
CHANGE_TRACKING = ON (
AUTO_CLEANUP = ON , -- 打開自動(dòng)清理選項(xiàng)
CHANGE_RETENTION = 1 HOURS -- 數(shù)據(jù)保存期為時(shí)
);
ALTER TABLE dbo . T_Trace ENABLE CHANGE_TRACKING
go
/***
3 、向表中增加修改刪除數(shù)據(jù)
*/
insert into T_Trace values ( 1 , ' 上海 ' ),( 2 , ' 北京 ' ),( 3 , ' 廣州 ' )
delete from T_Trace where id = 3
update T_Trace set name = ' 天津 ' where id = 1
2 、跟蹤分析，測試腳本和效果如下
/***
4 、獲取更改過的數(shù)據(jù)
*/
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
/*
其中，測試腳本中函數(shù) CHANGETABLE 的第二個(gè)參數(shù) 0 代表查詢開始的事物操作序列，這三條數(shù)據(jù)分別表示兩個(gè)插入（ I ）和一個(gè)刪除（ D ）操作并且用主鍵 ID 標(biāo)識(shí)出來。
* 這里主鍵為 1 的數(shù)據(jù)標(biāo)志為插入，是因?yàn)?Insert 和 Update 是在同一個(gè)跟蹤事務(wù)中查詢出來的。
3 、調(diào)整跟蹤范圍參數(shù)，我們從序列為 2 的操作開始跟蹤，這樣可以跟蹤到測試數(shù)據(jù)的 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 這個(gè)結(jié)果則表示，主鍵為 1 的數(shù)據(jù)數(shù)據(jù)執(zhí)行過更新操作 (U)
1、硬件測試環(huán)境：
2 、軟件測試環(huán)境：
Windows 2008Server ， SQLServer2008
3 、樣本數(shù)據(jù)：
/--**--/
4 、測試結(jié)果：其中判斷和提取更新表示查詢時(shí)間，包含了返回到 SQLServer 客戶端的傳輸時(shí)間。
| 序列 | 源表數(shù)據(jù) | 操作 | 判斷更新 | 提取更新 |
| 1 | 1000 條 | Delete 語句刪除 1000 條 | 0 秒 | 無 |
| 2 | 0 條 | Insert 語句插入 100 條 | 0 秒 | 0 秒 |
| 3 | 100 條 | Insert 語句插入 1000 條 | 0 秒 | 0 秒 |
| 4 | 1100 條 | Insert 語句插入 10000 條 | 0 秒 | 0 秒 |
| 5 | 11100 條 | Insert 語句插入 100000 條 | 3 秒 | 4 秒 |
| 6 | 111100 條 | Insert 語句插入 100000 條 | 6 秒 | 7 秒 |
| 7 | 211100 條 | Insert 語句插入 100000 條 | 7 秒 | 11 秒 |
| 8 | 311100 條 | Delete 語句刪除 100 條 | 0 秒 | 無 |
| 9 | 311100 條 | Update 語句更新 100 條 | 0 秒 | 0 秒 |
| 10 | 311100 條 | Update 語句更新 1000 條 | 0 秒 | 0 秒 |
| 11 | 311100 條 | Update 語句更新 10000 條 | 0 秒 | 0 秒 |
5 、測試評(píng)估：
在變更數(shù)據(jù)量萬級(jí)的情況下，可以很快地響應(yīng)跟蹤結(jié)果并提取出所需要的數(shù)據(jù)。