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

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

在 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 中，資料分割堆積視為資料分割識別碼上的邏輯索引。資料分割堆積上的資料分割刪除會在執行計畫中表示為資料表掃描運算子 (在資料分割識別碼上具有 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 個查詢掃描的每一個。

最佳作法

若要讓從大量資料分割資料表和索引中存取大量資料的查詢提升效能，我們建議您採取以下的最佳作法：

• 在多個磁碟之間條狀配置每一個資料分割。

• 盡可能使用具有充足主記憶體的伺服器，將經常存取的資料分割或所有資料分割納入記憶體中，以減少 I/O 成本。

• 如果您查詢的資料不納入記憶體中，請壓縮資料表和索引。如此可減少 I/O 成本。

• 請使用具有快速處理器的伺服器並盡量多使用您可以負擔的處理器核心，以充分利用平行查詢處理功能。

• 確定伺服器擁有足夠的 I/O 控制器頻寬。

• 在每一個大型資料分割資料表上建立叢集索引，以充分利用 B 型樹狀結構的掃描最佳化。

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

範例

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

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

請參閱

參考

概念