查詢存放區使用案例

適用于： SQL Server Azure SQL DatabaseAzure SQL 受控執行個體Azure Synapse Analytics

若追蹤並確保可預測的工作負載效能非常重要，就能在整組案例中廣泛使用查詢存放區。 以下是您可以考慮的一些範例︰

• 透過計畫選擇迴歸找出並修正查詢

• 識別與調整資源使用查詢

• A/B 測試

• 在升級至較新的SQL Server期間保持效能穩定性

• 識別並改善臨機操作工作負載

• 如需使用查詢存放區設定和管理的詳細資訊，請參閱使用 查詢存放區 監視效能。

• 如需使用查詢存放區探索可採取動作資訊並微調效能的資訊，請參閱使用 查詢存放區微調效能。

• 如需操作 Azure SQL Database 中查詢存放區的相關資訊，請參閱 操作 Azure SQL Database 中的查詢存放區。

透過計畫選擇迴歸找出並修正查詢

在定期查詢執行期間，查詢最佳化工具可能會決定選擇不同的計畫，因為重要的輸入變得不同：資料基數已變更、索引已建立、改變或卸載、統計資料已更新等等。通常，新方案較佳，或與先前使用的方案大致相同。 不過，還是會出現新計畫明顯較糟的情況，而這類情況則稱為計畫選擇變更迴歸。 在查詢存放區之前，很難識別並修正SQL Server未提供內建資料存放區的問題，讓使用者查看一段時間所使用的執行計畫。

有了查詢存放區，您就可快速地：

• 識別在感興趣的時段 (過去小時、天、週等) 中執行計量已降低的所有查詢。 使用SQL Server Management Studio中的回歸查詢來加速分析。

• 在回歸查詢中，很容易找到具有多個計畫的查詢，因為計畫選擇不正確而降低。 使用 [迴歸查詢] 中的 [計畫摘要] 窗格，以視覺化方式顯示某個迴歸查詢的所有計畫及其在某一段期間內的查詢效能。

• 從歷程記錄強制執行先前的計畫 (如果已證實該計畫比較好)。 使用 [迴歸查詢] 中的 [強制計劃] 按鈕，強制執行針對查詢所選取的計劃。

如需此案例的詳細描述，請參閱 Query Store:A flight data recorder for your database (查詢存放區︰適用於資料庫的飛行資料記錄器) 部落格。

識別與調整資源使用查詢

雖然您的工作負載可能會產生成千上萬個查詢，但通常只有其中幾個實際上最常使用系統資源，因而需要您多加注意。 在耗用最上層的資源查詢中，您通常會找到已回歸的查詢，或是可透過其他微調來改善的查詢。

開始探索的最簡單方式是在 Management Studio 中開啟 最耗用資源查詢 。 使用者介面會分成三個窗格︰代表熱門資源取用查詢的長條圖 (左)、所選取查詢的計畫摘要 (右)，以及所選取計畫的視覺化查詢計畫 (下方)。 選取 [ 設定 ] 來控制您想要分析的查詢數目和感興趣的時間間隔。 此外，您可以在不同的資源耗用維度 (持續時間、CPU、記憶體、IO、執行數目) 和基準 (平均、最小值、最大值、總計、標準差) 之間進行選擇。

查看右邊的計畫摘要來分析執行歷程記錄，並了解不同的計畫及其執行階段統計資料。 使用下方窗格來檢查不同的計畫，或是以視覺化的並排顯示方式來比較它們 (使用 [比較] 按鈕)。

當您識別出效能次佳的查詢時，您的動作將取決於問題的本質：

1. 如果查詢是使用多個計畫來執行，而最後一個計畫明顯比先前的計畫還差，您可以使用計畫強制機制來確保SQL Server未來執行的最佳計畫

2. 檢查最佳化工具是否在 XML 計畫中建議任何遺漏的索引。 如果是，請建立遺漏的索引，並使用查詢存放區來評估索引建立之後的查詢效能

3. 針對查詢所使用的基礎資料表，確定其中的統計資料為最新。

4. 確定查詢所使用的索引會重組。

5. 考慮重寫耗用資源的查詢。 例如，充分利用查詢參數化並減少動態 SQL 的使用。 在讀取資料時實作最佳邏輯 (在資料庫端套用資料篩選，而不是在應用程式端)。

A/B 測試

使用查詢存放區來比較應用程式變更前後的工作負載效能。

下列清單包含數個範例，您可以使用查詢存放區，來評估環境或應用程式變更對工作負載效能的影響︰

• 推出新的應用程式版本。

• 在伺服器上新增硬體。

• 在成本昂貴查詢所參考資料表上建立遺漏的索引。

• 套用安全性原則以取得資料列層級安全性。 如需詳細資訊，請參閱使用查詢存放區將資料列層級安全性最佳化 \(英文\)。

• 將暫時性系統版本設定新增到您 OLTP 應用程式經常修改的資料表中。

在上述任何案例中，套用下列工作流程︰

1. 在計畫的變更之前使用查詢存放區來執行您的工作負載，以產生效能基準。

2. 在目前受控制的時段中套用應用程式變更。

3. 繼續執行工作負載一段足夠的時間，才能產生變更之後的系統效能影像

4. 比較 #1 和 #3 的結果。

   1. 開啟 [整體資料庫耗用量] 來判斷對整個資料庫的影響。

   2. (開啟 熱門資源取用查詢 ，或使用 Transact-SQL) 執行您自己的分析，以分析變更對最重要查詢的影響。

5. 萬一無法接受新的效能，請決定要保留變更，或是執行復原。

下圖顯示發生遺漏索引建立情況的查詢存放區分析 (步驟 4)。 開啟 [熱門資源取用查詢] / [計畫摘要] 窗格，即可針對應該會受到索引建立影響的查詢取得這個檢視︰

此外，您可以將計畫在索引建立前後的情況並排顯示以進行比較 ([在另一個視窗中比較所選查詢的計畫] 工具列選項，工具列上以紅色方塊標示的選項)。

在索引建立 (plan_id = 1 之前規劃，上述) 缺少索引提示，而且您可以檢查叢集索引掃描是否為查詢 (紅色矩形) 中成本最高的運算子。

在遺漏索引建立 (plan_id = 15 之後規劃，下列) 現在有索引搜尋 (非叢集) ，可降低查詢的整體成本，並改善其效能 (綠色矩形) 。

根據分析，您很可能會保留索引，因為查詢效能已改善。

在升級至較新的SQL Server期間保持效能穩定性

在 SQL Server 2014 (12.x) 之前，使用者會在升級至最新平臺版本期間暴露效能回歸的風險。 原因在於，最新版的查詢最佳化工具會在安裝新的位元之後立即變成使用中狀態。

從 SQL Server 2014 開始， (12.x) 所有查詢最佳化工具變更都會系結至最新的資料庫相容性層級，因此計畫不會在升級時立即變更，而是當使用者將 變更 COMPATIBILITY_LEVEL 為最新的查詢最佳化工具時。 此功能會結合查詢存放區，可讓您在升級過程中對查詢效能擁有絕佳層級的控制。 下圖顯示建議的升級工作流程：

1. 升級SQL Server而不變更資料庫相容性層級。 它不會公開最新的查詢最佳化工具變更，但仍提供較新的SQL Server功能，包括查詢存放區。

2. 啟用查詢存放區。 如需詳細資訊，請參閱將查詢存放區調整為工作負載。

3. 讓查詢存放區擷取查詢和計劃，並利用來源/原資料庫相容性層級建立效能基準線。 請在這個步驟中待足時間，以擷取所有的計劃並取得穩定的基準。 這可以是生產工作負載之一般商務週期的持續時間。

4. 移至最新的資料庫相容性層級：讓您的工作負載公開至最新的查詢最佳化工具，以可能建立新的計畫。

5. 使用查詢存放區進行分析和回歸修正：通常，新的查詢最佳化工具改善應該產生更好的計畫。 不過，查詢存放區可讓您輕鬆找出計劃選擇迴歸，並使用計劃強制執行機制加以修正。 從 2017 SQL Server 2017 (14.x) 開始，使用自動計畫更正功能時，此步驟就會變成自動。

   a. 如果發生迴歸的情況，請強制執行查詢存放區中先前已知的良好計畫。

   b. 如果無法強制執行查詢計畫，或效能仍然不足，請考慮將資料庫相容性層級還原為先前的設定，然後通知 Microsoft 客戶支援。

提示

使用SQL Server Management Studio升級資料庫工作來升級資料庫的資料庫相容性層級。 如需詳細資訊，請參閱使用查詢調整小幫手來升級資料庫。

識別並改善臨機操作工作負載

某些工作負載沒有您可以調整的主要查詢，以改善整體應用程式效能。 這些工作負載通常是使用相對大量的各種查詢作為特性，這其中每一個查詢都會耗用部分的系統資源。 由於是唯一的，這些查詢很少執行 (通常只執行一次，因此會以特別的方式命名)，所以它們的執行階段耗用量並不重要。 另一方面，假設應用程式一段時間都會產生新的查詢，系統資源的重要部分會花在查詢編譯上，這並非最佳。 這不適用於查詢存放區，因為大量的查詢和計畫會填入您保留的空間，這表示查詢存放區最終可能會非常快速地處于唯讀模式。 若您啟用 [Size Based Cleanup Policy (大小基礎清除原則)] (強烈建議持續啟動並執行查詢存放區)，則背景處理程序大部分的時間將會清除查詢存放區結構，並佔用大量系統資源。

[ 最耗用資源查詢 ] 檢視可讓您第一次指出工作負載的臨機操作本質：

使用 [執行計數] 計量來分析您排名最前面的查詢是否是特定的 (這需要您使用 QUERY_CAPTURE_MODE = ALL 來執行查詢存放區)。 您可以從上圖看見 90% 的 前幾大耗用資源的查詢 只執行了一次。

或者，您可以執行 Transact-SQL 腳本來取得系統中的查詢文字、查詢和計畫總數，並藉由比較 query_hash 和 query_plan_hash 來判斷它們的不同程度：

--Do cardinality analysis when suspect on ad hoc workloads
SELECT COUNT(*) AS CountQueryTextRows FROM sys.query_store_query_text;  
SELECT COUNT(*) AS CountQueryRows FROM sys.query_store_query;  
SELECT COUNT(DISTINCT query_hash) AS CountDifferentQueryRows FROM  sys.query_store_query;  
SELECT COUNT(*) AS CountPlanRows FROM sys.query_store_plan;  
SELECT COUNT(DISTINCT query_plan_hash) AS  CountDifferentPlanRows FROM  sys.query_store_plan;  

若查詢臨機操作工作負載，這是您可能得到的一種結果：

查詢結果顯示，儘管查詢存放區 query_hash 中大量的查詢和計畫，但 query_plan_hash 實際上並無不同。 唯一查詢文字和唯一查詢雜湊之間的比率遠大於 1，這表示工作負載是適合用來參數化的候選項目，原因是查詢之間唯一的差異只有提供作為部分查詢文字的常值常數 (參數)。

如果您的應用程式會產生查詢 (而不是叫用預存程序或參數化查詢)，或者它依賴預設會產生查詢的物件關聯式對應架構，通常就會發生這種狀況。

如果您可以控制應用程式程式碼，或許能考慮重新撰寫資料存取層，以利用預存程序或參數化查詢。 不過，這種狀況也可以大幅改善，而不需要應用程式變更，方法是強制整個資料庫的查詢參數化， (所有查詢) 或具有相同 的 query_hash 個別查詢範本。

使用個別查詢範本的方法需要建立計畫指南︰

--Apply plan guide for the selected query template 
DECLARE @stmt nvarchar(max);  
DECLARE @params nvarchar(max);  
EXEC sp_get_query_template   
    N'<your query text goes here>',  
    @stmt OUTPUT,   
    @params OUTPUT;  
  
EXEC sp_create_plan_guide   
    N'TemplateGuide1',   
    @stmt,   
    N'TEMPLATE',   
    NULL,   
    @params,   
    N'OPTION (PARAMETERIZATION FORCED)';  

含有計畫指南的解決方案來得更精確，但需要更多工作。

如果您的所有查詢 (或大部分) 都是自動參數化的候選項目，請考慮 PARAMETERIZATION = FORCED 設定整個資料庫。 如需詳細資訊，請參閱 使用強制參數化的指導方針。

--Apply forced parameterization for entire database  
ALTER DATABASE <database name> SET PARAMETERIZATION FORCED;  

在您套用這其中任何步驟之後， 熱門資源取用查詢 將會針對您的工作負載顯示不同的圖片。

在某些情況下，您的應用程式可能產生許多各種不適合用作自動參數化候選項目的查詢。 在此情況下，您會在系統中看到大量查詢，但唯一查詢與唯一 query_hash 之間的比率很可能接近 1。

在此情況下，您可能想要啟用針對臨機操作工作負載最佳化伺服器選項，以避免將快取記憶體浪費在可能不會再次執行的查詢上。 若要避免在查詢存放區中擷取這些查詢，請將 QUERY_CAPTURE_MODE 設為 AUTO。

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO 
  
ALTER DATABASE [QueryStoreTest] SET QUERY_STORE CLEAR;  
ALTER DATABASE [QueryStoreTest] SET QUERY_STORE = ON   
    (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO);  

下一步

• 使用查詢存放區監視效能
• 使用查詢存放區的最佳作法
• 使用查詢調整小幫手來升級資料庫