使用查詢調整小幫手 (QTA) 調整查詢

  • 5 分鐘

您決定在移轉之前使用 [查詢存放區] 來監視資料庫效能，並將此資料與升級後的效能進行比較。 您計劃使用查詢調整小幫手 (QTA) 來尋找迴歸的查詢，並建議改進功能。 本單元說明使用 [查詢存放區] 和 QTA 維護查詢效能的步驟。

因為您的資料庫應用程式是支撐業務作業的重要系統，因此您需要有升級的行動計劃和應變計劃。 一次升級一個核心生產資料庫。 為已升級資料庫確定一個測試計劃，以確保再生及其他資料庫之前，每個資料庫都已回到生產環境中，且沒有任何問題。

您可以使用 [查詢存放區] 來持續監視查詢的效能，並透過 A/B 測試來測量變更的影響，例如資料庫升級。 QTA 會在升級後提供自動支援，根據查詢存放區中所擷取的資料尋找並修正迴歸查詢。

為了讓 QTA 正常運作，您需要依序套用下列步驟。

重要

請務必使用生產應用程式資料庫，或具有與生產資料庫工作負載密切相符的應用程式工作負載的資料庫，以便 [查詢存放區] 收集查詢的實際計量。

  1. 將資料庫移轉到 SQL Server 2022。
  2. 將相容性層級保持不變，保留在舊版 SQL Server。
  3. 在資料庫上啟用 [查詢存放區]。
  4. 讓 [查詢存放區] 根據足夠的實際使用者活動，收集查詢的基準計量。
  5. 將相容性層級升級為 SQL Server 2022 (160)。
  6. 同樣，讓 [查詢存放區] 根據足夠的實際使用者活動，收集查詢的資料。
  7. 使用 QTA 來比較資料庫相容性層級變更前後的查詢效能。 如果找到迴歸查詢，請識別修正程式。

移轉資料庫

當您準備好要移動到 SQL Server 2022 時，請先將您的資料庫移轉到新的執行個體。 有數種方法能完成這項移轉。 例如，您可以使用簡單的備份與還原、使用資料庫鏡像，或使用大量載入。 最適當的選擇取決於您目前環境的設定，以及您要移轉的來源 SQL Server 版本。 Azure 資料移轉服務 (DMS) 是良好的解決方案，因為它支援從 SQL Server 2005 以後的資料庫。

注意

Azure DMS 也支援將資料庫移轉至 Azure SQL 受控執行個體。 使用 Azure Data Studio 的 Azure SQL 移轉延伸模組 開始進行。

保持相容性層級不變

移轉資料庫之後，保持相容性層級不變。 這個步驟很重要，因為您應該使用目前的資料庫設定來衡量基準。 在您將相容性層級移至 SQL Server 2014 (120) 或更高版本之前，SQL Server 會使用舊版基數估計器。 SQL Server 2014 引進了升級的基數估計器，可讓大多數查詢受益，而不太會對效能造成負面影響。

啟用查詢存放區

雖然資料庫相容性層級會保留在先前版本，但您可以在資料庫上啟用 [查詢存放區]，因為 [查詢存放區] 是伺服器層級功能。 若要啟用 [查詢存放區]:

  1. 在 SQL Server Management Studio (SSMS) 中，以滑鼠右鍵按一下資料庫，然後選取 [屬性]。
  2. 在 [資料庫屬性] 視窗中，選取左窗格中的 [查詢存放區]。
  3. 將 [作業模式 (必要)] 設定為 [唯讀] 或 [讀寫]。
  4. 選取 [確定]。

或者，您可以執行下列陳述式，以預設 READ WRITE 模式啟用 [查詢存放區]:

ALTER DATABASE <database-name> SET QUERY_STORE = ON

讓 [查詢存放區] 收集資料

將您的移轉資料庫放回生產環境，並切換來自應用程式或報表的任何資料庫連結。 資料庫會開始接收來自生產應用程式的查詢。 允許 [查詢存放區] 執行足夠長的時間，以便在資料庫上收集實際的工作負載。

[查詢存放區] 應該擷取一般商務活動週期，包括上班時間、夜間處理、維護視窗和其他活動。 對許多企業來說，一週的活動就足夠，但對於某些企業來說，這個週期可能更短或更長。

許多企業都有主要的商務週期，因此在雙週工資發放或月末處理時會有獨特的活動。 您應該瞭解資料庫所經歷的商務週期時間。 對於雜貨店，每週庫存抵達和補貨週期涵蓋大部分的資料庫活動。

您可以瀏覽 [查詢存放區] 索引標籤來查看收集的資料。 若要查看該索引標籤，請在 SSMS [物件總管] 中，展開資料庫樹狀結構以顯示 [查詢存放區]。 當您認為收集了足夠的資料後，即可為升級進行排程。

升級相容性層級

在對資料庫進行任何變更之前，最好盡可能先在工作時間外備份資料庫。 進行備份之後，請升級相容性層級，如下所示:

  1. 請以滑鼠右鍵按一下 SSMS 中的資料庫 [物件總管]，然後選擇 [屬性]。
  2. 在 [資料庫屬性] 視窗中，選取 [選項] 索引標籤。
  3. 將相容性層級變更為 SQL Server 2022 (160)，然後選取 [確定]。

或者，您可以執行下列陳述式:

ALTER DATABASE <database-name> SET COMPATIBILITY_LEVEL = 160

讓 [查詢存放區] 繼續收集資料

在升級您的資料庫且應用程式繼續之後，[查詢存放區] 會繼續在背景執行，以收集查詢的計量。 由於查詢最佳化工具使用的新基數估計器，這些查詢現會暴露在潛在的問題之中。

繼續執行 [查詢存放區]，並允許它在升級前的期間內收集資料。 不過，查詢迴歸可能會立即顯示，因此您可以採取動作，立即補救任何效能問題。

執行查詢調整小幫手

執行 QTA 以解決任何迴歸查詢。 若要設定 QTA:

  1. 以滑鼠右鍵按一下 SSMS 中的資料庫 [物件總管] ，然後選取 [工作]>[資料庫升級]>[新增資料庫升級工作階段]。
  2. 在 [查詢調整小幫手精靈] 的 [設定] 畫面上，輸入 [要擷取的工作負載期間 (天數)]，以及 [目標資料庫相容性層級]。
  3. 選取 [下一步] 來設定 [設定] 和 [調整] 畫面。
  4. 選取 [完成]。

若要監視 QTA，請以滑鼠右鍵按一下資料庫名稱，選取 [工作]>[資料庫升級]>[監視工作階段]。 QTA 會為您提供前幾名迴歸查詢的摘要報告，並將觀察到的資料與基準資料進行比較。 接著，您可以檢視 QTA 對效能已降低的查詢進行調整所建議的變更。

摘要

在資料庫升級之後，使用 QTA 來尋找並修正因升級而迴歸的查詢。 為了讓 QTA 尋找迴歸查詢，您必須先使用查詢存放區測量舊版相容性層級的查詢，以建立基準。

然後，[查詢存放區] 會收集升級後的計量，讓您搭配 QTA 使用以根據基準比較新效能。 [查詢存放區] 收集升級前後的資料對QTA 的運作至關重要。

當 QTA 識別出迴歸查詢時，其會進行實驗以找出能夠改善效能的最佳動作。 接著您可以套用這些動作。