niedziela, 26 lutego 2012

Modyfikacje kolumn w tabeli

Dzisiaj napiszę parę słów o tym jak wpływa modyfikacja typu danych kolumny, bądź jej zakresu na wielkość tabeli.
W przypadku gdy zmieniany jest typ danych kolumny, SQL Server niekoniecznie modyfikuje tylko meta dane.
SQL Server może utworzyć nową kolumnę odpowiadającą nowemu typowi danych a starą usunąć, a w rzeczywistości tylko oznaczyć jako usuniętą, bądź też zmodyfikować tylko meta dane. Zależy to od typu danych oraz tego czy zakrez jest zmniejszany czy powiększany.

 

Modyfikacja kolumny o stałej długości

Załóżmy, że mamy następującą tabelę w bazie danych
   1: create table TestResize (
   2:     id int identity,
   3:     value char(5000),
   4:     constraint PK_TestResize primary key nonclustered(id) 
   5: )
   6: go
Celowo założyłem tutaj klucz podstawowy nie będący indeksem klastrowym. O tym dlaczego podjąłem taką decyzję będzie w daleszej części artykułu.

Nieprzypadkowo kolumna jest też typu danych char o długości 5000 znaków. SQL Server dane zapisuje na stronach, które nie mogą przekroczyć 8KB. Kolejnym ograniczeniem jest to, że na jednej stronie mogą znajdować się dane tylko z jednej tabeli. W przypadku tego typu danych jeden wiersz będzie znajdował się na jednej stronie. Pozostawiając dość dużo miejsca, które nigdy nie będze wykorzystane. Dodatkowo kolumna przyjmuje wartość NULL, czyli może być taka sytuacja, że jedynymi danymi, które będą znajdować się w tabeli będzie 11 bajtów (7 bajtów przechowujące informacje na temat wiersza i 4 bajty dla kolumny ID). Jednakże SQL Server zapisze ten wiersz na 8KB. W rzeczywistości będzie to 5011 bajtów, ale tych 5000 nie liczę tutaj, jako, że nie zawierają one żadnej informacji.

Skoro taka sytuacja może być to ją wykorzystajmy
   1: insert into TestResize(value) values(null)
   2: insert into TestResize(value) values(null)
   3: insert into TestResize(value) values(null)
Wykonując skrypt z tego wpisu bądź też trochę zmodyfikowaną wersję która jest poniżej zobaczymy, że dane dostały zapisane na 3 stronach (24 KB)
   1: declare @tableName varchar(255)
   2: set @tableName = 'TestResize'
   3:  
   4: select
   5:     obj.name as TableName,
   6:     col.name as ColumnName,
   7:     sipc.partition_column_id,
   8:     sipc.max_length,
   9:     ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count as data_pages,
  10:     ps.row_count,
  11:     cast(1. * ps.row_count / (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) as decimal(4,2)) as avg_number_rows_in_page,
  12:     sipc.is_dropped,
  13:     sipc.leaf_offset,
  14:     sip.max_leaf_length,
  15:     sip.min_leaf_length
  16: from sys.system_internals_partition_columns sipc
  17: inner join sys.dm_db_partition_stats ps
  18: on ps.partition_id = sipc.partition_id
  19: inner join sys.objects obj
  20: on obj.object_id = ps.object_id
  21: inner join sys.system_internals_partitions sip
  22: on sip.partition_id = sipc.partition_id
  23: left join sys.columns col
  24: on col.column_id = sipc.partition_column_id
  25: and col.object_id = ps.object_id
  26: where obj.name = @tableName
  27: and ps.index_id < 2

Średnia jeden wiersz na stronę w przypadku gdy danych jest zaledwie 8 bajtów jest bardzo dużym marnotrastwem zasobów.

Skoro jest tak źle to może warto by naprawić tę sytuację poprzez zmienienie typu danych kolumny value na varchar(5000).
   1: alter table TestResize
   2: alter column value varchar(5000)

Dodajmy jeszcze jeden wiersz do tabeli, tym razem rzeczywiście zawierający 5000 znaków.
1: insert into TestResize(value) values(replicate('a', 5000))

Cel osiągnięty? Niestety. Jest jeszcze gorzej. W przypadku zmiany typu danych kolumny o stałej długości SQL Server tworzy nową kolumnę a starą zaznacza jako usuniętą. Niestey nie widać tego w widoku sys.columns.

Jednakże informcje te będą dostęne wykonując skrypt znajdujący sie trochę wyżej.

Rezultat jest następujący:

Ilość kolumn: 3 (w tym jedna zaznaczona jako usunięta)

Ilość stron wykorzystanych do zapisania danych: 6 (dodatkowe strony wynikające z faktu, że przekroczona została wielkość wiersza). W tym przypadku mamy do czynienia z przepełnieniem, czyli faktycznie jest to zapisane w innym miejscu.

Ilość wierszy 4, średnia 0.67 wiersza na stronę. Wynik jednym słowem fatalny.

Modyfikacja kolumny o zmiennej długości

W przypadku modyfikacji kolumny o zmiennej długości sytuacja będzie wyglądać już znacznie lepiej. Jeżeli zakres danych będzie zwiększony to zmienią się tylko meta dane, jeżeli zostanie zmniejszony to zostanie utworzona nowa kolumna, jednakże dane zostaną dopasowane do typu danych nowej kolumny.

Testowanie pozostawiam już we własnym zakresie.

 

Indeks klastrowy przychdzi z pomocą

Jak więc odzyskać niewykorzystywane miejsce? Ano własnie tutaj z pomocą przchodzi indeks klastrowy.

Wykonując poniższy fragment kodu
   1: alter table TestResize
   2: drop constraint PK_TestResize
   3:  
   4: alter table TestResize
   5: add constraint PK_TestResize primary key clustered(id)
Bądź też
   1: alter index PK_TestResize on TestResize rebuild
w przypadku gdy indeks klastrowy istniał już wcześniej na tabeli rozwiązuje problem.

Jeżeli z jakiegoś powodu indeks klastrowy nie może zostać założony, to wtedy pozostaje jedynie tworzenie tabeli tymczasowej z odpowiednią strukturą, skopiowanie danych z pierwotnej tabeli. Usunięcie pierwotnej tabeli i zmiana nazwy tymczasowej na taka jak była wcześniej.

Prześlij komentarz