Mittendrin.

Zurück

Flurfunk der eXperts.

Hier erfahren Sie mehr über eXperts, Technologien und das wahre Leben in der SDX.

Parallelisierte Ausführung von SELECT INTO in SQL Server 2014

27.06.201408:00 Uhr , Andrej Kuklin

SQL Server 2014 brachte viele neue Funktionalitäten und Verbesserungen mit sich. Eine ziemlich coole Verbesserung, die keine Änderungen im bestehenden Code erfordert, ist die mögliche parallelisierte Ausführung von SELECT INTO-Abfragen.

In diesem Artikel werde ich mit realen Daten messen, wie groß der Parallelisierungseffekt sein kann. Da die parallelisierte Ausführung besonders in DWH-Szenarien vorteilhaft ist, werde ich zusätzlich die Kombination von SELECT INTO mit clustered columnstore Indexes – einem anderen neuen Feature in SQL Server 2014 – testen.

Selbstverständlich spielt bei solchen Lasttests die physikalische Infrastruktur eine ganz entscheidende Rolle. Mein System ist wie folgt ausgestattet:

  • Intel Core i7-2760QM (8 logische Kerne x 2,4 GHz)
  • 8 Gb RAM
  • Samsung SSD PM830

Ein geeignet großes Datengerüst habe ich auf der Basis von AdventureWorksDW mit dem Skript von Bob Duffy generiert (FactInternetSalesBig 3,5 Gb, 24 Mio Datensätze). Damit SELECT INTO Operationen nicht von Vergrößerungen der MDF-Datei ausgebremst werden, habe ich den Platz vorreserviert.

ALTER DATABASE [AdventureWorksDW2012] MODIFY FILE ( NAME = N'AdventureWorksDW2012_Data', SIZE = 53248000KB )

Als Erstes werde ich eine Baseline erstellen. Dafür versetze ich die Datenbank ins Kompatibilitätsmodus SQL Server 2012 und messe die Geschwindigkeit vom nicht parallelisierten SELECT INTO.

   1: ALTER DATABASE [AdventureWorksDW2012] SET COMPATIBILITY_LEVEL = 110;

   2: SELECT  * INTO dbo.FactInternetSales_rowstore_serial FROM dbo.FactInternetSalesBig;

rowstore_serial

Wie erwartet, ist der Ausführungsplan seriell, die Ausführungszeit beträgt 40,7 Sekunden. Jetzt ändere ich das Kompatibilitätsmodus in SQL Server 2014 und wiederhole den SELECT INTO-Befehl.

   1: ALTER DATABASE [AdventureWorksDW2012] SET COMPATIBILITY_LEVEL = 120;

   2: SELECT  * INTO dbo.FactInternetSales_rowstore_parallel FROM dbo.FactInternetSalesBig;

rowstore_parallel

Diesmal ist sowohl das Lesen der Quelltabelle als auch das Schreiben in die Zieltabelle parallelisiert. Die Ausführungszeit beträgt 19,9 Sekunden.

Als Nächstes erstelle ich einen clustered columnstore Index auf der Quelltabelle.

CREATE CLUSTERED COLUMNSTORE INDEX [CCSI_FactInternetSalesBig] ON [dbo].[FactInternetSalesBig];

Die Operation ist nicht gerade schnell und benötigt gute 20 Minuten. Dafür ist die Tabelle am Ende nur noch 234 Mb groß (ursprünglich 3,5 Gb).

NB: Ich konnte mit SQL Server 2014 RTM keinen clustered columnstore Index erstellen, da der SQL Server der Abfrage einen zu geringen Memory Grant zugeteilt hat und die Ausführung mit der bekannten Fehlermeldung „Msg 701, Level 17, State 128, Line 1. There is insufficient system memory in resource pool ‚default‘ to run this query” abgebrochen wurde. Erst nach der Installation vom Cumulative Update 1 hat das Erstellen geklappt. Es gibt noch weitere Möglichkeiten, wie man den Abbruch beim Erstellen des Indexes verhindern kann (unter Anderem durch Hochsetzen von REQUEST_MAX_MEMORY_GRANT_PERCENT und Einschränken der Parallelität mit MAXDOP), die im SQL Server Columnstore Index FAQ beschrieben sind.

Der serielle Ausführungsplan, der entweder mit ALTER DATABASE [AdventureWorksDW2012] SET COMPATIBILITY_LEVEL = 110 oder mit dem MAXDOP-Hint erzwungen werden kann, sieht wie folgt aus:

columnstore_serialD

Das Ergebnis ist etwas enttäuschend – 46 Sekunden, sogar geringfügig schlechter, als unsere Baseline.

Die parallelisierte Ausführung auf dem columnstore Index, wie unten abgebildet, ist aber wesentlich besser – 15,9 Sekunden.

columnstore_parallel

Folgende Tabelle zeigt den Vergleich aller vier Methoden.

Rowstore Columnstore
Seriell 100% 113%
Parallel 48,9% 39,1%

Fazit

Die parallelisierten SELECT INTO Abfragen in SQL Server 2014 sind bei vorhandenen IO-Ressourcen spürbar schneller als die seriellen. Beim Einsatz von clustered columnstore Indexes ist die Geschwindigkeit von diesen typischen DWH-Operationen noch etwas höher.

Leider sind die normalen INSERT-Befehle weiterhin seriell. Beim Einfügen in eine existierende Tabelle kann die Parallelität und Skalierbarkeit nur mit dem Einsatz von zusätzlichen Tools (z.B. SSIS oder BCP) erreicht werden.

4 Kommentare

27.06.20148:18 Uhr
Martin Preiss

Hallo Andrej,
sehr interessante Untersuchung. Mit welchem Parallelisierungsgrad arbeitet der Server da normalerweise und kann man den über MAXDOP (oder andere Optionen) anpassen (und wie würde sich das auf die Ergebnisse auswirken)?
Gruß
Martin

29.06.201418:08 Uhr
Andrej Kuklin

Hallo Martin,

wenn SQL Server entscheidet, dass die Abfrage vom Parallelism profitieren kann (s. cost threshold for parallelism http://msdn.microsoft.com/en-us/library/ms190949.aspx), versucht er, die Abfrage auf allen verfügbaren Kernen auszuführen (in meinem Fall 8). Das kann man mit verschiedenen Mitteln einschränken, z.B. mit der MAXDOP-Einstellung auf der Instanz- oder Abfragenebene oder mit dem Resource Governor.
Die Laufzeit skaliert in meinem Fall recht gut mit MAXDOP:
MAXDOP 1 – 40 Sekunden (serieller Plan)
MAXDOP 2 – 25 Sekunden
MAXDOP 4 – 21 Sekunden
Da mein System nur über 4 physikalische Kerne verfügt, bringt die Parallelisierung auf 8 logische Kern nicht viel. Auch die verfügbare IO-Bandbreite wird schon mit MAXDOP 4 fast voll ausgeschöpft (geprüft mit dem Performance Monitor=>Disk Queue Length).

Viele Grüße

Andrej

30.06.20148:52 Uhr
Martin Preiss

Vielen Dank für die Ergänzungen.

30.06.201414:09 Uhr
Thomas Darimont

Sehr interessant Danke 🙂

Dein Kommentar wartet auf Freischaltung.

Artikel kommentieren

Zurück

Tag Cloud