Verbesserte Abfrageverarbeitung bei partitionierten Tabellen und Indizes

SQL Server 2008 bietet für viele parallele Pläne eine bessere Verarbeitung von Abfragen in partitionierten Tabellen, eine geänderte Art der Darstellung paralleler und serieller Pläne und bessere Partitionierungsinformationen in Kompilierzeit- und Laufzeitausführungsplänen. In diesem Thema werden diese Verbesserungen vorgestellt. Außerdem erhalten Sie Hinweise zur Interpretation der Abfrageausführungspläne für partitionierte Tabellen und Indizes sowie zu bewährten Methoden zur Verbesserung der Abfrageleistung bei partitionierten Objekten.

Hinweis
Partitionierte Tabellen und Indizes werden nur in der Enterprise Edition, Developer Edition und Evaluation Edition von SQL Server unterstützt.

Neuer partitionsgerichteter Suchvorgang (SEEK)

In SQL Server 2008 wird die interne Darstellung einer partitionierten Tabelle so geändert, dass der Abfrageprozessor die Tabelle für einen mehrspaltigen Index mit PartitionID als führender Spalte hält. PartitionID ist eine verborgene berechnete Spalte, die intern die ID der Partition, die eine bestimmte Zeile enthält, repräsentiert. Beispiel: Die Tabelle T, die als T(a, b, c) definiert ist, wird in Spalte a partitioniert und enthält in Spalte b einen gruppierten Index. In SQL Server 2008 wird diese partitionierte Tabelle intern als nicht partitionierte Tabelle mit dem Schema T(PartitionID, a, b, c) und einem gruppierten Index im zusammengesetzten Schlüssel (PartitionID, b) behandelt. Auf diese Weise kann der Abfrageoptimierer Suchvorgänge basierend auf PartitionID in allen partitionierten Tabellen und Indizes durchführen.

Die Partitionsentfernung wird jetzt im Suchvorgang vorgenommen.

Außerdem wurde der Abfrageoptimierer so erweitert, dass jetzt zunächst ein Such- oder Scanvorgang mit einer Bedingung für PartitionID (als logischer führender Spalte) und ggf. für weitere Indexschlüsselspalten durchgeführt werden kann. Anschließend wird dann für jeden eindeutigen Wert, der die Kriterien des Suchvorgangs der ersten Ebene erfüllt hat, ein Suchvorgang der zweiten Ebene mit einer anderen Bedingung in einer oder mehreren zusätzlichen Spalten durchgeführt. Dies bedeutet, dass mit diesem Vorgang, der Skip-Scan genannt wird, der Abfrageoptimierer basierend auf einer Bedingung zunächst einen Such- bzw. Scanvorgang durchführen kann, mit dem die Partitionen ermittelt werden, auf die zugegriffen werden muss, und dann innerhalb dieses Operators einen Indexsuchvorgang der zweiten Ebene, durch den Zeilen in diesen Partitionen zurückgegeben werden, die eine andere Bedingung erfüllen. Sehen Sie sich zum Beispiel die folgende Abfrage an:

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

Gehen Sie nun davon aus, dass die Tabelle T, die als T(a, b, c) definiert ist, in Spalte a partitioniert wird und in Spalte b einen gruppierten Index enthält. Die Partitionsgrenzen für Tabelle T werden mit der folgenden Partitionsfunktion definiert:

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

Zur Auflösung der Abfrage führt der Abfrageprozessor zunächst einen Suchvorgang der ersten Ebene durch, in dem alle Partitionen mit Zeilen, die die Bedingung T.a < 10 erfüllen, gesucht werden. Hierdurch werden die Partitionen identifiziert, auf die zugegriffen werden muss. In diesen identifizierten Partitionen führt der Prozessor dann einen Suchvorgang der zweiten Ebene im gruppierten Index der Spalte b durch, um die Zeilen zu suchen, die die Bedingung T.b = 2 und T.a < 10 erfüllen.

Die folgende Abbildung ist eine logische Darstellung des Skip-Scan-Vorgangs. Sie zeigt die Tabelle T mit Daten in den Spalten a und b. Die Partitionen sind mit 1 bis 4 nummeriert, wobei die Partitionsgrenzen durch gestrichelte vertikale Linien angezeigt werden. Durch einen Suchvorgang der ersten Ebene in den Partitionen (nicht abgebildet) wurde ermittelt, dass die Partitionen 1, 2 und 3 die Suchbedingung, die durch die für die Tabelle definierte Partitionierung und das Prädikat für Spalte a vorgegeben wurde, erfüllen. Das heißt, sie erfüllen die Bedingung T.a < 10. Der vom Suchvorgang der zweiten Ebene innerhalb des Skip-Scan-Vorgangs durchlaufene Pfad ist anhand der Kurve zu erkennen. Im Wesentlichen wird beim Skip-Scan-Vorgang in diesen Partitionen nach Zeilen gesucht, die die Bedingung b = 2 erfüllen. Die Gesamtkosten für den Skip-Scan-Vorgang entsprechen den Kosten, die durch drei separate Indexsuchvorgänge entstehen würden.

Anzeigen von Partitionierungsinformationen in Abfrageausführungsplänen

Sie können die Ausführungspläne für Abfragen in partitionierten Tabellen und Indizes überprüfen, indem Sie die Transact-SQL SET-Anweisung SET SHOWPLAN_XML bzw. SET STATISTICS XML ausführen oder den in SQL Server Management Studio ausgegebenen grafischen Ausführungsplan verwenden. So können Sie zum Beispiel den Ausführungsplan für die Kompilierzeit anzeigen, indem Sie in der Symbolleiste Abfrage-Editor auf Geschätzten Ausführungsplan anzeigen klicken, und den Laufzeitplan, indem Sie auf Tatsächlichen Ausführungsplan einschließen klicken.

Mit diesen Tools können Sie die folgenden Informationen abrufen:

• Die Vorgänge (wie Scan- und Suchvorgänge, Einfügungen, Aktualisierungen und Zusammenführungen oder Löschvorgänge), bei denen auf partitionierte Tabellen oder Indizes zugegriffen wird.

• Die Partitionen, auf die durch die Abfrage zugegriffen wird. So finden sich zum Beispiel in Ausführungsplänen für die Laufzeit Informationen zur Gesamtanzahl der Partitionen sowie zu den Bereichen angrenzender Partitionen, auf die zugegriffen wird.

• Wann Skip-Scan in einem Such- bzw. Scanvorgang verwendet wird, um Daten aus einer oder mehreren Partitionen abzurufen.

Weitere Informationen zum Anzeigen von Ausführungsplänen finden Sie unter Vorgehensweisen für den Ausführungsplan.

Bessere Partitionierungsinformationen

SQL Server 2008 stellt verbesserte Partitionierungsinformationen sowohl für Kompilierzeit- als auch für Laufzeitausführungspläne bereit. Die Ausführungspläne enthalten jetzt die folgenden Informationen:

• Ein optionales Partitioned-Attribut, das anzeigt, dass für eine partitionierte Tabelle ein Operator wie Seek, Scan, Insert, Update, Merge oder Delete ausgeführt wird.

• Ein neues SeekPredicateNew-Element mit einem SeekKeys-Unterelement, das PartitionID als führende Indexschlüsselspalte enthält sowie Filterbedingungen, mit denen Bereichssuchen für PartitionID festgelegt werden. Das Vorhandensein von zwei SeekKeys-Unterelementen zeigt an, dass für PartitionID ein Skip-Scan-Vorgang verwendet wird.

• Zusammenfassende Informationen mit der Gesamtanzahl der Partitionen, auf die zugegriffen wird. Diese Informationen sind nur in Laufzeitplänen verfügbar.

Nehmen Sie die folgende Abfrage für die partitionierte Tabelle fact_sales als Beispiel zur Veranschaulichung, wie diese Informationen im grafischen Ausführungsplan und im XML-Showplan angezeigt werden. Durch diese Abfrage werden Daten in zwei Partitionen aktualisiert.

UPDATE fact_sales

SET quantity = quantity * 2

WHERE date_id BETWEEN 20080802 AND 20080902;

Die folgende Abbildung zeigt die Eigenschaften des Clustered Index Seek-Operators im Kompilierzeitausführungsplan für diese Abfrage. Die Definition der fact_sales-Tabelle und die Partitionsdefinition finden Sie in diesem Thema im Abschnitt "Beispiel".

Das Partitioned-Attribut

Wenn ein Operator wie Index Seek für eine partitionierte Tabelle oder einen partitionierten Index ausgeführt wird, enthalten der Kompilierzeit- und der Laufzeitausführungsplan das Attribut Partitioned, das auf True (1) gesetzt wird. Das Attribut wird nicht angezeigt, wenn es auf False (0) gesetzt ist.

Das Partitioned-Attribut kann in den folgenden physischen und logischen Operatoren erscheinen:

• Table Scan

• Index Scan

• Index Seek

• Insert

• Update

• Delete

• Merge

Wie in der obigen Abbildung zu sehen, wird das Attribut in den Eigenschaften des Operators, in dem es definiert ist, angezeigt. Im XML-Showplan erscheint das Attribut als Partitioned="1" im RelOp-Knoten des Operators, in dem es definiert ist.

Neues Suchprädikat (SEEK-Prädikat)

Im XML-Showplan wird das SeekPredicateNew-Element in dem Operator angezeigt, in dem es definiert ist. Das Element kann maximal zwei Instanzen des SeekKeys-Unterelements enthalten. Durch das erste SeekKeys-Element wird der Suchvorgang (SEEK) auf erster Ebene für die Partitions-ID des logischen Index festgelegt. In diesem Suchvorgang werden die Partitionen ermittelt, auf die zugegriffen werden muss, damit die Bedingungen der Abfrage erfüllt werden können. Durch das zweite SeekKeys-Element wird der Suchvorgang auf zweiter Ebene innerhalb des Skip-Scan-Vorgangs festgelegt, der in allen Partitionen durchgeführt wird, die im ersten Suchvorgang identifiziert wurden.

Zusammenfassende Partitionsinformationen

In Laufzeitausführungsplänen geben die zusammenfassenden Partitionsinformationen Auskunft darüber, auf wie viele und auf welche Partitionen zugegriffen wird. Anhand dieser Informationen können Sie überprüfen, ob in der Abfrage auf die richtigen Partitionen zugegriffen wird und ob alle anderen Partitionen vom Zugriff ausgenommen werden.

Die folgenden Informationen werden bereitgestellt: Tatsächliche Partitionsanzahl und Tatsächliche Partitionszugriffe

Tatsächliche Partitionsanzahl ist die Gesamtzahl der Partitionen, auf die durch die Abfrage zugegriffen wird.

Tatsächliche Partitionszugriffe ist im XML-Showplan die Übersichtsinformation zur Partition, die im neuen RuntimePartitionSummary-Element im RelOp-Knoten des Operators, in dem sie definiert ist, erscheint. Das folgende Beispiel zeigt den Inhalt des RuntimePartitionSummary-Elements, durch den angegeben wird, dass auf insgesamt zwei Partitionen (Partition 2 und 3) zugegriffen wird.

<RunTimePartitionSummary>

    <PartitionsAccessed PartitionCount="2" >

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

    </PartitionsAccessed>

</RunTimePartitionSummary>

Anzeigen von Partitionsinformationen mittels anderer Showplan-Methoden

Die Showplan-Methoden SHOWPLAN_ALL, SHOWPLAN_TEXT und STATISTICS PROFILE stellen keine der in diesem Thema beschriebenen Partitionsinformationen bereit, mit der folgenden Ausnahme: Als Teil des SEEK-Prädikats werden die Partitionen, auf die zugegriffen werden muss, durch ein Bereichsprädikat für die berechnete Spalte, die die Partitions-ID repräsentiert, identifiziert. Im folgenden Beispiel sehen Sie das SEEK-Prädikat für einen Clustered Index Seek-Operator. Es wird auf die Partitionen 2 und 3 zugegriffen, und der SEEK-Operator filtert die Zeilen heraus, die die Bedingung date_id BETWEEN 20080802 AND 20080902 erfüllen.

|--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)

Interpretieren von Ausführungsplänen für partitionierte Heaps

In SQL Server 2008 werden partitionierte Heaps als logischer Index für die Partitions-ID behandelt. Die Partitionsentfernung für einen partitionierten Heap wird in einem Ausführungsplan als Table Scan-Operator mit einem SEEK-Prädikat für die Partitions-ID dargestellt. Das folgende Beispiel zeigt die bereitgestellten Showplan-Informationen:

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

Interpretieren von Ausführungsplänen für zusammengefasste Verknüpfungen

Eine Zusammenfassung von Verknüpfungen kann eintreten, wenn zwei Tabellen mit derselben oder einer ähnlichen Partitionsfunktion partitioniert und die Partitionierungsspalten auf beiden Seiten der Verknüpfung in der Join-Bedingung der Abfrage angegeben werden. Der Abfrageoptimierer kann einen Plan erzeugen, in dem die Partitionen aller Tabellen mit identischer Partitions-ID separat verknüpft werden. Zusammengefasste Verknüpfungen sind jedoch möglicherweise schneller als nicht zusammengefasste, da sie ggf. weniger Arbeitsspeicher und weniger Verarbeitungszeit benötigen. Die Entscheidung, ob ein Plan für nicht zusammengefasste oder zusammengefasste Verknüpfungen erzeugt wird, fällt auf Grundlage der geschätzten Kosten.

Bei einem Plan für zusammengefasste Verknüpfungen liest der Nested Loops-Join eine oder mehrere zusammengefasste Tabellen- oder Indexpartitionen auf der Innenseite. Die Zahlen in den Constant Scan-Operatoren repräsentieren die Partitionsnummern.

Wenn parallele Pläne für zusammengefasste Verknüpfungen für partitionierte Tabellen oder Indizes erzeugt werden, wird ein Parallelism-Operator zwischen den Constant Scan-Verknüpfungsoperator und den Nested Loops-Verknüpfungsoperator eingefügt. In diesem Fall lesen und bearbeiten mehrere Threads auf der Außenseite der Verknüpfung jeweils eine andere Partition.

Die folgende Abbildung zeigt einen parallelen Abfrageplan für eine zusammengefasste Verknüpfung.

Parallele Ausführungsstrategie für Abfragen bei partitionierten Objekten

Der Abfrageprozessor verwendet eine parallele Ausführungsstrategie für Abfragen bei partitionierten Objekten. Im Rahmen dieser Ausführungsstrategie ermittelt der Abfrageprozessor die für die Abfrage erforderlichen Tabellenpartitionen und die den einzelnen Partitionen zugewiesenen Threadanteile. In den meisten Fällen ordnet der Abfrageprozessor den einzelnen Partitionen eine etwa gleich große Anzahl an Threads zu und führt anschließend die Abfrage partitionsübergreifend parallel aus. In den folgenden Absätzen wird die Threadzuordnung näher erläutert.

Wenn die Threadanzahl kleiner ist als die Partitionsanzahl, ordnet der Abfrageprozessor jeden Thread einer anderen Partition zu, und zunächst verbleiben eine oder mehrere Partitionen ohne Threadzuordnung. Wenn die Ausführung eines Threads für eine Partition abgeschlossen ist, weist der Abfrageprozessor diesen der nächsten Partition zu, bis jeder Partition ein Thread zugewiesen wurde. Dies ist der einzige Fall, in dem der Abfrageprozessor Threads anderen Partitionen neu zuordnet.

Wenn die Anzahl an Threads und an Partitionen gleich ist, wird jeder Partition ein Thread zugewiesen. Abgeschlossene Threads werden nicht erneut zugeordnet.

Wenn die Threadanzahl größer ist als die Partitionsanzahl, wird jeder Partition dieselbe Anzahl an Threads zugewiesen. Falls es sich bei der Anzahl an Threads nicht um ein Vielfaches der Anzahl an Partitionen handelt, weist der Abfrageprozessor einigen Partitionen einen weiteren Thread zu, sodass alle verfügbaren Threads verwendet werden. Wenn nur eine Partition vorhanden ist, werden alle Threads dieser Partition zugewiesen. In der Abbildung unten sind vier Partitionen und 14 Threads verfügbar. Jeder Partition werden drei Threads zugewiesen, und zwei Partitionen wird jeweils ein zusätzlicher Thread zugewiesen, sodass alle 14 Threads zugewiesen sind. Abgeschlossene Threads werden nicht erneut zugeordnet.

Die oben aufgeführten Beispiele sind einfache Beschreibungen der Threadzuordnung. Die tatsächliche Strategie ist komplexer und umfasst weitere Variablen, die sich während der Abfrageausführung ergeben. Beispiel: Wenn die Tabelle partitioniert ist, in Spalte A einen gruppierten Index aufweist und eine Abfrage mit der Prädikatklausel WHERE A IN (13, 17, 25), verwendet wird, weist der Abfrageprozessor jedem dieser drei Suchwerte (A=13, A=17 und A=25) statt jeder Tabellenpartition einen oder mehrere Threads zu. Die Abfrage muss nur für die Partitionen ausgeführt werden, die diese Werte enthalten. Wenn sich alle Suchwerte in derselben Partition befinden, werden alle Threads dieser Partition zugewiesen.

Ein weiteres Beispiel: Die Tabelle weist vier Partitionen in Spalte A mit Grenzpunkten (10, 20, 30) sowie einen Index in Spalte B auf, und für die Abfrage wird folgende Prädikatklausel verwendet: WHERE B IN (50, 100, 150). Da die Tabellenpartitionen auf den A-Werten basieren, können die B-Werte in allen Tabellenpartitionen enthalten sein. Somit sucht der Abfrageprozessor in jeder der vier Tabellenpartitionen nach jedem der drei B-Werte (50, 100, 150). Der Abfrageprozessor weist Threads proportional zu, sodass alle zwölf Abfragesuchläufe parallel ausgeführt werden können.

Tabellenpartitionen auf Grundlage der Spalte A	Suche in allen Tabellenpartitionen nach B-Spaltenwerten
Tabellenpartition 1: A < 10	B=50, B=100, B=150
Tabellenpartition 2: A >= 10 AND A < 20	B=50, B=100, B=150
Tabellenpartition 3: A >= 20 AND A < 30	B=50, B=100, B=150
Tabellenpartition 4: A >= 30	B=50, B=100, B=150

Bewährte Methoden

Wir empfehlen die folgenden bewährten Vorgehensweisen, um die Leistung von Abfragen zu verbessern, bei denen in großen partitionierten Tabellen und Indizes auf eine große Menge von Daten zugegriffen wird:

• Verteilen Sie alle Partitionen über viele Datenträger (Datenträgerstriping).

• Verwenden Sie möglichst einen Server mit einem Hauptspeicher, der groß genug ist für Partitionen, auf die häufig zugegriffen wird, bzw. für alle Partitionen, um die E/A-Kosten zu senken.

• Falls die abgefragten Daten nicht in den Arbeitsspeicher passen, komprimieren Sie die Tabellen und Indizes. Dies reduziert die E/A-Kosten.

• Verwenden Sie einen Server mit schnellen und möglichst vielen Prozessoren, um sich die Vorteile der parallelen Abfrageverarbeitung zu Nutze zu machen.

• Stellen Sie sicher, dass der Server über eine ausreichend große E/A-Controllerbandbreite verfügt.

• Erstellen Sie für jede große partitionierte Tabelle einen gruppierten Index, um den optimierten B-Strukturscan voll nutzen zu können.

• Beachten Sie die Empfehlungen für bewährte Vorgehensweisen im Whitepaper "Loading Bulk Data into a Partitioned Table", wenn Sie mittels Massenladen Daten in partitionierte Tabellen laden.

Beispiel

Im folgenden Beispiel wird eine Testdatenbank mit einer Tabelle, die sieben Partitionen aufweist, erstellt. Verwenden Sie die zuvor in diesem Thema vorgestellten Tools, wenn Sie die Abfragen in diesem Beispiel durchführen, um Partitionierungsinformationen für den Kompilierungszeitplan und den Laufzeitplan anzuzeigen.

Hinweis
In diesem Beispiel werden über eine Millionen Zeilen in die Tabelle eingefügt. Je nach Hardware kann die Ausführung dieses Beispiels einige Minuten dauern. Stellen Sie vor dem Ausführen dieses Beispiels sicher, dass mehr als 1,5 GB Speicherplatz zur Verfügung stehen.

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

Siehe auch

Verweis

Anzeigen von grafischen Ausführungsplänen (SQL Server Management Studio)

DBCC TRACEON (Transact-SQL)

Konzepte

Partitionierte Tabellen und Indizes

Anzeigen von Ausführungsplänen mithilfe der Showplan-SET-Optionen (Transact-SQL)

Verbesserte Abfrageverarbeitung bei partitionierten Tabellen und Indizes