sql服务器分区性能

Question

在我们的SQL Server数据库中，我们有一个在日期列上分区的表。对于每个日期，我们插入了50万条记录。我们注意到查询性能对于更接近分区开始范围的日期，并且随着时间的推移，它会逐渐降级。

这是我的分区函数

CREATE PARTITION FUNCTION partition_position_monthly (DATE)
AS RANGE RIGHT FOR VALUES ('2019-09-01', '2019-11-01');

表

CREATE TABLE PartitionTest(
ID int IDENTITY(1,1) NOT NULL,
col1 varchar(256) ,
col2 varchar(128) ,
col3 varchar(128) ,
BusinessDate date , -- partition column
) ON partition_scheme_monthly(BusinessDate)

BusinessDate列上存在聚集索引。

以下是使用的查询

select top 1000 * from PartitionTest where BusinessDate = ?

每个业务日期的CPU和IO记录

业务日期=2019-09-01CPU时间=31毫秒扫描计数1，逻辑读取80，物理读取0，预读读取0

业务日期=2019-09-02CPU时间=63毫秒扫描计数1，逻辑读取24905，物理读取0，预读读取3131

业务日期=2019-09-03CPU时间=125毫秒扫描计数1，逻辑读取49727，物理读取0，预读读取7

业务日期=2019-09-04CPU时间=172毫秒扫描计数1，逻辑读取74551，物理读取0，预读读取7

业务日期=2019-09-05CPU时间=234毫秒扫描计数1，逻辑读取99376，物理读取0，预读读取117

正如您所看到的，BusinessDate的CPU时间和逻辑读取逐渐增加其远离分区起始范围。

这是从分区获取数据时的预期行为吗？

我们计划对每月的数据进行分区，接近月底的几天的查询响应时间超出了我们可以接受的限制。有没有一种方法可以实现分区中每天恒定的CPU时间和逻辑读取？

粘贴计划链接

Answer 1

是的，这是意料之中的事。

为了在所有日期获得相同/可预测的性能，您可能需要一个前导列为BusinessDate的索引，或者将分区函数更改为更细粒度(每天而不是每月(

如果没有这一点，它所能做的最好的事情就是为日期找到合适的分区，并扫描所有行，直到找到1000个与日期谓词匹配的行。在您的执行计划中，它按照聚集索引键的顺序读取分区内的行，并且需要读取2,001,000，然后才能在BusinessDate='2019-10-27'上找到与谓词匹配的前一千行。

如果您发现较晚的日期较慢，则它们可能与您的聚集索引键相关(即，按聚集索引键排序较晚的行也往往具有较晚的日期(。