Opção de configuração de servidor optimize for ad hoc workloadsoptimize for ad hoc workloads Server Configuration Option

APLICA-SE A: simSQL Server nãoBanco de Dados SQL do Azure nãoAzure Synapse Analytics (SQL DW) nãoParallel Data Warehouse APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

A opção otimizar para cargas de trabalho ad hoc é usada para aperfeiçoar a eficiência do cache de planos para cargas de trabalho que contêm muitos lotes ad hoc de uso exclusivo.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 essa opção está definida como 1, o Mecanismo de Banco de DadosDatabase Engine armazena um pequeno stub de plano compilado no cache de planos quando um lote é compilado pela primeira vez, em vez do plano compilado completo.When this option is set to 1, the Mecanismo de Banco de DadosDatabase 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. Isso ajuda a aliviar a pressão sobre a memória não permitindo que o cache de planos fique cheio de planos compilados que não serão reutilizados.This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.

O stub de plano compilado permite que o Mecanismo de Banco de DadosDatabase Engine reconheça que esse lote ad hoc foi compilado antes, mas somente armazenou um stub de plano compilado, portanto, quando esse lote é invocado (compilado ou executado) novamente, o Mecanismo de Banco de DadosDatabase Engine compila o lote, remove o stub de plano compilado do cache de planos e adiciona o plano compilado completo ao cache de planos.The compiled plan stub allows the Mecanismo de Banco de DadosDatabase 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 Mecanismo de Banco de DadosDatabase Engine compiles the batch, removes the compiled plan stub from the plan cache, and adds the full compiled plan to the plan cache.

O stub de plano compilado é um dos cacheobjtypes exibidos pela exibição de catálogo sys.dm_exec_cached_plans.The compiled plan stub is one of the cacheobjtypes displayed by the sys.dm_exec_cached_plans catalog view. Ele tem identificadores sql e de plano exclusivos.It has a unique sql handle and plan handle. O stub de plano compilado não tem um plano de execução associado a ele e a consulta do identificador do plano não retornará um 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.

O sinalizador de rastreamento 8032 reverte os parâmetros de limite de cache para a configuração do SQL Server 2005 (9.x)SQL Server 2005 (9.x) RTM, que, em geral, permite que os caches sejam maiores.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 esta configuração quando entradas de cache reutilizadas com frequência não se ajustarem no cache e quando a opção de configuração do servidor de otimizar para cargas de trabalho ad hoc não tiver resolvido o problema com o cache do plano.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.

Aviso

O sinalizador de rastreamento 8032 pode causar baixo desempenho se os caches grandes deixarem menos memória disponível para outros consumidores de memória, como o pool de buffers.Trace flag 8032 can cause poor performance if large caches make less memory available for other memory consumers, such as the buffer pool.

RecomendaçõesRecommendations

Evite ter um grande número de planos de uso único no cache de planos.Avoid having a large number of single-use plans in the plan cache. Uma causa comum desse problema é quando os tipos de dados de parâmetros de consulta não estão definidos de maneira consistente.A common cause of this problem is when the data types of query parameters is not consistently defined. Particularmente, isso se aplica ao comprimento de cadeias de caracteres, mas pode se aplicar a qualquer tipo de dados que tenha um maxlength, uma precisão ou uma escala.This particularly applies to the length of strings but can apply to any data type that has a maxlength, a precision, or a scale. Por exemplo, se um parâmetro chamado @Greeting for passado como um nvarchar(10) em uma chamada e um nvarchar(20) na próxima chamada, serão criados planos separados para cada tamanho de parâmetro.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 uma consulta tiver vários parâmetros e eles não estiverem consistentemente definidos quando chamados, poderá existir um grande número de planos de consulta para cada consulta.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. Os planos poderiam existir para cada combinação de tipos de dados de parâmetro de consulta e comprimentos que foram usados.Plans could exist for each combination of query parameter data types and lengths that have been used.

Se o número de planos de uso único usar uma parte significativa da memória do Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine em um servidor OLTP e esses planos são Ad-hoc, use esta opção de servidor para diminuir o uso de memória com esses objetos.If the number of single-use plans take a significant portion of Mecanismo de Banco de Dados do 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. Para localizar o número de planos de uso único armazenados em cache, execute a seguinte consulta: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

Configurar a opção otimizar para cargas de trabalho ad hoc como 1 afeta apenas os planos novos; os planos que já estão no cache de planos não são afetados.Setting the optimize for ad hoc workloads to 1 affects only new plans; plans that are already in the plan cache are unaffected. Para afetar planos de consulta já armazenados em cache imediatamente, o cache do plano precisa ser limpo usando ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE ou então SQL ServerSQL Server precisa reiniciar.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.

Consulte TambémSee Also

sys.dm_exec_cached_plans (Transact-SQL) sys.dm_exec_cached_plans (Transact-SQL)
Opções de configuração do servidor (SQL Server)Server Configuration Options (SQL Server)