PowerPivot's Blog Techniczny blog na temat PowerPivot

Columnstore index–nowość SQL Server 2012, czyli trochę o VertiPaq

Blog o PowerPivot, a post o Columnstore… Tak dokładnie. Takie miałem zamierzenie. PowerPivot był nowością wprowadzającą w rozwiązaniu SQL Server jako pierwsze mechanizm VertiPaq. Przy nowej edycji SQL Server 2012 – o nazwie kodowej Denali powstaje cała ścieżka rozwiązań BI bazująca lub wykorzystująca mechanizm VertiPaq:

• Model BISM (Business Intelligence Semantic Model) w którym możemy projektować rozwiązania BI
• PowerPivot w wersji 2 (w tym Analysis Services Integreted dla SharePoint 2010 czyli PowerPivot dla SharePoint))
• PowerView (znany pod nazwą kodową Projekt Crescent) – nowoczesne narzędzie do wizualizacji danych (w tym danych z PowerPivot i BISM)
• SQL Server Analysis Services Tabular Mode
• Nowe indeksy w SQL Server 2012 – Columnstore Index (projekt Apollo)

Za całą pewnością właśnie te tematy będą gościć na tym blogu. Na pierwszy ogień na moim blogu biorę właśnie nowe indeksy w SQL Server 2012. Jestem pod wrażeniem tego mechanizmu i chciałem się tym podzielić z moimi czytelnikami.

Wprowadzenie do indeksów Columnstore

Dotychczasowo wszystkie dane SQL Server przechowywał na stronach wierszami, ostatnich wersjach SQL Server  2008/2008R2 umożliwiał już kompresję danych, co dawało bardzo często dobre efekty. W rozwiązaniu VertiPaq wymyślono, iż dane będą przechowywane nie wierszami, ale kolumnami Co to daje? Otóż jeżeli posiadamy miliony rekordów dotyczących np.: ceny sprzedaży, albo ilości sprzedaży można sobie wyobrazić jak często się te wartości powtarzają, ponadto należą do tego samego typu przez co można wykonać o wiele bardziej efektywną kompresję tych danych. Kolejną zaletą jest fakt, iż w danych analitycznych rzadziej pobieramy pojedyncze wiersze, a częściej wykonujemy różnego rodzaju agregaty właśnie na poszczególnych kolumnach.

Wszystkie dane silnik SQL Server umieszcza na stronach wierszami

w przypadku COLUMNSTORE (czyli wykorzystując mechanizm VertiPaq) dane umieszczone są kolumnami:

Co jeszcze daje nam taka zmiana? Po pierwsze zmniejsza nam ilość operacji I/O zarówno ze względu na wielkość danych, po drugie pobiera jedynie te kolumny, które akurat potrzebuję do danego zapytania, ale także ze względu na fakt, iż SQL Server odczytuje dane pewnym obszarami (w przypadku COLUMNSTORE mówi się segmentami) i ze względu na sposób położenia informacji SQL Server ma lepszą ilość trafień w zaczytanych danych z pewnym wyprzedzeniem, niż ma to miejsce przy odczycie zwykłych stron bazy danych.

Odczyt niezbędnych kolumn, a nie całych wierszy

Odczyt segmentów danych

Jednak to nie wszystkie zalety wykorzystywania indeksów typu COLUMNSTORE. Otóż w przypadku bardzo dużych zbiorów danych , dane przechowywane w indeks COLUMNSTORE mogą być przetwarzane w sposób wsadowy (BATCH MODE), daje to pełne wykorzystanie możliwości zrównoleglenie, gdyż każdy wsad jest niezależny od pozostałych operacji.

Ograniczenia

Struktura indeksów COLUMNSTORE ma pewne ograniczenia lub restrykcje, głównie wynikające z architektury tych indeksów:

• Dane w indeksie COLUMNSTORE nie mogą być modyfikowane!!!
• Nie może być w tym indeksie więcej niż 1024 kolumn
• Indeks ten jest jedynie nie klustrowanym indeksem
• Nie może być unikalnym
• Nie może być zakładany na widokach ani na widokach indeksowanych
• Nie można używać kolumn typu SPARSE
• Nie można zmieniać definicji indeksu pryz użyciu składni ALTER INDEX (można tym poleceniem jedynie przebudować i wyłączyć indeks)
• Nie ma takich opcji jak INCLUDE, ASC, DESC
• Nie wspiera mechanizmu SEEK (inne zastosowanie jest tego indeksu)
• Nie mechanizmów (kompresji – posiada własną; replikacji, CHANGE DATA CAPTURE, CHANGE TRACKING – jest tylko do odczytu; FILESTREAM)

Jak widać stosując taki indeks do hurtowni danych ograniczenia te nie są straszne. Część z nich jest wręcz oczywista ze względu na omówioną wcześniej architekturę.

Podsumowanie

Jak widać mechanizm ten jest bardzo ciekawy i daje naprawdę niesamowite rezultaty. Wykonałem już pierwsze testy na różnej ilości danych.  W kolejnych postach już niebawem na tym blogu przedstawię jak tworzyć COLUMNSTORE index, oraz omówię i przedstawię rezultaty testów jakie wykonałem.