optimize for ad hoc workloads (伺服器組態選項)

  • 發行項

適用於：SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體Analytics Platform System (PDW)

optimize for ad hoc workloads 選項是用來針對包含許多使用一次特定批次的工作負載，改善計畫快取的效率。 如果這個選項設定為 1，資料庫引擎就會在首次編譯批次時，將小型已編譯計劃虛設常式 (而非完整的已編譯計劃) 儲存在計劃快取中。 此選項可避免計劃快取填滿不重複使用的已編譯計劃，以協助減輕記憶體壓力。 不過，啟用此選項可能會影響您針對一次性計劃進行疑難排解的能力。

已編譯的計劃虛設常式可讓資料庫引擎辨識此臨機操作批次先前已編譯，而且只會儲存已編譯的計劃虛設常式。 再次叫用 (已編譯或執行) 此批次時，資料庫引擎會編譯批次、從計畫快取中移除已編譯的計畫虛設常式，並將完整已編譯的計畫新增至計畫快取。

您可以查詢 sys.dm_exec_cached_plans 目錄檢視並在 cacheobjtype 資料行中尋找 "Compiled Plan"，找到已編譯計劃虛設常式。 此虛設常式具有唯一的 plan_handle。 已編譯計劃虛設常式沒有相關聯的執行計劃，因此查詢計劃控制代碼不會傳回圖形化或 XML 執行程序表。

追蹤旗標 8032 會將快取限制參數還原為 SQL Server 2005 (9.x) RTM 設定，這項設定通常會允許使用更大的快取。 當經常重複使用的快取項目無法納入快取中，且 optimize for ad hoc workloads 選項無法解決計劃快取的問題時，請使用這項設定。

警告

如果大型快取為其他記憶體取用者 (例如緩衝集區) 提供較少的記憶體，追蹤旗標 8032 可能會導致效能降低。

備註

將 optimize for ad hoc workloads 選項設定為 1 只會影響新的計劃；已經存在於計劃快取中的計劃則不會受到影響。

若要立即影響已快取的查詢計劃，需要使用 ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE 清除計劃快取，否則 SQL Server 必須重新啟動。

建議

請避免在計畫快取中有大量的單次使用計畫。 常見的原因包括：

  • 未一致定義的查詢參數資料類型。 這特別適用於字串的長度，但可套用至具有長度上限、有效位數或小數位數的任何資料類型。 例如，如果名為 @Greeting 的參數在某次呼叫時作為 nvarchar(10) 傳遞，並在下次呼叫時作為 nvarchar(20) 傳遞，則會為每個參數大小建立個別的計劃。

  • 未參數化的查詢。 如果查詢有一或多個參數，硬式編碼值會提交至資料庫引擎，則每個查詢可能會有大量的查詢計劃。 每個曾使用之查詢參數資料類型和長度的組合都可能存在計畫。

如果一次性計劃的數目佔用 OLTP 伺服器中絕大部分的 SQL Server 資料庫引擎記憶體，且這些計劃為特定計劃，請使用此伺服器選項來降低這些物件的記憶體使用量。

如果已啟用 optimize for ad hoc workloads 選項，您無法檢視單一使用查詢的執行計畫，因為只會快取計畫虛設常式。 視環境和工作負載而定，您可能會受益於下列兩項功能：

  • SQL Server 2016 (13.x) 中引進的查詢存放區功能可協助您快速找出查詢計劃變更所造成的效能差異。 SQL Server 2022 (16.x) 和更新版本的新資料庫預設會啟用查詢存放區。

  • 強制參數化可藉由降低查詢編譯與重新編譯的頻率，來增進特定資料庫的效能。 經由強制參數化獲益的資料庫通常會有來自來源 (如銷售點應用程式) 的大量並行查詢。

    因為參數敏感度，強制參數化可能會導致效能問題。 如需詳細資訊，請參閱調查和解決參數敏感性問題。 針對 SQL Server 2022 (16.x) 和更新版本，您也可以啟用參數敏感性計畫最佳化。

範例

若要找到一次性快取計畫的數目，請執行下列查詢：

SELECT objtype,
    cacheobjtype,
    SUM(refcounts) AS AllRefObjects,
    SUM(CAST(size_in_bytes AS BIGINT)) / 1024 / 1024 AS SizeInMB
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc'
    AND usecounts = 1
GROUP BY objtype, cacheobjtype;

相關內容