SQL Server查询扫描比预期更多的分区

Question

因此，我的团队在我们的存储过程中有以下select语句

SELECT
    ai.Name
    ,dc.Component 
    ,SUM(dc.Value) Value
FROM 
    Warm.DailyCosts dc
    JOIN Warm.AccountInfo ai
        ON dc.AccountInfoId = ai.Id
WHERE
    ai.CorrelationId = '00000000-0000-0000-0000-000000000000'
    AND ((dc.CalendarId >= (20190601 + ai.DayOfMonth - 1)
        AND dc.CalendarId < 20190701)
    OR (dc.CalendarId >= 20270601
        AND dc.CalendarId < (20270601 + ai.DayOfMonth - 1)))
GROUP BY        
    Name
    ,Component

我们在每年的几个月中对DailyCosts表进行了如下划分：

CREATE PARTITION FUNCTION [PF_CostDateByYearMonth](int) AS RANGE RIGHT FOR VALUES (
    20180101,
    20180201,
    20180301,
    20180401,
    20180501,
    20180601,
    20180701,
    20180801,
    20180901,
    20181001,
    20181101,
    20181201,

    ...

    20300101,
    20300201,
    20300301,
    20300401,
    20300501,
    20300601,
    20300701,
    20300801,
    20300901,
    20301001,
    20301101,
    20301201)

我们注意到，当我们运行该过程时，它不是立即找到适当的分区，而是从两端扫描分区。在上面的例子中，它将分区20180101一直到20190701，20301201向下查看到20270601，总共有62个分区。

当我们从where语句(+ ai.DayOfMonth - 1)中删除数学时，读取的分区按预期下降到2、20190601和20270601。请注意，我们使用种子数据和DayOfMonth设置为15在每个帐户。

是什么原因导致服务器在包含此数学时扫描分区，实际上是查看这些分区中的所有索引，还是只是检查它们的范围并继续前进？

任何和所有的来源，你可以提供将在很大程度上帮助我们理解！

Answer 1

推理

当将非分区表的列与已分区表进行比较时，server将无法知道DayOfMonth将包含什么，即使它们都是15。

因此，当连接两个表时，它将不知道返回哪些分区来满足此筛选。

一个提供更多洞察力的不同示例可以找到这里。

测试

我可以重新创建您的问题，进一步的问题，请添加尽可能多的信息。这可能是表定义，索引，分区方案，.

DDL & DML在底部。

在运行查询时，我们可以获得相同的结果：

返回62个分区。

在搜索谓词上，它尝试过滤它能过滤的东西，这是CalendarId上没有+ DayOfMonth-1的两个过滤器。

这意味着

WHERE ...
    AND (( dc.CalendarId < 20190701)
    OR (dc.CalendarId >= 20270601))
...

使用这些筛选器运行查询时，您将看到在访问查询计划中的表时返回的行数相同。

只有在获得这些数据之后，它才能并且将被加入到AccountInfo‘ID's和DayOfMonth - 1上的表中

若要确认您的语句，即当我们删除仅扫描两个分区的列时：

SELECT
    ai.Name
    ,SUM(dc.Component )
    ,SUM(dc.Value) Value
FROM 
    Warm.DailyCosts dc
    JOIN Warm.AccountInfo ai
        ON dc.AccountInfoId = ai.Id
WHERE
    ai.CorrelationId = '00000000-0000-0000-0000-000000000000'
    AND ((dc.CalendarId >= (20190601  - 1)
        AND dc.CalendarId < 20190701)
    OR (dc.CalendarId >= 20270601
        AND dc.CalendarId < (20270601  - 1)))
GROUP BY            Name

访问和使用的只有两种：

溶液

我将假设DayOfMonth只持续到31日。

如果您知道这些边界，就可以对它们进行“硬编码”，以便sql server知道要查找哪些分区。在此之后，可以添加额外的筛选。

例如：

WITH CTE
AS
(
SELECT
    ai.Name
    ,dc.Component 
    ,dc.Value 
    ,dc.CalendarId
    ,ai.DayOfMonth
FROM 
    Warm.DailyCosts dc
    JOIN Warm.AccountInfo ai
        ON dc.AccountInfoId = ai.Id
WHERE
    ai.CorrelationId = '00000000-0000-0000-0000-000000000000'
    AND ((dc.CalendarId >= (20190601 )
        AND dc.CalendarId < 20190701)
    OR (dc.CalendarId >= 20270601
        AND dc.CalendarId < (20270701)))

)

SELECT Name,Component,SUM(Value)
FROM CTE 
WHERE ((CalendarId >= (20190601 + DayOfMonth - 1)
        AND CalendarId < 20190701)
    OR (CalendarId >= 20270601
        AND CalendarId < (20270601 + DayOfMonth - 1)))

GROUP BY        
    Name
    ,Component

cte的唯一目的是让server知道它也可以在dc.CalendarId >= dc.CalendarId >= (20190601 ) OR dc.CalendarId < (20270701))上进行过滤。

Sidenote:添加约束并不能强制执行该约束。

--这个查询给出了我们想要的结果，并使用正确的分区删除：

只访问分区19和21。

如果您愿意，也可以使用OR / AND逻辑获得相同的结果。重要的是弄清楚界限。

测试数据

CREATE SCHEMA WARM
GO

CREATE TABLE Warm.DailyCosts(ID INT IDENTITY(1,1) NOT NULL,
                            Component int, 
                             Value int, 
                             CalendarId INT, 
                             AccountInfoId int
                             )


CREATE TABLE Warm.AccountInfo(Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
                              Name VARCHAR(25),
                              CorrelationId uniqueidentifier,
                              DayOfMonth int
                              );
USE [master]
GO
ALTER DATABASE [test] ADD FILEGROUP [Partitionfg]
GO
USE [test]
GO
CREATE PARTITION FUNCTION [PF_CostDateByYearMonth](int) AS RANGE RIGHT FOR VALUES (
    20180101,
    20180201,
    20180301,
    20180401,
    20180501,
    20180601,
    20180701,
    20180801,
    20180901,
    20181001,
    20181101,
    20181201,
    20190101,
    20190201,
    20190301,
    20190401,
    20190501,
    20190601,
    20190701,
    20270601,
    20270701,
    20270801,
    20270901,
    20271001,
    20271101,
    20271201,
    20280101,
    20280201,
    20280301,
    20280401,
    20280501,
    20280601,
    20280701,
    20280801,
    20280901,
    20281001,
    20281101,
    20281201,
    20290101,
    20290201,
    20290301,
    20290401,
    20290501,
    20290601,
    20290701,
    20290801,
    20290901,
    20291001,
    20291101,
    20291201,
    20300101,
    20300201,
    20300301,
    20300401,
    20300501,
    20300601,
    20300701,
    20300801,
    20300901,
    20301001,
    20301101,
    20301201)


CREATE PARTITION SCHEME [PS_CostDateByYearMonth]  
AS PARTITION [PF_CostDateByYearMonth]  
ALL TO ( [Partitionfg] );  

CREATE UNIQUE CLUSTERED INDEX ptci ON  Warm.DailyCosts(CalendarId,Id) ON [PS_CostDateByYearMonth](CalendarId) ;
USE [master]
GO
ALTER DATABASE [test] ADD FILE ( NAME = N'TestPartition', FILENAME = N'D:\DATA\TestPartition.ndf' , SIZE = 3072KB , FILEGROWTH = 65536KB ) TO FILEGROUP [Partitionfg]
GO
USE [test]
GO

INSERT INTO  Warm.DailyCosts(Component, 
                             Value, 
                             CalendarId, 
                             AccountInfoId 
                             )
SELECT TOP(1000000) --1M
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        20180101 +  ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 1000 % 1000 ,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2 
-- 2018
INSERT INTO  Warm.DailyCosts(Component, 
                             Value, 
                             CalendarId, 
                             AccountInfoId 
                             )
SELECT TOP(1000000) --1M
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        20190101 +  ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 1000 % 1000 ,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2 
-- 2019
INSERT INTO  Warm.DailyCosts(Component, 
                             Value, 
                             CalendarId, 
                             AccountInfoId 
                             )

SELECT TOP(1000000) --1M
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        20280101 +  ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 1000 % 1000 ,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2 
-- 2028

INSERT INTO Warm.AccountInfo(
                              Name ,
                              CorrelationId,
                              DayOfMonth 
                              )

SELECT TOP(3000000) --3M
        CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS varchar(10)) + 'a',
       '00000000-0000-0000-0000-000000000000',
       15
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2 
CREATE NONCLUSTERED INDEX [IX_Component_Value_DailyCosts] ON Warm.DailyCosts
 (Component,Value) 
ON [PS_CostDateByYearMonth](CalendarId);
GO