ottimizzare per l'opzione di configurazione del server dei carichi di lavoro a hocoptimize for ad hoc workloads Server Configuration Option

QUESTO ARGOMENTO SI APPLICA A: SìSQL ServernonDatabase SQL di AzurenonAzure SQL Data Warehouse non Parallel Data WarehouseTHIS TOPIC APPLIES TO: yesSQL ServernoAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

L'opzione optimize for ad hoc workloads consente di migliorare l'efficienza della cache dei piani per carichi di lavoro che contengono molti batch ad hoc a uso singolo.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. Quando questa opzione viene impostata su 1, alla prima compilazione di un batch il Motore di databaseDatabase Engine archivia un piccolo stub del piano compilato nella cache dei piani, anziché il piano compilato completo.When this option is set to 1, the Motore di databaseDatabase 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. In questo modo si riducono le richieste di memoria evitando che la cache dei piani si riempia con piani compilati che non vengono riutilizzati.This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.

Poiché grazie allo stub del piano compilato il Motore di databaseDatabase Engine riconosce che il batch ad hoc è stato compilato in precedenza e ha archiviato solo uno stub del piano compilato, quando il batch viene nuovamente richiamato (compilato o eseguito), il Motore di databaseDatabase Engine compila il batch, rimuove lo stub del piano compilato dalla cache dei piani e aggiunge il piano compilato completo alla cache dei piani.The compiled plan stub allows the Motore di databaseDatabase 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 Motore di databaseDatabase Engine compiles the batch, removes the compiled plan stub from the plan cache, and adds the full compiled plan to the plan cache.

Lo stub del piano compilato è uno dei cacheobjtype visualizzato nella vista del catalogo sys.dm_exec_cached_plans.The compiled plan stub is one of the cacheobjtypes displayed by the sys.dm_exec_cached_plans catalog view. Dispone di handle SQL e del piano univoci.It has a unique sql handle and plan handle. Lo stub del piano compilato non ha un piano di esecuzione associato e l'esecuzione di query per l'handle del piano non restituisce uno showplan 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.

Il flag di traccia 8032 ripristina i parametri dei limiti di cache all'impostazione RTM di SQL Server 2005SQL Server 2005 che consente, in generale, di aumentare le dimensioni delle cache.Trace flag 8032 reverts the cache limit parameters to the SQL Server 2005SQL Server 2005 RTM setting which in general allows caches to be larger. Usare questa impostazione quando le voci della cache riutilizzate di frequente non rientrano nella cache e quando l'opzione di configurazione server Ottimizza per carichi di lavoro ad hoc non riesce a risolvere il problema della cache dei piani.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.

Avviso

Il flag di traccia 8032 può provocare prestazioni ridotte se cache di grandi dimensioni rendono disponibile meno memoria per altri consumer di memoria, ad esempio il pool di buffer.Trace flag 8032 can cause poor performance if large caches make less memory available for other memory consumers, such as the buffer pool.

IndicazioniRecommendations

Evitare la presenza di un numero elevato di piani a uso singolo nella cache dei piani.Avoid having a large number of single-use plans in the plan cache. Una causa comune di questo problema è la definizione non coerente dei tipi di dati o dei parametri di query.A common cause of this problem is when the data types of query parameters is not consistently defined. Questo è vero specificamente per la lunghezza delle stringhe ma è applicabile a qualsiasi tipo di dati che dispone di una lunghezza massima, una precisione o una scala.This particularly applies to the length of strings but can apply to any data type that has a maxlength, a precision, or a scale. Se ad esempio un parametro con nome @Greeting viene passato come nvarchar(10) in una chiamata e come nvarchar(20) nella chiamata seguente, vengono creati piani separati per ogni dimensione del parametro.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. Se una query include diversi parametri e questi non sono definiti in modo coerente quando vengono chiamati, è possibile che per ogni query esista un numero elevato di piani di query.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. È possibile che esistano piani per ogni combinazione di tipi di dati dei parametri di query e lunghezze.Plans could exist for each combination of query parameter data types and lengths that have been used.

Se il numero di piani a utilizzo singolo occupa una parte significativa della memoria di Motore di database di SQL ServerSQL Server Database Engine in un server OLTP e questi piani sono piani ad-hoc, usare questa opzione server per ridurre l'utilizzo della memoria con questi oggetti.If the number of single-use plans take a significant portion of Motore di database di SQL ServerSQL 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. Per trovare il numero di piani a uso singolo memorizzati nella cache, eseguire la query seguente: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;

Importante

L'impostazione dell'opzione optimize for ad hoc workloads su 1 influisce solo sui nuovi piani, mentre non si applica ai piani già presenti nella cache dei piani.Setting the optimize for ad hoc workloads to 1 affects only new plans; plans that are already in the plan cache are unaffected. Per influire immediatamente sui piani di query già memorizzati nella cache, è necessario cancellare il contenuto della cache dei piani usando ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE oppure riavviare SQL ServerSQL Server.To 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.

Vedere ancheSee Also

sys.dm_exec_cached_plans (Transact-SQL) sys.dm_exec_cached_plans (Transact-SQL)
Opzioni di configurazione del server (SQL Server)Server Configuration Options (SQL Server)