最近一個項目中需要監測SQLServer數據庫中某些表的數據更新情況,于是做了一番POC測試和簡單性能的評估.這里使用的是 SQLServer2008的更改跟蹤.因為需求原因,沒有考慮使用進一步的變更數據捕獲.
POC過程如下:
這里我們建立一個測試環境，模擬數據在 Insert ， Update 和 Delete 情況下的跟蹤效果。
1 、測試腳本的準備，下面腳本建立一個新的數據庫環境，并作相應的跟蹤配置后向表中添加刪除更改數據。
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
<!-- [if gte vml 1]><v:shapetype id="_x0000_t75" coordsize="21600,21600" o:spt="75" o:preferrelative="t" path="m@4@5l@4@11@9@11@9@5xe" filled="f" stroked="f"> <v:stroke joinstyle="miter"/> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"/> <v:f eqn="sum @0 1 0"/> <v:f eqn="sum 0 0 @1"/> <v:f eqn="prod @2 1 2"/> <v:f eqn="prod @3 21600 pixelWidth"/> <v:f eqn="prod @3 21600 pixelHeight"/> <v:f eqn="sum @0 0 1"/> <v:f eqn="prod @6 1 2"/> <v:f eqn="prod @7 21600 pixelWidth"/> <v:f eqn="sum @8 21600 0"/> <v:f eqn="prod @7 21600 pixelHeight"/> <v:f eqn="sum @10 21600 0"/> </v:formulas> <v:path o:extrusionok="f" gradientshapeok="t" o:connecttype="rect"/> <o:lock v:ext="edit" aspectratio="t"/> </v:shapetype><v:shape id="圖片_x0020_11" o:spid="_x0000_i1025" type="#_x0000_t75" style='width:415.5pt;height:65.25pt;visibility:visible;mso-wrap-style:square'> <v:imagedata src="file:///C:\Users\ADMINI~1\AppData\Local\Temp\msohtmlclip1\01\clip_image001.png" o:title=""/> </v:shape><![endif]--><!-- [if !vml]--><!-- [endif]-->
2 、軟件測試環境：
Windows 2008Server ， SQLServer2008
3 、樣本數據：
/--**--/
4 、測試結果：其中判斷和提取更新表示查詢時間，包含了返回到 SQLServer 客戶端的傳輸時間。
|
序列 |
源表數據 |
操作 |
判斷更新 |
提取更新 |
|
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 、測試評估：
在變更數據量萬級的情況下，可以很快地響應跟蹤結果并提取出所需要的數據。
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
