Verbesserte Abfrageverarbeitung bei partitionierten Tabellen und Indizes

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

Anzeigen von Partitionsinformationen mittels anderer Showplan-Methoden

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 Joins

Eine Zusammenfassung von Joins kann eintreten, wenn zwei Tabellen mit derselben oder einer ähnlichen Partitionsfunktion partitioniert und die Partitionierungsspalten auf beiden Seiten des Joins 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 Joins 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 Joins erzeugt wird, fällt auf Grundlage der geschätzten Kosten.

Bei einem Plan für zusammengefasste Joins 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 Joins für partitionierte Tabellen oder Indizes erzeugt werden, wird ein Parallelism-Operator zwischen den Constant Scan-Joinoperator und den Nested Loops-Joinoperator eingefügt. In diesem Fall lesen und bearbeiten mehrere Threads auf der Außenseite des Joins jeweils eine andere Partition.

Die folgende Abbildung zeigt einen parallelen Abfrageplan für einen zusammengefassten Join.

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.

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

Konzepte