Podziękowania i reaktywacja

Na początku tego wpisu chciałbym podziękować licznym osobom wypytującym mnie o dalsze losy tego bloga przy okazji konferencji SQLDay 2012, w której miałem przyjemność wziąć udział. To dzięki Wam mam motywację i chęci, by podjąć akcję “reaktywacja” :-) Dziękuję.

Myślałem trochę nad tematami na następne wpisy i sądzę, że jest sporo tematów, nad którymi wypada się pochylić, zarówno tematów dla DBA/DEV, jak i tematów z zakresu Business Intelligence.

Geneza

Pierwszy temat wpadł mi do głowy przy okazji przygotowywania prezentacji na 61. spotkanie warszawskiego oddziału PLSSUG (Polish SQL Server User Group). Wspólnie z Małgosią Borzęcką zaprezentowałem wprowadzenie do indeksów columnstore. W zasadzie chyba używając słowa “wprowadzenie” dokonałem nadużycia :-) Sesja miała co najmniej porządny poziom 300, ponieważ pokazywaliśmy chociażby plany wykonania zapytań, testy wydajności czy mechanizmy aktualizacji danych z użyciem przełączania partycji. Materiały z sesji są do pobrania tutaj.

Wówczas pomyślałem, że w sumie, dobrze byłoby napisać o indeksach columnstore, a przy okazji dokonać reanimacji bloga :-)

Indeksy columnstore – wprowadzenie

O indeksach columnstore napisano już wiele. Najlepszym bodaj zasobem, jaki udało mi się znaleźć w sieci, jest strona Columnstore FAQ na Technecie. Jest tam do znalezienia wiele ciekawych materiałów opisujących, jak zbudowane są indeksy columnstore i jak to się dzieje, że dzięki nim można optymalizować zapytania w hurtowniach danych. Dla osób zainteresowanych internalsami, polecam szczególnie artykuł z biuletynu IEEE.

A więc co to takiego indeks columnstore? Jest to nowy rodzaj indeksu, dostępny w SQL Server 2012 Enterprise Edition (i tylko w tej edycji, próba przeniesienia bazy z indeksem columnstore na niższe edycje kończy się zwróceniem przez serwer komunikatu błędu nr 980). Struktura danych w tym indeksie jest niepodobna do niczego, co znaliśmy z wcześniejszych wersji systemu SQL Server. Dane są w nim przechowywane kolumnowo, każda kolumna jest odseparowana w sensie stron danych od innych kolumn. Jednocześnie dane są posegmentowane, kolumny są grupowane (segmentowane) po ok. 1000 wartości i przechowywane w logicznych strukturach – segmentach (wszystkie kolumny są równane w tej segmentacji). Segment jest jednostką, jakiej SQL Server może używać do operacji odczytu (mówi się o agresywniejszym procesie read-ahead). Dane poddane są kodowaniu, sortowaniu (wg patentu Vertipaq / xVelocity) i kompresji i są przechowywane w postaci binarnej. Dzięki takiemu podejściu możliwe jest wykorzystanie indeksu columnstore do optymalizacji zapytań na dużych tabelach (np. tabelach faktów w hurtowniach), ponieważ wyciąganie pojedynczych kolumn z takiego indeksu nie jest dla SQL Servera problemem, a pomijanie segmentów sprawia, że ilość odczytów w zapytaniach może drastycznie spaść. Optymalizacji ulega nie tylko czas wykonywania zapytań, ale przede wszystkim ilość pamięci używanej do przechowywania danych w buforze (kompresja robi swoje), a często także i CPU. Skrypt ze spotkania PLSSUG umożliwia przetestowanie indeksu columnstore na tabeli faktów z ok. 50 milionami wierszy. Zapytania do struktury gwiazdy wykonują się w ułamkach sekund. Taka optymalizacja nie jest możliwa w przypadku tradycyjnych indeksów (te same zapytania trwają kilkanaście sekund).

Warto pamiętać, że indeksy columnstore mają, póki co, liczne ograniczenia. W tym:

  • Utworzenie indeksu columnstore blokuje możliwość edycji w tabeli (wyjątek – partition switching – patrz skrypt ze spotkania PLSSUG).
  • Kolumny niektórych typów (głównie LOB i typy specjalne, np. geography czy xml) nie mogą znaleźć się w kluczu indeksu columnstore.
  • Indeksy columnstore optymalizują wybrane klasy zapytań.

Ograniczeń jest więcej i można o nich poczytać w dokumentacji SQL Server 2012. Mimo tych ograniczeń uważam, że indeksy columnstore mogą znaleźć wiele zastosowań, o czym napiszę na sam koniec.

Indeksy columnstore – czasy tworzenia i rozmiary

Indeksy columnstore, jak każdy indeks tworzą swego rodzaju kopię danych z tabeli. Kopia ta jest, jak napisałem, skompresowana. Natomiast powstaje pytanie, jak dużo czasu potrzebne jest do utworzenia takiego indeksu i jaki jest rozmiar takiego indeksu?

Aby odpowiedzieć na to pytanie, wykonałem kilka testów. Do testów użyłem instancji SQL Server 2012 Developer Edition (build 11.0.2316) zainstalowanego na laptopie z 8GB RAM i procesorem Intel Core i7 (quadcore). Instancja miała ustawione max server memory na 4GB.

Skrypt, którego użyłem w testach:

USE ColumnstoreDemo;
GO
DECLARE @t datetime;
DECLARE @i int = 1;
DECLARE @num int;
DECLARE @num_rows TABLE (
  id int IDENTITY(1,1) NOT NULL,
  num int NOT NULL
);
DECLARE @results TABLE (
  num int NOT NULL,
  time_cl bigint NULL,
  size_cl numeric(10,2) NULL,
  time_nc bigint NULL,
  size_nc numeric(10,2) NULL,
  time_cs bigint NULL,
  size_cs numeric(10,2) NULL
);
INSERT INTO @num_rows (num)
VALUES
  (10000), (100000), (200000),
  (500000), (1000000), (2000000),
  (5000000), (10000000);
IF OBJECT_ID('dbo.FactInternetSales', 'U') IS NOT NULL
  DROP TABLE dbo.FactInternetSales;
CREATE TABLE dbo.FactInternetSales (
	FactID int IDENTITY(1,1) NOT NULL,
  ProductKey int NOT NULL,
	OrderDateKey int NOT NULL,
	CustomerKey int NOT NULL,
	PromotionKey int NOT NULL,
	CurrencyKey int NOT NULL,
	SalesTerritoryKey int NOT NULL,
	OrderQuantity smallint NOT NULL,
	UnitPrice money NOT NULL,
	SalesAmount money NOT NULL,
	TaxAmt money NOT NULL,
	Freight money NOT NULL
);
ALTER TABLE dbo.FactInternetSales
ADD CONSTRAINT PK_FactInternetSales
PRIMARY KEY (FactID);
WHILE @i <= (SELECT MAX(id) FROM @num_rows)
BEGIN
  RAISERROR('Starting for %d rows', 10, 1, @num) WITH NOWAIT;
  IF EXISTS (
    SELECT *
    FROM sys.indexes
    WHERE name = 'IX_CS_FactInternetSales_AllColumns'
    AND object_id = OBJECT_ID('dbo.FactInternetSales')
  )
    DROP INDEX IX_CS_FactInternetSales_AllColumns
    ON dbo.FactInternetSales;
  ALTER TABLE dbo.FactInternetSales
  DROP CONSTRAINT PK_FactInternetSales;
  TRUNCATE TABLE dbo.FactInternetSales;
  SELECT @num = num FROM @num_rows WHERE id = @i;
  INSERT INTO dbo.FactInternetSales (
    ProductKey,
    OrderDateKey,
    CustomerKey,
    PromotionKey,
    CurrencyKey,
    SalesTerritoryKey,
    OrderQuantity,
    UnitPrice,
    SalesAmount,
    TaxAmt,
    Freight
  )
  SELECT
    ((1000 - 1) * RAND(CHECKSUM(NEWID())) + 1),
    CONVERT(int, CONVERT(varchar(8), DATEADD(day, (1000 - 1) *
    RAND(CHECKSUM(NEWID())) + 1, '20100101'), 112)),
    ((10000 - 1) * RAND(CHECKSUM(NEWID())) + 1),
    CASE WHEN RAND(CHECKSUM(NEWID())) > 0.1 THEN 2 ELSE 1 END,
    CASE WHEN RAND(CHECKSUM(NEWID())) > 0.2 THEN 2 ELSE 1 END,
    ----<stale wartosci>
    --v2.number,
    --v1.number,
    --v2.number / 10,
    --v2.number,
    --v2.number / 10,
    --v2.number / 10
    ----</STALE wartosci>
    --<zmienne wartosci>
    (100 - 1) * RAND(CHECKSUM(NEWID())) + 1,
    (20 - 1) * RAND(CHECKSUM(NEWID())) + 1,
    (100 - 1) * RAND(CHECKSUM(NEWID())) + 1,
    (1000 - 1) * RAND(CHECKSUM(NEWID())) + 1,
    (100 - 1) * RAND(CHECKSUM(NEWID())) + 1,
    (100 - 1) * RAND(CHECKSUM(NEWID())) + 1
    --</ZMIENNE wartosci>
  FROM master.dbo.spt_values AS v1
  CROSS JOIN master.dbo.spt_values AS v2
  CROSS JOIN master.dbo.spt_values AS v3
  WHERE v1.type = 'P' AND v2.type = 'P' AND v3.type = 'P'
  AND v1.number BETWEEN 1 AND 10
  AND v2.number BETWEEN 1 AND 1000
  AND v3.number BETWEEN 1 AND (@num / 10000);
  SET @t = GETDATE();
  ALTER TABLE dbo.FactInternetSales
  ADD CONSTRAINT PK_FactInternetSales
  PRIMARY KEY (FactID);
  INSERT INTO @results (num, time_cl, size_cl)
  SELECT @num, DATEDIFF(ms, @t, GETDATE()), page_count * 8192 / (1024 * 1024.)
  FROM sys.dm_db_index_physical_stats(
    DB_ID(), OBJECT_ID('dbo.FactInternetSales'),
    NULL, NULL, 'LIMITED'
  )
  WHERE index_id = 1;
  SET @t = GETDATE();
  CREATE NONCLUSTERED COLUMNSTORE INDEX IX_CS_FactInternetSales_AllColumns
  ON dbo.FactInternetSales (
    FactID,
    ProductKey,
    OrderDateKey,
    CustomerKey,
    PromotionKey,
    CurrencyKey,
    SalesTerritoryKey,
    OrderQuantity,
    UnitPrice,
    SalesAmount,
    TaxAmt,
    Freight
  );
  UPDATE @results
  SET
    time_cs = DATEDIFF(ms, @t, GETDATE()),
    size_cs = (
      SELECT SUM(s.used_page_count) / 128.0 on_disk_size_MB
      FROM sys.indexes AS i
      JOIN sys.dm_db_partition_stats AS s
      ON i.object_id = s.object_id
      AND i.index_id = s.index_id
      WHERE i.object_id = object_id('dbo.FactInternetSales')
      AND i.type_desc = 'NONCLUSTERED COLUMNSTORE'
    )
  WHERE num = @num;
  IF EXISTS (
    SELECT *
    FROM sys.indexes
    WHERE name = 'IX_CS_FactInternetSales_AllColumns'
    AND object_id = OBJECT_ID('dbo.FactInternetSales')
  )
    DROP INDEX IX_CS_FactInternetSales_AllColumns
    ON dbo.FactInternetSales;
  IF EXISTS (
    SELECT *
    FROM sys.indexes
    WHERE name = 'IX_NC_FactInternetSales_AllColumns'
    AND object_id = OBJECT_ID('dbo.FactInternetSales')
  )
    DROP INDEX IX_NC_FactInternetSales_AllColumns
    ON dbo.FactInternetSales;
  SET @t = GETDATE();
  CREATE NONCLUSTERED INDEX IX_NC_FactInternetSales_AllColumns
  ON dbo.FactInternetSales (
    ProductKey,
    OrderDateKey,
    CustomerKey,
    PromotionKey,
    CurrencyKey,
    SalesTerritoryKey,
    OrderQuantity,
    UnitPrice,
    SalesAmount,
    TaxAmt,
    Freight
  );
  UPDATE @results
  SET
    time_nc = DATEDIFF(ms, @t, GETDATE()),
    size_nc = (
      SELECT SUM(s.used_page_count) / 128.0 on_disk_size_MB
      FROM sys.indexes AS i
      JOIN sys.dm_db_partition_stats AS s
      ON i.object_id = s.object_id
      AND i.index_id = s.index_id
      WHERE i.object_id = object_id('dbo.FactInternetSales')
      AND i.type_desc = 'NONCLUSTERED'
    )
  WHERE num = @num;
  IF EXISTS (
    SELECT *
    FROM sys.indexes
    WHERE name = 'IX_NC_FactInternetSales_AllColumns'
    AND object_id = OBJECT_ID('dbo.FactInternetSales')
  )
    DROP INDEX IX_NC_FactInternetSales_AllColumns
    ON dbo.FactInternetSales;
  SET @i += 1;
END;
SELECT * FROM @results;</zmienne></stale>

Baza danych ColumnstoreDemo działała w recovery mode Simple i została zwymiarowana przed uruchomieniem testów (10GB dane, 5GB log).

Wyniki (na osi odciętych mamy liczbę rekordów w tabeli):

image

image

Powyższe wyniki uzyskałem dla danych losowych (z małą lub zerową powtarzalnością wartości w kolumnach). Dla wartości nieco bardziej stałych (patrz zakomentowany fragment w skrypcie) wynik jest ciut inny:

image

image

Kilka obserwacji z testów:

  • Czas tworzenia indeksu columnstore jest zazwyczaj większy niż czas tworzenia tradycyjnych indeksów (mówi się, że średnio jest to 1,5 x dłużej).
  • Im więcej rekordów w tabeli, tym procentowa różnica czasów tworzenia indeksów jest mniejsza.
  • W niektórych przypadkach indeks columnstore powstaje w czasie krótszym niż pokrywający tabelę indeks nieklastrowany.
  • Kompresja użyta w indeksach columnstore powoduje, że ilość zajmowanego przez dane w tym indeksie miejsca jest zdecydowanie (nawet kilkukrotnie) mniejsza niż ilość miejsca zajmowanego przez tradycyjne indeksy (potwierdzony testami fakt – kompresja indeksu columnstore jest ok. 1,8 razy skuteczniejsza od kompresji PAGE dostępnej w SQL Server).

Indeksy columnstore – wydajność

Jak testować wydajność osiąganą za pomocą indeksów columnstore? Najlepiej wykorzystać jakąś hurtownię danych, w której znajduje się tabela faktów z co najmniej kilkunastoma milionami rekordów (a jeszcze lepiej, jeśli rekordów jest kilkadziesiąt lub kilkaset milionów). W czasie sesji na spotkaniu PLSSUG pokazywaliśmy demonstracje na dość dużej tabeli dbo.FactInternetSales z ponad 50 milionami wierszy (w skrypcie z prezentacji jest kod T-SQL do wstawienia takiej liczby rekordów). Zapytania, jakie należy testować, to typowe zapytania grupujące do struktury gwiazdy.

Tym razem jako przykład wybiorę bazę ContosoRetailDW (do pobrania tutaj). Jest w niej kilka tabel z milionami wierszy, w tym tabela dbo.FactOnlineSales.

Na dobry początek można założyć indeksy:

USE ContosoRetailDW;
GO
CREATE NONCLUSTERED INDEX IX_FactOnlineSales_DateKey_Include
ON dbo.FactOnlineSales (DateKey)
INCLUDE (ProductKey, SalesQuantity, SalesAmount);
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_CS_FactOnlineSales_AllColumns
ON dbo.FactOnlineSales (
  OnlineSalesKey,
  DateKey, StoreKey,
  ProductKey,
  PromotionKey,
  CurrencyKey,
  CustomerKey,
  SalesOrderNumber,
  SalesOrderLineNumber,
  SalesQuantity,
  SalesAmount,
  ReturnQuantity,
  ReturnAmount,
  DiscountQuantity,
  DiscountAmount,
  TotalCost,
  UnitCost,
  UnitPrice,
  ETLLoadID,
  LoadDate,
  UpdateDate
);
GO

Indeks nieklastrowany założyłem, by optymalizować “po staremu” proste zapytanie, które za moment zamierzam wykorzystać. Indeks columnstore pokrywa całą tabelę (i jest to swego rodzaju dobra praktyka).

Test:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
 
SELECT
  P.ProductKey,
  P.ProductName,
  SUM(F.SalesAmount) AS SalesAmount,
  SUM(F.SalesQuantity) AS SalesQuantity
FROM dbo.DimProduct AS P
INNER JOIN dbo.FactOnlineSales AS F
ON P.ProductKey = F.ProductKey
WHERE F.DateKey BETWEEN '20080101' AND '20081231'
GROUP BY
  P.ProductKey,
  P.ProductName
ORDER BY
  P.ProductName
OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX);
 
SELECT
  P.ProductKey,
  P.ProductName,
  SUM(F.SalesAmount) AS SalesAmount,
  SUM(F.SalesQuantity) AS SalesQuantity
FROM dbo.DimProduct AS P
INNER JOIN dbo.FactOnlineSales AS F
ON P.ProductKey = F.ProductKey
WHERE F.DateKey BETWEEN '20080101' AND '20081231'
GROUP BY
  P.ProductKey,
  P.ProductName
ORDER BY
  P.ProductName;
 
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

Wyniki testu:

  • W planach wykonania koszt zapytania relatywny do wsadu układa się w stosunku 87% vs 13%.
  • W planie wykonania z wykorzystaniem indeksu columnstore mamy nowy operator – Columnstore Index Scan (wykonany w trybie Batch):

image

  • Czasy dla zapytania bez użycia indeksu columnstore: CPU time = 1841 ms,  elapsed time = 561 ms.
  • Analogiczne czasy dla zapytania z użyciem indeksu columnstore: CPU time = 155 ms,  elapsed time = 141 ms.

Gdy zakomentowałem klauzulę WHERE w zapytaniach z testu i wykonałem test ponownie (tym razem czytając dane z całej tabeli faktów), wynik był bardziej na korzyść indeksu columnstore:

  • Czasy dla zapytania bez użycia indeksu columnstore: CPU time = 9641 ms,  elapsed time = 2732 ms.
  • Analogiczne czasy dla zapytania z użyciem indeksu columnstore: CPU time = 265 ms,  elapsed time = 175 ms.

A zatem jakieś 10-15 razy szybciej. Co ciekawe, wynik kilkadziesiąt, a nawet 100 razy szybciej, który uważałem za niemożliwy, jest do osiągnięcia (co pokazywaliśmy na prezentacji – zapytania do silnie poindeksowanej tabeli bez indeksu columnstore trwały do 10 sekund, a po utworzeniu indeksu columnstore – ok. 200 milisekund).

Podsumowanie

Indeksy columnstore to zdecydowanie jedna z ciekawszych i bardziej przydatnych nowości w SQL Server 2012. Jak w przypadku większości funkcjonalności, także i w przypadku indeksów columnstore trzeba pamiętać, że to nie jest technologia “do wszystkiego” (co oczywiście nie znaczy, że jest do niczego :-)).

Jakie widzę zatem zastosowania? Według mnie jest to funkcjonalność idealna dla systemów, w których raporty czerpią dane bezpośrednio z hurtowni danych (zapytaniami T-SQL). Czyli w scenariuszach, w których albo całkiem pomijamy modelowanie wielowymiarowe (agregacje w zapytaniach do indeksów columnstore spełnią rolę swego rodzaju zamiennika), albo dodatkowo chcemy raportować z hurtowni. Dodam, że o ile indeksy columnstore raczej nie optymalizują procesowania tradycyjnych kostek OLAP (wyjątkiem może być ROLAP, ale nie robiłem testów takiego scenariusza), o tyle nie przeszkadzają w sprawnym procesowaniu (przynajmniej tak wynika z moich testów, które przeprowadziłem szukając scenariusza, w którym indeksy columnstore mogłyby wesprzeć procesowanie kostki). Idealnym scenariuszem dla zastosowania indeksów columnstore wydaje się też być przypadek bazy OLAP w trybie tabelarycznym (tabular mode) i działającej w trybie bezpośredniego dostępu do danych (DirectQuery) i wykorzystywanej przez raporty Power View. Co więcej, możliwość wykorzystania mechanizmu przełączania partycji pozwala na implementację, na moje oko nowego, podejścia do tworzenia hurtowni danych – duża tabela faktów archiwalnych z indeksem columnstore + mniejsza tabela faktów do inkrementalnego uzupełniania danych.

Zachęcam do własnych testów i śledzenia, kiedy w planach wykonania operatory wykorzystujące indeksy columnstore wpadają w tryb Row (zamiast pożądanego trybu Batch). Co ciekawe, “wnętrzności” indeksów columnstore można oglądać tradycyjnymi metodami (DBCC IND + DBCC PAGE), więc co bardziej dociekliwi mogą odkryć jeszcze wiele ciekawostek :-)

Czekam na Wasze wyniki testów i obserwacje. A może ktoś już używa takiego indeksu produkcyjnie? Podzielcie się wrażeniami :-)