关于已分区表和索引的查询处理增强功能（联机手册）-阿里云开发者社区

QL Server 2008 改进了许多并行计划的已分区表的查询处理性能，更改了并行和串行计划的表示方式，增强了编译时和运行时执行计划中所提供的分区信息。本主题将说明这些改进并提供有关如何解释已分区表和索引的查询执行计划的指南，此外还将提供改进已分区对象的查询性能的最佳方法。

注意
只有 SQL Server Enterprise Edition、Developer Edition 和 Evaluation Edition 支持已分区表和已分区索引。

新增的可识别分区的查找操作

在 SQL Server 2008 中，已分区表的内部表示形式已发生变化，即已分区表将作为一个多列索引呈现给查询处理器，其中 PartitionID 是第一列。PartitionID 是一个隐藏的计算列，用于在内部表示包含特定行的分区的 ID。例如，假设一个定义为 T(a, b, c) 的表 T 在 a 列进行了分区，并在 b 列的聚集索引。在 SQL Server 2008 中，此分区表在内部被视为一个具有架构 T(PartitionID, a, b, c) 的未分区表，并具有组合键 (PartitionID, b) 的聚集索引。这样查询优化器便可以基于 PartitionID 对任何已分区表或索引执行查找操作。

现在，分区的排除任务已在此查找操作中完成。

此外，查询优化器的功能也得以扩展，可以针对 PartitionID（作为逻辑首列）以及其他可能的索引键列执行某一条件下的查找或扫描操作，然后，对于符合第一级查找操作的条件的每个不同值，再针对一个或多个其他列执行不同条件下的二级查找。也就是说，这种称为“跳跃扫描”的操作允许查询优化器基于某一条件来执行查找或扫描操作以确定要访问的分区，然后在该运算符内执行一个二级索引查找操作以返回这些分区中符合另一个不同条件的行。例如，请考虑以下查询。

SELECT * FROM T WHERE a < 10 and b = 2;

对于本示例，假设定义为 T(a, b, c) 的表 T 对 a 列进行了分区，并具有 b 的聚集索引。表 T 的分区边界由以下分区函数定义：

CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (3, 7, 10);

为求解该查询，查询处理器将执行第一级查找操作以查找包含符合条件 T.a < 10 的行的每个分区。这将标识要访问的分区。然后，在所标识的每个分区内，处理器将针对 b 列的聚集索引执行一个二级查找以查找符合条件 T.b = 2 和 T.a < 10 的行。

下图所示为跳跃扫描操作的逻辑表示形式，其中显示了在 a 列和 b 列中包含数据的表 T。分区编号为 1 到 4，分区边界由垂直虚线表示。对分区执行的第一级查找操作（图中未显示）已确定分区 1、2 和 3 符合查找条件（由为该表定义的分区和 a 列的谓词指示），即 T.a < 10。曲线指示了跳跃扫描操作的二级查找部分所遍历的路径。实际上，跳跃扫描操作将在这些分区的每个分区中查找符合条件 b = 2 的行。跳跃扫描操作的总开销等于三个单独索引查找之和。

显示查询执行计划中的分区信息

若要检查已分区表和索引的查询执行计划，可以使用 Transact-SQL SET 语句 SET SHOWPLAN_XML 或 SET STATISTICS XML，或者使用 SQL Server Management Studio 中的图形执行计划输出。例如，单击查询编辑器工具栏上的“显示估计的执行计划”可以显示编译时执行计划，单击“包括实际的执行计划”可以显示运行时计划。

使用这些工具，您可以确定以下信息：

访问已分区表或索引的操作，例如扫描、查找、插入、更新、合并和删除。
查询访问的分区。例如，运行时执行计划中包含所访问分区的总计数以及所访问的连续分区的范围。
何时在查找或扫描操作中使用跳跃扫描操作以便从一个或多个分区中检索数据。

有关显示执行计划的详细信息，请参阅执行计划操作指南帮助主题。

增强的分区信息

SQL Server 2008 为编译时执行计划和运行时执行计划都提供了增强的分区信息。现在，执行计划可以提供以下信息：

可选的 Partitioned 属性，它指示对某已分区表执行的某个运算符，例如 seek、scan、insert、update、merge 或 delete。
新增的 SeekPredicateNew 元素，它带有 SeekKeys 子元素，其中包含 PartitionID（作为第一个索引键列）和筛选条件（指定针对PartitionID 的查找范围）。如果存在两个 SeekKeys 子元素，则表明对 PartitionID 使用了跳跃扫描操作。
用于提供所访问分区的总计的摘要信息。只有在运行时计划中才有此信息。

为说明此信息在图形执行计划输出和 XML 显示计划输出中的显示方式，请考虑对已分区表 fact_sales 的以下查询。此查询将更新两个分区中的数据。

UPDATE fact_sales

SET quantity = quantity * 2

WHERE date_id BETWEEN 20080802 AND 20080902;

下图显示了此查询的编译时执行计划中的 Clustered Index Seek 运算符的属性。若要查看 fact_sales 表的定义和分区定义，请参阅本主题中的“示例”部分。

Partition information in the Showplan output.

Partitioned 属性

对已分区表或索引执行某个运算符（例如 Index Seek）时，Partitioned 属性将出现在编译时和运行时计划中并设为 True (1)。设为 False (0) 时将不会显示该属性。

Partitioned 属性可以出现在以下物理和逻辑运算符中：

Table Scan
Index Scan
Index Seek
Insert
Update
Delete
Merge

如上图所示，该属性显示在包含其定义的运算符的属性中。在 XML 显示计划输出中，该属性在包含其定义的运算符的 RelOp 节点中显示为Partitioned="1"。

新增的 Seek 谓词

在 XML 显示计划输出中，SeekPredicateNew 元素出现在包含其定义的运算符中。它最多可以包含两个 SeekKeys 子元素实例。第一个SeekKeys 实例项指定位于逻辑索引的分区 ID 级别的第一级查找操作。也就是说，该查找操作将确定为满足查询条件而必须访问的分区。第二个 SeekKeys 实例项指定在第一级查找中所标识的每个分区中进行的跳跃扫描操作的二级查找部分。

分区摘要信息

在运行时执行计划中，分区摘要信息提供了所访问分区的计数以及所访问的实际分区的标识。您可以使用此信息来验证查询中所访问的分区是否正确以及所有其他分区是否均排除在外。

所提供的信息包括以下内容：“实际分区计数”和“访问的分区”。

“实际分区计数”是查询所访问的分区总数。

在 XML 显示计划输出中，“访问的分区”分区摘要信息显示在新的 RuntimePartitionSummary 元素中，此元素则位于包含该元素定义的运算符的 RelOp 节点下。下面的示例显示了 RuntimePartitionSummary 元素的内容，它表明共访问了两个分区（分区 2 和 3）。

</PartitionsAccessed>

</RunTimePartitionSummary>

使用其他显示计划方法来显示分区信息

显示计划方法 SHOWPLAN_ALL、SHOWPLAN_TEXT 和 STATISTICS PROFILE 并不报告本主题中所述的分区信息，但以下情况例外。作为 SEEK谓词的一部分，要访问的分区由表示该分区 ID 的计算列的范围谓词标识。下面的示例显示了 Clustered Index Seek 运算符的 SEEK 谓词。访问的分区是分区 2 和 3，并且该查找运算符将筛选符合条件 date_id BETWEEN 20080802 AND 20080902 的行。

|--Clustered Index Seek(OBJECT:([db_sales_test].[dbo].[fact_sales].[ci]),

SEEK:([PtnId1000] >= (2) AND [PtnId1000] <= (3)

AND [db_sales_test].[dbo].[fact_sales].[date_id] >= (20080802)

AND [db_sales_test].[dbo].[fact_sales].[date_id] <= (20080902))

ORDERED FORWARD)

解释已分区堆的执行计划

在 SQL Server 2008 中，已分区堆被视为分区 ID 的逻辑索引。已分区堆的分区排除在执行计划中表示为一个 Table Scan 运算符，其中对分区 ID 使用了 SEEK 谓词。下面的示例显示了所提供的显示计划信息：

|-- Table Scan (OBJECT: ([db].[dbo].[T]), SEEK: ([PtnId1001]=[Expr1011]) ORDERED FORWARD)

解释归置联接的执行计划

使用相同或等效的分区函数对两个表进行分区并且在查询的联接条件中指定了来自联接两侧的分区依据列时就会发生联接归置。查询优化器可以生成一个计划，其中具有相等分区 ID 的每个表的分区将分别联接在一起。归置联接可能比非归置联接的执行速度快，因为前者可以只需较少的内存和处理时间。优化器会基于成本估计来选择非归置计划或归置计划。

在归置计划中，Nested Loops 联接从内侧读取一个或多个联接表或索引分区。Constant Scan 运算符内的数字表示分区号。

为已分区表或索引生成归置联接的并行计划时，在 Constant Scan 和 Nested Loops 联接运算符之间会出现一个 Parallelism 运算符。在此情况下，在联接外侧的多个线程会各自在不同的分区上进行读取和操作。

下图显示了一个归置联接的并行查询计划。

已分区对象的并行查询执行策略

查询处理器对从已分区对象选择的查询使用查询执行策略。作为执行策略的一部分，查询处理器会确定查询所需的表分区，以及要分配给每个分区的线程比例。在大多数情况下，查询处理器会为每个分区分配数量相等或几乎相等的线程，然后在这些分区中并行地执行查询。以下几段更详细地介绍了线程分配情况。

如果线程数小于分区数，则查询处理器会将每个线程分配给一个不同的分区，最初会有一个或多个分区没有获得分配的线程。当线程完成在一个分区上的执行时，查询处理器会将它分配给下一个分区，直到每个分区都分配有一个线程。这是查询处理器将线程重新分配给其他分区的唯一情况。

Shows thread reassigned after it finishes

如果线程数与分区数相等，则查询处理器会为每个分区分配一个线程。当线程完成时，不会重新分配给另一个分区。

Shows one thread allocated to each partition

如果线程数大于分区数，则查询处理器会为每个分区分配相等数量的线程。如果线程数并非恰好是分区数的倍数，则查询处理器会为某些分区额外分配一个线程，以使用所有可用线程。请注意，如果只有一个分区，则会将所有线程都分配给该分区。在下图中，有四个分区和 14 个线程。每个分区都分配有 3 个线程，两个分区具有一个额外的线程，总共分配了 14 个线程。当线程完成时，不会重新分配给另一个分区。

Shows multiple threads allocated to the partitions

尽管以上示例指出了一种分配线程的简单方式，但实际策略要复杂一些，并需要考虑在查询执行过程中出现的其他变化因素。例如，如果表已分区，并在 A 列上有一个聚集索引，并且查询有谓词子句 WHERE A IN (13, 17, 25)，则查询处理器将为这三个查找值（A=13、A=17 和A=25)）各分配一个或多个线程，而不是为每个表分区分配一个或多个线程。只需在包含这些值的分区中执行查询，并且如果所有这些查找谓词都恰好在同一个表分区中，则所有线程都将分配给同一个表分区。

为了举出另一个示例，假定表在 A 列上有四个分区（边界点为 (10, 20, 30)），在 B 列上有一个索引，并且查询有一个谓词子句 WHERE B IN (50, 100, 150)。因为表分区是基于值 A，所以值 B 可以出现在任何表分区中。这样，查询处理器将分别在四个表分区中查找三个 B 值 (50, 100, 150) 中的每一个值。查询处理器将按比例分配线程，以便它可以并行执行 12 个查询扫描中的每一个扫描。

基于 A 列的表分区	在每个表分区中查找 B 列
表分区 1：A < 10	B=50, B=100, B=150
表分区 2：A >= 10 AND A < 20	B=50, B=100, B=150
表分区 3：A >= 20 AND A < 30	B=50, B=100, B=150
表分区 4：A >= 30	B=50, B=100, B=150

最佳做法

为提高访问来自大型已分区表和索引的大量数据的查询性能，我们建议采用以下最佳方法：

跨越许多磁盘创建各个条带化分区。
尽可能使用具有足够主内存的服务器以便在内存中保留频繁访问的分区或所有分区，以减少 I/O 开销。
如果内存容纳不下所查询的数据，请压缩表和索引。这会减少 I/O 开销。
使用具有快速处理器的服务器以及尽可能多的处理器核，以充分利用并行查询处理能力。
确保服务器具有足够的 I/O 控制器带宽。
对每个大型已分区表创建聚集索引，以充分利用 B 树扫描优化。
向已分区表进行大容量数据加载时，请遵循白皮书 Loading Bulk Data into a Partitioned Table（将大容量数据加载到已分区表中）中的最佳方法建议。

示例

下面的示例创建一个测试数据库，其中包含一个带有七个分区的表。执行本示例中的查询时请使用前面所述的工具以查看编译时计划和运行时计划的分区信息。

注意
本示例要向表中插入超过 100 万行数据。根据您的硬件情况，运行本示例可能需要几分钟时间。在执行本示例之前，请确保您有超过 1.5 GB 的可用磁盘空间。

USE master;
GO
IF DB_ID (N'db_sales_test') IS NOT NULL
    DROP DATABASE db_sales_test;
GO
CREATE DATABASE db_sales_test;
GO
USE db_sales_test;
GO
CREATE PARTITION FUNCTION [pf_range_fact](int) AS RANGE RIGHT FOR VALUES 
(20080801, 20080901, 20081001, 20081101, 20081201, 20090101);
GO
CREATE PARTITION SCHEME [ps_fact_sales] AS PARTITION [pf_range_fact] 
ALL TO ([PRIMARY]);
GO
CREATE TABLE fact_sales(date_id int, product_id int, store_id int, 
    quantity int, unit_price numeric(7,2), other_data char(1000))
ON ps_fact_sales(date_id);
GO
CREATE CLUSTERED INDEX ci ON fact_sales(date_id);
GO
PRINT 'Loading...';
SET NOCOUNT ON;
DECLARE @i int;
SET @i = 1;
WHILE (@i<1000000)
BEGIN
    INSERT INTO fact_sales VALUES(20080800 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
    SET @i += 1;
END;
GO
DECLARE @i int;
SET @i = 1;
WHILE (@i<10000)
BEGIN
    INSERT INTO fact_sales VALUES(20080900 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
    SET @i += 1;
END;
PRINT 'Done.';
GO
-- Two-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080802 AND 20080902
GROUP BY date_id ;
GO
SET STATISTICS XML OFF;
GO
-- Single-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080801 AND 20080831
GROUP BY date_id;
GO
SET STATISTICS XML OFF;
GO

    本文转自 Fanr_Zh 博客园博客，原文链接http://www.cnblogs.com/Amaranthus/archive/2011/05/12/2044116.html：，如需转载请自行联系原作者