Novinky v SQL Server 2014 - díl 2.

Microsoft vydal novou verzi databázového serveru SQL Server 2014. V seriálu si povíme, jak mnohé z nových funkcí využít pro zvýšení výkonu, spolehlivosti a bezpečnosti SQL Serveru.

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 databáze. 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 serveru 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).

Klepněte pro větší obrázek


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ší.

Klepněte pro větší obrázek

V případě použití pouze klasických indexů 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 ve 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.

Klepněte pro větší obrázek

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ů.

Klepněte pro větší obrázek


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.

Autor: Marek Chmel, MVP SQL Server, at&t
 

 

Články ze série Microsoft TechNet nevytváří redakce Živě.cz, ale partneři programu Microsoft TechNet. Jsou publikovány v rámci mediálního partnerství Živě.cz a společnosti Microsoft.

Váš názor Další článek: Chromebooky: mýty a skutečnost

Témata článku: Značný rozdíl, Fragmentace, Velká výhoda, Sloupec, Datový typ, Zásadní vylepšení, Maja, Index, Jednotlivý díl, Hraniční rychlost, Díl, Důležitý faktor, Následující příklad, Proprietární řešení, Důležitý sloupec, Jimi, Značné snížení, ETL, Ratio, Rozdíl, SQL, Jednotlivé verze, CCI, Zásadní rozdíl, Razantní snížení


Určitě si přečtěte

Velký test televizorů v Computeru: i levnější značky překvapily kvalitou obrazu

Velký test televizorů v Computeru: i levnější značky překvapily kvalitou obrazu

** Jak funguje biometrické zabezpečení ve Windows ** Nejlepší správci hesel ** Létáme v Microsoft Flight Simulator

Časopis Computer | 5

Nejlepší aplikace na předpověď počasí: Kde nejlépe zjistit, co zrovna chcete vědět

Nejlepší aplikace na předpověď počasí: Kde nejlépe zjistit, co zrovna chcete vědět

Obecných aplikací na předpověď počasí je nespočet, jenže často skončí jen u základní informace o počasí a nenabídnou odpovědi na řadu praktických otázek. A tak jsme si položili právě několik takových otázek a hledali aplikace či meteoslužby, které nám nejlépe odpoví.

Karel Kilián | 4

Není jen Flightradar: Našli jsme další aplikace pro sledování letadel, některé ukážou i víc

Není jen Flightradar: Našli jsme další aplikace pro sledování letadel, některé ukážou i víc

** 8 služeb pro sledování leteckého provozu ** Nejznámější je Flightradar24, ale alternativy leckdy prozradí více ** Letadla i v této pohnuté době čile létají a je co pozorovat

Karel Kilián | 14

Nejjednodušší cesta, jak nepřijít o data: nastavte si zálohování a zapomeňte

Nejjednodušší cesta, jak nepřijít o data: nastavte si zálohování a zapomeňte

** Přijít o důležitá data je jednodušší, než si umíte představit ** To, zda a jak snadno je získáte zpět, záleží především na vás ** Když si nastavíte zálohování, může to být otázka několik minut

Karel Kilián | 30

Nvidia představila grafické karty GeForce RTX 3090, RTX 3080 a RTX 3070. Známe české ceny

Nvidia představila grafické karty GeForce RTX 3090, RTX 3080 a RTX 3070. Známe české ceny

** Nvidia uvedla nové desktopové grafické karty GeForce RTX 3000 ** Jedná se o modely GeForce RTX 3070, 3080 a 3090 ** K výrobě se používá 8nm technologii od Samsungu

Karel Javůrek | 67

Google mapy, Seznam mapy, Apple mapy... Velké srovnání šesti internetových map. Kdo to dělá nejlépe?

Google mapy, Seznam mapy, Apple mapy... Velké srovnání šesti internetových map. Kdo to dělá nejlépe?

** Která klasická webová mapa se vám líbí nejvíce? ** Srovnali jsme šest velkých služeb v několika situacích ** Hlasujte v anketě

Jakub Čížek | 81

Filmové pirátství asi jen tak nezmizí. Když už musíte, stahujte bezpečně v Seedru

Filmové pirátství asi jen tak nezmizí. Když už musíte, stahujte bezpečně v Seedru

** Máme HBO Go, máme Netflix... ** Ale stejně krademe filmy a seriály ** Když už musíte, stahujte torrenty bezpečně v Seedru

Jakub Čížek | 141


Aktuální číslo časopisu Computer

Megatest televizí do 25 000 Kč

Nejlepší herní klávesnice

Srovnání správců hesel

Jak upravit fotky pro tisk