As linhas reais e estimadas diferem muito

Conforme discutido nas perguntas e respostas relacionadas, como o SQL Server sabe que os predicados estão correlacionados? O SQL Server assume que os predicados são completamente independentes por padrão.

Ele só tem informações estatísticas detalhadas (histogramas) na única coluna inicial, mesmo quando são usados ​​índices ou estatísticas de várias colunas. A questão então é como combinar dois histogramas estatísticos de dois predicados separados.

Por exemplo, digamos que você tenha uma consulta com WHERE c1 = x AND c2 = y. A seletividade de c1 = xé calculada como 0,2 a partir da informação do histograma. A seletividade de c2 = yé calculada como 0,1 a partir de um histograma separado.

Qual é a seletividade dos dois predicados juntos? 0,2? 0,1? 0,2 x 0,1? Em algum lugar no meio?

Sem informações adicionais específicas, o SQL Server precisa fazer um palpite. O padrão original era assumir total independência. A estrutura de estimativa de cardinalidade mais recente usa recuo exponencial (a opção 'em algum lugar no meio').

Seu caso é um pouco diferente, pois você tem dois testes de igualdade em colunas em um índice de várias colunas, que vem com estatísticas de várias colunas. Estes não são tão grandiosos como podem parecer. Ainda obtemos apenas um histograma na coluna principal, mas o objeto de estatísticas contém informações de densidade média para várias colunas.

Por exemplo, um índice em (a,b,c) forneceria informações de densidade para (a), (a,b) e (a,b,c). Essas informações de frequência capturam algo sobre correlação, mas é um único número em cada nível. Isso significa que uma estimativa baseada em frequência sempre produzirá a mesma estimativa com o mesmo número de colunas.

O SQL Server produz uma estimativa de seletividade a partir das informações de frequência de várias colunas, mas também calcula a seletividade dos histogramas de colunas individuais (quando disponíveis). A estimativa do histograma assume independência e não usa recuo exponencial.

O servidor escolhe a estimativa baseada em histograma se for uma seletividade mais alta do que a estimativa baseada em frequência. Este parece ser o caso no seu exemplo.

Plan for computation:

  CSelCalcPointPredsFreqBased
      Distinct value calculation:
          CDVCPlanLeaf
              1 Multi-Column Stats, 0 Single-Column Stats, 0 Guesses

      Individual selectivity calculations:
          CSelCalcColumnInInterval
              Column: QCOL: [IIAI].DGItemID

          CSelCalcColumnInInterval
              Column: QCOL: [IIAI].AnswerSourceID

Loaded histogram for column QCOL: [IIAI].DGItemID from stats with id 2
Loaded histogram for column QCOL: [IIAI].AnswerSourceID from stats with id 3

Cardinality using multi-column statistics 5.45574e-07 and 
with independence assumption 0.00231336. 
Picking cardinality 0.00231336

Selectivity: 0.00231336

A partir das informações da pergunta, as seletividades individuais são:

• DGItemID = 1063536 de 19299400
• AnswerSourceID = 970079 de 19299400

Assumindo independência, pois ANDmultiplicamos essas seletividades e depois multiplicamos pela cardinalidade da tabela completa para produzir a estimativa de linha:

19299400 * (1063536/19299400 * 970079/19299400) = 53458.3427124.

Há uma série de variações de modelos internos que abordam a tarefa de maneiras diferentes. Apenas alguns são documentados publicamente e expostos por meio de dicas ou sinalizadores de rastreamento.

Normalmente, parece que a seguinte dica pode ser útil:

SELECT
    COUNT_BIG(*)
FROM [VaxxTracker].[dbo].[IRItemAnswer_Info] AS iiai1
WHERE
    iiai1.DGItemID = 1949 
    AND iiai1.AnswerSourceID = 1607
OPTION (USE HINT('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES'));

Documentação

• 'ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES'
  Faz com que o SQL Server gere um plano usando seletividade mínima ao estimar E predicados para filtros para considerar a correlação completa. Esse nome de dica é equivalente ao sinalizador de rastreamento 4137 quando usado com o modelo de estimativa de cardinalidade do SQL Server 2012 (11.x) e versões anteriores e tem efeito semelhante quando o sinalizador de rastreamento 9471 é usado com o modelo de estimativa de cardinalidade do SQL Server 2014 (12.x ) ou mais alto.

Infelizmente, essa dica não se aplica quando a estimativa de cardinalidade começou com um cálculo baseado em frequência, usando estatísticas de várias colunas.

Você pode obter um resultado melhor no seu caso usando o modelo CE original:

USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')

Tente este índice em vez disso

CREATE NONCLUSTERED INDEX IRItemAnswerInfo_DGItemID_AnswerSourceID
ON dbo.IRItemAnswer_Info (DGItemID, AnswerSourceID,[IncidentID])
INCLUDE([AnswerBoolean])

A outra coisa que eu consideraria se isso não ajudar é solucionar problemas do índice como uma única seleção (sem junções) e colocar os resultados diretamente em uma tabela temporária e, em seguida, juntar-se a ela mais tarde.

Por exemplo, tente algo como a consulta abaixo e veja se consegue obter estimativas corretas. Se puder, isso deve confirmar que você encontrou o índice correto. Em seguida, amarre todo o resto de volta.

SELECT *
FROM IRItemAnswer_Inf
WHERE IncidentID = @IncidentId
    AND DGItemID = @Answer1
    AND AnswerSourceID = @Answer1SetID

Você pode tentar, com a seguinte reescrita:

WITH 
Phases (PhaseID) AS
(
SELECT IRPhaseSID
FROM   IncidentPhase_Code
WHERE  IRPhaseSID > 0
  AND  IRPhaseSID <> 1002

UNION ALL

SELECT IRPhaseSID
FROM   IncidentPhase_Code
WHERE  IRPhaseSID = -3
),
CTE (Age, IncidentID) AS
(
SELECT irai.AltLabel, 
       iria.IncidentID
FROM  IRItemAnswer_Info iria WITH(NOLOCK)
      INNER JOIN Incident_Info inci WITH(NOLOCK) ON inci.IncidentSID = iria.IncidentID
      INNER JOIN Phases ON Phases.PhaseID = inci.IRPhaseID
      INNER JOIN IncidentType_XRef intx WITH(NOLOCK) ON intx.IncidentID = inci.IncidentSID
      INNER JOIN dbo.IncidentTypeHierarchy_GetChildNodeIDs(1666) ithc ON ithc.IncidentTypeNodeSID = intx.IncidentTypeNodeID
      INNER JOIN IRDGroupItem_Info idgi WITH(NOLOCK) ON idgi.DGItemSID = iria.DGItemID
      INNER JOIN IRAlternative_Info irai WITH(NOLOCK) ON irai.AltSID = iria.AnswerSourceID
      INNER JOIN IRItemAnswer_Info iiai1 WITH(NOLOCK) ON iiai1.IncidentID = inci.IncidentSID
                                                         AND iiai1.DGItemID = @Answer1
                                                         AND iiai1.AnswerSourceID = @Answer1SetID
WHERE iria.DGItemID = 1879
      AND iria.AnswerBoolean = 1
      AND (inci.IncidentDate >= @DateRangeFrom)
      AND (inci.IncidentDate < DATEADD(d, 1, @DateRangeTo))
      AND (inci.FacilityID = @FacilityID)
               
)
SELECT Age AS Label, 
      COUNT(IncidentID) AS Cnt
FROM CTE
GROUP BY Age
ORDER BY Cnt DESC OPTION(RECOMPILE);

Você também pode tentar com:

SELECT IRPhaseSID
FROM   IncidentPhase_Code
WHERE  IRPhaseSID > 0

UNION ALL

SELECT IRPhaseSID
FROM   IncidentPhase_Code
WHERE  IRPhaseSID = -3

EXCEPT 

SELECT IRPhaseSID
FROM   IncidentPhase_Code
WHERE  IRPhaseSID = 1002

Pelo menos, verifique se você tem esses índices:

1. IRItemAnswer_Info (DGItemID, AnswerBoolean, IncidentID, AnswerSourceID)
2. Incident_Info (FacilityID, IncidentDate, IncidentSID, IRPhaseID)
3. Fases (PhaseID)
4. IncidentType_XRef (IncidentID, IncidentTypeNodeID)
5. IRDGroupItem_Info (DGItemSID)
6. IRAlternative_Info (AltSID) INCLUDE (AltLabel)
7. IRItemAnswer_Info (DGItemID, AnswerSourceID, IncidentID)

Se não criar e testar...