首先寫到這裡我發現，30天根本不夠，所以後面還有很多章節會在30天之後持續更新。

然後這邊要給出一個效能調教的結論就是

效能調教並沒有一個唯一答案，在做真正的效能調教之前要先把一種觀念給淘汰掉 :
看到 A 問題，就做XX處理。

例如說 : 看到跑得慢就做索引、看到死鎖就直接KILL

這種類似公式的事情並不存在。

如果有無限的金錢可以無限的堆疊硬體，那當然這是最簡單的方式，但硬體會有極限，現實中你的錢也是。

所以我們必須去建立一套方法，用來辨識到底是哪裡出了問題，理解 SQL Server 引擎內部的運作方式 :

• 用擴充事件蒐集 SQL Server 查詢行為的相關資訊
• 辨識並處理 blocking
• 理解執行計畫
• 統計資料如何運作
• 現代索引技術
• 理解 recompilation
• 善用 Query Store
• 建立 optimizer
• 善用 AI

接下來的操作會圍繞 AdventureWorks2022、Wide World Importers 進行。

我會預設基本的 SQL Server 操作已經都會了，所以什麼備份還原、安裝的我都不會在這裡講。

↓　載 AdventureWorks2022.bak
https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks

↓　載 Wide World Importers.bak
https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0

查詢效能調教流程

要開始調教查詢之前，我們要先辨識出，哪一個特定的查詢需要我們注意。

這表示我們要有一套方法，來監控查詢。

對沒有錯這邊一直強調"查詢"，原因是查詢以外的調教都不會出現在這裡，基本上那些其他的調教都已經在基礎篇說明解釋清楚了。

然後再針對這些問題最多的查詢去找效能緩慢的原因，進行疑難排解，這時候才會套用各種機制來解決的些問題。

最後變更之後量化效能，如此才算完整的流程。

1. 監控
2. 解決
3. 量化

然後通常會發現，這個流程會一再重複，因為沒有任何單一方法可以永遠修正效能問題，所以需要準備好針對各種變更與解決方案進行實驗、量化效果，最後判斷。

在解決問題的時候的最佳習慣之一是 : 一次只變更一件事情

有時候可能判斷，新增一個索引可以大幅改善某個查詢效能，但他也可能導致其他查訊變慢。其他查詢變慢，可能是因為它們沒有使用那個索引，而那個索引本身對它們並沒有那麼好；也可能是因為它們是資料修改查詢，現在當資料被加入、變更或從系統中移除時，另一個索引也必須被修改。

所以在這種環環相扣的情況下，一次只具去變更一件事情會是個比較好的做法。

可能會引發的效能問題

效能調教並不只是查詢和索引。

硬體、OS、雲端供應商、網路、其他應用程式，都可能最伺服器造成負面影響，但是這些不在這裡討論，只是要特別說明出來，效能調教並不只是去調語法、調索引。

以下是會影響效能，但是不在這次討論的

• 雲端供應商上的效能層級不足。
• SQL Server 以外的應用程式正在消耗伺服器資源。
• SQL Server 服務的組態設定。
• 伺服器所執行的硬體，或虛擬機器的容量。
• 網路硬體或組態問題。
• 對伺服器執行的應用程式碼或報表，本身可能設定錯誤，或沒有最佳化。
• 容器管理與資源的設定錯誤或實作不良。
• 資料庫設計本身可能沒有最佳化。

最常見的效能問題 T-SQL、不良索引、I/O 程式碼、schema、過期統計資訊

• 索引不足或索引品質不佳
• 統計資料不準確或遺失
• 不良的 T-SQL
• 有問題的執行計畫
• 過度 blocking
• 死結
• 非集合導向的操作
• 不正確的資料庫設計
• 不良的執行計畫重用
• 查詢頻繁重新編譯

上述幾點是最常見引發校能問題的原因，我說的是最常見，不是一定只有這些

索引不足或索引品質不佳

很多人會說，只要在 table 上加上一個索引就好了；

實際上，缺少索引確實是 SQL Server 校能問題中最大的問題之一。

有時候也會發現，雖然有索引，但他不是正確的索引；或者那個索引在 key 的選擇上很差。

如果 SQL Server 在篩選 data 的時候，沒有索引，那他除了讀完整張 table 以外別無選擇。

這就會導致硬碟校能、RAM 使用量的問題，此外，也會有大量資源競爭，因為 scan 這件事情會導致很多 blocking。

所以，撰寫 T-SQL 跟 索引建立，最好是綁再一起，一起建立出來。

雖然索引非常重要，但其實也可能建立太多索引。

每一次需要透過 INSERT、DELETE 或 UPDATE 作業修改資料時，任何包含該資料作為 key 或 INCLUDE 欄位的索引，也都需要一起更新。可能會看到資料修改查詢出現嚴重的效能衝擊，因為它們必須等待索引被更新。

統計資料不準確或遺失

SQL Server 內部的查詢最佳化器，會根據它預期受到查詢影響的資料列數，進行大量計算，以判斷要如何滿足某個查詢。

這些資料列數估算，直接來自資料庫中存在於索引與欄位上的統計資料。假如統計資料遺失，或是嚴重過期，最佳化器做出的選擇可能會非常糟糕。

可能會看到整張資料表被掃描，但其實如果能對索引進行 seek，原本可以改善效能。相反地，你也可能會看到使用 seek，但其實 scan 會更有效率。

如果沒有良好的資料列數估算，最佳化器就無法做出好的選擇。

不良的 T-SQL

雖然索引可以幫助你的查詢跑得更快，統計資料也能讓最佳化器取得更好的資料列數估算，但不良的程式碼選擇可能會完全抵消這些好處。

可能用某種方式撰寫程式碼，導致索引無法被使用，進而造成 scan 與過量的 I/O。

可能移動了太多資料，並且打算在應用程式端進行篩選。

可能把程式碼寫得過度複雜，導致最佳化器很難拆解它，並產生良好的執行計畫。

可能不正確地使用 SQL Server 中不同的物件類型，導致效能非常差。

這部分，可以用 AI 來處理，前提是，T-SQL 語法上沒有機敏資訊，或是用落地式 AI。

有問題的執行計畫

嚴格來說，並沒有「壞的」或「不正確的」執行計畫這種東西。最佳化器產生的每一個執行計畫都能運作。不過，有些執行計畫會比其他執行計畫更好。

大多數時候，執行計畫可以透過以下方式修正：

• 修改程式碼
• 更新統計資料
• 新增索引

這也是可以貼給 AI 的東西

過度 blocking

SQL Server 透過套用 ACID 機制，保證儲存在其中的資料會保持一致且正確。

簡單來說，在資料庫中的變更完成之前，它不會受到其他變更的影響。查詢只能看到修改完成之前或完成之後的資料。雖然有一些方法可以改變這種預設行為，但它們仍然都遵循一些基本規則。

這種資料修改的隔離性，允許多個查詢以共享方式存取資訊，而不會互相 blocking。不過，當超過一個查詢嘗試存取正在被修改的資料，或是持有修改相關資訊時，你就會看到其中一個查詢等待另一個查詢。這就是實際發生的 blocking。

Blocking 會稍微變得更嚴重，是因為 SQL Server 會把資訊儲存在磁碟上的 8KB page 中。兩個程序即使沒有更新同一列資料，也可能互相 blocking。

另外，資源不足、記憶體或 CPU 不夠、磁碟速度不夠快，也可能導致額外的 blocking。當程序等待資源釋放時，它們會持有其他程序需要的資料列或 page 上的鎖，這些都會讓 blocking 變得更嚴重。

死結

Deadlock 與 blocking 有關；事實上，deadlock 是由 blocking 所造成的。不過，blocking 和 deadlock 是兩個非常不同的主題，應該在腦中把它們分開看待。

造成 deadlock 的核心問題，是兩個程序各自都在某個 page 上持有獨占鎖，而對方又需要那個 page，才能完成交易。這通常被稱為 deadly embrace。

其中一個程序必須被允許繼續完成。另一個程序會被選為 deadlock victim，而它尚未完成的變更會被 rollback。

真正造成效能頭痛的是 rollback。不只是某個查詢沒有完成，現在 SQL Server 還必須額外工作，把系統中那些已經部分完成的變更移除。這可能導致更多資源競爭、更多效能變慢，以及額外的 blocking。

除此之外，你幾乎可以確定，遇到 rollback 的使用者一定會重新送出他們的查詢，甚至可能會重送多次。這會讓 deadlock 所造成的效能問題更加惡化。

從問題根源來看，deadlock 本身就是一個與效能相關的問題。如果你所有查詢都能夠足夠快速地完成，發生 deadlock 的機率就會非常低。

非集合導向的操作

一般來說，SQL，尤其是 T-SQL，都是設計來以集合的方式處理資料。

不同於許多程式語言，不應該用「一次處理一列資料」的方式來思考查詢。有人會使用 cursor 或其他類型的迴圈操作，強迫 SQL Server 以逐列處理的方式運作。

這會徹底破壞效能。

不正確的資料庫設計

SQL Server 是一套關聯式資料儲存引擎。這代表很多事情，其中之一是：SQL Server 被設計來搭配具有外鍵約束的資料表一起運作，透過外鍵約束作為機制，強制維持資料表之間的關聯。

確保資料庫有正確正規化，可以提升效能。你會透過消除重複值來減少欄位數量。你也會透過把資訊儲存在多個資料表中，來減少資料列數量。當這些事情正確完成時，都能提升效能。

Clustered index 和 clustered columnstore index 會定義資料儲存方式，而這又會控制資料擷取方式。如果是 rowstore index，正確的索引以及索引上正確的 key，和資料表定義一樣，都是資料庫設計的一部分。

正確儲存資料，也就是 datetime 要放進 datetime 欄位，字串要放進 char、varchar 或 nvarchar 欄位，會正面影響效能。

不良的執行計畫重用

由查詢最佳化器編譯執行計畫，是一項成本很高的作業。正因如此，SQL Server 會把執行計畫儲存在一塊稱為 plan cache 的記憶體空間中。

概念很簡單：許多查詢，甚至可能是大多數查詢，都是重複執行的。因此，這些執行計畫可以被重用，大幅降低產生它們所需的額外負擔。

Stored procedure 和 prepared statement 的參數化，會讓執行計畫重用變得更簡單。另一個稱為 simple parameterization 的程序也是如此；在這個程序中，最佳化器會替簡單查詢加入參數。所有這些做法，都是為了嘗試重用執行計畫。

不過，查詢也可能因為結構寫法的關係，導致它們無法重用執行計畫，即使它們其實和先前執行過的查詢相同。產生動態 T-SQL 可能會導致這個問題。設定不良的 ORM 工具也可能產生不適當的參數，進而阻止執行計畫重用。

查詢頻繁重新編譯

就像缺乏執行計畫重用會對系統造成過多額外負擔一樣，大量重新編譯執行計畫也會造成非常類似的問題。

一般來說，重新編譯執行計畫是一個理想的程序。重新編譯通常是由資料變更造成的，而資料變更會導致統計資料被更新。透過新的資訊，最佳化器可能會為你的查詢做出更好的選擇。

不過，就像許多其他好東西一樣，太多就會變成問題。你可能有高度波動的資料集合，導致大量重新編譯。你也可能因為不良的程式碼實務而造成重新編譯。

理解 “夠好”

不可能有那個時間或能力，把每一個查詢都調到最後一微秒的效能。

那到底要調叫到什麼樣的程度就算很好? 這問題並沒有唯一正確答案。

一個執行時間 10 毫秒的查詢你會說他很好，直到這個查詢在一分鐘內被呼叫數千次的時候你就覺得他很糟糕了。
但這時候如果讓這個查詢少了 1 到 2 毫秒，可能就會拯救整個系統。

相反的一個執行 30 豪秒的查詢，每小時只被使用幾次，那根本不值得去花時間在為了這個查詢去浪費時間調校。

所以看的出來，夠好的關鍵在於時間，不是查詢時間，是人類的時間。

查詢調教是一個非常消耗時間的工作，

1. 建立監控機制之後，要花時間消化收集的資料
2. 然後辨識出要調校的查訊後，要花很多時間閱讀這些查詢，理解他正在做什麼、如何做、為什麼這樣做
3. 然後再花更多時間看執行計畫，去理解有沒有調校機會
4. 最後花時間在測試伺服器上測試調校有沒有成功。

所以不要為了多額外榨出 1 毫秒效能，浪費太多時間在不重要的查詢上。

建立比較基準

做到夠好的查詢調校後，要有一個比較基準才能量化這次的調校結果，通常需要包含以下內容 :

• 對系統上的查詢進行完整量測，甚至可能細到每一個資料庫中的個別陳述式。這樣你之後就可以進行詳細分析，確認你的查詢調校是否真的有效。
• 量測呼叫資料庫的應用程式或報表效能。它們的 round-trip time 通常是大家唯一在意的事情，因此這是衡量查詢效能的一個好方法。
• 使用儲存在 plan cache 中的資訊，立即查看系統中查詢最近的行為。plan cache 是 SQL Server 執行個體中的一塊記憶體空間。雖然它不是詳細的效能量測資料，但確實可以提供一個比較基準。
• 立即執行一次查詢，看看效能表現如何。這不建議用在已經承受嚴重壓力的系統上，當然也不適合用在資料修改查詢上；但如果你能量測查詢行為，它就能提供一個比較基準。
• 在非正式環境中執行查詢。雖然正式環境中的完整行為範圍，可能無法在其他環境中完全重現，但一個執行緩慢的查詢，仍然是一個執行緩慢的查詢。

要量測的東西也不只是時間，還有CPU使用量、硬碟 I/O、ram 等等。

有時候會發現，我故意讓查詢跑慢一點，以便減少 CPU、I/O、RAM 使用量，但整體系統反而更快。這取決於問題出在哪裡。

所以不是盡可能地把查詢時間縮短，就是好的調校。