Začínáme s indexy columnstore pro provozní analýzy v reálném čase

platí pro:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL databáze v Microsoft Fabric

SQL Server 2016 (13.x) zavádí provozní analýzu v reálném čase, schopnost spouštět úlohy analýzy i OLTP na stejných databázových tabulkách současně. Kromě spouštění analýz v reálném čase můžete také eliminovat potřebu ETL a datového skladu.

Vysvětlení provozních analýz v reálném čase

Podniky tradičně měly samostatné systémy pro provozní úlohy (tj. OLTP) a analytické úlohy. U takových systémů úlohy extrakce, transformace a načítání (ETL) pravidelně přesouvají data z provozního úložiště do analytického úložiště. Analytická data se obvykle ukládají do datového skladu nebo datového tržiště vyhrazeného pro spouštění analytických dotazů. Toto řešení je sice standardní, ale má tyto tři klíčové výzvy:

• Komplexnost. Implementace ETL může vyžadovat značné kódování, zejména pro načtení pouze upravených řádků. Může být složité určit, které řádky byly změněny.
• Náklady. Implementace ETL vyžaduje náklady na nákup dalších hardwarových a softwarových licencí.
• Latence dat Implementace ETL přidává časové zpoždění pro spuštění analýzy. Pokud se například úloha ETL spustí na konci každého pracovního dne, analytické dotazy se budou spouštět na datech, která jsou alespoň den stará. U mnoha firem je toto zpoždění nepřijatelné, protože firma závisí na analýze dat v reálném čase. Například detekce podvodů vyžaduje analýzu provozních dat v reálném čase.

Provozní analýza v reálném čase nabízí řešení těchto problémů.

Při spouštění analytických úloh a úloh OLTP ve stejné podkladové tabulce nedochází ke zpoždění. Ve scénářích, které můžou používat analýzu v reálném čase, se náklady a složitost výrazně snižují tím, že eliminují potřebu ETL a potřebu nákupu a údržby samostatného datového skladu.

Poznámka:

Provozní analýzy v reálném čase cílí na scénář jednoho zdroje dat, jako je aplikace plánování podnikových zdrojů (ERP), na které můžete spustit provozní i analytickou úlohu. To nenahrazuje potřebu samostatného datového skladu, pokud potřebujete před spuštěním analytické úlohy integrovat data z více zdrojů nebo když vyžadujete extrémní výkon analýzy pomocí předem agregovaných dat, jako jsou datové krychle.

Analýzy v reálném čase používají aktualizovatelný neklastrovaný columnstore index v tabulce typu rowstore. Index columnstore udržuje kopii dat, takže úlohy OLTP a analýzy běží na samostatných kopiích dat. Tím se minimalizuje dopad na výkon obou úloh spuštěných současně. Databázový stroj automaticky udržuje změny indexu, takže změny OLTP jsou vždy up-to-datem analýzy. Díky tomuto návrhu je možné a praktické provádět analýzy v reálném čase na datech typu up-to. To funguje pro tabulky optimalizované pro disky i tabulky optimalizované pro paměť.

Příklad - Začněte

Začínáme s analýzou v reálném čase:

1. Identifikujte tabulky v provozním schématu, které obsahují data potřebná k analýze.

2. U každé tabulky zahoďte všechny indexy B-tree, které jsou primárně navržené tak, aby urychli stávající analýzy úloh OLTP. Nahraďte je jedním neklastrovaným indexem sloupcového úložiště. To může zlepšit celkový výkon úlohy OLTP, protože je potřeba udržovat méně indexů.

   --This example creates a nonclustered columnstore index on an existing OLTP table.
   --Create the table
   CREATE TABLE t_account (
       accountkey int PRIMARY KEY,
       accountdescription nvarchar (50),
       accounttype nvarchar(50),
       unitsold int
   );
   
   --Create the columnstore index with a filtered condition
   CREATE NONCLUSTERED COLUMNSTORE INDEX account_NCCI
   ON t_account (accountkey, accountdescription, unitsold)
   ;
   

   Index columnstore v tabulce optimalizované pro paměť umožňuje provozní analýzu integrací OLTP v paměti a technologií columnstore pro zajištění vysokého výkonu pro úlohy OLTP i analýzy. Index columnstore v paměťově optimalizované tabulce musí být seskupený index, jinými slovy, musí obsahovat všechny sloupce.

   -- This example creates a memory-optimized table with a columnstore index.
   CREATE TABLE t_account (
       accountkey int NOT NULL PRIMARY KEY NONCLUSTERED,
       Accountdescription nvarchar (50),
       accounttype nvarchar(50),
       unitsold int,
       INDEX t_account_cci CLUSTERED COLUMNSTORE
       )
       WITH (MEMORY_OPTIMIZED = ON );
   

Teď jste připraveni spustit provozní analýzy v reálném čase, aniž byste v aplikaci museli provádět žádné změny. Analytické dotazy se budou spouštět proti indexu columnstore a operace OLTP budou pracovat proti B-tree indexům OLTP. Úlohy OLTP nadále fungují, ale za účelem údržby indexu columnstore se účtují další režijní náklady. Podívejte se na optimalizace výkonu v další části.

Blogové příspěvky

Další informace o provozních analýzách v reálném čase najdete v následujících blogových příspěvcích. Pokud se nejprve podíváte na blogové příspěvky, může být snazší pochopit sekce s tipy na výkon.

• Obchodní případ pro provozní analýzy v reálném čase

• Použití neclusterovaného indexu columnstore pro provozní analýzy v reálném čase

• Jednoduchý příklad použití neclusterovaného indexu columnstore

• Jak SQL Server udržuje neclusterovaný index columnstore v transakční úloze

• Minimalizace dopadu údržby neclusterovaného indexu columnstore pomocí filtrovaného indexu

• Pomocí zpoždění komprese minimalizovat dopad údržby neklastrovaného indexu sloupcového úložiště

• Minimalizace dopadu údržby neklastrovaného columnstore indexu pomocí zpoždění komprese – výkonnostní údaje

• Provozní analýza v reálném čase s tabulkami optimalizovanými pro paměť

• Columnstore index a zásady sloučování pro skupiny řádků

Videa

Videoseriál Data Exposed se ponoří do více podrobností o některých možnostech a úvahách.

• Část 1: Jak Azure SQL umožňuje provozní analýzu v reálném čase (HTAP)
• Část 2: Optimalizace existujících databází a aplikací pomocí provozní analýzy
• Část 3: Vytvoření provozní analýzy pomocí funkcí Windows

Tip k výkonu č. 1: Použití filtrovaných indexů ke zlepšení výkonu dotazů

Spouštění provozních analýz v reálném čase může mít vliv na výkon úlohy OLTP. Tento dopad by měl být minimální. Příklad A ukazuje, jak pomocí filtrovaných indexů minimalizovat dopad neclusterovaného indexu columnstore na transakční úlohy a přitom stále poskytovat analýzy v reálném čase.

Pokud chcete minimalizovat režijní náklady na údržbu neklastrovaného columnstore indexu v provozní úloze, můžete pomocí filtrované podmínky vytvořit neklastrovaný columnstore index pouze na teplých nebo pomalu se měnících datech. Například v aplikaci pro správu objednávek můžete vytvořit neclusterovaný index columnstore u objednávek, které už byly odeslány. Jakmile je objednávka odeslána, zřídka se změní, a proto se dají považovat za teplá data. U filtrovaného indexu vyžadují data v neclusterovaném columnstore indexu méně aktualizací, což snižuje dopad na transakční zátěž.

Analytické dotazy transparentně přistupují k teplým i horkým datům podle potřeby, aby poskytovaly analýzu v reálném čase. Pokud se významná část provozní úlohy týká "horkých" dat, tyto operace nevyžadují další údržbu indexu columnstore. Osvědčeným postupem je mít clusterovaný index rowstore ve sloupcích používaných v definici filtrovaného indexu. Databázový stroj používá clusterovaný index k rychlé kontrole řádků, které nesplňují filtrovanou podmínku. Bez tohoto clusterovaného indexu se k vyhledání těchto řádků vyžaduje úplná kontrola tabulky rowstore, která může negativně ovlivnit výkon analytických dotazů. Bez clusterovaného indexu byste mohli vytvořit doplňkový filtrovaný neclusterovaný index B-tree, který identifikuje takové řádky, ale nedoporučuje se, protože přístup k velkému rozsahu řádků prostřednictvím neclusterovaných indexů B-tree je nákladný.

Poznámka:

Filtrovaný neclusterovaný index columnstore je podporován pouze v tabulkách založených na disku. Nepodporuje se u tabulek optimalizovaných pro paměť.

Příklad A: Přístup k horkým datům z indexu B-tree, teplá data z indexu columnstore

Tento příklad používá filtrovanou podmínku (accountkey > 0) k určení řádků, které jsou zahrnuty v indexu columnstore. Cílem je navrhnout filtrovanou podmínku a následné dotazy pro přístup k často se měnícím "horkým" datům z indexu stromu B+ a získat přístup k stabilnějším "teplým" datům z indexu columnstore.

Poznámka:

Optimalizátor dotazů zvažuje, ale ne vždy vybere index typu columnstore pro plán dotazu. Když optimalizátor dotazů zvolí filtrovaný index columnstore, transparentně kombinuje řádky z indexu columnstore i řádky, které nesplňují filtrovanou podmínku, aby umožňovaly analýzu v reálném čase. To se liší od běžného neclusterovaného filtrovaného indexu, který lze použít pouze v dotazech, které se omezují na řádky, které jsou přítomné v indexu.

-- Use a filtered condition to separate hot data in a rowstore table
-- from "warm" data in a columnstore index.

-- create the table
CREATE TABLE orders (
AccountKey int not null,
CustomerName nvarchar (50),
OrderNumber bigint,
PurchasePrice decimal (9,2),
OrderStatus smallint not null,
OrderStatusDesc nvarchar (50)
);

-- OrderStatusDesc  
-- 0 => 'Order Started'  
-- 1 => 'Order Closed'  
-- 2 => 'Order Paid'  
-- 3 => 'Order Fulfillment Wait'  
-- 4 => 'Order Shipped'  
-- 5 => 'Order Received'  

CREATE CLUSTERED INDEX orders_ci ON orders(OrderStatus);

--Create the columnstore index with a filtered condition
CREATE NONCLUSTERED COLUMNSTORE INDEX orders_ncci ON orders  (accountkey, customername, purchaseprice, orderstatus)
WHERE OrderStatus = 5;

-- The following query returns the total purchase done by customers for items > $100 .00
-- This query will pick  rows both from NCCI and from 'hot' rows that are not part of NCCI
SELECT TOP (5) CustomerName, SUM(PurchasePrice)
FROM orders
WHERE PurchasePrice > 100.0
GROUP BY CustomerName;

Analytický dotaz se spustí s následujícím plánem dotazu. Můžete vidět, že řádky, které nesplňují filtrovanou podmínku, jsou přístupné prostřednictvím clusterovaného indexu B-tree.

Další informace najdete v tématu Blog: Filtrovaný neklastrovaný columnstore index.

Tip k výkonu č. 2: Přesměrování zpracování analýz na sekundární čitelné funkce AlwaysOn

I když můžete minimalizovat údržbu indexu columnstore pomocí filtrovaného indexu columnstore, analytické dotazy můžou stále vyžadovat významné výpočetní prostředky (procesor, vstupně-výstupní operace, paměť), které mají vliv na výkon provozní úlohy. U většiny důležitých úloh doporučujeme použít konfiguraci AlwaysOn. V této konfiguraci můžete eliminovat dopad spouštění analýz tím, že ji přenesete na čitelnou sekundární repliku.

Tip k výkonu č. 3: Snížení fragmentace indexu udržováním aktivních dat v deltových skupinách řádků

Tabulky s indexem columnstore se můžou výrazně fragmentovat (tj. odstraněné řádky), pokud úloha aktualizuje nebo odstraní řádky, které byly komprimované. Fragmentovaný index columnstore vede k neefektivnímu využití paměti nebo úložiště. Kromě neefektivního využití prostředků má také negativní vliv na výkon analytického dotazu z důvodu dodatečných vstupně-výstupních operací a nutnosti filtrovat odstraněné řádky ze sady výsledků.

Odstraněné řádky nejsou fyzicky odebrány, dokud nespustíte defragmentaci indexu pomocí REORGANIZE příkazu nebo znovu sestavte index columnstore v celé tabulce nebo ovlivněných oddílech. Index REORGANIZE a REBUILD jsou nákladné operace, které odebírají prostředky, které by jinak byly použity pro úlohu. Navíc pokud jsou řádky komprimované příliš brzy, může být nutné je několikrát znovu zkomprimovat kvůli aktualizacím, které vedou k plýtvání režijními náklady na kompresi.

Fragmentaci indexu můžete minimalizovat pomocí COMPRESSION_DELAY možnosti.

-- Create a sample table
CREATE TABLE t_colstor (
accountkey int not null,
accountdescription nvarchar (50) not null,
accounttype nvarchar(50),
accountCodeAlternatekey int
);

-- Creating nonclustered columnstore index with COMPRESSION_DELAY. 
-- The columnstore index will keep the rows in closed delta rowgroup 
-- for 100 minutes after it has been marked closed.
CREATE NONCLUSTERED COLUMNSTORE INDEX t_colstor_cci ON t_colstor
(accountkey, accountdescription, accounttype)
WITH (DATA_COMPRESSION = COLUMNSTORE, COMPRESSION_DELAY = 100);

Další informace najdete v tématu Blog: Zpoždění komprese.

Tady jsou doporučené osvědčené postupy:

• Úloha vložení/dotaz: Pokud vaše úloha primárně vkládá data a dotazuje se na ni, doporučujeme použít výchozí COMPRESSION_DELAY hodnotu 0. Nově vložené řádky se zkomprimují, jakmile se do jedné delta skupiny řádků vloží 1 milion řádků. Mezi příklady takových úloh patří tradiční úloha DW nebo analýza výběrového datového proudu, když potřebujete analyzovat výběrový vzor ve webové aplikaci.

• Pracovní zátěž OLTP: Pokud je úloha DML intenzivní (tj. intenzivní kombinace operací Aktualizace, Odstranění a Vložení), můžete zjistit fragmentaci indexu columnstore prozkoumáním dynamických pohledů správy (DMV) sys.dm_db_column_store_row_group_physical_stats. Pokud vidíte, že > 10% řádků je v nedávno komprimovaných skupinách řádků označeno jako odstraněné, můžete pomocí COMPRESSION_DELAY možností přidat časové zpoždění, když jsou řádky způsobilé ke kompresi. Pokud například pro vaši úlohu nově vložené zůstane „horké“ (to znamená, že se aktualizuje vícekrát) po dobu 60 minut, měli byste zvolit hodnotu COMPRESSION_DELAY jako 60.

Výchozí hodnota COMPRESSION_DELAY možnosti by měla fungovat pro většinu zákazníků.

Pro pokročilé uživatele doporučujeme spustit následující dotaz a shromáždit % odstraněných řádků za posledních 7 dnů.

SELECT row_group_id,
       CAST(deleted_rows AS float)/CAST(total_rows AS float)*100 AS [% fragmented],
       created_time
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('FactOnlineSales2')
      AND state_desc = 'COMPRESSED'
      AND deleted_rows > 0
      AND created_time > DATEADD(day, -7, GETDATE())
ORDER BY created_time DESC;

Pokud počet odstraněných řádků v komprimovaných skupinách > řádků 20%, plateauing ve starších skupinách řádků s < 5% variantami (označuje se jako studená skupina řádků), pak nastavte COMPRESSION_DELAY = (youngest_rowgroup_created_time - current_time). Tento přístup funguje nejlépe se stabilní a relativně homogenní úlohou.

Související obsah

• Sloupcové indexy – přehled
• Sloupcové indexy – průvodce načítáním dat
• Sloupcové indexy – Výkon dotazů
• Sloupcové indexy v datových skladech
• Optimalizace údržby indexů za účelem zlepšení výkonu dotazů a snížení spotřeby prostředků