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: Novinky, IO, Díl, Index, Sloupec, Fragmentace, Značný rozdíl, Jednotlivé verze, Novi, Datový typ, CCI, SQL Server, Ratio, Značné snížení, Hraniční rychlost, SQL, Důležitý sloupec, Jimi, ETL, Následující příklad, Zásadní vylepšení, Zásadní rozdíl, Důležitý faktor, Jednotlivý díl, Velká výhoda


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

Nejlepší notebooky do 10 000 korun: Co má ještě smysl kupovat. A co ne?

Nejlepší notebooky do 10 000 korun: Co má ještě smysl kupovat. A co ne?

** Notebooky s cenou do deseti tisíc korun jsou plné kompromisů ** Existuje několik modelů dobře použitelných pro nenáročné použití ** Vhodnou alternativou jsou tablety nebo repasované počítače

David Polesný | 94

David Polesný
Jak vybrat notebookNotebooky
Fedora 33 prostě funguje. Linux si zaslouží dobýt laptop, je to ale asi opět marné

Fedora 33 prostě funguje. Linux si zaslouží dobýt laptop, je to ale asi opět marné

** Desktopový Linux funguje a vypadá stále lépe ** Fedora 33 není výjimkou ** Ve stínu Windows a macOS tu vyrostly skvělé alternativy

Jakub Čížek | 156

Jakub Čížek
FedoraOperační systémyLinux
Google spouští vlastní VPN a konkurenci se to vůbec nelíbí
Lukáš Václavík
SoukromíVPNGoogle
Vyšel Windows 10 October 2020 Update. Poradíme, jak je stáhnout a co je nového
Vladislav Kluska
October 2020 UpdateWindows 10
Google vymyslel technologii superpřesného GPS. Už ji podporuje Pixel 5 a dorazí i na ostatní telefony

Google vymyslel technologii superpřesného GPS. Už ji podporuje Pixel 5 a dorazí i na ostatní telefony

** Kvalita GPS ve městech občas stojí za starou bačkoru ** Mohou za to odrazy signálu od okolních budov ** Google má jejich 3D model, a tak spolupracuje s výrobci GPS čipů

Jakub Čížek | 40

Jakub Čížek
NavigaceTechnologieGoogle
Starlink podle betatesterů: Rychlejší a levnější než satelitní internet v Česku

Starlink podle betatesterů: Rychlejší a levnější než satelitní internet v Česku

** Reddit se začíná plnit zkušenostmi se Starlinkem ** Při přímé viditelnosti dá i 120 Mb/s ** Klasický satelitní internet už teď dalece překonává

Jakub Čížek | 48

Jakub Čížek
StarlinkPoskytovatelé internetu
Velký den pro Apple: Uvedl tři nové Macy s vlastním procesorem M1
Lukáš Václavík
PočítačeApple
Čím nahradit WhatsApp: Vyberte si z 10 alternativních komunikátorů

Čím nahradit WhatsApp: Vyberte si z 10 alternativních komunikátorů

** Z WhatsAppu kvůli novým podmínkám utíkají tisíce uživatelů ** Čím nahradit populární aplikaci pro zasílání zpráv? ** Vybrali jsme pro vás 10 alternativních komunikátorů

Karel Kilián | 79

Karel Kilián
KomunikaceWhatsAppInstant Messaging

Aktuální číslo časopisu Computer

Jak prodloužit výdrž notebooku

Velké testy: gamepady a inkoustové tiskárny

Důkladný test Sony Playstation 5