針對特定工作負載最佳化伺服器組態選項optimize for ad hoc workloads Server Configuration Option

適用於: 是SQL Server 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

optimize for ad hoc workloads 選項是用來針對包含許多使用一次特定批次的工作負載,改善計畫快取的效率。The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. 如果這個選項設定為 1, Database EngineDatabase Engine 就會在首次編譯批次時,將小型已編譯計畫虛設常式 (而非完整的已編譯計畫) 儲存在計畫快取中。When this option is set to 1, the Database EngineDatabase Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. 這會透過避免計畫快取填滿不重複使用的已編譯計畫,協助減輕記憶體不足的壓力。This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.

已編譯計畫虛設常式可讓 Database EngineDatabase Engine 辨識出這個特定批次先前已經編譯,但是只儲存已編譯計畫虛設常式,如此再次叫用 (編譯或執行) 這個批次時, Database EngineDatabase Engine 就會編譯此批次、從計畫快取中移除已編譯計畫虛設常式,並且將完整的已編譯計畫加入至計畫快取。The compiled plan stub allows the Database EngineDatabase Engine to recognize that this ad hoc batch has been compiled before but has only stored a compiled plan stub, so when this batch is invoked (compiled or executed) again, the Database EngineDatabase Engine compiles the batch, removes the compiled plan stub from the plan cache, and adds the full compiled plan to the plan cache.

已編譯計畫虛設常式是 sys.dm_exec_cached_plans 目錄檢視所顯示的其中一個 cacheobjtype。The compiled plan stub is one of the cacheobjtypes displayed by the sys.dm_exec_cached_plans catalog view. 它具有唯一的 SQL 控制代碼和計畫控制代碼。It has a unique sql handle and plan handle. 已編譯計畫虛設常式沒有相關聯的執行計畫,因此查詢計畫控制代碼將不會傳回 XML 執行程序表。The compiled plan stub does not have an execution plan associated with it and querying for the plan handle will not return an XML Showplan.

追蹤旗標 8032 會將快取限制參數還原為 SQL Server 2005 (9.x)SQL Server 2005 (9.x) RTM 設定,這項設定通常會允許使用更大的快取。Trace flag 8032 reverts the cache limit parameters to the SQL Server 2005 (9.x)SQL Server 2005 (9.x) RTM setting which in general allows caches to be larger. 當經常重複使用的快取項目無法納入快取中,以及 [針對特定工作負載最佳化伺服器組態選項] 無法解決計畫快取的問題時,請使用這項設定。Use this setting when frequently reused cache entries do not fit into the cache and when the optimize for ad hoc workloads Server Configuration Option has failed to resolve the problem with plan cache.

警告

如果大型快取為其他記憶體取用者 (例如緩衝集區) 提供較少的記憶體,追蹤旗標 8032 可能會導致效能降低。Trace flag 8032 can cause poor performance if large caches make less memory available for other memory consumers, such as the buffer pool.

建議Recommendations

請避免在計畫快取中有大量的單次使用計畫。Avoid having a large number of single-use plans in the plan cache. 此問題經常是查詢參數的資料類型未一致定義所致。A common cause of this problem is when the data types of query parameters is not consistently defined. 這特別適用於字串的長度,但可套用至具有長度上限、有效位數或小數位數的任何資料類型。This particularly applies to the length of strings but can apply to any data type that has a maxlength, a precision, or a scale. 例如,如果名為 @Greeting 的參數在某次呼叫時作為 Nvarchar(10) 傳遞,並在下次呼叫時作為 Nvarchar(20) 傳遞,則會為每個參數大小建立個別的計畫。For example, if a parameter named @Greeting is passed as an nvarchar(10) on one call and an nvarchar(20) on the next call, separate plans are created for each parameter size. 如果查詢具有數個參數,而且其在呼叫時未一致定義,則每個查詢可能存在大量的查詢計畫。If a query has several parameters and they are not consistently defined when called, a large number of query plans could exist for each query. 每個曾使用之查詢參數資料類型和長度的組合都可能存在計畫。Plans could exist for each combination of query parameter data types and lengths that have been used.

如果一次性計畫的數目佔用 OLTP 伺服器中絕大部分的 SQL Server Database EngineSQL Server Database Engine 記憶體,且這些計畫為特定計畫,請使用此伺服器選項來降低這些物件的記憶體使用量。If the number of single-use plans take a significant portion of SQL Server Database EngineSQL Server Database Engine memory in an OLTP server, and these plans are Ad-hoc plans, use this server option to decrease memory usage with these objects. 若要找到一次性快取計畫的數目,請執行下列查詢:To find the number of single-use cached plans, run the following query:

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

重要

optimize for ad hoc workloads 設定為 1 只會影響新的計畫。已經存在計畫快取中的計畫則不會受到影響。Setting the optimize for ad hoc workloads to 1 affects only new plans; plans that are already in the plan cache are unaffected. 若要立即影響已快取的查詢計畫,需要使用 ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE 清除計畫快取,或必須重新啟動 SQL ServerSQL ServerTo affect already cached query plans immediately, the plan cache needs to be cleared using ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, or SQL ServerSQL Server has to restart.

另請參閱See Also

sys.dm_exec_cached_plans (Transact-SQL) sys.dm_exec_cached_plans (Transact-SQL)
伺服器組態選項 (SQL Server)Server Configuration Options (SQL Server)