ALTER TABLE DROP COLUMN ne oslobađa prostor koji je kolona zauzimala u tabeli
Jedan kolega danas mi je dao ideju za blog postavivši mi pitanje: „Kako natjerati SQL Server tabelu da oslobodi prostor nakon što se „dropa“ kolona?“
Radi se o izvršavanje ALTER TABLE naredbe, gdje mnogi pretpostave da će SQL server „fizički“ obrisati podatke iz tabele kada se izvrši naredba ALTER TABLE DROP COLUMN i da će tabela osloboditi prostor koji su podaci zauzimali pa će baza imati više slobodnog prostora za korištenje. Međutim, nije sve baš tako jednostavno.
ALTER TABLE komanda interno se ne izvršava uvijek na isti način. Nekada ALTER TABLE naredba izvrši samo promjenu metapodataka (što je slučaj kod DROP COLUMN naredbe) a sami podaci se ne diraju, nekada je potrebno pročitati sve podatke i izvršiti promjenu samo u metapodatcima (npr. kod promjene kolone u NOT NULL treba prvo provjeriti sve redove da se vidi da li zadovoljavaju novu osobinu) a nekada se izvrši promjena za sve redove u tabeli kao i u metapodatcima (recimo kada se dodaje nova kolona s default-nom vrijednošću).
Na početku, da napomenem, da rješenje spomenutog problema nije jednoznačno tj. ovisi od toga da li se radi o clustered tabeli (tabela koja ima clustered index) ili heap tabeli (tabela koja nema clustered index).
ALTER TABLE DROP COLUMN nad CLUSTERED tabelom
Prvo ću kreirati tabelu, clustered index i unijeti 500 slogova u tabelu.
-- Kreiranje tabele
CREATE TABLE dbo.Test1
(col1 int identity(1,1) NOT NULL,
col2 varchar(2000) NOT NULL,
col3 varchar(3500) NOT NULL)
GO
--Kreiranje clustered indeks-a
CREATE CLUSTERED INDEX CI_Test1_Col1 ON Test1 (col1)
GO
-- Napuni 500 slogova u tabelu
INSERT INTO Test1 (col2, col3)
SELECT REPLICATE('a',1000), REPLICATE('b',2000)
GO 500
S sp_spaceused procedurom možemo provjeriti koliko tabela zauzima prostora.
Znači, tabela ima 500 slogova i za nju je rezervirano 2056 KB prostora. Podaci zauzimaju 2000KB.
Ukoliko izvršite sljedeće naredbe, vidjet će te da nakon drop-a kolone, prostor koji je rezerviran za tabelu i prostor koji tabela zauzima na disku ostaje isti kao i prije dropa kolone.
-- Drop kolone Col3
ALTER TABLE Test1
DROP Column Col3
GO
-- Provjeri koliko tabela zauzima prostora
exec sp_spaceused 'Test1', true
GO
Izvršena je promjena samo u metapodacima. Da bi „natjerali“ tabelu da oslobodi prostor bazi i da se „fizički izbrišu“ podaci iz page-a neophodno je izvršiti rebuild tabele odnosno clustered index-a.
DBCC DBREINDEX ('Test1')
go
exec sp_spaceused 'Test1', true
GO
Nakon rebuild-a, podaci u page-ovima su defragmentirani (složeni kako treba) a tabela je oslobodila suvišan prostor bazi na korištenje.
Treba napomenuti da reorganizacija indeksa s DBCC INDEXDEFRAG neće pomoći.
Rješenje za clustered tabelu je prilično jednostavno, a što ako se radi o HEAP tabeli.
ALTER TABLE DROP COLUMN nad HEAP tabelom
Ako probamo uraditi istu stvar samo s heap tabelom možemo vidjeti da rebuild tabele neće pomoći. Sljedeća skripta kreira tabelu, napini 500 slogova, provjeri prostor, dropa kolonu, provjeri prostor, rebuilda tabelu i na kraju provjeri prostor koji tabela zauzima.
CREATE TABLE dbo.Test1
(col1 int identity(1,1) NOT NULL,
col2 varchar(2000) NOT NULL,
col3 varchar(3500) NOT NULL)
GO
-- Napuni 500 slogova u tabelu
INSERT INTO Test1 (col2, col3)
SELECT REPLICATE('a',1000), REPLICATE('b',2000)
GO 500
--Provjeri prostor
exec sp_spaceused 'Test1', true
GO
--Drop kolone Col3
ALTER TABLE Test1
DROP Column Col3
GO
--Provjeri prostor
exec sp_spaceused 'Test1', true
GO
--Rebuild tabele
DBCC DBREINDEX ('Test1')
go
--Provjeri prostor
exec sp_spaceused 'Test1', true
GO
Ako izvršite gornju skriptu vidjet će te da rebuild tabele nije rješio problem kod heap tabele.
Rješenje je u kreiranju clustered indeksa na tabeli. Ako baš ne želite imati clustered indeks možete ga poslije izbrisati (iako je u večini slučajeva dobro imati clustered indeks).
Nakon kreiranja clustered indeksa na heap tabeli sve izgleda OK. Tabela zauzima 576 KB umjesto 2032 KB kako je bio slučaj prije kreiranja clustered inkdeksa.
Zaključak:
Da bi oslobodili prostor nakon drop-a kolone potrebno je:
- Za clustered tabelu napraviti rebuild tabele (clustered indeksa)
- Za heap tabelu kreirati clustered indeks
Spomenuto ponašanje ALTER TABLE naredbe ne mora predstavljati problem kod malih tabela, međutim ako imate veliku tabelu s velikim brojem slogova (npr. tabela koja je „teška“ stotine MB ili možda GB) a kolona koja se dropa je relativno velika, tabela postaje fragmentirana , ne koristi se efikasno prostor na disku pa je potreban dosta veći broj IO operacija što smanjuje performanse upita nad tabelom.