Eu tenho um banco de dados SQL Server 2012. Percebi valor Reason for early termination of statement optimizationpara algumas consultas e todas deram Good Enough Plan Found. Agora minhas perguntas são:
- Quais são todos os tipos possíveis de “Razão para término antecipado da otimização de instrução”. Eu procurei por isso no msdn, mas não obtive uma lista completa de valores.
Existe um DMV ou evento estendido para listar todas as consultas para as quais a otimização foi encerrada devido a outros motivos que não o Plano Bom o Suficiente Encontrado? Referi-me a seguir dois artigos que não listam a lista completa de possibilidades. [Além disso, eles me dão resultados diferentes no meu banco de dados].
Limite de memória excedido
O otimizador foi forçado a parar de procurar alternativas de plano melhores devido à pressão de memória. A razão para isso deve ser investigada e corrigida, então a compilação da consulta deve ser tentada novamente. O plano retornado pode muito bem não ser aquele que o otimizador teria selecionado se a condição de pouca memória não existisse.
Tempo esgotado
Esta razão é muito mal compreendida .
O otimizador de consulta visa encontrar um plano razoável rapidamente . Não realiza uma busca exaustiva para encontrar o melhor plano possível. Por design, evita gastar mais tempo na otimização do que o necessário. Um desses recursos que funciona para garantir isso é o 'tempo limite' (não uma medida de tempo).
O otimizador define para si mesmo um 'orçamento de exploração' com base na complexidade da consulta lógica, estimativas de cardinalidade e no custo estimado do plano mais barato encontrado até o momento (se houver). Consultas mais complexas com maior cardinalidade recebem um orçamento maior.
Se este orçamento for excedido durante uma de suas fases de busca, a fase termina. Isso faz parte do projeto e da operação normal do otimizador. As consultas que precisam de mais esforço do otimizador obtêm isso; aqueles que não, não.
Pense em 'Tempo limite' como 'Plano suficientemente bom encontrado'.
Plano bom o suficiente encontrado
Isso significa exatamente o mesmo que um motivo em branco. É simplesmente uma peculiaridade histórica que os planos com um custo abaixo de 0,909090... (1/1,1) sejam rotulados dessa forma. Nada para antes ou é tratado de outra forma especial ou diferente no código do otimizador quando esse motivo aparece.
Além do Memory Limit Exceeded, nenhum dos 'motivos de encerramento antecipado' significa muito (se é que significa alguma coisa) para ajuste de consulta ou análise de desempenho. Eu geralmente os ignoro.
Adendo
Direcione os esforços de ajuste de consulta com base em métricas de desempenho reais (tempo decorrido, uso de CPU/memória, ... o que for importante no contexto). Se uma consulta for muito lenta para a finalidade pretendida, gaste tempo tornando-a mais rápida. Meça o desempenho real, compare-o com a linha de base e o histórico e direcione o esforço de ajuste para as variações importantes.
Armazene dados limpos garantidos em um esquema relacional adequado, com estatísticas e índices úteis e consultas bem escritas e amigáveis ao otimizador.
Se você acessar http://schemas.microsoft.com/sqlserver/2004/07/showplan/showplanxml.xsd (que é o link que verá se abrir um plano de execução como xml), verá o três razões listadas, que são:
Os artigos que você mencionou parecem ok para encontrar esses eventos, você está tendo um problema específico? A única coisa a ter em mente é que esses DMVs não capturam todos os comandos SQL já executados no servidor e são redefinidos quando o servidor é reiniciado. Você poderia interceptar showplan xml com Extended Events e consultá-lo, mas parece um exagero para mim.
Eu também não me preocuparia muito com GoodEnoughPlanFound, parece que o otimizador está fazendo seu trabalho (de encontrar um bom plano rapidamente) muito bem.
HTH