在現實的生產環境中,有可能遇到高并發insert的應用.在此應用時由于堆表(Heap)和聚集表的結構不同導致在高并發的情形下insert效率不盡相同.接下來我會簡單的以測試用例來簡要說明.并舉例說明如果提高聚集表下高并發插入效率.

在測試前我們先簡單了解下堆表和聚集表都是如何完成插入操作的.

關于堆表和聚集表的介紹:SQL Server 索引知識-結構,實現

堆表Insert

方式1 a獲得第一個IAM頁

b 獲取與之相關的PFS頁,從中找到第一個能容納Insert數據行大小的數據頁

c 如果沒有找到相應數據頁則轉到下一個IAM頁然后重復b操作

d 如果到最后的IAM頁還是沒有找到可容納的數據頁則分配新的擴展區(extent)

e Insert指定行

方式2 a 獲取所有IAM頁

b 獲取與之相關的PFS頁(s)找到能容納數據行(s)的數據頁(s)

c 如果沒有相關數據頁,或者沒有足夠的相關數據頁,分配新擴展區(extent)

d 插入相應的行(s)

聚集表Insert

由于聚集表本身的特性,插入數據的時的數據行必須在葉子節點的特定位置.

a 獲取root頁

b 通過B-tree需找到指定插入行的數據頁的位置

c 如果此時數據頁中空間可以容納此行,則insert,如果不能則分配新數據頁或者頁分裂.然后insert

注:頁分裂相對于頁內的DML操作(insert,update)消耗巨大,頁分裂的頻率上升會明顯影響實例的性能

測試用例

測試工具: sqlquerystress

測試環境:sql2008R2,3臺不同服務器上分別安裝運行sqlquerystress,100 threads/server

2000 times/thread

注意：如要模擬高并發需多臺機器共同執行,單臺即便多threads測試,測試結果也不能合理反應高并發情況.

測試腳本

我們在相應的機器上sqlquerystress中分兩次(堆表,聚集表)分別運行insert into tempdb.dbo.tx(str1) select 'aa'測試

然后比較堆表,聚集表相關的性能指標.(Batch requests/sec,elapsed time,wait stats)

堆表

create table t1(id int identity(1,1),str1 char(5))goDBCC SQLPERF ("sys.dm_os_wait_stats", CLEAR)with NO_INFOMSGS -----run the sqlquerystress at three servers-----100 threads /per server-----2000 times /threadselect * from sys.dm_os_wait_stats order by waiting_tasks_count desc

聚集表

checkpointdbcc dropcleanbufferscreate table t2(id int identity(1,1) PRimary key,---clustered index defaultstr1 char(5))DBCC SQLPERF ("sys.dm_os_wait_stats", CLEAR)with NO_INFOMSGS -----run the sqlquerystress at three servers-----100 threads /per server-----2000 times /threadselect * from sys.dm_os_wait_stats order by waiting_tasks_count desc

測試結果

執行時的吞吐量Batch requests/sec

可以看出在我們的測試環境下堆表的吞吐量是聚集表的2倍以上 圖1-1