在 Azure SQL 受控執行個體中微調應用程式和資料庫的效能
適用於：
Azure SQL 受控執行個體
在您識別了由於 Azure SQL 受控執行個體而面臨的效能問題後，這篇文章的設計旨在協助您：
- 微調您的應用程式，並套用一些可以改善效能的最佳做法。
- 變更索引和查詢來微調資料庫，更有效率地使用資料。
本文假設您已檢閱過監控與微調概觀及使用查詢存放區監控性能。 此外，本文假設您沒有與 CPU 資源使用率相關的效能問題，若要解決此問題，可藉由增加計算大小或服務層級以提供更多資源給 SQL 受控執行個體。
註
如需 Azure SQL 資料庫的類似指引，請參閱在 Azure SQL Database 中微調應用程式和資料庫的效能。
微調應用程式
在傳統的內部部署 SQL Server 中，初始容量規劃的程序通常會和在生產環境中執行應用程式的程序分開。 先購買硬體和產品授權，之後再微調效能。 當您使用 Azure SQL 時，最好是將執行和微調應用程式的程序交織在一起。 透過容量隨選的付費模型，您可以調整應用程式以使用目前需要的最少資源，而不是根據對應用程式未來成長計劃的猜測 (這通常是不正確的) 來過度佈建。
某些客戶可能會選擇不微調應用程式，而改為選擇過度佈建硬體資源。 若您不想在忙碌時期變更關鍵應用程式，這種方法可能很適合。 但是，微調應用程式可以將資源需求降到最低，並降低每月的費用。
Azure SQL 受控執行個體應用程式設計的最佳做法和反模式
雖然 Azure SQL 受控執行個體服務層級的設計可以改善應用程式的效能穩定性和可預測性，但某些最佳做法可以協助您微調應用程式，以更充分利用某一計算大小的資源。 雖然許多應用程式只藉由切換至較高的計算大小或服務層級就能有顯著的效能提升，但是某些應用程式需要額外的微調才能從較高的服務等級獲益。
為了提高效能，請考慮為具有下列特性的應用程式進行額外的應用程式微調︰
因為「多對話」行為而使效能變差的應用程式
頻繁通訊的應用程式執行過多的資料存取操作，對網路延遲非常敏感。 您可能需要修改這類應用程式以減少資料庫的資料存取作業數目。 比方說，您可以藉由使用某些技術來改善應用程式效能，例如批次處理特定查詢或將查詢移至預存程序。 如需詳細資訊，請參閱 批次查詢。
無法由整部單一電腦支援之具有大量工作負載的資料庫
資源超過最高 Premium 計算層級的資料庫可能會受益於擴展工作負載。 如需詳細資訊，請參閱跨資料庫分區化和功能資料分割。
具有次優查詢的應用程式
未經適當微調查詢的應用程式可能無法受益於較高的計算資源。 這包括缺少 WHERE 子句、具有遺漏的索引或具有過時統計資料的查詢。 這些應用程式會受益於標準查詢效能微調技術。 如需詳細資訊，請參閱遺漏索引和查詢微調和提示。
具有不理想資料存取設計的應用程式
具有內在資料存取並行問題的應用程式，例如死結，可能無法受益於較高的計算大小。 請考慮藉由使用 Azure 快取服務或其他快取技術來快取用戶端的資料，以減少對資料庫的往返作業。 請參閱 應用程式層快取。
微調資料庫
在本節中，我們會討論一些技術，您可以用這些技術來微調資料庫以獲取應用程式的最佳效能，並且盡可能在最小的計算大小中執行。 其中有些技術符合傳統的 SQL Server 微調最佳做法，但是其他技術則是專屬於 Azure SQL 受控執行個體。 在某些情況下，您可以檢查資料庫已取用的資源來尋找要進一步微調的區域，並擴充傳統的 SQL Server 技術以使其適用於 Azure SQL 受控執行個體。
識別並新增遺漏索引
OLTP 資料庫效能中常見的問題與實體資料庫設計相關。 通常，資料庫結構在設計和部署時不會經過大規模測試（無論是負載測試還是資料量測試）。 不幸的是，查詢計劃的效能在小規模時可能可以接受，但在處理生產級別的資料量時可能會大幅降低。 此問題最常見的來源是缺少適當的索引來滿足篩選或查詢中的其他限制。 遺漏的索引通常會在索引搜尋可以滿足時，以資料表掃描的形式呈現。
在此範例中，選取的查詢計劃會在搜尋即可滿足需要時使用掃描︰
DROP TABLE dbo.missingindex;
CREATE TABLE dbo.missingindex (col1 INT IDENTITY PRIMARY KEY, col2 INT);
DECLARE @a int = 0;
SET NOCOUNT ON;
BEGIN TRANSACTION
WHILE @a < 20000
BEGIN
INSERT INTO dbo.missingindex(col2) VALUES (@a);
SET @a += 1;
END
COMMIT TRANSACTION;
GO
SELECT m1.col1
FROM dbo.missingindex m1 INNER JOIN dbo.missingindex m2 ON(m1.col1=m2.col1)
WHERE m1.col2 = 4;
自 2005 年起，SQL Server 內建的 DMV 會查看查詢編譯，其中的索引會大幅減少執行查詢的估計成本。 執行查詢期間，資料庫引擎會追蹤每個查詢計劃的執行頻率，並追蹤執行查詢計劃和其中存在該索引的假設查詢計劃之間的預估落差。 您可以使用這些 DMV 查詢，以快速估計哪些對實體資料庫設計的變更可能會改善資料庫和其實際工作負載的總體成本。
您可以使用下列查詢來評估潛在的遺漏索引︰
SELECT
CONVERT (varchar, getdate(), 126) AS runtime
, mig.index_group_handle
, mid.index_handle
, CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact *
(migs.user_seeks + migs.user_scans)) AS improvement_measure
, 'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' +
CONVERT (varchar, mid.index_handle) + ' ON ' + mid.statement + '
(' + ISNULL (mid.equality_columns,'')
+ CASE WHEN mid.equality_columns IS NOT NULL
AND mid.inequality_columns IS NOT NULL
THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '') + ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
, migs.*
, mid.database_id
, mid.[object_id]
FROM sys.dm_db_missing_index_groups AS mig
INNER JOIN sys.dm_db_missing_index_group_stats AS migs
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
ON mig.index_handle = mid.index_handle
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
在此範例中，查詢導致了這項建議︰
CREATE INDEX missing_index_5006_5005 ON [dbo].[missingindex] ([col2])
在創建之後，同一個 SELECT 陳述式會選擇一個不同的計劃，使用查找而不是掃描，然後更有效率地執行該計劃。
重要的觀念是共用商品系統的 IO 容量會比專用伺服器電腦的容量受到更多限制。 優先考量應最小化不必要的 IO，以便在各服務層級的不同計算資源中充分利用系統。 適當的實體資料庫設計選項可以大幅改善個別查詢的延遲、改善每個縮放單位中可處理的並行要求輸送量，並最小化滿足查詢所需的成本。
如需使用遺漏索引要求調整索引的詳細資訊，請參閱使用遺漏索引建議調整非叢集索引。
查詢微調和提示
Azure SQL 受控執行個體內的查詢最佳化工具類似於傳統的 SQL Server 查詢最佳化工具。 微調查詢和了解查詢最佳化工具之推論模型限制的大多數最佳做法也適用於 Azure SQL 受控執行個體。 如果您微調 Azure SQL 受控執行個體中的查詢，您可以因為減少彙整資源需求而獲得額外好處。 您的應用程式可能比未經調整的等效應用程式以更低的成本運行，因為它能夠在較小的計算資源規模下運行。
SQL Server 中常見並且也適用於 Azure SQL 受控執行個體的範例是查詢最佳化工具如何「探查」參數。 在編譯期間，查詢最佳化工具會評估參數值以判斷該值是否可以產生較佳的查詢計劃。 雖然這個策略產生的查詢計劃通常在速度上明顯優於不知道參數值就編譯的計劃，但是此策略目前在 Azure SQL 受控執行個體中的運作狀況並不完美。 (SQL Server 2022 引入了一項名為參數通知標準計劃最佳化的新智慧型查詢效能功能，該功能解決了參數化查詢的單一快取計劃對於所有可能的傳入參數值並非最佳的情況。目前，參數通知標準計劃最佳化在 Azure SQL 受控執行個體中不可用。)
有時候參數沒有被探查，有時候參數雖然被探查了，但產生的計劃對於工作負載中的所有參數值來說卻不是最佳的。 Microsoft 包括查詢提示 (指令)，因此您可以更明確地指定意圖及覆寫參數探查的預設行為。 當特定客戶工作負載的預設行為不完善時，可以選擇使用提示。
下一個範例會示範查詢處理器如何產生對效能和資源需求都不盡理想的計劃。 此範例也會示範如果您使用查詢提示，則可以降低資料庫的查詢執行時間和資源需求：
DROP TABLE psptest1;
CREATE TABLE psptest1(col1 int primary key identity, col2 int, col3 binary(200));
DECLARE @a int = 0;
SET NOCOUNT ON;
BEGIN TRANSACTION
WHILE @a < 20000
BEGIN
INSERT INTO psptest1(col2) values (1);
INSERT INTO psptest1(col2) values (@a);
SET @a += 1;
END
COMMIT TRANSACTION
CREATE INDEX i1 on psptest1(col2);
GO
CREATE PROCEDURE psp1 (@param1 int)
AS
BEGIN
INSERT INTO t1 SELECT * FROM psptest1
WHERE col2 = @param1
ORDER BY col2;
END
GO
CREATE PROCEDURE psp2 (@param2 int)
AS
BEGIN
INSERT INTO t1 SELECT * FROM psptest1 WHERE col2 = @param2
ORDER BY col2
OPTION (OPTIMIZE FOR (@param2 UNKNOWN))
END
GO
CREATE TABLE t1 (col1 int primary key, col2 int, col3 binary(200));
GO
設定程式碼會建立一個資料表，其中 t1 資料表的資料分布不規則。 最佳的查詢計劃會根據選取的參數而有差異。 不幸的是，計劃快取行為並不一定會根據最常見的參數值重新編譯查詢。 因此，即使在平均而言不同計劃可能是更佳的選擇，仍然有可能將次優的計劃快取並應用於多個值。 然後，查詢計劃會建立兩個完全相同的預存程序，唯一的差異是其中一個有特殊的查詢提示。
-- Prime Procedure Cache with scan plan
EXEC psp1 @param1=1;
TRUNCATE TABLE t1;
-- Iterate multiple times to show the performance difference
DECLARE @i int = 0;
WHILE @i < 1000
BEGIN
EXEC psp1 @param1=2;
TRUNCATE TABLE t1;
SET @i += 1;
END
建議您先等待至少 10 分鐘再開始範例的第 2 部分，這樣一來，所產生之遙測資料的結果才會截然不同。
EXEC psp2 @param2=1;
TRUNCATE TABLE t1;
DECLARE @i int = 0;
WHILE @i < 1000
BEGIN
EXEC psp2 @param2=2;
TRUNCATE TABLE t1;
SET @i += 1;
END
此範例中的每個部分都嘗試執行參數化的 insert 陳述式 1000 次 (以產生足夠的負載來做為測試資料集)。 當它執行預存程序時，查詢處理器會檢查第一次編譯 (參數「探查」) 期間傳遞至程序的參數值。 即使參數值不同，處理器還是會快取產生的計劃並用於稍後的叫用。 最佳的計劃可能不會用於所有情況。 有時候，您需要引導優化器選擇一個比查詢首次編譯時的特定案例更適合平均情況的計劃。 在此範例中，初始計劃會產生「掃描」計劃，此計劃會讀取所有資料列來尋找符合參數的每個值：
因為我們以值 1 執行此程序，所以產生的計劃會是值 1 的最佳選擇，但是對資料表中的其他值而言未必是最佳的。 如果您隨機挑選每個計劃，因為該計劃執行得更慢而且使用更多資源，因此結果可能不是您要的。
如果您以 SET STATISTICS IO 設為 ON 來執行測試，此範例會在幕後完成邏輯掃描工作。 您可以看到該計劃完成了 1,148 次資料讀取（如果平均情況下只需傳回一個資料列，這樣的計劃效率就很低）：
此範例的第二個部分使用查詢提示告訴最佳化工具，在編譯處理程序期間使用特定值。 在此情況下，它會強制查詢處理器忽略傳遞做為參數的值，並改為假設 UNKNOWN。 這表示資料表中具有平均頻率的值 (忽略扭曲)。 產生的計劃是以搜尋為基礎的計劃，平均而言會比此範例第 1 部分的計劃更快速並使用較少資源：
您可以在 sys.server_resource_stats 系統目錄檢視中看到效果。 資料會在 5 到 10 分鐘的間隔內收集、彙總和更新。 每報告 15 秒產生一行資料。 例如：
SELECT TOP 1000 *
FROM sys.server_resource_stats
ORDER BY start_time DESC
您可以檢查 sys.server_resource_stats 來判斷測試使用的資源比另一個測試多或少。 在比較資料時，請將測試的時間隔開，讓它們不在 sys.server_resource_stats 檢視的同一個 5 分鐘時間範圍內。 練習的目標是要最小化使用的資源總量，而不是最小化尖峰資源。 一般而言，最佳化一段延遲的程式碼也可減少資源耗用量。 請確定您對應用程式所做的變更是必要的，而且這些變更不會讓使用應用程式的某人在使用查詢提示時對客戶體驗造成負面影響。
如果工作負載具有一組重複的查詢，擷取並驗證您計劃選項的最適化通常是合理的，因為這會讓主控資料庫所需的資源大小單位降到最低。 在驗證之後，不時地檢查計劃以確保它們沒有劣化。 您可以深入了解 查詢提示 (Transact-SQL)。
Azure SQL 受控執行個體中超大型資料庫結構的最佳做法
下列兩節將討論解決 Azure SQL 受控執行個體中超大型資料庫問題的兩個選項。
跨資料庫分區化
因為 Azure SQL 受控執行個體會在商用硬體上執行，所以個別資料庫的容量限制會比傳統的內部部署 SQL Server 安裝更低。 有些客戶會在資料庫作業不符合 Azure SQL 受控執行個體中的個別資料庫限制時，使用分區化技術在多個資料庫散佈這些作業。 在 Azure SQL 受控執行個體中使用分區化技術的大部分客戶都會在跨多個資料庫的單一維度上分割其資料。 要採取這種方法，您需要了解 OLTP 應用程式通常會執行的交易只會套用至資料庫結構中的單一資料列或一小群資料列。
例如，如果資料庫有客戶名稱、訂單及訂單詳細資料 (如在 AdventureWorks 資料庫中)，則您可以透過以相關訂單和訂單詳細資料等資訊來分組客戶，將這項資料分割至多個資料庫。 您可以保證客戶的資料會保留在個別資料庫內。 應用程式會將不同客戶分配到不同的資料庫，有效地將負載分散到多個資料庫。 透過分區化，客戶不但可以避免資料庫大小上限，Azure SQL 受控執行個體也能處理明顯大於不同計算大小限制的工作負載，前提是每個個別資料庫符合其服務層級限制。
雖然資料庫分區化不會減少方案的彙整資源容量，但可以非常有效地支援分配到多個資料庫的非常大型方案。 每個資料庫可以在不同的計算大小執行以支援具有高資源需求且非常大型的「有效」資料庫。
功能分區
使用者通常會在個別資料庫內結合許多功能。 例如，如果應用程式有管理商店庫存的邏輯，該資料庫可能具有的邏輯會與庫存、追蹤訂單、預存程序和管理月底報告的索引或具體化檢視相關聯。 這項技術可以簡化備份等作業的資料庫管理，但也需要您調整硬體大小來跨應用程式的所有功能處理尖峰負載。
如果您在 Azure SQL 受控執行個體中使用相應放大架構，則最好將應用程式的不同功能分割至不同資料庫。 如果您使用此技術，每個應用程式都可以獨立縮放。 隨著應用程式變得更忙碌 (而且資料庫上的負載增加)，系統管理員可為應用程式內的每個功能選擇獨立的計算大小。 此架構在達到限制時，應用程式會比單一商用電腦可處理的應用程式還大，因為負載會分配給多部電腦。
批次查詢
對於使用大量、頻繁的特定查詢存取資料的應用程式而言，大量的回應時間都花費在應用程式層和和資料庫層之間的網路通訊上。 即使應用程式和資料庫都位於相同的資料中心，兩者之間的網路延遲還是可能因為大量資料存取作業而放大。 若要減少資料存取作業的網路往返，請考慮選擇批次處理特定查詢或將其編譯成預存程序。 如果您批次處理特定查詢，您可以將多個查詢當做一個大型批次，在單一往返中傳送到資料庫。 如果您在預存程序中編譯特定查詢，您可以達到和對其進行批次處理時相同的結果。 使用預存程序還有一個好處，就是能提高在資料庫中快取查詢計劃的機會，這樣您就可以再次使用這個預存程序。
某些應用程式是寫入密集型的。 有時候，您可以藉由考慮如何一併批次處理寫入來減少資料庫上的 IO 總負載。 通常情況下，這就像在預存程序和臨時批次中使用顯式交易，而不是自動提交交易一樣簡單。 如需評估您可以使用的不同技術，請參閱 Azure 中資料庫應用程式的批次處理技術。 實驗您自己的工作負載來尋找正確的批次處理模型。 請務必了解，模型在交易一致性保證上可能稍有不同。 要尋找可最小化資源使用的適當工作負載，就必須找出一致性與效能權衡取捨的正確組合。
應用程式層快取
某些資料庫應用程式具有大量讀取工作負載。 快取層可能會減少資料庫上的負載，並可能透過使用 Azure SQL 受控執行個體來降低支援資料庫所需的運算資源。 使用 Azure Cache for Redis時，如果您具有大量讀取工作負載，您可以讀取資料一次 (或可能每個應用程式層電腦讀取一次，取決於設定方式)，然後將該資料儲存在您的資料庫之外。 此方式可減少資料庫負載 (CPU 和讀取 IO)，但會對交易一致性造成影響，因為從快取讀取的資料可能會與資料庫中的資料不同步。 雖然許多應用程式可接受一定程度的不一致性，但並非所有工作負載都是如此。 您應該充分了解應用程式的任何需求，然後再實作應用程式層快取策略。