Quando os planos de consulta não parametrizados, não triviais e ad hoc são reutilizados

Atualmente, estou investigando um aplicativo que parece gerar 99% de planos de consulta ad hoc em relação ao banco de dados que está consultando. Posso verificar isso executando a seguinte instrução para recuperar um resumo dos objetos no cache do plano de consulta:

_{Desculpe, não foi possível inserir o código no editor SE, daí a captura de tela}

^{Referência: planejar o cache e otimizar para cargas de trabalho ad hoc (SQLSkills.com / K. Tripp) com pequenas modificações}

Os resultados da consulta acima são os seguintes:

CacheType            Total Plans          Total MBs                               Avg Use Count Total MBs - USE Count 1                 Total Plans - USE Count 1
-------------------- -------------------- --------------------------------------- ------------- --------------------------------------- -------------------------
Adhoc                158997               5749.520042                             2             2936.355979                             126087
Prepared             1028                 97.875000                               695           46.187500                               576
Proc                 90                   69.523437                               39659         21.187500                               21
View                 522                  75.921875                               99            0.453125                                3
Rule                 4                    0.093750                                22            0.000000                                0
Trigger              1                    0.070312                                12            0.000000                                0    

Das 158.997 consultas ad hoc no cache do plano, 126.087 consultas foram executadas apenas uma vez.

Em um exame mais aprofundado das consultas ad hoc, descobri que algumas consultas são geradas várias vezes. Examinei o cache do plano com a seguinte consulta para recuperar planos de execução idênticos:

SELECT SUM(cplan.usecounts) AS [Unique Same Single Plans],
       qtext.text
FROM   sys.dm_exec_cached_plans AS cplan
       CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qtext
             CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qplan
       JOIN sys.databases AS sdb
            ON  sdb.database_id = qplan.dbid
WHERE  1 = 1
       AND cplan.objtype = 'Adhoc'   -- <-- only Adhoc plans
       AND sdb.name = 'DATABASENAME' -- <-- for a certain database
       AND cplan.usecounts = 1       -- <-- with a usecounts of 1
GROUP BY
       qtext.text having sum(cplan.usecounts) > 1
ORDER BY
       1 DESC --,cplan.objtype, cplan.usecounts   

^{Referência: Não me lembro. Deixe-me saber se foi originalmente seu e eu vou atribuí-lo.}

Isso me fornece uma lista de consultas ad hoc que têm um plano de consulta idêntico a um plano de consulta idêntico existente e a soma de planos de consulta idênticos exclusivos no cache do plano.

Como você pode ver nos GUIDs editados, há muitos planos de consulta ad hoc exclusivos que foram criados várias vezes.

Para provar que estou indo na direção certa, peguei uma declaração de cima que tinha uma contagem única de 3 e usei a declaração como um filtro na minha declaração de resumo do cache do plano para recuperar as declarações e os planos de consulta:

SELECT cplan.usecounts,
       qtext.text,
       qplan.query_plan
FROM   sys.dm_exec_cached_plans AS cplan
       CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qtext
       CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qplan
       JOIN sys.databases AS sdb
            ON  sdb.database_id = qplan.dbid
WHERE  1 = 1
       AND cplan.objtype = 'Adhoc'
       AND sdb.name = 'DATABASENAME'
       AND qtext.text = 
           'SELECT description,id,name,osguid,profil FROM omitted WHERE osguid IN (SELECT osgroupguid FROM omitted WHERE osuserguid=''81C4B8_REMOVED_SOME_9DD2'')'
ORDER BY
       1 DESC

^{Referência: Não me lembro. Deixe-me saber se foi originalmente seu e eu vou atribuí-lo.}

Isso me fornece uma lista de consultas ad hoc exclusivas que foram criadas e armazenadas no cache do plano:

Agora, os números na captura de tela acima mostram que uma consulta já foi reutilizada novamente, porque tem uma contagem de 3. No entanto, todas as consultas são idênticas.

Agora, pelo que li até agora, suponho que:

• consultas adhoc são consultas que foram passadas para o SQL Server Query Optimizer pela primeira vez em sua vida (possivelmente curta)
• instruções que não possuem parâmetros são consideradas exclusivas e resultarão na Adhoccriação de uma entrada no cache do plano
• consultas adhoc podem ser triviais, o que resulta na criação de planos de consulta individuais para cada instrução, mesmo que sejam idênticos

Estou igualmente ciente de que:

• a ativação optimize for ad hoc workloadsresultará em uma ligeira redução no tamanho dos planos de consulta no cache para planos ad hoc que são usados ​​apenas uma vez
• correr ALTER DATABASE [DATABASENAME] SET PARAMETERIZATION FORCEDpode ser uma boa ideia no meu caso, mas isso...
  • existem restrições (veja os artigos de BrentOzar)
  • parametrização no programa seria melhor

Perguntas

Depois de ler todos os artigos e algumas perguntas relacionadas que surgiram enquanto digitava esta pergunta, tenho as duas perguntas a seguir:

1. Em quais casos os planos de consulta Adhoc não parametrizados e não triviais são reutilizados?
2. Por que existem vários planos de consulta em cache para instruções idênticas?

Percebo que minhas perguntas são contraditórias, devido ao fato de que os planos de consulta não parametrizados são considerados exclusivos, mas por que alguns planos de consulta adhoc não parametrizados estão sendo reutilizados novamente?

Em resposta ao comentário de @DenisRubashikin:

Salve planos para consultas 'idênticas' em formato XML e compare os arquivos, acho que pode haver alguma diferença (em opções de conjunto, por exemplo) – Denis Rubashkin 29 minutos atrás

A opção SET são idênticas. As únicas diferenças em todos os planos estão na segunda linha após a <StatementSetOptions>seção em CompileTimee CompileCPU. Colei as duas partes relevantes abaixo:

QueryPlan1.xml

      <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="false" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
      <QueryPlan CachedPlanSize="32" CompileTime="4" CompileCPU="4" CompileMemory="472">

QueryPlan2.xml

      <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="false" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
      <QueryPlan CachedPlanSize="32" CompileTime="3" CompileCPU="3" CompileMemory="472">

Nenhuma outra diferença encontrada.

Material de referência usado para selecionar esta pergunta:

• Planeje o cache e a otimização para cargas de trabalho ad hoc (SQLSkills.com)
• Guia de arquitetura de processamento de consultas (Microsoft SQL Docs)
• Opções ALTER DATABASE SET (Transact-SQL) (Microsoft SQL Docs)
• Por que vários planos para uma consulta são ruins (BrentOzar.com)
• A parametrização forçada pode dar errado? (BrentOzar. com)
• Resultado do Blitz: Parametrização Forçada (Brentoar.com)