sql - 操作员使用tempdb溢出数据。 估算的行少于实际的行(Operator used tempdb to spill data . The estimated row are less than real rows)

Question

I have a query that takes around 32 seconds, I tried many ways to fix the performance problem.

(我的查询大约需要32秒，我尝试了多种方法来解决性能问题。)

The first thing I tried is adding a new column that contain date and time as an integer.

(我尝试的第一件事是添加一个包含日期和时间作为整数的新列。)

After I added this column to a non clustered index, the query now is executed in 2 seconds.

(在将该列添加到非聚集索引之后，现在将在2秒内执行查询。)

but when I look at the execution plan, i see that there are some warnings.

(但是当我查看执行计划时，发现有一些警告。)

Here is the screen of execution plan:

(这是执行计划的屏幕：)

Query:

(查询：)

DECLARE @StartDate int=2010010110,--2010-01-01 10:00
        @EndDate int=2019101011 --2019-10-10 11:00

SELECT IdFluide,
       newDate,
       IdUsage,
       SUM(c.[Conso-KW]) AS ConsoKW
FROM fait.Consommation AS c 
INNER JOIN geo.DimGeographie dg ON c.IdGeographie = dg.IdGeographie
INNER JOIN geo.ActivePerimeterDetail apd  ON dg.ContractId = apd.ContractId
                                         AND dg.IdNiveau3=apd.PerimeterId
                                         AND apd.PerimeterLevelId=3
WHERE dg.ModeGeo='PA'
      AND IdUsage <> 0
      AND IdSource = 1
      AND dg.ContractId = 2
      AND apd.ContractId = 2
      AND IdTypeFluide = 1
      AND UserId = 8
      AND newDate >= @StartDate
      AND newDate <= @EndDate
GROUP BY IdFluide,
     newDate,
     IdUsage

The table fait.Consommation contain around 4.5M rows.

(表fait.Consommation包含约450万行。)

The table geo.DimGeographie contain around 20K rows.

(表geo.DimGeographie包含约2万行。)

The table geo.ActivePerimeterDetail contain around 43K rows.

(表geo.ActivePerimeterDetail包含约43K行。)

I have clustered and non clustered index in all tables.

(我在所有表中都有聚簇索引和非聚簇索引。)

I tried to update statistics and I saw many articles talking about such problems, but trying all what they suggest, doesn't solve my problem.

(我试图更新统计信息，并且看到许多文章都在讨论此类问题，但是尝试他们提出的所有建议并不能解决我的问题。)

The estimated row is less than real rows.

(估计的行少于实际的行。)

when i add this code to tables in the query, i have a new execution plan and speed up the query, now the query execute in one second but still have same warning

(当我将此代码添加到查询表中时，我有了新的执行计划并加快了查询速度，现在查询在一秒钟内执行，但仍然有相同的警告)

WITH(FORCESCAN, INDEX(0))

  ask by Omar AMEZOUG translate from so

Answer 1

等待大神答复