optimize for ad hoc workloads (opção de configuração do servidor)

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzurePDW (Analytics Platform System)

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. Esta opção pode ajudar 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 são reutilizados. No entanto, habilitar esta opção pode afetar sua capacidade de solucionar problemas de planos de uso único.

O stub de plano compilado permite que o Mecanismo de Banco de Dados reconheça que esse lote ad hoc foi compilado antes e armazena apenas um stub de plano compilado. Quando esse lote é invocado (compilado ou executado) novamente, o Mecanismo de Banco de Dados compila o lote, remove o stub do plano compilado do cache do plano e adiciona o plano compilado completo ao cache de planos.

Você pode encontrar stubs de plano compilados consultando a exibição de catálogo sys.dm_exec_cached_plans e procurando por "Plano Compilado" na coluna cacheobjtype. O stub tem uma plan_handle exclusiva. 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 retorna um plano de execução gráfico ou XML.

O sinalizador de rastreamento 8032 reverte os parâmetros de limite de cache para a configuração do SQL Server 2005 (9.x) RTM, 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 a opção Otimizar para cargas de trabalho ad hoc não tiver resolvido o problema com o cache de planos.

Comentários

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.

Recomendações

Evite ter um grande número de planos de uso único no cache de planos. As causas mais comuns incluem:

• Tipos de dados de parâmetros de consulta que não são definidos de forma 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 como um nvarchar(20) na próxima chamada, serão criados planos separados para cada tamanho de parâmetro.

• Consultas que não são parametrizadas. Se uma consulta tiver um ou mais parâmetros para os quais valores com código rígido são enviados ao Mecanismo de Banco de Dados, poderá existir um grande número de planos de consulta para cada consulta. Os planos poderiam existir para cada combinação de tipos e comprimentos de dados de parâmetro de consulta 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.

Se a opção otimizar para cargas de trabalho ad hoc estiver habilitada, você não poderá exibir planos de execução para consultas de uso único, porque apenas o stub do plano será armazenado em cache. Dependendo do ambiente e da carga de trabalho, você pode se beneficiar dos dois recursos a seguir:

• O recurso Repositório de Consultas, introduzido no SQL Server 2016 (13.x), ajuda a localizar rapidamente as diferenças de desempenho causadas por alterações no plano de consulta. O Repositório de Consultas é habilitado por padrão em novos bancos de dados no SQL Server 2022 (16.x) e versões posteriores.

• A parametrização forçada pode melhorar o desempenho de alguns bancos de dados reduzindo a frequência de compilações e recompilações de consulta. Os bancos de dados se beneficiam da parametrização forçada geralmente experimentam grandes volumes de consultas simultâneas de origens tais como aplicativos de ponto-de-venda.

  A parametrização forçada pode causar problemas de desempenho devido à sensibilidade dos parâmetros. Para obter mais informações, consulte Investigar e resolver problemas sensíveis a parâmetros. Para o SQL Server 2022 (16.x) e versões posteriores, você também pode habilitar a otimização do Plano Sensível a Parâmetros.

Exemplos

Para localizar o número de planos de uso único armazenados em cache, execute a seguinte consulta:

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;

Conteúdo relacionado

• sys.dm_exec_cached_plans (Transact-SQL)
• Opções de configuração do servidor (SQL Server)
• Monitorar o desempenho usando o Repositório de Consultas
• Guia de arquitetura de processamento de consultas
• Otimização do plano sensível de parâmetro