Az oszlopcentrikus indexek használatának első lépései valós idejű üzemeltetési elemzésekhez

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseFelügyelt Azure SQL-példánySQL-adatbázis a Microsoft Fabricben

Az SQL Server 2016 (13.x) valós idejű üzemeltetési elemzéseket vezet be, lehetővé teszi az elemzések és az OLTP számítási feladatok egyidejű futtatását ugyanazon adatbázistáblákon. Az elemzés valós idejű futtatása mellett az ETL és az adattárház igényét is kiküszöbölheti.

A valós idejű üzemeltetési elemzés magyarázata

A vállalatok hagyományosan külön rendszerekkel rendelkeztek az üzemeltetési (azaz az OLTP) és az elemzési számítási feladatok számára. Ilyen rendszerek esetén a kinyerési, átalakítási és betöltési (ETL) feladatok rendszeresen áthelyezik az adatokat az operatív tárolóból egy elemzési tárolóba. Az elemzési adatokat általában egy adattárházban vagy az elemzési lekérdezések futtatására kijelölt adattárházban tárolják. Bár ez a megoldás a standard, három fő kihívással rendelkezik:

• Complexity. Az ETL implementálása jelentős kódolást igényelhet, különösen a módosított sorok betöltéséhez. Bonyolult lehet azonosítani, hogy mely sorok lettek módosítva.
• Cost. Az ETL implementálásához további hardver- és szoftverlicencek vásárlásának költsége szükséges.
• Adatkésés. Az ETL implementálása időbeli késleltetést ad az elemzés futtatásához. Ha például az ETL-feladat minden munkanap végén fut, az elemzési lekérdezések legalább egy napos adatokon fognak futni. Sok vállalkozás esetében ez a késés elfogadhatatlan, mert az üzlet az adatok valós idejű elemzésétől függ. A csalásészleléshez például valós idejű elemzésre van szükség a működési adatokon.

A valós idejű üzemeltetési elemzés megoldást kínál ezekre a kihívásokra.

Ha az elemzések és az OLTP-számítási feladatok ugyanazon az alapul szolgáló táblán futnak, nincs idő késleltetés. A valós idejű elemzést használó forgatókönyvek esetében a költségek és az összetettség jelentősen csökken azáltal, hogy nincs szükség az ETL-re, és külön adattárházat kell vásárolni és fenntartani.

Note

A valós idejű üzemeltetési elemzés egyetlen adatforrás, például egy nagyvállalati erőforrás-tervezési (ERP) alkalmazás forgatókönyvét célozza meg, amelyen a működési és az elemzési számítási feladat is futtatható. Ez nem helyettesíti a különálló adattárház szükségességét, ha több forrásból származó adatokat kell integrálnia az elemzési számítási feladat futtatása előtt, vagy ha szélsőséges elemzési teljesítményt igényel előre összesített adatok, például kockák használatával.

A valós idejű elemzések frissíthető, nem klaszteres oszloptár-indexet használnak egy sortártáblán. Az oszlopcentrikus index megőrzi az adatok másolatát, így az OLTP és az elemzési számítási feladatok az adatok különálló másolatai között futnak. Ez minimalizálja az egyidejűleg futó mindkét számítási feladat teljesítményre gyakorolt hatását. Az adatbázismotor automatikusan fenntartja az indexváltozásokat, így az OLTP-módosítások mindig up-todátumot az elemzéshez. Ezzel a kialakítással az elemzések valós időben futtathatók up-to-date adatokon. Ez lemezalapú és memóriaoptimalizált táblák esetében is működik.

Első lépések – példa

A valós idejű elemzés első lépései:

1. Azonosítsa azokat a táblákat a működési sémában, amelyek az elemzéshez szükséges adatokat tartalmazzák.

2. Minden táblához ejtse az összes olyan B-fa indexet, amelyek elsősorban az OLTP-számítási feladatok meglévő elemzéseinek felgyorsítására lettek kialakítva. Cserélje le őket egyetlen nem klaszterezett oszlop-tároló indexre. Ez javíthatja az OLTP-számítási feladatok általános teljesítményét, mivel kevesebb indexet kell fenntartani.

   --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)
   ;
   

   A memóriaoptimalizált táblák oszlopcentrikus indexe lehetővé teszi a működési elemzést a memóriabeli OLTP és az oszlopcentrikus technológiák integrálásával, így az OLTP és az elemzési számítási feladatok egyaránt nagy teljesítményt nyújtanak. A memóriaoptimalizált táblák oszlopcentrikus indexének fürtözött indexnek kell lennie, vagyis az összes oszlopot tartalmaznia kell.

   -- 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 );
   

Most már készen áll a valós idejű üzemeltetési elemzések futtatására anélkül, hogy bármilyen módosítást végez az alkalmazáson. Az elemzési lekérdezések az oszlopbolti indexen futnak, és az OLTP-műveletek továbbra is az OLTP B-fa indexeken futnak. Az OLTP számítási feladatok továbbra is futnak, de további terhelést okoznak az oszlopalapú index fenntartása miatt. Tekintse meg a teljesítményoptimalizálásokat a következő szakaszban.

Blogbejegyzések

A valós idejű üzemeltetési elemzéssel kapcsolatos további információkért olvassa el az alábbi blogbejegyzéseket. Ha először a blogbejegyzéseket tekinti meg, könnyebben megértheti a teljesítménytippeket tartalmazó szakaszokat.

• Üzleti eset valós idejű üzemeltetési elemzésekhez

• Nem fürtözött oszlopstore index használata valós idejű operatív elemzésekhez

• Egyszerű példa nem klaszteres oszlopraktár index használatával

• Hogyan tart fenn az SQL Server nem fürtözött oszlopalapú indexet egy tranzakciós munkaterhelésen?

• A nem klaszterezett columnstore index karbantartás hatásának minimalizálása szűrt index használatával

• A tömörítési késleltetéssel a nem klaszterezett oszlopalapú index karbantartásának hatását minimalizáljuk

• A nem halmozott oszlop-tárházas index karbantartás hatásainak minimalizálása tömörítési késleltetéssel – teljesítményadatok

• Valós idejű működési elemzés memóriaoptimalizált táblákkal

• Oszlopcentrikus index és sorcsoportok egyesítési szabályzata

Videos

A Data Exposed videósorozat további részleteket tartalmaz az egyes képességekről és szempontokról.

• 1. rész: Hogyan teszi lehetővé az Azure SQL a valós idejű operatív elemzést (HTAP)
• 2. rész: Meglévő adatbázisok és alkalmazások optimalizálása operatív elemzéssel
• 3. rész: Működési elemzések létrehozása a Window Functions használatával.

1. teljesítménytipp: Szűrt indexek használata a lekérdezési teljesítmény javítása érdekében

A valós idejű üzemeltetési elemzések futtatása hatással lehet az OLTP számítási feladatok teljesítményére. Ennek a hatásnak minimálisnak kell lennie. Az A példa bemutatja, hogyan használhat szűrt indexeket a nemclustered oszlopcentrikus index tranzakciós számítási feladatokra gyakorolt hatásának minimalizálására, miközben valós időben biztosít elemzéseket.

Ha minimálisra szeretné csökkenteni a nemclustered oszlopcentrikus index működési számítási feladatokon való fenntartásának többletterhelését, szűrt feltétellel csak a meleg vagy lassan változó adatokon hozhat létre nemclustered oszlopcentrikus indexet. Egy rendeléskezelő alkalmazásban például létrehozhat egy nemclustered oszlopcentrikus indexet a már kiszállított rendeléseken. A megrendelés kiszállítása után ritkán változik, ezért meleg adatnak tekinthető. Szűrt index esetén a nem klaszteres oszlopcentrikus index adatai kevesebb frissítést igényelnek, ezáltal csökkentve a tranzakciós munkaterhelésre gyakorolt hatást.

Az elemzési lekérdezések a szükség szerint átlátható módon férnek hozzá mind a meleg, mind pedig a forró adatokhoz, hogy valós idejű elemzést biztosítsanak. Ha az operatív számítási feladatok jelentős része a "gyakori" adatokat érinti, ezek a műveletek nem igényelnek további karbantartást az oszlopcentrikus indexen. Ajánlott gyakorlat, hogy a szűrt indexdefinícióban használt oszlop(ok)nak legyen egy sorcentrikus fürtözött indexe. Az adatbázismotor a fürtözött index használatával gyorsan megvizsgálja a szűrt feltételnek nem megfelelő sorokat. A fürtözött index nélkül a sortártábla teljes táblázatvizsgálata szükséges a sorok megkereséséhez, ami negatívan befolyásolhatja az elemzési lekérdezések teljesítményét. Fürtözött index hiányában létrehozhat egy kiegészítő szűrt, nem fürtözött B-fa indexet az ilyen sorok azonosításához, de ez nem ajánlott, mert a nagy sortartományok elérése nem fürtözött B-fa indexeken keresztül költséges.

Note

A szűrt, nem halmazon alapuló oszlopcentrikus index csak lemezalapú táblákon támogatott. A memóriaoptimalizált táblákon nem támogatott.

Példa A: Forró adatok elérése a B-fa indexből, langyos adatok az oszlopcentrikus indexből.

Ez a példa egy szűrt feltételt (accountkey > 0) használ annak megállapításához, hogy mely sorok szerepelnek az oszlopcentrikus indexben. A cél a szűrt feltétel és az azt követő lekérdezések megtervezése annak érdekében, hogy a B+ fa indexből gyakran változó, "forró" adatokhoz, illetve az oszlopalapú index stabilabb, "meleg" adataihoz jussunk hozzá.

Note

A Lekérdezésoptimalizáló a lekérdezésterv oszlopcentrikus indexét veszi figyelembe, de nem mindig választja. Amikor a lekérdezésoptimalizáló kiválasztja a szűrt oszlopcentrikus indexet, transzparens módon egyesíti a sorokat az oszloptár-indexből, valamint azokat a sorokat, amelyek nem felelnek meg a szűrt feltételnek a valós idejű elemzés engedélyezéséhez. Ez eltér a normál, nem konklúziós szűrt indextől, amely csak olyan lekérdezésekben használható, amelyek az indexben található sorokra korlátozzák magukat.

-- 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;

Az elemzési lekérdezés a következő lekérdezési tervvel fut. Láthatja, hogy a szűrt feltételnek nem megfelelő sorok fürtözött B-fa indexen keresztül érhetők el.

További információért lásd a következő blogot: Szűrt nem klaszterezett oszlopos adattár index.

Teljesítménytipp #2: Az elemzést az Always On olvasható másodlagos kiszolgálóra szervezzük ki

Annak ellenére, hogy a szűrt oszlopcentrikus index használatával minimalizálhatja az oszlopcentrikus indexek karbantartását, az elemzési lekérdezések továbbra is jelentős számítási erőforrásokat (CPU, I/O, memória) igényelhetnek, amelyek befolyásolják az üzemeltetési számítási feladatok teljesítményét. A legtöbb kritikus fontosságú számítási feladat esetében javasoljuk az Always On konfiguráció használatát. Ebben a konfigurációban kiküszöbölheti az analitikai műveletek hatását, ha egy olvasható másodlagos rendszerre helyezi át azokat.

3. teljesítménytipp: Az indextöredezettség csökkentése a gyakori adatok változássorcsoportokban való megőrzésével

Az oszlopcentrikus indexet tartalmazó táblák jelentősen töredezettek lehetnek (azaz törölt sorok), ha a számítási feladat frissíti/törli a tömörített sorokat. A töredezett oszlopcentrikus index a memória és tárhely nem hatékony kihasználásához vezet. Az erőforrások nem hatékony használata mellett az elemzési lekérdezések teljesítményét is negatívan befolyásolja a többlet I/O- és a törölt sorok szűrésének szükségessége az eredményhalmazból.

A törölt sorok fizikailag nem lesznek eltávolítva, amíg nem futtatja az index töredezettségmentesítését parancs használatával REORGANIZE , vagy nem építi újra újra az oszlopcentrikus indexet a teljes táblán vagy az érintett partíció(ka)n. Mind az REORGANIZE indexelés, mind a REBUILD költséges műveletek, amelyek elvonják az erőforrásokat, amelyek egyébként a számítási feladathoz lennének felhasználhatók. Emellett, ha a sorok túl korán tömörítve vannak, előfordulhat, hogy többször újra kell tömöríteni a frissítések miatt, ami felesleges tömörítési többletterheléshez vezet.

Az index töredezettségét a beállítással COMPRESSION_DELAY minimalizálhatja.

-- 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);

További információ : Blog: Tömörítési késleltetés.

Az ajánlott eljárások a következők:

• Számítási feladat beszúrása/lekérdezése: Ha a számítási feladat elsősorban adatokat szúr be és kérdez le, a 0 alapértelmezett COMPRESSION_DELAY értéke az ajánlott beállítás. Az újonnan beszúrt sorok tömörítve lesznek, ha 1 millió sort szúrtak be egyetlen delta sorcsoportba. Ilyen számítási feladatokra néhány példa egy hagyományos DW-számítási feladat vagy egy select-stream elemzés, ha a kiválasztási mintát egy webalkalmazásban kell elemeznie.

• OLTP számítási feladat: Ha a számítási feladat DML-intenzív (vagyis a frissítés, a törlés és a beszúrás intenzív keveréke), akkor a DMV sys.dm_db_column_store_row_group_physical_stats, vizsgálatával megjelenhet az oszloptároló index töredezettsége. Ha azt látja, hogy > 10% sor megjelölve törölve van a közelmúltban tömörített sorcsoportokban, akkor időtúllépést adhat COMPRESSION_DELAY hozzá, amikor a sorok jogosultakká válnak a tömörítésre. Ha például a számítási feladat szempontjából az újonnan beszúrt elemek „forrók” maradnak (azaz többször frissülnek) mondjuk 60 percig, akkor az COMPRESSION_DELAY értékét 60-ra kell választani.

A beállítás alapértelmezett értékének COMPRESSION_DELAY a legtöbb ügyfélnél működnie kell.

A haladó felhasználók számára javasoljuk, hogy futtassa az alábbi lekérdezést, és gyűjtse össze a törölt sorok % az elmúlt hét napban.

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;

Ha a törölt sorok száma a tömörített sorcsoportokban > kevesebb mint 20%, és a régebbi sorcsoportok stabilizálódnak < 5% variációval (ezek az ún. hideg sorcsoportok), akkor állítsa be az értéket COMPRESSION_DELAY = (youngest_rowgroup_created_time - current_time) értékkel. Ez a megközelítés egy stabil és viszonylag homogén számítási feladattal működik a legjobban.

Kapcsolódó tartalom

• Oszlopalapú indexek: Áttekintés
• Oszlopalapú indexek – Adatbetöltési útmutató
• oszloptároló indexek – Lekérdezési teljesítmény
• Oszlopalapú indexek az adattárházakban
• Indexkarbantartás optimalizálása a lekérdezési teljesítmény javítása és az erőforrás-felhasználás csökkentése