SQL Server 2016 – Real Time Operational Analytics

W ramach serii “Poznaj SQL Server” omówimy sobie kolejną nowość, która dosyć znacznie zmienia podejście do wszelkiego rodzaju analiz i raportowania w oparciu o SQL Server. Wraz z najnowszą wersją – SQL Server 2016 dostajemy narzędzia dzięki, którym możemy tworzyć analizy czasu rzeczywistego z pominięciem struktur analitycznych takich jak hurtownia danych, kostka OLAP i tym podobne. Jak to działa? Zapraszam do lektury!

W wersji SQL Server 2012 świat obiegła wiadomość o wdrożeniu do swojego produktu przez Microsoft nowego indeksu zwanego indeksem kolumnowym (ang. Columnstore). Oprócz powszechnego zachwytu nad nowym rozwiązaniem – użytkownicy napotkali na bardzo nieprzyjazne uniedogodnienia takie jak:

• indeks istniał jedynie w wersji nonclustered, a więc był kopią tabeli źródłowej
• indeks był jedynie do odczytu – aby wstawić wiersze do tabeli trzeba było go wyłączyć
• nowe podejście do przetwarzania wierszy tzw. “Batch processing” był ograniczony do bardzo małej ilości operatorów planów zapytania
• indeks mógł być stworzony na ograniczonej liczbie typów danych
• słabe zarządzanie zasobami

Powyższe wady znacznie ograniczyły produkcyjne użycie powyższej funkcjonalności. Niektórzy specjaliści oczywiście wymyślili szereg obejść, które niejako pozwalały część z wad ograniczyć – wiązało się to jednak z dodatkową czasochłonnością i np. utratą czytelności rozwiązania. W wersji SQL Server 2014 Columnstore przeszedł niemałą metamorfozę. Wprowadzono wersję Clustered indeksu kolumnowego – przy czym należy mieć na uwadze, iż pojęcie clustered wcale nie oznacza, że dane wewnątrz indeksu są w jakikolwiek sposób posortowane. Chodzi przede wszystkim o to, że indeks clustered columnstore jest tabelą samą w sobie, a nie jej kopią. Jedną z ważniejszych zalet tego rozwiązania jest fakt, iż indeks ten stał się modyfikowalny. Co prawda część kolumnowa indeksu sama w sobie nie ulegała modyfikacji, a jedynie nowo powstały delta store będący wewnętrzną strukturą wierszową przechowującą wprowadzane wiersze. Jednak dzięki temu podejściu architektonicznemu deweloperzy przestali się martwić o czasochłonne wyłączanie i włączanie indeksu. Ponadto w wersji SQL Server 2014 poszerzono liczbę obsługiwanych typów danych oraz operatorów działających w trybie batch. Pozostało kilka ograniczeń takich jak np. fakt iż po stworzeniu clustered columnstore musiał być jedynym indeksem tabeli – jednak wersja ta znajduje dużo szersze zastosowanie w porównaniu do poprzednika.

To co zostało niezmienne od samego początku istnienia tej technologii to fakt, iż columnstore był w zasadzie z góry wykluczony z użycia na bazie typu OLTP. Środowiska tego typu charakteryzują się stosunkowo dużą zmiennością i w dodatku zapytania na bazach tego typu zazwyczaj opierają się o dużo mniejsze zbiory niż te na środowiskach hurtowni danych. Oba te czynniki są słabszymi elementami opisywanego mechanizmu i to się nie zmieniło po dziś dzień. Microsoft poszedł jednak po rozum do głowy i w SQL 2016 umożliwił w ramach pojedynczej tabeli tworzyć zarówno nieklastrowane indeksy kolumnowe jak i tradycyjne rowstore. Sprawdźmy jak to działa!

Na powyższym rysunku przedstawiony został mechanizm umieszczania nieklastrowanych indeksów kolumnowych (NCCI) na tradycyjnych tabelach z indeksem wierszowym ze strukturą b-drzewa (Btree Index). W momencie stworzenia NCCI na tabeli z indeksem wierszowym musimy zdawać sobie sprawę z faktu, iż wszelkie operacje aktualizacji czy np. wstawiania danych będą wiązać się z dodatkowym narzutem. Indeksy NCCI po raz pierwszy w wersji SQL Server 2016 mogą być modyfikowane, w rzeczywistości jednak dane trafiają do tzw. delta store, który jest widoczny na rysunku, a o którym wspominałem wcześniej. Pomijając algorytm działania tego mechanizmu być może dla najbardziej “gorących” danych możemy nie chcieć aby NCCI spowalniał wszelkie operacje związane z ich modyfikacją. Dlatego też Microsoft zaproponował aby stworzyć na takiej tabeli filtrowany NCCI, który będzie obejmował jedynie dane zmieniane sporadycznie. W takim podejściu indeks rzeczywiście nie będzie miał żadnego wpływu na gorące dane, kosztem jednak wydajności ich pobierania ponieważ wtedy zapytanie będzie część danych musiało pobrać z NCCI, a część z tabeli samej w sobie. Oczywiście to tylko proponowane podejście w przypadku gdy obciążenie generowane przez operacje wstawiania itp. do indeksu NCCI nie są dla nas problemem nie musimy nic odfiltrowywać. Ta elastyczność podejścia jest bardzo korzystna dla nas ze względu na fakt możliwego dostosowania rozwiązania do konkretnych potrzeb. Tutaj może pojawić się pytanie – po co nam takie rozwiązanie? Już spieszę z wyjaśnieniami.

Na powyższym obrazku przedstawione zostało jedno z tradycyjnych podejść do budowania rozwiązania raportowego. Dane ze źródła są pobierane procesem ETL do obszaru przejściowego, a następnie do hurtowni danych. Z poziomu hurtowni danych dane trafiają bezpośrednio na raporty lub z wykorzystaniem dodatkowej warstwy pod postacią modelu tabelarycznego bądź też kostki analitycznej. Wielu z Was zna to podejście prawda? Otóż wiąże się z nim dwie dosyć istotne dla przedsiębiorstw sprawy, a mianowicie koszt zbudowania takiego rozwiązania oraz opóźnienie w danych związane z cyklicznym uruchamianiem procesu ładującego. W tym miejscu przychodzi nam z pomocą Operational Analytics, którego uproszczoną architekturę możecie zobaczyć poniżej.

W tym podejściu pomijamy całą warstwę hurtowni danych i odpytujemy źródło bezpośrednio z raportu, bądź też dokładamy warstwę analityczną pod postacią kostki lub tabulara, które używając takich technologii jak Direct Query lub ROLAP odpytują źródło w trybie online. Przed wersją SQL Server 2016 implementacja takiej architektury technicznie rzecz biorąc również była możliwa – jednak zapytania raportujące zazwyczaj są na tyle “ciężkie”, że mogły mieć zbyt duży wpływ na normalne działanie systemu OLTP. w tym momencie po odpowiednim przygotowaniu bazy źródłowej z wykorzystaniem indeksacji columnstore i np. in-memory oltp (o którym powiemy w ramach odrębnego artykułu) możemy osiągnąć pożądany efekt.

Sprawdźmy działanie opisywanej technologii na przykładzie. Na początku tradycyjnie stwórzmy sobie testową bazę danych:

CREATE DATABASE RT_OperationalAnalytics
GO

Następnie skopiujmy sobie jedną z istniejących tabel bazy WideWorldImportersDW do naszej testowej bazy.

USE RT_OperationalAnalytics
GO

SELECT
*
INTO
Orders
FROM
WideWorldImportersDW.Fact.[Order]

Następnie stwórzmy sobie klasyczny indeks rowstore oparty o b-drzewo o nazwie CI_Orders, a potem indeks nieklastrowany typu Columnstore o nazwie NCCI_Orders.

CREATE CLUSTERED INDEX [CI_Orders] ON [dbo].[Orders]
 	 	(
 	 	 [Order Key] ASC
 	 	)
GO

CREATE NONCLUSTERED COLUMNSTORE INDEX [NCCI_Orders] ON [dbo].[Orders]
 	 	(
 	 	 [Order Key],
 	 	 [City Key],
 	 	 [Customer Key],
 	 	 [Stock Item Key],
 	 	 [Order Date Key],
 	 	 [Picked Date Key],
 	 	 [Salesperson Key],
 	 	 [Picker Key],
 	 	 [WWI Order ID],
 	 	 [WWI Backorder ID],
 	 	 [Description],
 	 	 [Package],
 	 	 [Quantity],
 	 	 [Unit Price],
 	 	 [Tax Rate],
 	 	 [Total Excluding Tax],
 	 	 [Tax Amount],
 	 	 [Total Including Tax],
 	 	 [Lineage Key]
 	 	)
WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0)

To na co warto zwrócić uwagę w powyższym zapytaniu tworzącym indeks kolumnowy jest wskazówka COMPRESSION_DELAY która mówi ile czasu w minutach ma minąć zanim zamknięte grupy wierszy poddane zostaną kompresji. Po stworzeniu indeksów sprawdźmy sobie plany wykonania zapytań na nowo powstałej tabeli.

SELECT 
   [Picked Date Key],
   [Salesperson Key],
   SUM(Quantity)
 FROM
   [dbo].[Orders]
 GROUP BY
   [Picked Date Key],
   [Salesperson Key]

Plan ten nie charakteryzuje się niczym szczególnym – aby pobrać dane optymalizator zdecydował się na użycie operatora Columnstore Index Scan( przypomnijmy, że w przypadku indeksów kolumnowych to jedyny możliwy operator – nie występuje Index Seek). Następnie wykonajmy to samo zapytanie tym razem wymuszając użycie indeksu klastrowanego lub innymi słowy zignorowanie indeksu kolumnowego:

SELECT 
 	[Picked Date Key],
 	[Salesperson Key],
 	SUM(Quantity)
FROM
        [dbo].[Orders]
GROUP BY
        [Picked Date Key],
        [Salesperson Key]
OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX)

Aby wymusić na optymalizatorze to żeby zignorował indeks kolumnowy użyłem wskazówki IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX. Jak widać oba indeksy mogą ze sobą koegzystować. Natomiast spróbujmy zaimplementować indeks kolumnowy w taki sposób aby obejmował “zimne dane” – zrobimy to wpisując w definicji indeksu warunek filtrujący.

CREATE NONCLUSTERED COLUMNSTORE INDEX [NCCI_Orders] ON [dbo].[Orders]
(
 	 	 [Order Key],
 	 	 [City Key],
 	 	 [Customer Key],
 	 	 [Stock Item Key],
 	 	 [Order Date Key],
 	 	 [Picked Date Key],
 	 	 [Salesperson Key],
 	 	 [Picker Key],
 	 	 [WWI Order ID],
 	 	 [WWI Backorder ID],
 	 	 [Description],
 	 	 [Package],
 	 	 [Quantity],
 	 	 [Unit Price],
 	 	 [Tax Rate],
 	 	 [Total Excluding Tax],
 	 	 [Tax Amount],
 	 	 [Total Including Tax],
 	 	 [Lineage Key]

)
  WHERE [Order Date Key]<'20160101'
  WITH (DROP_EXISTING = ON, COMPRESSION_DELAY = 10)

Jak widać powyżej założyliśmy, iż w dużej mierze modyfikowane będą dane w bieżącym roku (na moment pisania artykułu był to rok 2016 – oczywiście w warunkach produkcyjnych warunek ten powinien być dynamiczny). Dodatkowo opóźniliśmy etap kompresji o 10 minut. W przypadku zapytań odpytujących mechanizm działania jest dosyć jasny: optymalizator ma zadanie oszacować czy bardziej wydajne będzie pobranie danych z columnstore czy też z rowstore. Jak możecie się domyślać operacje wyszukujące stosunkowo małe ilości wierszy oparte o przeszukiwanie (Seek) dużo bardziej wydajne będzie w tradycyjnym b-drzewie. Zapytania które z kolei agregują ogromne ilości danych lepiej będą przetwarzane z wykorzystaniem indeksu kolumnowego.

Największa przeszkodą, która pojawia się na horyzoncie w momencie gdy chcemy używać indeksów kolumnowych w systemach OLTP są operacje wstawiania, modyfikacji oraz usuwania danych – zachowanie w przypadku tych operacji SQL Server jest naprawdę ciekawe. Wstawmy pojedynczy wiersz, który jest zawarty zarówno w indeksie kolumnowym jak i tradycyjnym wierszowym – wybrałem do tego wiersz z identyfikatorem zamówienia równym pięć. Dodatkowo musiałem włączyć możliwość wstawiania danych do kolumny ze zdefiniowaną autoinkrementacją co z kolei spowodowało konieczność wypisania wszystkich kolumn w definicji polecenia INSERT SELECT.

SET IDENTITY_INSERT [dbo].[Orders] ON
GO

INSERT INTO dbo.Orders
(
[Order Key]
      ,[City Key]
      ,[Customer Key]
      ,[Stock Item Key]
      ,[Order Date Key]
      ,[Picked Date Key]
      ,[Salesperson Key]
      ,[Picker Key]
      ,[WWI Order ID]
      ,[WWI Backorder ID]
      ,[Description]
      ,[Package]
      ,[Quantity]
      ,[Unit Price]
      ,[Tax Rate]
      ,[Total Excluding Tax]
      ,[Tax Amount]
      ,[Total Including Tax]
      ,[Lineage Key]
)
SELECT [Order Key]
      ,[City Key]
      ,[Customer Key]
      ,[Stock Item Key]
      ,[Order Date Key]
      ,[Picked Date Key]
      ,[Salesperson Key]
      ,[Picker Key]
      ,[WWI Order ID]
      ,[WWI Backorder ID]
      ,[Description]
      ,[Package]
      ,[Quantity]
      ,[Unit Price]
      ,[Tax Rate]
      ,[Total Excluding Tax]
      ,[Tax Amount]
      ,[Total Including Tax]
      ,[Lineage Key]
 FROM [dbo].[Orders]
WHERE [Order Key]=5

Spójrzmy na plan wykonania powyższego zapytania ( do wizualizacji tym razem użyłem Plan Explorera na którym ten plan jest dużo bardziej czytelny)

Jak widać SQL Server wykonał następującą sekwencję kroków (pomijając Compute Scalar, który w tym wypadku nie jest dla nas interesujący, a odpowiada za ustawienie identity):

1. Wyszukał z tradycyjnego indeksu tabeli Orders ten wiersz który nas interesuje
2. Zbuforował sobie pobraną wartość w tabeli TempDb używając operatora Table Spool typu Eager
3. Wstawił do tradycyjnego indeksu zgrupowanego pożądaną wartość
4. Pobrał wcześniej zbuforowaną wartość i odfiltrował na podstawie warunku filtrującego zawartego w definicji indeksu kolumnowego (właściwy predykat można znaleźć we właściwościach operatora, który został przedstawiony poniżej)
5. Następnie wstawił te dane, które spełniały predykat do indeksu kolumnowego (ściślej rzecz biorąc do delta store)

Obok każdej ze strzałek łączących operatory możemy zauważyć, że cały czas działamy na jednym wierszu. W tym miejscu może pojawiać się pytanie co się stanie w przypadku gdy chcemy wstawić dane, które nie są zgodne z predykatem indeksu kolumnowego? Sprawdźmy to – tym razem aby niepotrzebnie rozszerzać artykułu pokażę Wam jedynie plan wykonania zapytania.

Jak widać na pierwszy rzut oka wszystko wygląda niemal identycznie – jednakże jak możemy się domyślać żaden wiersz nie przeszedł z operatora Filter do Columnstore Index Insert. W przypadku operacji delete oraz update operacje również są wykonywane w sposób niemal przezroczysty dla użytkowników. Ze względu na to, iż obie te operacje wymagają wprowadzenia nowych pojęć omówię je w osobnym artykule w całości poświęconym nowościom w indeksach kolumnowych w SQL Server 2016.

Podsumowując niniejszy artykuł – Microsoft wprowadzając możliwość tworzenia filtrowanych niezgrupowanych indeksów kolumnowych, które jednocześnie mogą koegzystować z tradycyjnymi indeksami b-drzewa. Podejście to daje nam możliwość takiego rozplanowania naszej bazy danych, aby służyła ona zarówno celom analitycznym jak i raportowym. Oczywistym jest fakt, iż w ogromnej ilości przypadków takie podejście nie będzie możliwe do wdrożenia gdyż wymaga zbyt wielu zmian architektonicznych i wiąże się ze zbyt dużym narzutem na działania operacyjne jednakże moim zdaniem operational analytics jak najbardziej  ma rację bytu i poprzez rozszerzenie wachlarza możliwości może znaleźć zastosowanie w naszej codziennej pracy.