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.
USE [master];
GO
IF DB_ID('Demo') IS NOT NULL
 BEGIN
 ALTER DATABASE [Demo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
 DROP DATABASE [Demo];
 END
GO
CREATE DATABASE [Demo];
GO
USE [Demo];
GO
--truncate vs. delete
IF OBJECT_ID('dbo.T1') IS NOT NULL
 DROP TABLE [dbo].[T1];
GO
CREATE TABLE [dbo].[T1]
(
 [col1] int NOT NULL IDENTITY,
 [col2] char(10) DEFAULT REPLICATE('A',10),
 CONSTRAINT PK_T1 PRIMARY KEY CLUSTERED ([col1])
);
GO
INSERT INTO [dbo].[T1] DEFAULT VALUES;
GO 1000

SELECT * FROM [dbo].[T1];
CHECKPOINT;
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.
BEGIN TRAN
DELETE FROM [dbo].[T1]
SELECT * FROM sys.fn_dblog(NULL, NULL);
ROLLBACK
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.
CHECKPOINT;
BEGIN TRAN
TRUNCATE TABLE [dbo].[T1]
SELECT * FROM sys.fn_dblog(NULL, NULL);
ROLLBACK
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 <a href="https://msdn.microsoft.com/en-us/library/ms188071.aspx" target="_blank">$partition</a> függvény használatával, hogy melyik sor, melyik partíción van.
--create partition function, scheme and a partitioned table
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = 'psNumbers')
 DROP PARTITION SCHEME [psNumbers];
GO
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = 'pfNumbers')
 DROP PARTITION FUNCTION [pfNumbers];
GO
CREATE PARTITION FUNCTION [pfNumbers] (int)
 AS RANGE LEFT FOR VALUES (1, 2, 3, 4, 5) ;
GO
CREATE PARTITION SCHEME [psNumbers]
 AS PARTITION [pfNumbers] ALL TO ([PRIMARY]) ;
GO
IF OBJECT_ID('dbo.T1') IS NOT NULL
 DROP TABLE [dbo].[T1];
GO
CREATE TABLE [dbo].[T1]
(
 [col1] int NOT NULL IDENTITY,
 [col2] char(10) DEFAULT REPLICATE('A',10),
 CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED ([col1])
) ON [psNumbers]([col1]);
GO
INSERT INTO [dbo].[T1] DEFAULT VALUES;
GO 1000
SELECT [col1], [col2], $partition.pfNumbers([col1]) FROM [dbo].[T1];
GO
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.
-- create staging table
IF OBJECT_ID('dbo.T1_Staging') IS NOT NULL
 DROP TABLE [dbo].[T1_Staging];
GO
CREATE TABLE [dbo].[T1_Staging]
(
 [col1] int NOT NULL IDENTITY,
 [col2] char(10) DEFAULT REPLICATE('A',10),
 CONSTRAINT [PK_T1_Staging] PRIMARY KEY CLUSTERED ([col1])
) 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.
-- switch out partition 6 -->995 rows
ALTER TABLE [dbo].[T1] SWITCH PARTITION 6 TO [dbo].[T1_Staging] ;
GO
TRUNCATE TABLE [dbo].[T1_Staging];
GO

SELECT * FROM [dbo].[T1]
SELECT * FROM [dbo].[T1_Staging]

Látható, hogy ezzel a megoldással a T1 táblában 5 sor maradt, a T1_Staging táblába átmozgatott 995 sor pedig törlésre került. A tranzakciós logba pedig nem 995 sornyi törlés, hanem jóval kevesebb (24 sor a partition switch miatt és 25 sor a truncate table miatt).

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:
TRUNCATE TABLE [dbo].[T1] WITH (PARTITIONS (1));
GO
TRUNCATE TABLE [dbo].[T1] WITH (PARTITIONS (3 TO 5));
GO
SELECT * FROM [dbo].[T1];
GO
Í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.

Add comment