Columnstore a widoki indeksowane

Columnstore a widoki indeksowane to zależność między kolumnowym formatem indeksu a materializowanymi widokami bazy danych.

Dwa różne sposoby materializacji danych

Columnstore i widok indeksowany rozwiązują różne problemy, chociaż oba mogą ograniczać ilość pracy wykonywanej podczas zapytań. Indeks columnstore przechowuje dane według kolumn, a nie według całych wierszy. W praktyce wartości jednej kolumny są grupowane razem, kompresowane i udostępniane silnikowi zapytań w formie dogodnej dla skanowania wybranych atrybutów. Taki układ jest szczególnie przydatny podczas agregacji dużej liczby rekordów, gdy zapytanie odczytuje niewiele kolumn z szerokiej tabeli.

Widok indeksowany w Microsoft SQL Server jest natomiast widokiem, którego wynik został fizycznie zapisany dzięki utworzeniu unikatowego indeksu klastrowanego. Silnik nie musi za każdym razem wykonywać całej definicji widoku, ponieważ może odczytać wcześniej obliczone połączenia, filtry lub agregaty. Widok indeksowany nadal podlega ograniczeniom składniowym i deterministycznym wymaganiom SQL Server. Nie jest dowolnym buforem zapytania ani ogólnym mechanizmem przechowywania wyników każdego polecenia SELECT.

Indeks columnstore na wyniku widoku

W SQL Server widok indeksowany musi najpierw uzyskać unikatowy indeks klastrowany. Dopiero istniejąca, fizycznie zmaterializowana reprezentacja widoku może być dodatkowo rozpatrywana jako obiekt dla indeksu columnstore, zależnie od używanej wersji silnika i ograniczeń dotyczących konkretnej definicji widoku. W typowym układzie indeks klastrowany zapewnia materializację i identyfikację wierszy, a dodatkowy indeks columnstore tworzy kolumnową ścieżkę dostępu do tych samych danych.

Takie połączenie nie oznacza, że widok automatycznie staje się tabelą analityczną bez kosztów utrzymania. Każda zmiana w tabelach bazowych może wymagać aktualizacji zmaterializowanego wyniku oraz powiązanych indeksów. Przy dużej liczbie operacji INSERT, UPDATE i DELETE dodatkowe struktury zwiększają koszt zapisu. Przed zastosowaniem należy więc porównać czas zapytań odczytowych z narzutem aktualizacji, rozmiarem danych i częstotliwością modyfikacji.

Wybór ścieżki przez optymalizator zapytań

Obecność indeksu columnstore na widoku nie gwarantuje jego wykorzystania. Optymalizator SQL Server analizuje predykaty, agregacje, dostępne indeksy, statystyki oraz zgodność zapytania z definicją widoku. Jeżeli zapytanie odwołuje się do tabel bazowych, silnik może rozważyć automatyczne dopasowanie do widoku indeksowanego, ale możliwość i sposób takiego dopasowania zależą między innymi od edycji oraz konfiguracji SQL Server. Wymuszenie rozważenia indeksowanego widoku można uzyskać przez wskazówkę NOEXPAND używaną przy odwołaniu do widoku.

Do oceny efektu nie wystarcza sprawdzenie, czy plan zawiera słowo „columnstore”. Należy porównać plan wykonania przed i po zmianie, liczbę odczytanych stron, czas procesora, czas trwania, liczbę przetworzonych wierszy oraz koszt operacji modyfikujących dane. Warto również sprawdzić, czy zapytanie korzysta z przetwarzania wsadowego, czy występują kosztowne konwersje typów oraz czy filtr może zostać zastosowany na wczesnym etapie. Indeks columnstore daje największą korzyść wtedy, gdy rzeczywiście ogranicza zakres odczytu i pozwala efektywnie wykonać operacje analityczne.

Ograniczenia projektowe i diagnostyka

Projekt widoku indeksowanego musi spełniać reguły SQL Server dotyczące między innymi deterministyczności wyrażeń, schematu obiektów, typów danych, konstrukcji JOIN oraz wymaganych ustawień sesji. Nie każda definicja widoku może zostać zindeksowana. Dodatkowo zastosowanie indeksu columnstore trzeba oceniać w odniesieniu do klucza indeksu klastrowanego, kardynalności, rozkładu wartości i przewidywanego sposobu filtrowania. Mały, często zmieniany widok może nie odnieść korzyści z kolumnowego formatu, nawet jeśli jego źródłowa tabela jest duża.

Diagnostyka powinna rozpocząć się od sprawdzenia definicji widoku, istniejących indeksów i statystyk oraz rzeczywistych planów wykonania. Następnie należy wykonać testy zarówno dla zapytań raportowych, jak i dla operacji zapisujących dane. Szczególnej uwagi wymagają zmiany schematu tabel bazowych, przebudowy indeksów, fragmentacja oraz aktualność statystyk. W środowisku produkcyjnym warto także obserwować blokady i czas trwania transakcji, ponieważ utrzymywanie materializowanego wyniku może zmieniać charakter obciążenia całej bazy. W przypadku problemów z wydajnością komputera używanego do pracy z narzędziami bazodanowymi analizuje się również pamięć operacyjną, nośnik danych i obciążenie procesora, ale nie zastępuje to analizy planu zapytania oraz konfiguracji SQL Server.