Configuração do servidor: otimizar para cargas de trabalho ad hoc

Aplica-se a:Servidor SQLBanco de Dados SQL do AzureInstância Gerenciada SQL do AzureSistema de plataforma de análise (PDW)

A optimize for ad hoc workloads opção é usada para melhorar a eficiência do cache de plano para cargas de trabalho que contêm muitos lotes ad hoc de uso único. Quando essa opção é definida como 1, o Mecanismo de Banco de Dados armazena um pequeno stub de plano compilado no cache de plano quando um lote é compilado pela primeira vez, em vez do plano compilado completo. Essa opção pode ajudar a aliviar a pressão da memória, não permitindo que o cache do plano seja preenchido com planos compilados que não são reutilizados. No entanto, habilitar essa 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 anteriormente 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 de plano compilado do cache de plano e adiciona o plano compilado completo ao cache de plano.

Você pode encontrar stubs de planos compilados consultando a visualização do sys.dm_exec_cached_plans catálogo e procurando por "Plano compilado" na cacheobjtype coluna. O esboço tem um único plan_handle. O stub de plano compilado não tem um plano de execução associado a ele, e consultar o identificador de plano não retorna um showplan gráfico ou XML.

O sinalizador de rastreamento 8032 reverte os parâmetros de limite de cache para a configuração RTM do SQL Server 2005 (9.x), que, em geral, permite que os caches sejam maiores. Use essa configuração quando as entradas de cache reutilizadas com freqüência não se encaixarem no cache e quando a optimize for ad hoc workloads opção não conseguir resolver o problema com o cache do plano.

Advertência

O sinalizador de rastreamento 8032 pode causar um desempenho insatisfatório se caches grandes disponibilizarem menos memória para outros consumidores de memória, como o pool de buffers.

Observações

Definir a optimize for ad hoc workloads opção para 1 afetar apenas novos planos, os planos que já estão no cache de planos não são afetados.

Para afetar os planos de consulta já armazenados em cache imediatamente, o cache do plano precisa ser limpo usando ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE ou o SQL Server precisa ser reiniciado.

Recommendations

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

• Tipos de dados de parâmetros de consulta que não são definidos de forma consistente. Isso se aplica particularmente 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 nvarchar(10) em uma chamada e nvarchar(20) na próxima chamada, planos separados serão criados 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 codificados são enviados ao Mecanismo de Banco de Dados, um grande número de planos de consulta pode existir para cada consulta. Poderiam existir planos para cada combinação de tipos de dados de parâmetros de consulta e comprimentos usados.

Se o número de planos de uso único tomar uma parte significativa da memória do Mecanismo de Banco de Dados do SQL Server em um servidor OLTP e esses planos forem planos ad hoc, use essa opção de servidor para diminuir o uso de memória com esses objetos.

Se a optimize for ad hoc workloads opção estiver habilitada, você não poderá exibir planos de execução para consultas de uso único, porque apenas o stub de 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 você a encontrar rapidamente 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 determinados bancos de dados, reduzindo a frequência de compilações e recompilações de consultas. Os bancos de dados que se beneficiam da parametrização forçada geralmente experimentam grandes volumes de consultas simultâneas de fontes, 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.

Examples

Para localizar o número de planos armazenados em cache de uso único, 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 Server
• Monitorar o desempenho usando o Repositório de Consultas
• Guia de arquitetura de processamento de consultas
• Otimização do Plano Sensível a Parâmetros