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

Aplica-se a:SQL ServerBase de Dados SQL do AzureInstância Gerida de SQL do AzureSistema de Plataforma de Análise (PDW)

A opção optimize for ad hoc workloads é utilizada para melhorar a eficiência da cache de planos para cargas de trabalho que contêm muitos lotes ad hoc de utilização única. 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.

Pode encontrar esboços de planos compilados consultando a vista de catálogo sys.dm_exec_cached_plans e procurando por "Plano compilado" na coluna cacheobjtype. O stub tem um plan_handle único. O esboço do plano compilado não tem um plano de execução associado, e a consulta do identificador do plano não devolve um plano gráfico nem 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. Utilize esta configuração quando as entradas de cache reutilizadas com frequência não couberem na cache e quando a opção optimize for ad hoc workloads não tiver resolvido o problema com a cache do plano.

Observações

Definir a opção optimize for ad hoc workloads como 1 afeta apenas os novos planos; os planos que já estão na cache de planos não são afetados.

Para afetar imediatamente planos de consulta já em cache, a cache de planos precisa de ser limpa usando ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, ou o SQL Server tem de ser reiniciado.

Recommendations

Evite que exista um grande número de planos de utilização única na cache de planos. 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 utilização única ocupar uma parte significativa da memória do Motor de Base de Dados do SQL Server num servidor OLTP, e se esses planos forem planos ad hoc, utilize esta opção de servidor para reduzir a utilização de memória por estes 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