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

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

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:

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

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:

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.