維護計畫重建索引比更新統計值快？

客戶有提到他們有一個「重建索引」的維護計畫，每次都跑很久，有時候會跑到上班時間還在跑，造成DB LOCK嚴重，問我們有沒有什麼辦法。

依微軟官方說明

https://learn.microsoft.com/zh-tw/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver16 

重建索引會一併執行該索引的統計值更新，依官方的說明，重建索引之所以會有明顯的效能提升，大多不在於索引重建本身，而是在於索引重建後的統計值更新。 

所以，當時我建議客戶將重建索引，改成統計值更新即可，執行時間會快很多，既達到效能優化的目地，又節省時間，可以避免凌晨跑不完造成上班時間DB-LOCK。

但奇怪的是，客戶依我的建議改了之後說，統計值更新執行時間竟然是原本重建索引的3倍。反而跑更久了~~

照理說，只做統計值更新，應該會比重建索引快很多。後來去查才看到有個選項沒說清楚。

 我們知道統計值有兩種

一、索引統計值：索引建立或重建時由系統自動產生，它的統計值名稱通常即為索引名稱。

二、非索引統計值：系統依查詢的where條件去找相應的統計值，如果找不到，則會依where條件建立一個統計值。例如你用身份證號建了一個索引，但有人在查詢時用生日當where條件。由於並沒有生日的索引統計值，故系統將自動建立一個生日的統計值，這就是非索引統計值，它的命名都是以「_WA_SYS_....」開頭，如下圖。

重建索引時確實會一併執行統計值更新，但它只更新該索引的統計值，而不包含非索引統計值。 

執行計畫中的「更新統計值」（如下圖），預設是更新所有的統計值，包含索引及非索引統計值

由於客戶TABLE中「非索引統計值」的數量幾乎是索引統計值的5倍，因此如果以上圖預設「所有現有的統計資料」，確實有可能會比重建索引要慢。

 

經跟客戶說明，並請客戶改維護計畫，勾選「僅限索引統計值」，執行時間由原本「重建索引」的3倍，降為1/2。

為何執行計劃沒有顯示遺漏索引（Missing Index）

 

Begin

同事在測試missing index，建立一個簡單的Heap TABLE，新增了1萬多筆資料，接著執行一句select。本來預期SQL SERVER的執行計劃中應該要出現遺漏索引的建議，但實際上卻沒有。

到底SQL SERVER是如何決定要不要提出遺漏索引建議的？

Background

從SQL SERVER 2005開始，新增了一個遺漏索引的功能，用於提供使用者一個最佳的索引建議，以節省查詢成本。當SQL 語句被執行時，若有遺漏索引則系統會將其資訊將紀錄於下列DMV中。（評估執行計劃所顯示的遺漏索引不會紀錄）

底下是微軟官方對遺漏索引的說明：

 

Study

上圖中寫道「未針對簡單查詢計劃提出建議」，原文為「Suggestions aren't made for trivial query plans」，它的意思是當執行計劃是一個「簡單查詢計劃」時，不會提供遺漏索引的建議。

註：執行計劃（execution plan）與查詢計劃（query plan）是同義字，兩者意義相同。

 

~~ 那麼，什麼是「簡單的查詢計劃」呢 ..？ 

 

我們可以在執行計劃的根節點→屬性→最佳化層級（optimization level）,查看此執行計畫的優化層級是TRIVIAL或FULL。當值為TRIVIAL時，即是所謂簡單查詢計劃。

 

何謂TRIVIAL及FULL

查詢優化器是基於成本的，所謂的「成本」包含了優化成本+執行成本。我們也不希望優化器花費數分鐘來產生僅需幾秒鐘即可執行完的執行計劃。因此，優化器會避免在優化上花費的時間多於在查詢上節省的時間。故優化的目標並不是探索每一個可能的執行方案來找到最佳的執行計劃。而是快速找到一個已經足夠好的執行計劃。

註：所以有時候我們必須使用查詢提示或計劃指南來手動優化，以產生特定的更好的執行計劃。

 

優化器有兩個大的優化層級TRIVIAL及FULL (Exploration)，其中FULL又可細分為3個階段search 0~2，所以優化器最多會運行4個階段。

每個階段各有其進入條件和終止條件。進入條件意味著可以跳過一個階段；例如search 0在輸入樹中至少需要三個連接表引用，如果少於3個連接，那麼將直接跳過search 0進入search 1。

終止條件有助於確保優化器不會花在優化上的時間超過它節省的時間。如果當前最低計劃成本邊界低於配置值，則搜索將提前終止，並顯示「已找到足夠好的計劃」。

 

TRIVIAL

前面提到遺漏索引「未針對簡單查詢計劃提出建議」，所謂簡單查詢計劃即是TRIVIAL。因此當執行計劃的最佳化層級是TRIVIAL時，優化器將不會產生遺漏索引的建議。

官方文件及相關資料中並沒有清楚說明什麼樣的情況下會產生TRIVIAL執行計劃，經測試及參考，歸納出下列五點，當同時滿足下列五點時，即會產生一個TRIVIAL執行計劃。反之，則將進入FULL優化階段。

一、      SQL語句中沒有子查詢

二、      SQL語句中沒有join其它table

三、      SQL語句的來源TABLE中沒有任何non-clustered index

四、      估計成本低於Cost Threshold for Parallelism

五、      跟踪旗標8757為off狀態（trace flag 8757用於控制是否跳過TRIVIAL plan）

依上面所說，以本文開頭的例子，若要避開TRIVIAL plan，可以在where條件下加入一個無用的子查詢「and 1=(select 1)」，即會產生FULL plan並產生遺漏索引建議（如下圖）

你也可以將Cost Threshold for Parallelism調整為0，因為任何執行計劃成本必定大於0，所以任何的執行計劃的最佳化層級都將為FULL。當然你也可以使用trace flag 8757跳過TRIVIAL plan。這些有興趣的人可以自行試看看。

 

 

系統何時才會產生遺漏索引建議：

一、當最佳化層級為FULL，且發現有更好的索引而TABLE中沒有時。

二、遺漏索引所渉及的相關欄位在SQL語句中沒有發生隱含式轉換。

 

 遺漏索引的建議可信嗎？可否直接依SQLSERVER建議建立遺漏索引？

 個人建議對「遺漏索引」抱持審慎懷疑的態度，因為：

一、 遺漏索引所建議的索引鍵，沒有按照索引優化的順序，最好重新審視那些索引鍵欄位的順序。

二、 有時遺漏索引會建議include一些欄位，但SQL Server在建議大量include欄位時，不會對產生的索引大小執行任何成本效益分析。若發現遺漏索引建議的include欄位太多，需審慎斟酌。

三、 遺漏索引的建議，可能會與現存的索引類似，需將遺漏索引與現存索引比對，並儘可能合併索引。

四、在SQL 2008有個bug，遺漏索引會建議一個已經存在的索引，也就是即使你依照遺漏索引建立了，下次SQL語法執行時，系統又建議相同的遺漏索引，而這個遺漏索引其實已經存在於TABLE之中。官方雖稱在SQL 2012解決了，但我在SQL 2019中測試仍然存在。

 遺漏索引bug測試，可以參考下列連結

https://www.sqlskills.com/blogs/paul/missing-index-dmvs-bug-that-could-cost-your-sanity/

也可直接使用下列scripts

--建立測試用TABLE，並塞入資料

CREATE UNIQUE CLUSTERED INDEX t1_clus ON t1 (c1);

GO

SET NOCOUNT ON;

GO

INSERT INTO t1 DEFAULT VALUES;

GO 100000

 

--查詢語句，使用cursor，看執行計畫它有遺漏索引，即使你依照建議建立相同的遺漏索引，下次再執行語法時，它仍然跳出遺漏索引。

DECLARE testcursor CURSOR FOR

    SELECT c1 FROM t1

    WHERE

        c2 BETWEEN 10 AND 1000

        AND c3 > 1000;

 

DECLARE @var BIGINT;

OPEN testcursor;

FETCH NEXT FROM testcursor INTO @var;

WHILE (@@fetch_status <> -1)

BEGIN

    --empty body

    FETCH NEXT FROM testcursor INTO @var;

END

CLOSE testcursor;

DEALLOCATE testcursor;

遺漏索引何時會清除

依微軟官網所述

註：上圖提到當我們在TABLE建立索引時，會變更資料表的中繼資料。而資料表的中繼資料變更會清除該資料表相關的遺漏索引。因此只要你在該TABLE的任何欄位建立索引，該TABLE的相關遺漏索引都會從遺漏索引清單中移除。

一、SQL SERVER重啟（清除所有遺漏索引）

二、HA failover（清除所有遺漏索引）

三、DB offline（清除該DB所有遺漏索引）

四、Table meta-data變更，亦即針對TABLE結構的變更，包含在TABLE上建立任何索引（清除該TABLE所有遺漏索引）。

五、Index rebuild（清除該TABLE所有遺漏索引）。

End

遺漏索引只有在執行計劃最佳化層級為FULL時，才可能出現。以前一直沒有注意到這個問題，只知道有這個功能。它是一個蠻好用的功能，也確實在SQL語法優化時有很大的幫助，不過系統提出的遺漏索引應審慎懷疑，最好經過評估後再建立。

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

問：AlwaysOn次要節點設為「可讀」會對主要節點效能產生影響嗎？

答：會 （但不算太嚴重）

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

場景

AlwaysOn同步認可流程示意

我們知道在讀取資料時會在相關的row/page或table上獲取共享鎖（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需知道可能會造成碎片的增加並增加碎片檢查的頻率，再用一般維護時相同的方式去處理碎片問題即可。

SQL SERVER 有辦法中止 Rollback 嗎！

我仍記得在好多年前在某公司擔任AP兼DBA，有次在手動清除一些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鎖住其它人，那麼當這個delete被rollback時，仍然會鎖住其它人。

停止rollback的方法

要停止rollback的方法步驟如下：

一、   alter database DBNAME set offline with rollback immediate。

二、   使用sp_who2檢查status=rollback的連線，是不是只剩下你要中止rollback的連線。

三、   shutdown with nowait，強制將SQL SERVER關閉。

四、   從OS層級刪除rollback所在DB的log file。

五、   啟動SQL SERVER，rollback所在DB由於沒有log file，無法開始recovery，將會被置於「復原暫止recovery pending」的狀態。

六、   Alter database DBNAME set emergency，將rollback所在DB設為「緊急模式 emergency mode」，emergency mode可以解釋為「bypass recovery」，它會跳過recovery步驟，並允許使用者去存取資料庫。

七、   重建log檔，有兩種方法可以重建LOG檔

(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所在DB的log 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將無法開始recovery，SQL SERVER可以正常啟動，但該資料庫將會被標示為「復原暫止 recovery pending」，且無法被讀取。

接著，我們可以將資料庫置於「緊急模式 emergency mode」。

alter database TestRollback set emergency

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

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

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

在執行kill後它開始rollback，rollback動作是參考log file的紀錄逐條做undo，如果你是逐筆insert那麼你可以想像成系統就逐筆delete。

我們前面kill insert時已經insert了44722筆，直到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去更正系統頁面及系統表。

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

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