共用方式為

  • 發行項

資料分割資料表和索引上的查詢處理增強功能

SQL Server 2008 針對許多平行計畫提升了資料分割資料表上的查詢處理效能、變更了平行計畫和序列計畫的表示方式，以及增強了編譯時間和執行階段執行計畫內所提供的資料分割資訊。本主題將描述這些改進的功能、提供如何解譯資料分割資料表和索引之查詢執行計畫的指引，以及提供用來改善資料分割物件上之查詢效能的最佳作法。

[!附註]

只有 SQL Server Enterprise、Developer 和 Evaluation 版本才支援資料分割資料表和索引。

新資料分割感知的搜尋作業

在 SQL Server 2008 資料分割資料表的內部表示法已有了變更，好讓呈現給查詢處理器的資料表會是多重資料行的索引，其中以 PartitionID 為第一列資料行。PartitionID 是一個隱藏的計算資料行，會在內部使用它來表示包含特定資料列之資料分割的識別碼。例如，假設定義為 T(a, b, c) 的資料表 T 在資料行 a 上分割，而且在資料行 b 上有叢集索引。在 SQL Server 2008 中，這個資料分割資料表會在內部視為非資料分割資料表，而且具有結構描述 T(PartitionID, a, b, c) 及複合索引鍵 (PartitionID, b) 上的叢集索引。如此可讓查詢最佳化工具根據任何資料分割資料表或索引上的 PartitionID 來執行搜尋作業。

現在完成了此搜尋作業中的資料分割刪除。

此外，查詢最佳化工具已經過擴充，能讓具有一個條件的搜尋或掃描作業在 PartitionID (當做邏輯前端資料行) 上完成，而其他索引鍵資料行及第二層搜尋 (具有另一個條件) 也可能會在一或多個其他資料行上完成 (針對符合第一層搜尋作業資格的每一個相異值)。也就是說，這個作業 (稱為「略過掃描」 (Skip Scan)) 可讓查詢最佳化工具根據一個條件來執行搜尋或掃描作業，以判斷要存取的資料分割及該運算子內的第二層索引搜尋作業，以便從符合另一個條件的這些資料分割中傳回資料列。例如，假設有以下的查詢。

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 條件的資料列。

下圖為略過掃描作業的邏輯表示法。此圖顯示資料表 T，其中的資料行 a 和 b 中有資料。資料分割以 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 屬性，例如搜尋、掃描、插入、更新、合併或刪除。

  • 新的 SeekPredicateNew 元素，其中會將包含 PartitionID 的 SeekKeys 子元素當做前端索引鍵資料行以及在 PartitionID 上指定範圍搜尋的篩選條件。兩個 SeekKeys 子元素的存在表示會使用 PartitionID 上的略過掃描作業。

  • 提供所存取之資料分割總計數的摘要資訊。只有在執行階段計畫中才能使用這項資訊。

為了示範如何在圖形化執行計畫輸出和 XML 執行程序表輸出中顯示這項資訊，假設資料分割資料表 fact_sales 上有以下的查詢。此查詢會更新兩個資料分割中的資料。

UPDATE fact_sales

SET quantity = quantity * 2

WHERE date_id BETWEEN 20080802 AND 20080902;

下圖會在此查詢的編譯時間執行計畫內顯示叢集索引搜尋運算子的屬性。若要檢視 fact_sales 資料表和資料分割的定義，請參閱本主題的「範例」。

Showplan 輸出中的資料分割資訊。

Partitioned 屬性

在資料分割資料表或索引上執行類似索引搜尋的運算子時，Partitioned 屬性會出現在編譯時間和執行階段的計畫內，而且會設定為 True (1)。當這個屬性設定為 False (0) 時，就不會顯示。

Partitioned 屬性可出現在下列實體和邏輯運算子內：

  • 資料表掃描

  • 索引掃描

  • 索引搜尋

  • 插入

  • 更新

  • 刪除

  • 合併

如同上圖所示，這個屬性 (Attribute) 會顯示在其定義所在之運算子的屬性 (Property) 內。在 XML 執行程序表輸出中，這個屬性會以 Partitioned="1" 的形式出現在其定義所在之運算子的 RelOp 節點內。

新的搜尋述詞

在 XML 執行程序表輸出中，SeekPredicateNew 元素會出現在其定義所在的運算子內。它最多可包含兩個 SeekKeys 子元素，第一個 SeekKeys 項目會在邏輯索引的資料分割識別碼層級上指定第一層搜尋作業。也就是說，這個搜尋會判斷為了滿足查詢條件所必須存取的資料分割。第二個 SeekKeys 項目會指定發生於第一層搜尋中識別之每一個資料分割內之略過掃描作業的第二層搜尋部分。

資料分割摘要資訊

在執行階段執行計畫中，資料分割摘要資訊提供了所存取之資料分割以及所存取之實際資料分割識別的計數。您可以使用這項資訊來確認已存取查詢中的正確資料分割，而且所有其他資料分割都不在考量之內。

下列是提供的資訊：實際資料分割計數和存取的資料分割。

實際資料分割計數是查詢所存取的資料分割總數。

XML 執行程序表輸出內存取的資料分割為資料分割摘要資訊，該資訊出現在它定義所在之運算子的 RelOp 節點內的新 RuntimePartitionSummary 元素中。下列範例會顯示 RuntimePartitionSummary 元素的內容，指示總共會存取兩個資料分割 (資料分割 2 和 3)。

<RunTimePartitionSummary>

    <PartitionsAccessed PartitionCount="2">

        <PartitionRange Start="2" End="3" />

    </PartitionsAccessed>

</RunTimePartitionSummary>

使用其他執行程序表方法來顯示資料分割資訊

執行程序表方法 SHOWPLAN_ALL、SHOWPLAN_TEXT 和 STATISTICS PROFILE 不會報告本主題所述的資料分割資訊，但是以下情況例外。要存取的資料分割 (屬於 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 中，資料分割堆積視為資料分割識別碼上的邏輯索引。資料分割堆積上的資料分割刪除會在執行計畫中表示為資料表掃描運算子 (在資料分割識別碼上具有 SEEK 述詞)。下列範例會顯示所提供的執行程序表資訊：

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

解譯共現聯結的執行計畫

當使用相同或相當的資料分割函數來分割兩個資料表，而且聯結兩端的資料分割資料行指定於查詢的聯結條件內時，可能會發生聯結共現。查詢最佳化工具可以產生一個計畫，好讓每一個資料表中具有相同資料分割識別碼的資料分割都會個別聯結。共現聯結的速度快於非共現聯結，因為共現聯結所需的記憶體和處理時間比較少。查詢最佳化工具會根據成本估計來選擇非共現計畫或共現計畫。

在共現計畫中，巢狀迴圈聯結會從內部讀取一或多個聯結資料表或索引資料分割。固定掃描運算子內的數字代表資料分割編號。

當針對資料分割資料表或索引產生共現聯結的平行計畫時，平行處理原則運算子會出現在固定掃描與巢狀迴圈聯結運算子之間。在此情況下，聯結外部的多個執行緒每個都會讀取和處理不同的資料分割。

下圖將示範共現聯結的平行查詢計畫。

相同位置聯結執行計畫

資料分割物件的平行查詢執行策略

查詢處理器會將平行執行策略用於從資料分割物件選取的查詢。在執行策略中，查詢處理器會判斷查詢所需的資料表資料分割，以及配置給每一個資料分割的執行緒比例。在大多數情況下，查詢處理器會將相同或幾乎相同的執行緒數目配置給每一個資料分割，然後以平行方式在資料分割之間執行查詢。以下段落將更詳細地說明執行緒配置。

如果執行緒數目小於資料分割數目，查詢處理器會將每一個執行緒指派給不同的資料分割，一開始會讓一或多個資料分割未指派執行緒。當執行緒在資料分割上完成執行時，查詢處理器會將它指派給下一個資料分割，直到每一個資料分割都已指派單一執行緒為止。這是查詢處理器將執行緒重新配置給其他資料分割的唯一情況。

顯示完成後重新指派的執行緒

如果執行緒數目等於資料分割數目，則查詢處理器會將一個執行緒指派給每一個資料分割。當執行緒完成時，不會將它重新配置給另一個資料分割。

顯示每一個資料分割所配置的單一執行緒

如果執行緒數目大於資料分割數目，則查詢處理器會將相同的執行緒數目指派給每一個資料分割。如果執行緒數目不是資料分割數目的倍數，則查詢處理器會將一個額外的執行緒配置給某些資料分割，如此才可使用所有可用的執行緒。請注意，如果只有一個資料分割，則所有執行緒都將指派給該資料分割。在下圖中，有四個資料分割和 14 個執行緒。每一個資料分割都指派 3 個執行緒，其中的兩個資料分割有一個額外的執行緒，所以一共指派了 14 個執行緒。當執行緒完成時，不會將它重新指派給另一個資料分割。

顯示資料分割所配置的多個執行緒

雖然上面的範例建議一個直接的方式來配置執行緒，但是實際的策略會更複雜，而且要考量在查詢執行期間所發生的其他變數。例如，如果資料表已分割，而且在資料行 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 型樹狀結構的掃描最佳化。

  • 當您將資料大量載入資料分割資料表中時，請遵循＜將大量資料載入資料分割資料表中＞白皮書 (英文) 中的最佳作法建議。

範例

下列範例會建立一個測試資料庫，其中包含具有七個資料分割的單一資料表。當執行此範例中的查詢時，請使用之前所述的工具，以檢視編譯時間和執行階段計畫的資料分割資訊。

[!附註]

這個範例會將一百萬個以上的資料列插入資料表中。執行此範例可能需要好幾分鐘的時間 (視您的硬體而定)。在執行此範例之前，請確認有 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