Opção de configuração de servidor optimize for ad hoc workloads

Aplica-se a: simSQL Server (todas as versões compatíveis) SimBanco de Dados SQL do Azure SimInstância Gerenciada do Azure SQL simParallel 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. Quando essa opção está definida como 1, o Mecanismo de Banco de Dados armazena um pequeno stub de plano compilado no cache de planos quando um lote é compilado pela primeira vez, em vez do plano compilado completo. Isso ajuda a aliviar a pressão sobre a memória ao não permitir que o cache de planos fique cheio de planos compilados que não serão reutilizados.

O stub de plano compilado permite que o Mecanismo de Banco de Dados 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 Dados compila o lote, remove o stub de plano compilado do cache de planos e adiciona o plano compilado completo ao cache de planos.

O stub de plano compilado é um dos cacheobjtypes exibidos pela exibição de catálogo sys.dm_exec_cached_plans. Ele tem identificadores sql e de plano exclusivos. 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 plano de execução XML.

O sinalizador de rastreamento 8032 reverte os parâmetros de limite de cache para a configuração do RTM SQL Server 2005 (9.x), que, em geral, permite que os caches sejam maiores. Use esta configuração quando entradas de cache reutilizadas com frequência não se ajustarem no cache e quando o optimize for ad hoc workloads Server Configuration Option não tiver resolvido o problema com o cache de planos.

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.

Recomendações

Evite ter um grande número de planos de uso único no cache de planos. Uma causa comum desse problema é quando os tipos de dados de parâmetros de consulta não estão definidos de maneira consistente. 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. 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. Se uma consulta tiver vários parâmetros e eles não estiverem definidos de maneira consistente quando chamados, poderá existir um grande número de planos de consulta para cada consulta. Os planos poderiam existir para cada combinação de tipos de dados de parâmetro de consulta e comprimentos que foram usados.

Se o número de planos de uso único usa uma parte significativa da memória do Mecanismo de Banco de Dados do SQL Server 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. Para localizar o número de planos de uso único armazenados em cache, execute a seguinte consulta:

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. 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 Server precisa reiniciar.

Consulte Também