主页 / dba / 问题 / 125308
Accepted
swasheck
Asked: 2016-01-06 13:02:58 +0800 CST

是否可以强制优化器消除此分区视图中不相关的表？

  • 772

我正在为大表测试不同的体系结构，我看到的一个建议是使用分区视图，从而将一个大表分成一系列较小的“分区”表。

1 , 2 , 3 , 4

在测试这种方法时，我发现了一些对我来说意义不大的东西。当我在事实视图上过滤“分区列”时，优化器只在相关表上查找。此外，如果我在维度表上过滤该列，优化器会消除不必要的表。

但是，如果我过滤优化器在每个基表的 PK/CI 上寻找的维度的其他方面。

以下是有问题的查询：

select 
    od.[Year], 
    AvgValue = avg(ObservationValue)
from dbo.v_Observation o 
join dbo.ObservationDates od
    on o.ObservationDateKey = od.DateKey
where o.ObservationDateKey >= 20000101
    and o.ObservationDateKey <= 20051231
group by od.[Year];

select 
    od.[Year], 
    AvgValue = avg(ObservationValue)
from dbo.v_Observation o 
join dbo.ObservationDates od
    on o.ObservationDateKey = od.DateKey
where od.DateKey >= 20000101
    and od.DateKey <= 20051231
group by od.[Year];

select 
    od.[Year], 
    AvgValue = avg(ObservationValue)
from dbo.v_Observation o 
join dbo.ObservationDates od
    on o.ObservationDateKey = od.DateKey
where od.[Year] >= 2000 and od.[Year] < 2006
group by od.[Year];

关键的事实过滤器

按键上的暗淡过滤器

方面的暗淡过滤器

下面是SQL Sentry Plan Explorer 会话的链接。

我正在努力对较大的表进行实际分区，以查看是否可以通过分区消除以类似的方式做出响应。

我确实为在维度的一个方面进行过滤的（简单）查询进行了分区消除。

同时，这里是数据库的仅统计副本：

https://gist.github.com/swasheck/9a22bf8a580995d3b2aa

“旧的”基数估计器得到一个更便宜的计划，但这是因为对每个（不必要的）索引搜索的基数估计较低。

我想知道是否有办法让优化器在按维度的另一个方面进行过滤时使用键列，以便它可以消除对不相关表的搜索。

SQL 服务器版本：

Microsoft SQL Server 2014 - 12.0.2000.8 (X64) 
    Feb 20 2014 20:04:26 
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
sql-server sql-server-2014

2 个回答

  1. Best Answer

    启用跟踪标志 4199。

    我还必须发出：

    UPDATE STATISTICS dbo.ObservationDates 
WITH ROWCOUNT = 73049;

    得到如下所示的计划。上传中缺少此表的统计信息。73,049 这个数字来自计划资源管理器附件中的表基数信息。我使用带有两个逻辑处理器的 SQL Server 2014 SP1 CU4（内部版本 12.0.4436），最大内存设置为 2048 MB，除 4199 外没有跟踪标志。

    然后你应该得到一个具有动态分区消除功能的执行计划：

    select 
    od.[Year], 
    AvgValue = avg(ObservationValue)
from dbo.v_Observation o 
join dbo.ObservationDates od
    on o.ObservationDateKey = od.DateKey
where 
    od.[Year] >= 2000 and od.[Year] < 2006
group by 
    od.[Year]
option (querytraceon 4199);

    计划片段：

    计划片段

    这可能看起来更糟，但过滤器都是启动过滤器。一个示例谓词是：

    过滤器属性

    循环的每次迭代都会测试启动谓词，只有当它返回 true 时，它​​下面的 Clustered Index Seek 才会执行。因此，动态分区消除。

    这可能不如静电消除那么有效，特别是如果计划是并行的。

    您可能需要在视图上尝试或之类MAXDOP 1的提示以获得相同的计划。分区视图（如分区表）的优化器成本选择可能很棘手。FAST 1FORCESEEK

    关键是您需要一个具有启动过滤器功能的计划，以通过分区视图获得动态分区消除。

    带有嵌入式USE PLAN提示的查询：（通过 gist.github.com）：

    • 10

  2. 我的观察一直是，您必须在查询中明确指定分区列的值（或值范围），以便在分区视图中进行“表消除”。这是基于从 SQL Server 2000 到 SQL Server 2014 在生产中使用分区视图的经验。

    SQL Server 没有循环连接运算符的概念，在循环连接运算符中，引擎可以根据循环外侧行的值动态地将查找直接瞄准循环内侧的正确表。然而，正如Paul 的回答所解释的那样，有可能有一个带有启动过滤器的计划，以便在恒定时间内动态地跳过循环内侧的不相关表（而不是通过实际执行查找来进行对数）。

    但是请注意，对于分区表，支持这种类型的查找（到特定分区）。

    如果您固定使用分区视图，另一种选择是将您的查询拆分为多个查询，例如：

    -- Gather than the min/max values for the partition column
DECLARE @minDateKey INT,
        @maxDateKey INT
SELECT @minDateKey = MIN(DateKey),
        @maxDateKey = MAX(DateKey)
FROM dbo.ObservationDates od
WHERE od.[Year] >= 2000 and od.[Year] < 2006

-- Since I have a stats-only copy of the database, simulate having run the query above
-- (You can comment this out since you have the actual data.)
SELECT @minDateKey = 20000101, @maxDateKey = 20051231

-- Adjust the query to use the min/max values of the partition column
-- rather than filtering on a different column in the dimension table
select 
    od.[Year], 
    AvgValue = avg(ObservationValue)
from dbo.v_Observation o 
join dbo.ObservationDates od
    on o.ObservationDateKey = od.DateKey
WHERE od.DateKey >= @minDateKey AND od.DateKey <= @maxDateKey
group by od.[Year]
-- Must use OPTION RECOMPILE; otherwise the plan will touch all tables because it
-- must do so in order to be valid for all values of the parameters!
OPTION (RECOMPILE)

    这产生了以下计划。现在有一个额外的查询命中维度表，但对（可能更大的）事实表的查询进行了优化。

    在此处输入图像描述

    • 6

相关问题