sys.dm_exec_query_stats interação com recompilação

Question

Usamos sys.dm_exec_query_stats para rastrear consultas e consultas lentas que são invasores de IO.

Isso funciona muito bem, temos muitas estatísticas muito perspicazes. É claro que isso não é tão preciso quanto executar um rastreamento do profiler, pois você não tem idéia de quando o SQL Server decidirá lançar um plano de execução.

Temos várias consultas em que o plano de execução incorreto é armazenado em cache. Por exemplo, consultas como as seguintes:

SELECT TOP 30
        a.Id
FROM    Posts a
        JOIN Posts q ON q.Id = a.ParentId
        JOIN PostTags pt ON q.Id = pt.PostId
WHERE   a.PostTypeId = 2
        AND a.DeletionDate IS NULL
        AND a.CommunityOwnedDate IS NULL
        AND a.CreationDate > @date
        AND LEN(a.Body) > 300
        AND pt.Tag = @tag
        AND a.Score > 0
ORDER BY a.Score DESC

O problema é que o plano ideal realmente depende da data selecionada (captura de tela do plano ideal):

Noentanto,seoplanoerradoforarmazenadoemcache,serátotalmenteprejudicialquandooperíodoforgrande:(observeasgrandeslinhasdegordura)

Para superar isso, recomendamos que você use OPTION (OPTIMIZE FOR UNKNOWN) ou OPTION (RECOMPILE)

OPTIMIZE FOR UNKNOWN resulta em um plano um pouco melhor, o que está longe de ser ideal. As execuções são rastreadas em sys.dm_exec_query_stats .

RECOMPILE resulta no melhor plano escolhido, no entanto, nenhuma contagem de execução e as estatísticas são rastreadas em sys.dm_exec_query_stats .

Existe outro DMV que podemos usar para rastrear estatísticas de consultas com OPTION (RECOMPILE) ? Esse comportamento é design? Existe outra maneira de recompilar enquanto mantemos estatísticas controladas em sys.dm_exec_query_stats ?

_{Observação: o framework sempre executará consultas parametrizadas usando sp_executesql}

    

Answer 1

Talvez você deva usar um guia de plano em vez da opção RECOMPILE. Você já tem um bom plano, basta adicioná-lo como um guia de plano para sua consulta e o otimizador produzirá esse plano todas as vezes. Consulte Como otimizar consultas em aplicativos implantados usando os guias de planejamento e Especificando os planos de consulta com o Forçamento de planos .

No seu caso é realmente trivial, basta ligar sp_create_plan_guide_from_handle com o bom plano de consulta manusear:

You can use this stored procedure to ensure the query optimizer always uses a specific query plan for a specified query.