SQL Server 2016 újdonságok - TRUNCATE TABLE

Májusban megjelent az SQL Server 2016 CTP 2, majd ezt követte két újabb előzetes, melyekben sok újdonsággal lehet találkozni. Most a TRUNCATE TABLE újdonságait, illetve a DELETE és a TRUNCATE TABLE közti különbségeket szeretném bemutatni.

DELETE vs. TRUNCATE TABLE?

Mielőtt az újdonságokba belekezdenék, szeretném megmutatni, hogy mi a különbség a DELETE és a TRUNCATE TABLE között. Miért is fontos ez? Sokan nem tulajdonítanak nagy jelentőséget ennek, pedig fontos lehet: a DELETE parancs során a tranzakciós logba bekerül az összes törölt sor, míg a TRUNCATE TABLE esetén nem, csak a vezérlőlapokon, illetve a metaadatokon történt változások. Ez persze így nagyon le van sarkítva, nézzük meg ezt egy példán keresztül:

Először létrehozok egy adatbázist és egy táblát, amibe 1000 sort beszúrok.

 1USE [master];
 2GO
 3IF DB_ID('Demo') IS NOT NULL
 4 BEGIN
 5 ALTER DATABASE [Demo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
 6 DROP DATABASE [Demo];
 7 END
 8GO
 9CREATE DATABASE [Demo];
10GO
11USE [Demo];
12GO
13--truncate vs. delete
14IF OBJECT_ID('dbo.T1') IS NOT NULL
15 DROP TABLE [dbo].[T1];
16GO
17CREATE TABLE [dbo].[T1]
18(
19 [col1] int NOT NULL IDENTITY,
20 [col2] char(10) DEFAULT REPLICATE('A',10),
21 CONSTRAINT PK_T1 PRIMARY KEY CLUSTERED ([col1])
22);
23GO
24INSERT INTO [dbo].[T1] DEFAULT VALUES;
25GO 1000
26 
27SELECT * FROM [dbo].[T1];
28CHECKPOINT;

Alapvetően minden adatváltoztatás bekerül a tranzakciós logba, beleértve a törlést is. Azonban az nem mindegy, hogy mennyi adatot kell a logba írni, mennyire gyorsan történik meg az adott művelet. Vajon a DELETE vagy a TRUNCATE TABLE a "gyorsabb"? Ezt nagyon könnyű kideríteni, az alábbi két példa meg fogja válaszolni ezt a kérdésünket.

DELETE

A T1 táblában van 1000 sor, amit kitörlök és megnézem a tranzakciós logot, hogy mit tartalmaz, majd visszavonom a tranzakciót, hogy a másik példában is tudjam használni az adatokat. A tranzakciós log kiolvasásához a sys.fn_dblog nem dokumentált függvényt fogom használni.TE TABLE a "gyorsabb"? Ezt nagyon könnyű kideríteni, az alábbi két példa meg fogja válaszolni ezt a kérdésünket.

1BEGIN TRAN
2DELETE FROM [dbo].[T1]
3SELECT * FROM sys.fn_dblog(NULL, NULL);
4ROLLBACK

A kód lefuttatása után 1007 sort kaptam vissza! Az első 3 sor nem igazán érdekes (a CHECKPOINT parancshoz tartozik), azok nem tartoznak a törlési tranzakcióhoz, azonban a 4. sorban kezdődik a tranzakciónk a LOP_BEGIN_XACT művelettel, ami a BEGIN TRAN.

A LOP_DELETE_ROWS a sor törlése, ami igazság szerint nem is igazi törlés, mert előbb csak megjelöli törlésre a sorokat - ghost rekord - majd egy háttérfolyamat, a ghost cleanup task fogja kitörölni a azokat. Figyeljük meg, hogy pontosan 1000 sor van a törléshez + 1 sor a tranzakció induláshoz és 3 sor a PFS page frissítéshez! Mivel a tranzakciót visszavontam, egy újabb lekérdezése a lognak újabb sorokat mutatna, egész pontosan 1000 sor az adataim újra létrehozásához (LOP_INSERT_ROWS), illetve a PFS page frissítéshez 4 (LOP_SET_BITS) + a tranzakció visszavonásáról 1 (LOP_ABORT_XACT). Ez "csak" 1000 sor volt, most képzeljük el ezt egy olyan táblán, amiben több milliárd sor van!

TRUNCATE TABLE

Most ennek az 1000 sornak a törlését nézzük meg a TRUNCATE TABLE használatával. Nem meglepő módon itt nincs 1000 sor a tranzakciós logban! A TRUNCATE TABLE "csak" a vezérlő lapokon IAM, PFS) végzett változtatásokat írja be a logba, amelyek meghatározzák, hogy mely táblának hol vannak az adatai letárolva (megint csak nagyon leegyszerűsítve ;-)). Esetünkben 24 sor van a logban, amiből az első 3 sor ismét nem érdekes (a CHECKPOINT parancshoz tartozik).

A 4. sorban indul a tranzakció (LOP_BEGIN_XACT), majd laz 5. sorban egy SCH_M lock (LOP_LOCK_XACT), ami meggátolja, hogy a TRUNCATE TABLE során a tábla struktúráját bárki megváltoztassa. A 6-14 sorok az IAM és a PFS lapok változtatásai, majd a 15. sorban a memóriában tárolt vezérlő lapok invalidálása végül a 16. sortól a metaadatok módosítása történik meg. Látható, hogy a tranzakciós logba sokkal kevesebb információ került be, illetve egy ROLLBACK esetén is 15 új sorral bővülne a log, ami ismét csak a fenti változtatások visszaállítását tartalmazza, azaz nincs 1000 insert, mint a DELETE parancs esetén.

DELETE vagy TRUNCATE TABLE?

Attól függ! A DELETE esetén tudunk szűrni, azaz lehet WHERE feltétel, a TRUNCATE TABLE parancs esetén nincs ilyen lehetőségünk. Utóbbit akkor szoktam választani, amikor egy adott táblát teljesen ki kell törölni a lehető leggyorsabban, akár több millió sor esetén is. Ennél a pontnál jött el az idő arra, hogy megemlítsük a táblaparticionálást: alapvetően nagy, jellemzően több tíz vagy száz GB méretű táblák skálázásához alkalmazzuk ezt a technikát. A particionálás pontos részleteibe nem mennék bele, azonban a törlési technikákba igen. Hogyan töröljünk particionált táblából? A válasz ismét: attól függ! Nem mindegy például, hogy:

• egy sort akarok törölni,
• egy vagy több partíciót akarok teljesen törölni,
• egy részét akarom egy vagy több partíciónak törölni.

Az első eset egyértelmű, itt a DELETE parancs WHERE feltétellel. A második és harmadik eset már nem ennyire egyszerű, a továbbiakban csak a második esettel foglalkozunk.

Egy vagy több partíció teljes törlése

Az alábbiakban a 2005-2014 és a 2016-os verziók közti különbségeket mutatom be egy példán keresztül.

SQL Server 2005 - 2014 verziók esetén

Sajnos itt előbb egy ún. switch outra van szükségünk, azaz a törölni kívánt partíció(ka)t előbb egy másik táblá(k)ba kell átmozgassuk, majd ott tudjuk kiadni a TRUNCATE TABLE parancsot. Lássuk ezt lépésről lépésre. Először létrehozom a particionáláshoz szükséges objektumaimat: PARTITION SCHEME, PARTITION FUNCTION, majd a particionált táblát (vegyük észre az ON psNumbers részt a CREATE TABLE kódrészletben). Ezek után beszúrok 1000 sort, majd lekérdezem, a $partition függvény használatával, hogy melyik sor, melyik partíción van.

 1--create partition function, scheme and a partitioned table
 2IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = 'psNumbers')
 3 DROP PARTITION SCHEME [psNumbers];
 4GO
 5IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = 'pfNumbers')
 6 DROP PARTITION FUNCTION [pfNumbers];
 7GO
 8CREATE PARTITION FUNCTION [pfNumbers] (int)
 9 AS RANGE LEFT FOR VALUES (1, 2, 3, 4, 5) ;
10GO
11CREATE PARTITION SCHEME [psNumbers]
12 AS PARTITION [pfNumbers] ALL TO ([PRIMARY]) ;
13GO
14IF OBJECT_ID('dbo.T1') IS NOT NULL
15 DROP TABLE [dbo].[T1];
16GO
17CREATE TABLE [dbo].[T1]
18(
19 [col1] int NOT NULL IDENTITY,
20 [col2] char(10) DEFAULT REPLICATE('A',10),
21 CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED ([col1])
22) ON [psNumbers]([col1]);
23GO
24INSERT INTO [dbo].[T1] DEFAULT VALUES;
25GO 1000
26SELECT [col1], [col2], $partition.pfNumbers([col1]) FROM [dbo].[T1];
27GO

A lekérdezés eredménye az alábbi (1000 sor, de csak 11-et mutatok helyspórolás végett).

Látható, hogy a pfNumbers függvény miatt az 1-es érték az 1-es partición, a 2-es a 2-es partición, stb, a 6. partició pedig minden olyan számot tartalmaz, amely ötnél nagyobb. Így a 6. partíción 995, az 1-5 partíciókon 1-1 sorok vannak. Tételezzük fel, hogy a 6. partíción található összes sort ki kell törölnünk. Erre használhatnánk a DELETE parancsot is, itt a 995 soron működik is elég gyorsan, azonban már tudjuk, hogy ez minimum 995 sornyi tranzakcióslog-bejegyzést is generál! Most ezt képzeljük el több millió sor esetére is! Ugye nem is olyan jó ez a DELETE ilyenkor? ;). Lássuk, hogyan is működik a switch out: először kell egy nem particionált tábla, amely definició szerint megegyezik a particionált táblámmal.

 1-- create staging table
 2IF OBJECT_ID('dbo.T1_Staging') IS NOT NULL
 3 DROP TABLE [dbo].[T1_Staging];
 4GO
 5CREATE TABLE [dbo].[T1_Staging]
 6(
 7 [col1] int NOT NULL IDENTITY,
 8 [col2] char(10) DEFAULT REPLICATE('A',10),
 9 CONSTRAINT [PK_T1_Staging] PRIMARY KEY CLUSTERED ([col1])
10) ON [PRIMARY]

Következő lépésben a 6. particiót átmozgatom a T1 táblából a T1_Staging táblába, majd kitörlöm a TRUNCATE TABLE segítségével.

1-- switch out partition 6 -->995 rows
2ALTER TABLE [dbo].[T1] SWITCH PARTITION 6 TO [dbo].[T1_Staging] ;
3GO
4TRUNCATE TABLE [dbo].[T1_Staging];
5GO
6 
7SELECT * FROM [dbo].[T1]
8SELECT * FROM [dbo].[T1_Staging]

SQL Server 2016 esetén

Itt jön be az újdonság! SQL Server 2016 esetén nem kell ún. switch out és utána a TRUNCATE TABLE, hanem ezt már a particionált táblán is meg lehet csinálni! Új paraméterként megjelent a partíció száma is. A fenti példáknál maradva, szeretném kitörölni az 1-es, majd a 3-5 partíciókat. Ehhez az alábbi parancsot tudom felhasználni:

1TRUNCATE TABLE [dbo].[T1] WITH (PARTITIONS (1));
2GO
3TRUNCATE TABLE [dbo].[T1] WITH (PARTITIONS (3 TO 5));
4GO
5SELECT * FROM [dbo].[T1];
6GO

Így végül a táblában csak a 2 partíción lesz adat, azaz 1 sor maradt.

Összefoglalva a fentieket látható, hogy milyen nagy különbség van "törlés" és "törlés" között, illetve milyen plusz dolgokra kell figyelni ilyen esetekben. Az SQL Server 2016 újdonságai között a TRUNCATE TABLE partíció támogatása az én személyes kedvencem, nem tűnik nagy dolognak, de egy hatalmas segítség a napi munkában. (A sys.fn_dblog által visszaadott sorok száma nagyban függ az egyéb terheléstől, illetve SQL Server verziótól, így a fenti példák során visszakapott eredményhalmaz eltérhet.) Fontos tudni, hogy az itt leírt funkciók nem és/vagy másképpen működhetnek a termék végleges verziójában, a példakód a Microsoft SQL Server 2016 (CTP2.2) - 13.0.400.91 (X64) verzióra vonatkozik. A minták során nem dokumentált függvények és megoldások is alkalmazásra kerülhetnek, melyek használata éles környezetben nem ajánlott! A kódok használata csak saját felelősségre történhet! A cikk írója nem vállal sem közvetlen, sem közvetett felelősséget az itt megjelenő kódok használatából eredő károkért.