V minulém díle seriálu o novinkách SQL Serveru 2014 jsme si představili několik zásadních vylepšení, které přináší SQL Server 2014 v nové verzi. V tomto díle budeme pokračovat představením nových a vylepšených funkcí pro rychlejší práci s daty.

První z nich je columnstore index. Samotný columnstore index byl uveden již ve verzi SQL Server 2012, a to jako nonclustered index. Novinkou ve verzi 2014 je clustered columnstore index. V čem představuje clustered index tak zásadní rozdíl proti nonclustered indexu? Samotný index je stuktrura na disku, asociovaná s tabulkou nebo pohledem, která má za cíl zrychlit získání dat z database. Každý index obsahuje klíče složené z jednoho nebo vice sloupců, které jsou uloženy ve stromové struktuře (B-Tree), která umožnuje server vyhledat data podle daného klíče. V případě clustered indexu jsou data tabulky přímo uložena v datových stránkách indexu a podle klíče jsou I seřazena. Proto může být v tabulce vytvořen pouzen jeden clustered index. V případě, že v tabulce clustered index vytvořen není, jsou data uložena na disku nesetříděně ve struktuře nazvané heap. Naproti tomu nonclustered indexy maji svou strukturu oddělenou od dat, obsahují tedy pouze klíče a ukazatele na datové stránky existujícího clustered indexu, nebo heap alokační mapy.

NONCLUSTERED COLUMNSTORE INDEX

Nonclustered columnstore index (NCI) byl uveden ve verzi SQL Server 2012. Jedná se o nový doplňkový index, který neukládá do datových stránek jednotlivé řádky a jejich sloupce (rowstore) níbrž sloupce z jednotlivých řádků(columnstore). Velkou výhodou tohoto formátu uložení dat je možnost pro SQL Server pracovat pouze s požadovanými sloupci, je tedy možné číst z disku méně dat ve srovnání s tradičním rowstore přístupem. Dotazy bývají často optimalizovány, aby pracovaly pouze s nutnými sloupci a data na sloupcích, se kterými dotaz nepracuje nejsou čtena do paměti. Tím dochází k lepšímu využití buffer manageru a zvýšení cache hit ratio, protože v paměti  máme pouze ta data, která potřebujeme. Dalším důležitým faktorem, který zvyšuje rychlost zpracování dotazu je komprese sloupců. V mnoha případech dochází k vysokému poměru komprese, protože sloupce obsahují obdobná data. Díky kompresi se zvyšuje rychlost zpracování dotazů, snižuje paměťová zátěž a snižuje se celkový počet IO operací.

Využití NCI je vhodné zejména u datových skladů, na velkých tabulkách faktů, kde může dojít ke značnému zrychlení dotazů, které využívají agregace nad danými sloupci. Využití těchto indexů nemá pouze výhody, ale jsou zde i značné limity. Prvním z nich je nemožnost upravovat data. Tabulka s vytvořeným nonclustered columnstore indexem je read-only. Jedinou možností je tedy index odstranit nebo deaktivovat a následně po úpravě dat index aktivovat a obnovit nebo znovu vytvořit index. NCI je tedy vhodný pouze na statická data. V případě ETL procesů je nutné zohlednit i nutnost deaktivovat index, aby mohla být data načtena do tabulky.

CLUSTERED COLUMNSTORE INDEX

Velkou novinkou ve verzi SQL 2014 je možnost vytvořit clustered columnstore index. Prvním rozdílem mezi nonclustered a clustered columnstore indexem je možnost měnit data. Index je použit jako primární úložiště pro tabulku, a jsou v tomto indexu zahrnuty všechny sloupce. Je-li vytvořen tento index, není možné v tabulce vytvářet jakékoli jiné indexy.

Pro dočasné uchování změněných dat používá clustered columnstore index tzv. DeltaStore. DeltaStore slouží k zrychlení zpracování dat a snížení fragmentace samotného indexu, kdy jednotlivá data při dotazu jsou kombinována z clustered indexu a deltastore. DeltaStore je dočasná tabulka na pozadí, do které jsou ukládány jednotlivé řádky při DML operacích (tj. Insert, update a delete). Jakmile počet řádků v deltastore dosáhne hraniční hodnoty, jsou tyto řádky automaticky přesunuty do columnstore indexu. Mezi další výhody CCI patří i nastavení nové kompresní metody nad tabulkou columnstore_archive, která dále zlepšuje možnosti komprimace dat v tomto novém sloupcovém uložení dat.

Využití columnstore indexu má i své nevýhody. Předně columnstore index není určen pro každý scénář, a je nutné zvážit, zda-li vyvtoření columnstore indexu bude mít vliv na rychlost zpracování konkrétních dotazů. Dále není možné využít columnstore index v případě, že tabulka obsahuje sloupce s následujícími datovými typy Text,ntext a image (tyto jsou již deprecated, neměly by se používat)

• Varchar(max)
• Rowversion
• Spatial typy (geometry, geography)
• HierarchyID
• XML

Nejen datové typy mohou znamenat problém s využitím columnstore indexu. Pokud se nejedná o proprietární řešení, tak je možné pomocí vazeb na další tabulku datové typy jako XML a spatial rešit uložením do dalších tabulek, ale columnstore index není možné využít i v případě některých databázových možností, které nám server nabízí. Columnstore index není kompatibilní s row a page kompresí, která umožňuje komprimovat data na disku a dosáhnout zajímavých úspor na fyzickém úložišti a také snížení IO operací. Což by se mohlo jevit jako problém, ale columnstore index sám kompresi jednotlivých sloupců obsahuje, jen používá jiný přístup než klasická datová komprese. Columnstore index není také možné vytvořit v případě, že tabulka obsahuje sloupce typu sparse.

Dále není možné použít columnstore index v případě, že je v databázi použita

• Replikace
• Change tracking
• Change data capture
• A Filestream

VÝKON

V databází AdventureWorks si můžeme ukázat jaký vliv může mit columnstore index na zpracování dotazů. Pro ilustraci použijeme tabulku o cca 20mil řádků (získaná kopií Sales.SalesOrderDetail v AdventureWorks2012).

V případě dotazu s využitím pouze klasických indexů je vidět, že SQL Server musí zpracovat velké množství dat, načtených datových stránek je v porovnání s columnstore indexem opravdu hodně a i doba zpracování dotazu je bez využití columnstore indexu mnohem delší.

V případě použití pouze klasických indexu bylo nutné provést 9x sken tabulky, a celkem provést 483692 logických IO operací pro čtení. U columnstore indexu stačilo pouhých 587 IO reads. Časové rozdíly jsou v značném nepoměru v razantní prospěch columnstore indexu. Ale to platí pouze v případě dotazů, které dokáží škálovatelnosti columnstore využít.

Nad tabulkou byl vytvořen nonclustered columnstore index pro tři sloupce, které jsou adresovány v dotazu.

Celkové statistiky pro následující příklad jasně ukazují výhody columnstore indexu pro agregované dotazy, zejména v případě DW scénárů.

ZÁVĚREM

Columnstore index je možné použít od SQL Server verze 2012 a nově ve verzi 2014 je tento index vylepšen a rozšířen tak, aby mohla být data v tabulce s tímto indexem upravována. Jedná se o funkcionalitu databáze, která vede k značné škálovatelnosti a lze díky tomuto indexu dosáhnout zkrácení času nutného pro zpracování dotazů, a také snížení IO operací na disku. Columnstore index, jak clustered tak nonclustered, je dostupný pouze u Enterprise edice.