2020年2月23日 星期日

SQL SERVER 有辦法中止 Rollback 嗎!



我仍記得在好多年前在某公司擔任APDBA,有次在手動清除一些TABLE的資料時,因為TABLE太多了沒有去檢查這些TABLE的筆數。就直接執行
Delete TABLE1 where 日期<’xxxx/xx/xx’
Delete TABLE2 where 日期<’xxxx/xx/xx’
….
Delete TABLEn where 日期<’xxxx/xx/xx’

然後放著讓它跑,就去做別的事了

大約過了幾小時吧,接到電話說系統卡住了都跑不出來,上去 DB 看後發現是我剛才的 delete 跑到了某個大 TABLE 而 delete 了很久,鎖住了許多 query。為了怕影響系統的運作,當時想說就把 delete 的作業暫停應該就好了。

在我 kill 了delete之後,再問系統人員還是說卡住….
我再進 DB 看,發現原本的 delete 的 session 還在,並沒有立即中止,sp_who 看 status 欄位顯示是 rollback再嘗試多次的 kill,那個 rollback 的 session 仍然無法中止。

隨著電話不斷的來,長官也紛紛跑來關切;google了一下,都說 rollback 是無法中止的,只能等它自己跑完………。當下真的臉都綠了,只能不斷的抱歉,心驚膽顫的過了一兩個小時終於 rollback 結束了
......
.....
那次出了一次大包,印象非常深刻。

如今,我成了專職的 DBA了,對於所謂的系統結構、交易、rollback…等問題也有了比較清楚的認識。
去年曾經有朋友問過我 rollback 如何中止,今天去拜訪客戶時也被問到相同的問題。又讓我想起這慘痛的經歷,所以就想和大家分享一下如何中止 rollback


~~~讓我們回到主題~~~

問題:SQL SERVER 有辦法中止rollback嗎?
答案:可以,但rollback所在DB交易資料不一致的風險自負

情境模擬
首先建立一個資料庫及TABLE
create database TestRollback
go
use TestRollback
go
create table test0221(c1 int identity,c2 char(1024))
go
接著我做一段迴圈讓它不斷去insert資料
declare @x int = 1
begin tran
    while (@x<10000000)
        begin
            insert into test0221 values('aaaaaaaaaaaaaaaaaaaaaa')
            set @x+=1
            print @x
        end
當它跑到某個階段後,將這個session kill





















sp_who2看,可以看到它正在rollback



























以上是簡單的模擬正在rollback情況,以這個例子來說,當rollback發生時,原本已經insert進去的資料系統會自動delete
Rollback發生時會保留rollback前所擁有的鎖,一個基本的判斷原則是如果你的session目前是table lock,那麼rollback期間也會是table lock,例如我最前面的經歷,因為我的delete鎖住其它人,那麼當這個deleterollback時,仍然會鎖住其它人。


停止rollback的方法
要停止rollback的方法步驟如下:
一、   alter database DBNAME set offline with rollback immediate
二、   使用sp_who2檢查status=rollback的連線,是不是只剩下你要中止rollback的連線。
三、   shutdown with nowait,強制將SQL SERVER關閉。
四、   OS層級刪除rollback所在DBlog file
五、   啟動SQL SERVERrollback所在DB由於沒有log file,無法開始recovery,將會被置於「復原暫止recovery pending」的狀態。
六、   Alter database DBNAME set emergency,將rollback所在DB設為「緊急模式 emergency mode」,emergency mode可以解釋為「bypass recovery」,它會跳過recovery步驟,並允許使用者去存取資料庫。
(1)     Dbcc checkdb(‘DBNAME’,repair_allow_data_loss)
(2)     Alter database DBNAME rebuild log on(
Name=’xxxx_log’,
Filename=’c:\xxxx\xxxx\xxxx.ldf
)
八、   alter database DBNAME set multi_user


實作
為了最大程度的降低交易資料不一致的損害,首先執行:
alter database Testrollback set offline with rollback immediate
正在rollback很久的session不會因為這句而中斷,但這句語法它可以先強制關閉其它正常的session,如果有其它交易中的session也會正常的rollback,並拒絕新的連線。
接著使用sp_who2檢查,確認狀態為rollback的連線,僅剩你想要中止的連線。
這個目的是要避免其它交易受波及,並確認交易不一致的風險在掌控的範圍內。
  

上面確認無誤後,就可以將SQL SERVER 強制關閉,不等rollback完成
















接著從OS層級將rollback所在DBlog file刪除
















之所以要刪除log file,是由於任何對DB的異動(包含rollback),都會被紀錄在log中。而SQL SERVER啟動時會先讀取每個資料庫的log file然後執行recovery (redo+undo),由於我們是rollback未完成就直接shutdown,如果不刪除log file,在SQL SERVER重新啟動時仍會依照log file的紀錄繼續執行rollback
所以,要想避免又開始rollback,我們需要把資料庫的交易紀錄檔從OS中刪除,這樣一來由於沒有交易紀錄檔可以參考,所以SQL SERVER啟動時該DB也不會有rollback發生。

接著我們重新啟動SQL SERVER~~

由於沒有交易紀錄檔,該DB將無法開始recoverySQL SERVER可以正常啟動,但該資料庫將會被標示為「復原暫止 recovery pending」,且無法被讀取。

接著,我們可以將資料庫置於「緊急模式 emergency mode」。
alter database TestRollback set emergency














所謂「緊急模式 emergency mode 可以解釋為「bypass recovery」,它要求 SQL SERVER 跳過recovery步驟,並允許使用者去讀取資料庫(read_only)。

這個過程簡單的說就是SQL SERVER找不到DBlog file,無法對這個DB進行recovery,所以DBSQL SERVER中止上線。而我們將DB設為緊急模式就是告訴SQL SERVER這個DB有急用,要求略過recovery的步驟讓DB上線。

進入緊急模式後就可以去讀取資料庫,我們來看看test0221這個TABLE是否有資料?














在執行kill後它開始rollbackrollback動作是參考log file的紀錄逐條做undo,如果你是逐筆insert那麼你可以想像成系統就逐筆delete
我們前面kill insert時已經insert44722筆,直到SQL SERVER shutdown,期間會有部份資料已經rollback回去,所以我們現在看到的筆數為44565

到此時我們已經成功停止了rollback的動作,最後就是去重建DB的交紀紀錄檔讓DB上線即可。
重建交易紀錄檔,建議使用dbcc checkdb來讓系統自動重建交易紀錄檔,並且去修復因遺失log file而造成的系統頁面或系統表的紀錄失真。

接著我們執行dbcc checkdb讓系統重建log file,並修復系統頁面及系統表


















dbcc的返回訊息中,可以看到交易紀錄檔已重建,一些系統頁面的錯誤也己更正。

註:
在這種情況下,執行dbcc checkdb REPAIR_ALLOW_DATA_LOSS將會執行下列幾件事
Ø   跳過損壞的交易紀錄,並儘可能的做recovery
Ø   建立一個新的交易紀錄檔。
Ø   完整的DBCC CHECKDB (N'DBNAME', REPAIR_ALLOW_DATA_LOSS)指令運行。
Ø   嘗試使DB online


最後,將資料庫設為正常模式即可
alter database TestRollback set multi_user


結語
要停止rollback,需要強制shutdown SQL SERVER,並刪除log file。會導致SQL SERVER服務中斷,並影響rollback DB交易資料一致性,要特別小心。
另外,使用dbcc checkdb重建交易紀錄檔所需的時間視資料庫大小而定,如果資料庫很大使用dbcc重建交易紀錄檔的時間也會需要一段時間。因此如果真的急著要讓DB線,可以使用alter database ….rebuild log去重建LOG file,之後再找時間執行dbcc checkdb去更正系統頁面及系統表。

DBAkill session或執行相關維護指令時,真的要特別注意不要掉進rollback的坑。如果不慎掉進去了又急著上線,可以參考這個方法去中止rollback,但DBA必需知道它的影響範圍....。再次強調這是一個應急的方法,如果能允許等待自動rollback結束,建議還是乖乖的等。

若要更深入的理解「中止rollback」背後的機制,建議大家可以去了解交易、交易紀錄(commit/rollback的行為)SQL 啟動過程(recovery等主題。瞭解了之後,掌握它動作的原理,在你中止rollback過程中也比較不會心驚驚。

































































































































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則像是對號座,不能隨便亂坐……..呵呵~~~^__^….