Mittendrin.

Zurück

Flurfunk der eXperts.

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

Besonderheiten in der Komprimierung auf Heap Tabellen

02.02.201508:00 Uhr , Andrej Kuklin

Bei einem unserer Kunden werden im nächtlichen ETL Snapshots der aktuell verarbeiteten Daten bereitgestellt (vorgefiltert und voraggregiert), die später während des Tages von externen Systemen abgezogen werden. Die Tabellen für diese Snapshot-Daten sind als page-komprimierte Heaps definiert. Dabei ist aufgefallen, dass manche Tabellen spürbar größer waren, als man nach der Komprimierung erwarten würde.
Nachfolgend ein Beispiel auf der Basis von AdventureWorksDW.

Ich erzeuge ein Snapshot der Tabelle FactProductInventory, die ich für diesen Test auf 10 Mio Datensätze (466 Mb) vergrößert habe. Die Snapshot-Tabelle ist wie folgt definiert.

   1: CREATE TABLE dbo.FactProductInventorySnapshot

   2: (

   3:      ProductKey INT NOT NULL

   4:     ,DateKey INT NOT NULL

   5:     ,MovementDate DATE NOT NULL

   6:     ,UnitCost MONEY NOT NULL

   7:     ,UnitsIn INT NOT NULL

   8:     ,UnitsOut INT NOT NULL

   9:     ,UnitsBalance INT NOT NULL

  10: ) ON [PRIMARY] WITH (DATA_COMPRESSION = PAGE)

Die Originaltabelle ist nicht komprimiert.

   1: INSERT  INTO dbo.FactProductInventorySnapshot

   2:         SELECT  ProductKey

   3:                ,DateKey

   4:                ,MovementDate

   5:                ,UnitCost

   6:                ,UnitsIn

   7:                ,UnitsOut

   8:                ,UnitsBalance

   9:         FROM    dbo.FactProductInventory

image

Jetzt prüfen wir, wie das Ergebnis aussieht:

   1: SELECT  index_type_desc

   2:        ,page_count

   3:        ,record_count

   4:        ,compressed_page_count

   5: FROM    sys.dm_db_index_physical_stats(DB_ID('AdventureWorksDW'),

   6:                                        OBJECT_ID('dbo.FactProductInventorySnapshot'),

   7:                                        0, NULL, 'DETAILED')

image

Die Tabelle belegt 43163 Pages (8Kb Blöcke), aber anscheinend sind alle Pages unkomprimiert (compressed_page_count=0).

Klarheit bringt der Data Loading Performance Guide (eine lange, aber sehr empfehlenswerte Lektüre):

However, you should be aware of an exception: When bulk loading into a page compressed heap, you must use the TABLOCK hint to achieve page compression. If the TABLOCK hint is not used, only row level compression is done on the heap.

Ein zweiter Versuch mit dem TABLOCK Hint:

image

image

Ein schöner Nebeneffekt von dem TABLOCK Hint ist, dass SQL Server dann das Minimal Logging verwendet und damit deutlich weniger Transaction Log Traffic produziert (mehr als 3 Gb ohne Hint vs. etwa 5 Mb mit Hint).

Zwei wichtige Fragen müssen noch beantwortet werden:

  1. Warum braucht so eine einfache Abfrage, die “lediglich” 466 Mb durch die Gegend schiebt so lange? (Intel Core i7-2760 @ 2,4 GHz mit 8 logischen CPUs, 8 Gb RAM, eine schnelle SSD Platte)
  2. Warum läuft die Abfrage mit dem Hint bei allen genannten Vorteilen 40 Sekunden und die Originalabfrage nur 30 Sekunden?

Beides hat etwas mit der Skalierbarkeit zu tun. Wie man aus dem Ausführungsplan erkennen kann, wird das Insert unparallelisiert ausgeführt (INSERTs werden im SQL Server immer seriell ausgeführt, die einzige Ausnahme ist SELECT INTO ab SQL Server 2014). Das bedeutet, dass der ganze Komprimierungsaufwand nur von einem Prozessor abgearbeitet wird und die Page-Komprimierung braucht länger als die im Vergleich triviale Row-Komprimierung. Das fällt auf meinem Rechner viel mehr ins Gewicht als I/O-Einsparungen.

Ich spiele jetzt eine andere Vorgehensweise durch. Die Snapshot-Tabelle wird initial ohne Komprimierung definiert. Das Insert läuft deswegen viel schneller durch, da nicht komprimiert werden muss (12,5 Sekunden). Komprimiert wird durch Anlegen eines Clustered Indexes – eine Operation, die parallelisiert ausgeführt werden kann (11,6 Sekunden).

image

Bei Bedarf kann der Index sofort gelöscht werden. Dabei werden nur die non-leaf Knoten des B-Baums entfernt, die Operation ist also sehr schnell.

Die Ergebnisse sehen schließlich so aus:

 

Belegte Pages

Laufzeit (s)

Insert ohne Tablock Hint

43163

30

Insert mit Tablock Hint

34049

41

Insert in eine nicht komprimierte Tabelle

+Komprimierung mit einem Clustered Index

19648

24

Die Differenz zwischen 34049 Pages für Heap und 19648 für Clustered Index kann vermutlich durch eine bessere Sortierung (und dadurch bessere Page-Komprimierung) erklärt werden. Auch nach dem Löschen des Clustered Index bleibt im dritten Fall die Anzahl von belegten (komprimierten) Pages bei 19648.

Update

Mit DBCC PAGE kann man gut nachvollziehen, woran der Unterschied in Page-Anzahl zwischen dem zweiten (komprimierte Heap-Tabelle) und dem dritten Fall (komprimierter Clustered Index) liegt. Dafür ist nicht wie ursprünglich vermutet eine günstigere Sortierung verantwortlich, sondern die aktivierte Snapshot Isolation auf AdventureWorksDW.

DBCC IND (AdventureWorksDW, 'dbo.FactProductInventorySnapshot', 1);

DBCC TRACEON (3604);

DBCC PAGE(AdventureWorksDW, 1, 194832, 3);

 

--snip

Version Information = 

    Transaction Timestamp: 570685

    Version Pointer: Null

--snip

Die Inserts schreiben die für Snapshot Isolation notwendigen Versionierungsinformationen mit. Beim Erstellen des Clustered Indexes kann SQL Server sicher sein, dass niemand auf die Tabelle zugreift, deswegen werden die Versionierungsinformationen entfernt, was zu einer zusätzlichen Platzeinsparung führt.

Tags: SQL Server

2 Kommentare

02.02.201510:49 Uhr
Martin Preiss

Hallo Andrej,
sehr interessante Ergebnisse – insbesondere für den dritten Fall. Wenn ich mich richtig erinnere, besteht page compression aus drei Komponenten: row comrpession, prefix compression (wiederholte prefix Werte (einzelner Spalten) werden in einer compress information (CI) Struktur direkt hinter dem page header eingefügt und an den passenden Stellen referenziert) und dictionary compression (wiederholte Werte der page werden in die CI area eingefügt und an den passenden Stellen referenziert, wobei diese Ersetzung Spalten-übergreifend erfolgt). Zumindest hinsichtlich der prefix compression dürfte der clustered index eine deutlich bessere Komprimierung erlauben; bei der dictionary compression hängt der Effekt dann vermutlich eher vom Spalten-übergreifenden Clustering der Daten ab.

Eine andere Frage wäre, wie sich die Komprimierung auf lesende Zugriffe auswirkt. Thomas Kejser hat da gelegentlich recht erstaunliche Ergebnisse veröffentlicht, die darauf hindeuten, dass die CPU-Kosten beim Auspacken der komprimierten Daten erstaunlich hoch sein können: https://kejserbi.wordpress.com/2013/03/11/quantifying-the-cost-of-compression/.

Gruß
Martin

04.02.201517:46 Uhr
Andrej Kuklin

Hallo Martin,

danke für den Link. Die Erklärung für den dritten Fall ist doch eine andere, als ich zuerst vermutet habe. In AdventureWorksDW ist Snapshot Isolation aktiviert, was dafür sorgt, dass die Inserts Versionierungsinformationen zusätzlich persistieren. Der nachträgliche CREATE INDEX entfernt die Versionierungsinfo und spart somit Platz (Update zum Artikel folgt).

In Fällen wenn man aus irgendwelchen Gründen von der CPU-Leistung begrenzt ist (z.B. eine serielle Abfrage, die nicht parallelisiert werden kann, oder man hat den Luxus, dass alle zu lesenden Daten im RAM bzw. auf einem sehr schnellen PCI-E SSD System liegen, wie bei Thomas Kejser), ist der lesende Zugriff auf die komprimierten Daten selbstverständlich langsamer als auf die unkomprimierten. Wie so oft, ist die allgemeingültige Empfehlung nicht möglich, die Entscheidung ist immer davon abhängig, wie die zur Verfügung stehende Hardware und das relevante Workload aussieht.

Viele Grüße

Andrej

Dein Kommentar wartet auf Freischaltung.

Artikel kommentieren

Zurück

Tag Cloud