MS SQL Server 2011 column store index

Premiera MS SQL Server 2011 zapowiedziana jest na listopad. Już teraz można zapoznać się z nową wersją popularnej bazy i zobaczyć na własne oczy nowości które wprowadza. Co prawda nie ma takiego postępu jak między wersją 2000 a 2005 jednak i tak sporo nowinek czeka zarówno DBA jak i deweloperów. Dzisiaj napiszę o jednej nowości która z pewnością przyda się w pracy z hurtowaniami danych - lub bardzo dużymi zbiorami danych. Nowa wersja serwera SQL wprowadzi tzw. column storing. Co kryje się pod tą nazwą? Spójrzmy na rysunek poniżej:

Rysunek przedstawia klasyczne podejście do przechowywania danych. Podstawową jednostką jest tutaj strona, strona przechowuje dane które są w postaci wierszy. Możemy nazwać ten sposób jako row store. Podczas tworzenia zapytań, i tak mimowolnie odczytujemy dane z innych kolumn, chociaż tak na prawdę nie są nam one do niczego potrzebne. Co za tym idzie zwiększa się ilość operacji I/O, wzrasta zapotrzebowanie na pamięć RAM. Przechowywanie kolumnowe (column store) przełamuje dotychczasowe zasady przechowywania danych w wiersza, na przechowywanie w kolumnach. Spójrzmy znów na pomocniczy rysunek:

Tak więc wybrane kolumny zapisywane są na pojedynczych stronach. Oprócz tego, że dane zapisywane są na stronach kolumnowo, zwiększa się możliwość ich kompresji - co wpływa znacząco na obniżenie ilości zajmowanego miejsca przez indeks. Column store index: - indeks który przechowuje dane w postaci kolumnowej, w przeciwieństwie do modelu wierszowego - dane są czytane tylko z wybranych kolumn co zmniejsza ilość operacji I/O Składnia polecenia tworzącego ten indeks jest następująca:

CREATE NONCLUSTERED COLUMNSTORE INDEX [nazwa_indeksu] ON [nazwa_tabeli] ([kolumny])

Po nałożeniu tego indeksu z tabeli nie można usuwać, dodawać ani modyfikować rekordów. Próba takich operacji skutkuje odpowiednim błędem. Jeżeli chcemy mieć możliwość dodawania operacji możemy wyłączyć na czas operacji indeks a później znów go nałożyć:

ALTER INDEX [nazwa] ON [tabela] DISABLE;

Operacje

ALTER INDEX [nazwa] ON [tabela] REBUILD;

Index ten ma oczywiście pewne ograniczenia o których warto wiedzieć, zanim przystąpi się do pracy z nim:
- nie ma możliwości utworzenia CLUSTERED INDEX tego typu
- tylko NONCLUSTERED index jest dozwolony
- nie ma możliwości zawierania kolumn w takim indeksie
- nie jest dostępny dla kolumn wyliczeniowych
- można utworzyć tylko 1 taki indeks

Stosowanie tego indeksu jest zalecane w następujących sytuacjach:
- hurtownie danych
- duża kompresja wartości klucza dla indeksu
- mniejsze zużycie pamięci, dysku, czasu procesora podczas wykonywania zapytań, co zwiększa możliwości zgromadzenia w cache większej ilości zapytań
- bardzo szybkie otrzymanie wyników podczas wykorzystania tego indeksu na dużych zbiorach danych .

Na koniec warto obejrzeć ten filmik, prezentujący jak sprawuje się nowy typ indeksu w pracy z tabelą w której znajdują się 2 miliardy wierszy:

Źródło: http://social.technet.microsoft.com/wiki/contents/articles/sql-server-columnstore-index-faq.aspx