SQL Server 中所有效能表現，都是從查詢最佳化工具開始，而且往往也會在這裡決定結果。

他就像棒球投手一樣，每一個 play 一定是從投手開始，而每一個查詢也都是從查詢最佳化開始。

因為這非常重要，所以接下來會深入探討最佳化工具的運作方式。

最佳化過程會產生一個執行計畫。執行計畫是查詢引擎要遵循的執行路徑，同時也是一種機制，用來呈現最佳化工具在產生計畫時所做出的選擇。

最佳化過程本身相當耗費資源，尤其是 CPU 資源，因此也可能影響查詢的整體執行時間。

查詢最佳化流程

SQL Server 中的查詢最佳化工具會使用一套以成本為基礎的分析機制，來決定如何滿足提交的查詢需求。

為了處理一個查詢，SQL Server 必須做出大量選擇。例如：

• 要使用哪一個索引來存取資料表
• 兩個資料表要如何進行聯結
• 資料是否需要排序。

這些選擇，以及許多其他可能的選項，最佳化工具都會為它們估算成本。

最佳化工具也了解資料庫本身的結構。從欄位的資料型別，到主索引鍵定義、條件約束，以及外部索引鍵，這些物件都會影響最佳化工具在做決策時所使用的成本估算。

最後，透過資料統計資訊的使用，資料也會被納入成本計算之中。

最佳化工具並不會試圖產生一個完美的執行計畫。相反地，最佳化工具會根據資料庫中的物件與統計資訊，使用數學模型來產生一個「足夠好」的計畫。

因此，以成本為基礎的分析會試圖在使用盡可能少的資源、並盡快完成查詢的前提下，產生一個盡可能最佳化的計畫。

不過，在最佳化開始之前，還有一些初始的準備階段必須先完成。

最佳化準備

由於最佳化工具需要大量關於系統的中繼資料，因此在真正進行最佳化之前，必須先透過數個步驟將這些資訊整理完成。

這些步驟的執行順序如下：

1. 剖析（Parsing） 代數化器的第一步
2. 繫結（Binding） 代數化器的第二步
3. 最佳化（Optimization）

這個流程中有一組相當複雜的輸入與輸出。

代數化器

查詢這個動作會經過兩個引擎

第一個是關聯式引擎

第二個式儲存引擎

關聯式引擎負責理解 T-SQL :
剖析
物件繫結
產生執行計畫
依照執行計畫要求資料
儲存引擎負責取資料

所以當一個查訊開始，他會先去關連式引擎裡面，然後關聯式引擎裡面的第一關就是代數化器。

代數化器會執行很多步驟

1. 第一步查詢剖析。
   這個程序的作用很單純，就只是去確認語法正不正確而以，有錯誤的話流程會立即終止，後續的所有流程也都會直接忽略。
   這個剖析是以一個批次為單位，批次之中有錯誤，整批救會被取消，而且剖析器會在第一次遇到錯誤就停止，就算語法裡面有很多錯誤，她也只會幫你看第一個錯誤是什麼。

2. 一但通過第一步剖析之後，就會建立一個僅供內部使用的結構，剖析樹然後傳到下一個步驟。

3. 接著代數器會用剖析樹來識別構成該查詢的所有物件。
   這份物件清單包含 Table、Column、Index 等等。
   這個程序就叫做繫結
   所有正在處理的資料型別都會被識別出來，彙總及其他運算也會一併對應完成。接著，這些物件與處理程序會被整合成另一個內部結構，稱為 查詢處理器樹。
   代數化器也會透過在處理器樹中加入步驟，來處理隱含資料轉換。也可能會看到語法最佳化的情況，也就是送交給 SQL Server 的程式碼實際上會被改寫。

所謂的隱含資料轉換是這樣

-- 打開執行計畫
USE AdventureWorks2022
SELECT soh.AccountNumber,
       soh.OrderDate,
       soh.PurchaseOrderNumber,
       soh.SalesOrderNumber
FROM Sales.SalesOrderHeader AS soh
WHERE soh.SalesOrderID
BETWEEN 62500 AND 62550;

會看到類似這種東西，會發現雖然 T-SQL 寫 BETWEEN 但實際上經過代數化器轉換後，變成了 ≥ AND ≤ 的這種形式，所以其實 BETWEEN 跟 大於 AND 小於 是等價的。

還有如果去分析執行計畫，會看到一個驚嘆號警告

會發現在做這個查詢的時候，SQL Server 把 SalesOrderID 做了一次型別轉換

警告很不清楚。在這個案例中，警告不是來自查詢的 WHERE 中參照的 SalesOrderID，而是來自計算欄位 SalesOrderNumber

不過在這個案例中，查詢並沒有任何篩選條件參照這個欄位，所以可以直接忽略。

題外話
你第一次用 AdventureWorks2022 而且又真的很仔細看得話，會覺得這第一點到底在公三小
明明看到驚嘆號底下的警告是寫，CONVERT …. SalesOrderID ….，明明轉換的是 ID，為何我那邊寫是受到 SalesOrderNumber 影響。
這是因為 SalesOrderNumber 在這張 table 裡面是被定義成這樣:
[SalesOrderNumber] AS (isnull(N'SO'+CONVERT(nvarchar,[SalesOrderID]),N'*** ERROR ***'))
而這裡出現警告，是因為出現型別轉換的時候，SQL Server 會無法確定這東西會不會影響估算，所以才警告。
那可以忽略的原因就很直覺了，因為他是去影響估算結果，但是這個 OrderNumber 又沒有出現在 WHERE，所以不管估算如何都跟效能沒有關係，所以忽略。

最佳化

第一步 簡化

在這個階段，最佳化會確認查詢中的物件，是不是真的都需要被使用。

統計資訊與資料列數量會開始被收集使用。

這個步驟也會檢查條件約束。

這步驟有一個重點 : 聯結消除

雖然 T-SQL 寫了 JOIN 某些表，但最佳化發現那些表其實對結果沒有影響，所以直接把他們從執行計畫中移除。

--假設現在有一個查詢如下
SELECT o.OrderID, o.OrderDate
FROM Orders AS o
JOIN Customers AS c
    ON o.CustomerID = c.CustomerID
WHERE o.OrderDate >= '2025-01-01';
我們有 JOIN Customers
但實際上整個查詢 不論是 WHERE 或 SELECT 都沒有用到 Customers
那這時候還有需要去讀 Customers 嗎？
答案是 : 不一定需要
當然不需要的話效能肯定會更好
至於最佳化決定會不會去讀 Customers 的關鍵是 : Foreign Key
如果 Orders.CustomerID 有 Foreign Key Customer.CustomerID 的話
代表每一筆 Orders 裡的 CustomerID，一定都能在 Customers 找到對應資料
所以有沒有去 Join Customer 都變得無所謂了，這種時候，最佳話就會拿掉這個 Join

這種問題在嚴謹的 T-SQL 下應該不會發生，沒有必要 JOIN 的 TABLE 根本也不該寫進語法了，但如果你不小心寫了，最佳話會做最後一個把關。

第二步 簡單計畫比對

當查詢極度簡單的時候，最佳話會直接放棄執行最佳化，在這種模式下是因為最佳化沒有其他選擇，只有一條路可以走，所以她放棄。

最簡單的例子就是 :
一個沒有索引的表，因為他沒有索引，所以這種 TABLE 唯一存取的方式就是整張表掃描，這樣的話她就會放棄執行最佳化。

這種狀況下去看執行計畫，會看到一個標記，叫做 Trivial。

第三步 最佳化階段

一旦確定找不到簡單計畫，就會進入這個階段

這個階段是最重要的一個階段

在這個階段裡，會把流程拆成很多個步驟，每個步驟都會盡可能少做工作，這些階段如下 :
但並不是說每次都一定會把這三個階段走一遍，最佳化會去判斷這次的查詢複雜程度來決定要走到哪一步驟。

• Search0 也叫 Transaction : 在這個階段就能滿足地查詢，通常是簡單的 OLTP。這類查詢通常只有少量 JOIN，而且不需要進行轉換，例如說像重新排列 JOIN 順序。
• Search1 也叫 Quick Plan : 這個階段會出現複雜的操作，例如重新排列 JOIN 順序，以及其他轉換，用以產生一個足夠好的計劃。
• Search2 也叫 Full Optimization : 當查詢較複雜，就會經過全部三個最佳化步驟，達到完整最佳化。這個階段包含最複雜的評估，例如複合索引使用、子查詢展開、相關子查詢轉換 JOIN。

如同一開始說的，他不一定是從 0 1 2 這樣順序下來，他可能直接一開始就跳到 2 去執行。

這些階段都可以決定如何執行特定 JOIN 操作，或決定透過 SCAN 還是 SEEK 來存取資料。

大多數最佳化在決策的主要依據是資料列數量。資料列數量通常來自篩選條件中欄位的統計資訊。例如 ON、HAVING、WHERE 裡面的欄位。

有了這些統計資訊之後，最佳化工具就會根據滿足查詢所需的估計 CPU、RAM、I/O 進行選擇與計算，然後得到一個執行的總成本。

這個成本不是是估算出來的，實際上是一個純粹的數學模型。

然後她會進行反覆嘗試，用不同策略嘗試不同計劃，然後計算哪個計劃具有最低成本。

當最佳化找到符合所有計算需求的計劃時，即使可能存在更好的計劃，他也會停止繼續找。這叫做最佳化提前終止。

還有一種讓最佳化停下來的方式叫做 Timeout，這並不是說他沒跑完，相反是他已經跑完了，只是用 Timeout 這個機制讓它結束最佳化。

如果查詢進入 Search2，這裡會評估是否要從序列計劃轉換成平行計劃。

透過執行計畫，就可以看到最佳化所做的大量工作。

-- 隨便示範一個查詢，要去看執行計畫
SELECT soh.SalesOrderNumber,
       sod.OrderQty,
       sod.LineTotal,
       sod.UnitPrice,
       sod.UnitPriceDiscount,
       p.Name AS ProductName,
       p.ProductNumber,
       ps.Name AS ProductSubCategoryName,
       pc.Name AS ProductCategoryName
FROM Sales.SalesOrderHeader AS soh
     JOIN Sales.SalesOrderDetail AS sod
          ON soh.SalesOrderID = sod.SalesOrderID
     JOIN Production.Product AS p
          ON sod.ProductID = p.ProductID
     JOIN Production.ProductModel AS pm
          ON p.ProductModelID = pm.ProductModelID
     JOIN Production.ProductSubcategory AS ps
          ON p.ProductSubcategoryID =
             ps.ProductSubcategoryID
     JOIN Production.ProductCategory AS pc
          ON ps.ProductCategoryID =
             pc.ProductCategoryID
WHERE soh.CustomerID = 29658;

-- 除了用 gui 以外也有一個 dmv 可以去看最佳化流程的匯總訊息
SELECT deqoi.counter,
       deqoi.occurrence,
       deqoi.value
FROM sys.dm_exec_query_optimizer_info AS deqoi;

可以看出這個示範查詢還沒有很複雜，因為沒有進行任何 search 2 的操作。

執行計畫的內部細節會在之後解釋。

第四步 產生平行執行計畫 ( 如有必要才進行這步 )

如果執行計畫變得足夠複雜，就會考慮使用平行執行。

不過，平行執行是一項成本非常高的操作，所以在平行執行之前，我們必須確定該查詢真的能從平行處理上受益。事實上，有許多以平行方式執行的查詢，反而可能因為這個流程變得更慢。

當最佳化工具決定每個複雜查詢是否要採用平行計畫的時候，會考量很多因素如下 :

• SQL Server 可用的 CPU 數量
• SQL Server 版本
• 可用記憶體
• 平行處理成本閥值
• 需要處理的資料列數
• 目前作用中的並行連線
• 最大平行處理程度 ( MDP )

這些因素最重要的三個是 : 實際可用 CPU 數量、MDP、平行處理閥值

控制 MDP 的方式在基礎那邊有寫過，以及要怎麼控制

USE master;
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE;
EXEC sp_configure 'max degree of parallelism', 2;
RECONFIGURE;

-- 也可以用 QUERY HINT 去控制 MDP
SELECT e.ID,
       e.SomeValue
FROM dbo.Example AS e
WHERE e.ID = 42
OPTION (MAXDOP 2);

-- 控制閥值
USE master;
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE;
EXEC sp_configure 'cost threshold for parallelism',
35;
RECONFIGURE;

閥值預設是 5，這對所有系統而言都太低。

微軟通常不會同意這種說法，但在任何我負責的系統上，我都會立刻把這個值調高。

再次強調，這個數值取決於每一個系統的查詢與負載
判斷閥值到底要調成多少，是去查看系統上查詢的估計成本。

WITH XMLNAMESPACES
(
    DEFAULT
    N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
),
TextPlans
AS
(
    SELECT CAST(detqp.query_plan AS XML) AS QueryPlan,
           detqp.dbid AS DatabaseId
    FROM sys.dm_exec_query_stats AS deqs
    CROSS APPLY sys.dm_exec_text_query_plan(
        deqs.plan_handle,
        deqs.statement_start_offset,
        deqs.statement_end_offset
    ) AS detqp
),
QueryPlans
AS
(
    SELECT RelOp.pln.value(N'@EstimatedTotalSubtreeCost',
                           N'float') AS EstimatedCost,
           RelOp.pln.value(N'@NodeId',
                           N'integer') AS NodeId,
           tp.DatabaseId,
           tp.QueryPlan
    FROM TextPlans AS tp
    CROSS APPLY tp.QueryPlan.nodes(N'//RelOp') RelOp(pln)
)
SELECT qp.EstimatedCost AS [估計成本],
       qp.NodeId AS [節點 ID],
       qp.DatabaseId AS [資料庫 ID]
FROM QueryPlans AS qp

還有 DML 查詢的資料變更動作都是以 序列方式執行。

第五步 執行計畫快取

當上述的動作都已完成，產生的結果就是一個執行計畫。

這個計畫會存在 SQL Server 記憶體中的某個位置，稱為 plan cache。

將計畫儲存到快取中，是 SQL Server 為了讓查詢更快而執行的另一種最佳化方式。透過儲存計畫，之後就可以重複使用該計畫，而不必再次經過最佳化流程。

執行計畫老化
因為這是快取在記憶體，但記憶體還會存其他東西，所以遲早有一天會塞滿。

為了避免這種情況，SQL Server 會動態控制計畫快取中執行計畫的保留方式

保留經常使用的執行計畫，丟棄一段時間內沒有被使用的計畫。

原理不太需要了解看過就好

大概就是 SQL Server 會透過為執行計畫關聯一個 **age 欄位，**這是用來追蹤該執行計畫被重複使用的頻率。

一個執行計畫剛產生，age 上面會有一個成本值，如果一段時間不用，Lazy writer 會去把這個值越降越低直到為 0。

0 就是進入被移除的候選項目，反之一直使用的話，成本值會變得越來越高。

變成 0 不代表馬上就丟棄，如果記憶體還很多的話，就只會一直放著。