2020年4月29日 星期三

SQL SERVER AlwaysOn 可讀次要對主要複本效能的影響


問:AlwaysOn次要節點設為「可讀」會對主要節點效能產生影響嗎?
答:會 (但不算太嚴重)

有人可能覺得奇怪,次要節點在邏輯上而言是另一台獨立主機,在它上面的讀取的動作應該消耗的是它自己主機的資源,跟主要節點有什麼關係呢?

場景

AlwaysOn同步認可流程示意














我們知道在讀取資料時會在相關的row/pagetable上獲取共享鎖(share lock),如果次節點正在select,而主節點也在update,那這update的動作傳送到次節點並寫入次要節點的交易紀錄檔後,即算是同步完成(同步認可模式),接著傳回ACK通知主節點已同步完成。在這一階段它並不會因次節點正在select而被block,因為資料還沒有真正的寫進data file

接著當Redo Thread去讀取交易紀錄並重做時,正常模式下由於資料正在select,update就必需等待。這時候在次要節點就會產生block。這將導致後續其它的異動全都要排隊,無法即時同步到次要節點。如果次要節點在執行的是一個長時間的報表查詢,那麼同步延遲的時間將不斷拉大。若有其它使用者去查詢別的資料,他查到的可能是數分鐘甚至數小時前的資料,這跟我們所認知的「同步認可」差異太大,顯然不是一個好的同步運作方式。

AlwaysOn為了避免上述的情況,所有在可讀次要節點執行的Queries,都將自動設為 read-committed snapshot isolation
在這個隔離層級下,Queries不會在相關的資料上獲取共享鎖,因此不會發生block而影響主節點的異動,也避免增加資料的延遲。

這看起來不錯啊!也確實能解決block的問題….

影響
有興趣的朋友可以去瞭解一下read-committed snapshot isolation,它會在tempdb中儲存修改前的資料,並且在自身的實體資料尾部增加一個14 bytes的指標來儲存資料版本訊息。

那麼問題來了~~

AlwaysOn中是採用Log Redo來達成同步,並不是以SQL Statement的方式同步(如replication)。所以在主要及次節要點上,兩者的資料在實體上必需是一致。
如果次要節點設為可讀,它必需在實體資料尾部增加14 bytes來儲存版本資訊,這樣一來主次要的實體資料就不一致了。所以,這個14 bytes的空間及版本資訊,必需由主節點增加,並寫入交易紀錄透過同步機制同步過去,才能讓次要可讀節點去使用及判斷。

因此,如果你將AlwaysOn的次要節點設為可讀,當你在主節點執行update/insert/delete時,會在原本的資料行尾部多出14 bytes的空間以紀錄版本資訊。

這代表著,假設你去update資料時(加入AlwaysOn後未曾update),受影響的每筆資料長度將額外多出14 bytes
一、   容易造成頁面分割(page split),增加索引頁面的破碎。
二、   資料及交易紀錄容量的增加。

結語
以上簡單的描述了AlwaysOn可讀次要對主節點效能的影響。有興趣的朋友可以自行去做個測試。其實它主要是索引破碎率增加,這個問題並不算嚴重或許也因此很少被high-light出來。
當一個現有DB被加入AlwaysOn後(次要可讀),DBA需知道可能會造成碎片的增加並增加碎片檢查的頻率,再用一般維護時相同的方式去處理碎片問題即可。


2019年11月29日 星期五

Heap table insert資料時的空間配置



前幾天在SQL PASS的講座中,聽到老師提到一個有趣的問題

heap tableinsert資料時,在某個條件下,即使檔案的某些page還有足夠的空間容納新增的資料,但系統不會寫進該page,而是新增一個page再將資料寫入新的page,條件是:
一、 page現有資料所佔空間 > 50%(頁面空間)
二、 insert進來的資料長度 > 20%(頁面空間)

例如page現有資料量為51%,這時有筆資料要insert進來(資料量為page21%),照理說這個page的剩餘空間是可以容納這筆資料的;但是,SQL SERVER卻是又配置一個新的page來存放新insert的資料!
這會造成空間的浪費,從大面向來看你會發現total insert進來的資料假設只有1G,但或許資料檔(mdf/ndf)卻成長了2G,這會令人疑惑;也會造成內部碎片。

因時間有限,老師有提到heap table的空間分配是由PFS控管,沒有辦法再細說原因;我覺得這個問題蠻有趣的,我想在此依個人瞭解針對這個問題再做個測試及筆記。


PFSPage Free Space
PFS是一個系統的頁面(page,它主要用來紀錄:
一、各個頁面的型態(例如是否為混合頁、IAM頁)
二、各個頁面是否閒置(閒置頁面表示可用,可以重分配)
三、各個頁面的空間使用率

PFS也是一個8Kpage,扣除page headermeta data後,有8088bytes可以用來表示相對page的狀態;亦即一個PFS,可以管理8088page,在資料檔(mdf/ndf)中每隔8088頁就會有一個PFS來管理。

 

PFS可用來表示page的「大約已使用空間」以及page的種類、page是否已分配給TABLE等。從PFS的設計來看,它只將page空間的使用情況分成5等級:

0%50%80%95%100%

所以,假設我們有一個空的TABLE,塞入一筆只有幾個byte的資料(例如只佔不到1%的空間),在PFS中,它會標示這個page的空間已被使用50%

測試驗證(1)
我們做個實驗來確認是否如此:
create table HeapSpaceTest (ID varchar(8000))  --建立一個TABLE,只有一個varchar欄位
go
insert into HeapSpaceTest values('A'--新增一筆資料,只有一個byte
go
建立一個TABLE,並insert一筆資料,這筆資料內容只有1byte,我們先使用DMV查詢頁面的真實使用空間
SELECT object_name(object_id) as objname,
                       page_count,
               avg_fragmentation_in_percent,
               avg_page_space_used_in_percent
       FROM    sys.dm_db_index_physical_stats (DB_ID(), object_id('HeapSpaceTest'), NULL, NULL, 'DETAILED')








我們可以看到頁面真實的使用空間僅有0.14%

接著我們看看PFS的紀錄

先用dbcc ind查出該TABLE的資料頁面編號為1:536











再去看PFS頁對於1:536頁的紀錄是怎樣的






















從上面的截圖可以看到,PFS紀錄1:536頁的頁面資料使用率為50%
這是因為PFS只將頁面使用率分為5個等級(如上面PFS結構圖)。以這個例子來說,頁面真實的使用率為0.14%,落在>0-50之間,這個級距在PFS中紀錄它的頁面使用率都將為50%這個差很大啊..>_<||.. 真實的資料佔比僅0.14%,但PFS卻紀錄為50%)。

至此我們驗證了PFS所紀錄的頁面空間使用率只分為5個級距,與真實的頁面空間使用率有不小的落差。

Heap table的資料新增 vs PFS

一筆資料的真實長度 = 資料內容長度 + internal overhead + slot offset

Internal overhead是指描述這筆資料的資料,例如這筆資料有幾個欄位、哪個欄位是null…等,它的長度不固定,其長度視資料有多少欄位而不同,至少有7bytes,欄位越多則Internal overhead會越長。至於slot offset則是固定為2 bytes

Heap table在實體資料儲存上不需要考慮排序的問題。換句話說SQL SERVER不需要考慮這筆資料要寫到哪個頁面;只要哪個頁面有空間就都可以寫。

那麼,要如何找到有足夠空間的page呢?..首先SQL SERVER掃瞄該TABLEIAM頁取得該TABLE的所有頁面編號,接下來有下列兩種方法,來確定哪個頁面的空間足夠容納資料:
一、 從頭開始去檢查每個pagepage headerm_FreeCnt,找到符合空間條件的page後即停止檢查。
二、 PFS頁面檢查相關page的空間用量。

上面兩種尋找可用空間的方法,顯然第二種要有效率的多,它只要掃瞄PFS一個頁面即可。而第一種方法在極端的情況下(所有頁面都沒有足夠空間),有可能會full table scan造成I/O的負載。

因此,Heap tableinsert資料時,SQL SERVER使用這筆資料的真實長度,再去PFS中依照其紀錄的空間使用率,就可以很快的確定哪個頁面可以容納這筆資料,並將資料寫入該頁面中。

PFS頁面上紀錄的是頁面使用率,所以SQL SERVER在計算該頁面可用空間時,使用如下的公式:
頁面可用空間 = (1-PFS頁面使用率) × 8096
註:
經測試SQL SERVERPFS計算可用空間是以8096為基底來算,它的由來是「頁面大小-頁面標頭 = 8192 – 96 = 8096」,但page中一筆資料的長度最長仍限制為8060,超過的話若有可變長度欄位則會另外以ROW_OVER_FLOWBLOB方式存放。

以前面測試的這個例子來說:
真實頁面使用率=0.14%
PFS頁面使用率=50%
我們可以從PFS的角度算一下這個頁面可接受insert資料的空間
(1-0.0014)*8096=8084 (bytes) --頁面真實可用空間
(1-0.5)*8096=4048 (bytes)  --PFS角度來看的頁面可用空間

也就是說下一次insert進來的資料長度如果大於4048 Bytes,即使真實頁面空間足夠容納這筆資料,SQL SERVER也會分配新的頁面,並將資料寫入新的頁面。這是因為SQL SERVER將參考PFS的空間使用率作為判斷要將資料寫到哪個page中而不會參考真實的頁面空間。


測試驗證(2)
我們以前面的例子繼續測試:
(1-0.0014)*8096=8084 (bytes) --頁面真實可用空間
(1-0.5)*8096=4048 (bytes)  --PFS角度來看的頁面可用空間

如果再insert一筆內容長度為4048 bytes的資料,再加上internal overhead,實際資料長度會略大於4048 bytes,也就是說真實頁面空間足以容納這筆資料,但從PFS角度來看頁面空間則是不足。依照上一節所說的,SQL SERVER應該會分配一個新的頁面並將資料寫入新頁面中。

--再新增一筆資料,長度為 4048 bytes
insert into HeapSpaceTest values(replicate('A',4048))


新增後,用DMV檢查頁面






果然系統多分配了一個頁面,用來存儲新insert的資料,我們再用dbcc ind看一下,確實多了一個1:537頁面








select出來確認













從上面的簡單測試,我們了解到insert資料到Heap table時,SQL SERVER是透過PFS去尋找空間合適的頁面而不考慮真實的頁面空間。

回顧
heap tableinsert資料時,在某個條件下,即使檔案的某些page還有足夠的空間容納新增的資料,但系統不會寫進該page,而是新增一個page再將資料寫入新的page,條件是:
一、 page現有資料所佔空間 > 50%(頁面空間)
二、 insert進來的資料長度 > 20%(頁面空間)

 這種狀況可用下面的圖來表示









結論
Heap table的資料存放不需考慮資料排序的問題,表示當有資料insert進來時,它可以被放在這個TABLE中任何有足夠空間的頁面;在這個條件下SQL SERVER將使用PFS去尋找哪個page有足夠的空間。這樣的尋找空間方式會比逐頁檢查空間要快的多。
所以,如果你設計的系統中,有TABLE要承接大量的insert資料的話,Heap table是個可以的選擇;但由於PFS只將空間分為5個級距,所以使用PFS去確認空間使用率會存在較大的偏差,故必然會造成較高的內部碎片以及空間的浪費,查詢時的I/O也會增加。

Cluster table資料因為是有序的儲存,所以SQL SERVER本來就必需透過b-tree找到資料應該要存在哪個頁面上,才能做有序的儲存。因此它不能透過PFS去找,PFS只是紀錄頁面的空間,它無法知道資料的順序。

寫到這裡突然想到,Heap table就好像高鐵自由座,只要有空位都可以坐;而Cluster table則像是對號座,不能隨便亂坐……..呵呵~~~^__^….





2019年3月7日 星期四

Truncate table 被系統背景程序 block


今天AP人員跟我反應說他在truncate table時一直跑不出來,我上去看,發現truncate tableblock,如下圖



當時心想,就是一個單純的block而己,很容易處理的。
然後我就去看block的源頭…………

意外的是,block的源頭竟是一個系統背景程序,而且這個背景程序在做「Create index..
~~~~頓時一頭霧水~~~~













~~這到底是什麼啊,系統自己在create index!!!


首先我先確認了這個session是系統的程序

剛開始搞不清楚,從沒聽過系統會自己建index。我試著kill看看,果然不行,系統程序不能kill














然後我用dbcc inputbuffer想看看它在跑什麼指令,結果是空的,沒辦法查到它在跑的指令。

再來我用dbcc opentran看看它的交易資訊,發現了一個關鍵字「TupleMover



















TupleMover
TupleMoverSQL 2014開始引進的一個背景程序,主要是定期將columnStore indexdeltaStore中的資料,壓縮並寫入columnStore中。

columnStore index其實包含了兩部份(columnStore deltaStore)。如果TABLEcolumnStore index,當對這個TABLEinsert / update時,columnStore index不會立即被同步修改,這些資訊會先存到一個稱為deltaStore的地方。當使用者查詢時,columnStoreDeltaStore會被一併搜尋。

deltaStore可以理解為columnStore的暫存區,它由RowSet所組成,這些RowSet仍然是傳統的Row-Base index結構。當收集到足夠多的資料後,RowSet會關閉。

TupleMover這支背景程序,就是用來將deltaStore中的「已關閉」的RwoSet,壓縮並寫入columnStoreTupleMover預設每5分鐘會啟動一次,或者當有使用者手動執行alter index reorganize/rebuild時啟動。

TupleMover在執行期間,會在目標TABLE獲取S鎖,並在最後階段獲取SCH-M鎖,從sysprocess中看,它的cmd會是「CREATE INDEX」。

檢查
瞭解了這些後,再看truncate的目標TABLE,果然它有個columnStore index,如下圖

至此,總算是知道原因了,我詢問AP人員,他說在truncate之前有支JOB在做分批大量的insert的操作(3億筆),由於空間不夠,AP人員把JOB中斷,然後要truncate table以清出空間。但由於已有大量的資料insert進去了,TupleMover需要較長的作業時間。

truncate table需要獲取SCH-M鎖,此時TupleMover在該TABLE上已獲取了S鎖,所以truncate tableblocked

原因終於搞清楚了~~~
但這個問題,目前只能等,沒有其它方式。於是只能就跟AP人員說這必需要等到TupleMover執行完,再做truncate

後記
我們可以使用 trace flag 634去關閉TupleMover,關閉之後系統將不會自動啟動TupleMover去處理deltaStore的資料,直到使用者執行alter index reorganize/rebuild


2019年1月28日 星期一

Delete heap table後的空間重用及釋放問題


Scenario
SQL PASS的好友Alexis有天談到:
delete 一個table的資料,這個table是一個heap table,但資料刪完後,table所佔用的閒置空間並沒有釋放出來還給database
無巧不巧,另一位好友JasonChen也遇到相同的問題,如下圖,table筆數已經為0,但卻仍佔用10GB的空間






要說明這個問題,首先要瞭解一下何謂閒置空間
(1)    空的Page,意即page中的資料已經完全被標示為刪除,沒有資料了。
(2)    半空的pagepage中的資料並沒填滿,尚有空間可以容納新的資料進來。

首先確立一個觀念,SQL SERVER空間管理是以page為單位,要將table的閒置空間還給DB,必需是該table所屬的「空page」。

所以在delete過程中,必需是該頁面的資料被刪除到一筆不剩,這個page才能釋出還給DB

空的page
做個測試,重現第1種「空的page」的情況
--建立一個table,讓每筆資料會佔用一個page,方便評估
create table testSpace(c1 int identity,c2 char(8000)) 
go
--新增1000筆資料,估計會佔用1000page
insert into testSpace values(N'打破沙鍋問到底.....') 
go 1000

檢查tablepage count
SELECT object_name(object_id) as obj_name, index_depth AS Depth,index_type_desc
, index_level AS [level]
, record_count AS 'RowCount'
, page_count AS 'PageCount'
FROM sys.dm_db_index_physical_stats
(DB_ID ()
, OBJECT_ID ('testSpace')
, 0, NULL, 'DETAILED');






從上圖可見,確認有1000筆資料佔用了1000page
接著使用一般的delete指令刪除table 500
begin tran 
--刪除500筆資料,測試空間是否會歸還給DB
delete top(500) from testSpace
--查詢lock統計
select resource_type,request_mode,request_status,count(*) as LockCount
 from sys.dm_tran_locks
 where request_session_id=72
group by resource_type,request_mode,request_status

commit













可以看到,SQL SERVER使用row-level lock在被刪除資料行獲取x-lock


再次檢查table page count,可以發現筆數已經減少,但所佔用的page,仍然不變,由於是一筆資料會佔用一個page,這也就是說有500個空的page,而這些空page其實沒有還給database,仍然由該table佔用。





我們再刪500筆,把table整個清空
delete top(500) from testSpace
再檢查page count,可以看到table已經沒有任何資料,但空間仍然佔用1000 page




DB的報表來看,也是一樣




以上測試,我們重現了第1種的閒置空間情境,table已沒有資料,但所佔用的空間仍然不變。


How
~~那麼,有什麼方法可以在刪除後將空page釋出還給DB呢?~~

根據微軟的說法,有下列3種方式,可以在刪除heap table資料後所剩餘的空間釋放出來。

節錄微軟官網描述
當資料列從堆積中刪除時, Database Engine 可能會在作業時使用資料列或頁面鎖定。 如此一來,由刪除作業清空的頁面仍然會配置給堆積 如果未取消空白頁面的配置,資料庫中的其他物件就無法重複使用相關聯的空間
若要刪除堆積中的資料列及取消配置頁面,請使用下列其中一個方法。
·         DELETE 陳述式中指定 TABLOCK 提示。 使用 TABLOCK 提示會造成刪除作業對資料表進行獨佔鎖定,而非資料列或頁面鎖定。如此可允許取消配置頁面。 如需 TABLOCK 的詳細資訊,請參閱資料表提示 (Transact-SQL)
·         如果要從資料表刪除所有資料列,請使用 TRUNCATE TABLE
·         請先在堆積上建立叢集索引之後,再刪除資料列。 您可以在刪除資料列之後卸除叢集索引。 這個方法會比之前的方法耗用更多的時間,而且會使用更多的暫存資源。

微軟所說的第二點沒什麼好討論的。至於第三點或者用alter table .. rebuild主要是將table重建然後刪除舊有的page,這也沒什麼問題。
有趣的是第一點,delete .. with(tablock)就會在刪除後立即把空間還給database

我們再來測試一下
drop table testSpace
go
--建立一個table,讓每筆資料會佔用一個page,方便評估
create table testSpace(c1 int identity,c2 char(8000)) 
go
--新增1000筆資料,估計會佔用1000page
insert into testSpace values(N'打破沙鍋問到底.....') 
go 1000








接著我們使用with(tablock)來刪除資料
begin tran 
--刪除500筆資料,使用tablock,測試空間是否會歸還給DB
delete top(500) from testSpace with(tablock)
--查詢lock統計
select resource_type,request_mode,request_status,count(*) as LockCount
 from sys.dm_tran_locks where request_session_id=72
group by resource_type,request_mode,request_status
commit











table上有加x-lock


檢查空間,可以看到空間立即被釋放,原本1000 pages只剩下500 pages







果然加上with(tablock)後,刪除後空間(空的page)確實立即被釋放出來。

~~~~~但,為什麼?~~~~
~~~~~with(tablock)難道有什麼特殊的作用?~~~~




Why
page都已經清空了,系統為什麼不預設自己將空間釋出,還要使用者自己加with(tablock)呢?

我們知道每個配置單元(allocation_unit)會有一個IAM pageIAM中紀錄了這個配置單元所使用到的頁面。

heap table中的資料頁是由IAM page所控制。例如, SQL Server 讀取第xxx頁時, 它不知道下一頁是什麼或在何處。因此, heap tableDML執行前,首先要讀取該物件的 IAM(會在IAM頁要求share lock),以確定它必須讀取哪些頁面才能進行完整資料表掃描。有了這些資訊, 才可以啟動查詢, 並且可以讀取每個資料頁。這個過程稱為「Allocation Scan」。

假設有一個heap table,在IAM紀錄中有5個頁面
(1)交易T1正在delete資料( tablock),它首先讀取IAM頁成功,接著準備往下執行,我們再假設這交易會delete清空第3

(2)交易T2在交易T1讀取完IAM尚未開始delete時啟動,它是select 所有資料









上圖所示的這情況,如果T1delete後立即將閒置的頁面還給DB, 那麼T2在查詢到第3頁的時候,就會遇到「找不到資料頁面」的情況。這是一個嚴重的錯誤。所以 SQL SERVER為了資料的一致性,必需保留該頁面的配置。

因此,一般tablockdelete在執行完後,即使有頁面被清空,它也不會釋放給DB以供重新配置。
這些閒置空間,仍然屬於該table,如果後續有對這個tableinsert,那麼這些閒置空間仍然會被新增的資料所使用。

那麼為什麼加上with(tablock)就會將空間釋放呢?

其實很簡單,那是因為delete..with(tablock), 它會在目標table上要求x-lock,而這x-lock會延伸到該tableIAM頁。

所以這時IAM頁是被鎖定隔離的,其它程序無法讀取IAM,也就可以避開上面所講的「找不到資料頁面」的情況。










因此在tablock的情況下,delete後如果有產生閒置的頁面,系統就會將這些頁面還給DB


鎖定升級
在同一SQL語句單一instance的單一物件上獲取超過5000row/page lock,將達到鎖定升級的閥值(5000只是概略數字)。

或許有人有疑問,有時候delete並沒有加with(tablock),也會將閒置頁面釋出啊!
那是因為「鎖定升級」的關係,系統將原本的row/page lock自動升級到tablock,所以閒置空間會被釋放。

不過delete的鎖定升級,並不是一開始就評估好要升級,而是隨著delete的過程,row/page lock的用量越來越多,超過閥值後,會觸發鎖定升級。

測試
drop table testSpace
go
--建立一個table,讓每筆資料會佔用一個page,方便評估
create table testSpace(c1 int identity,c2 char(8000)) 
go
--新增20000筆資料,估計會佔用20000page
insert into testSpace values(N'打破沙鍋問到底.....') 
go 20000
刪除資料,並檢視lock統計
begin tran 
--刪除10000筆資料
delete from testSpace where c1<10001
--查詢lock統計
select resource_type,request_mode,request_status,count(*) as LockCount
 from sys.dm_tran_locks
 where request_session_id=54
group by resource_type,request_mode,request_status
commit










檢查table空間





筆數已經被刪成10000筆,page數應該也會是10000但卻有13124
。這是因為剛delete開始時系統是使用row-lock,直到系統判斷lock數太多,而啟用鎖定升級為table x-lock。而自獲得table x-lock後所刪除的資料空間(page)才會被釋出還給DB

delete..with(tablock)所能釋放的空間,僅限於它獲得x-lock的期間所清出的空間。如果你在之前有用一般的delete去刪資料,後面才用delete..with(tablock),那麼這些之前所釋出的空間是不會被放掉的。


Conclusion
Heap table delete時,閒置的空間可以分成下列兩類
(1)    空的page
(2)    半空的page

一個heap table如果你想要在delete時一併釋放空間,那麼你必需加上with(tablock)。注意delete指令只會將這次delete過程中所清空的page釋出還給DB。之前的空page不會被這次delete所釋放。

半空的page,由於page中仍有資料,雖然不能釋出給DB,但是可以被本身的資料重用。如果你想把這些半空的page或者上述全空而沒有釋放的page空間釋放出來,可以使用alter table..rebuild空間重整後剩餘空的page會釋出還給DB。或者對此table建立clustered index再刪除clustered index也行。

至於DB的可用空間,則是要經由shrink才能釋出給OS

註:
Clustered table由於它的搜尋並不是走IAM,而是依靠page header中的nextpageprevpage,所以即使不加with(tablock), 也會將刪除過程中所清空的page釋出還給DB