实际行和估计行差异很大

正如相关问答中所讨论的，SQL Server 如何知道谓词是相关的？SQL Server 默认假定谓词是完全独立的。

它仅在单个前导列上具有详细的统计信息（直方图），即使在使用多列索引或统计信息的情况下也是如此。那么问题是如何组合来自两个独立谓词的两个统计直方图。

例如，假设您有一个带有 的查询WHERE c1 = x AND c2 = y。根据直方图信息计算出的选择性为c1 = x0.2。c2 = y从单独的直方图中计算出的选择性为0.1。

两个谓词在一起的选择性是什么？0.2? 0.1? 0.2 x 0.1？中间某个地方？

如果没有特定的附加信息，SQL Server 必须做出有根据的猜测。最初的默认设置是假设完全独立。较新的基数估计框架使用指数退避（“介于两者之间”选项）。

您的情况略有不同，因为您对多列索引中的列进行了两次相等测试，该索引带有多列统计信息。这些并不像听起来那么宏伟。我们仍然只得到前列的直方图，但统计对象确实包含多列的平均密度信息。

例如，(a,b,c) 上的索引将提供 (a)、(a,b) 和 (a,b,c) 的密度信息。这个频率信息确实捕捉到了一些关于相关性的信息，但它在每个级别都是一个数字。这意味着在给定相同数量的列的情况下，基于频率的估计将始终产生相同的估计。

SQL Server 确实从多列频率信息中生成选择性估计，但它也从各个列直方图（如果可用）计算选择性。直方图估计假设独立，并且不使用指数退避。

如果它比基于频率的估计具有更高的选择性，则服务器选择基于直方图的估计。在您的示例中似乎就是这种情况。

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

根据问题中的信息，个人选择性是：

• DGItemID = 19299400 中的 1063536
• AnswerSourceID = 19299400 中的 970079

假设独立，因为AND我们将这些选择性相乘，然后乘以全表基数以产生行估计：

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

有许多内部模型变体以不同的方式处理任务。只有少数被公开记录并通过提示或跟踪标志公开。

通常，以下提示似乎会有所帮助：

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'));

文档

• 'ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES'
  使 SQL Server 在估计过滤器的 AND 谓词以考虑完全相关时使用最小选择性生成计划。当与 SQL Server 2012 (11.x) 及更早版本的基数估计模型一起使用时，此提示名称等效于跟踪标志 4137，并且当跟踪标志 9471 与 SQL Server 2014 (12.x) 的基数估计模型一起使用时，具有类似的效果） 或更高。

不幸的是，当基数估计从使用多列统计的基于频率的计算开始时，该提示不适用。

使用原始 CE 模型，您可能会获得更好的结果：

USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')

试试这个索引

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

如果这没有帮助，我会考虑的另一件事是将索引作为单个选择（无连接）进行故障排除并将结果直接放入临时表中，然后稍后再加入。

例如，试试下面的查询，看看你是否能得到正确的估计。如果可以，那应该确认您找到了正确的索引。然后将其他所有内容重新绑定。

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

您可以尝试以下重写：

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);

您也可以尝试：

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

至少，检查你是否有这些索引：

1. IRItemAnswer_Info（DGItemID、AnswerBoolean、IncidentID、AnswerSourceID）
2. Incident_Info（FacilityID、IncidentDate、IncidentSID、IRPhaseID）
3. 阶段 (PhaseID)
4. IncidentType_XRef (IncidentID, IncidentTypeNodeID)
5. IRDGroupItem_Info (DGItemSID)
6. IRAlternative_Info (AltSID) 包括 (AltLabel)
7. IRItemAnswer_Info（DGItemID、AnswerSourceID、IncidentID）

如果没有创建它并测试...