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.

piątek, 24 lutego 2012

EVENTARGUMENT, EVENTTARGET i postback w ASP.NET

Dzisiaj wyjatkowo poruszę temat nie związany z SQL-em.
W ASP.NET do obslugi postback wykorzystywane są dwa ukryte pola na formularzu oraz funkcja javascript. Problem polega na tym , że ASP.NET generuje domyślnie ten kod tylko w przypadku gdy na formularzu występuje kontrolka, która tego wymaga. Może to być np. LinkButton, TextBox, CheckBox, DropDownList (właściwość AutoPostBack musi być ustawiona na true).
W przypadku gdy powyższe jest spełnione to podglądając kod HTML zobaczymy następujący kod
   1: <input type="hidden" name="__EVENTTARGET" id="__EVENTTARGET" value="" />
   2: <input type="hidden" name="__EVENTARGUMENT" id="__EVENTARGUMENT" value="" />
   1: <script type="text/javascript">
   1:  
   2: //<![CDATA[
   3: var theForm = document.forms['Form1'];
   4: if (!theForm) {
   5:     theForm = document.Form1;
   6: }
   7: function __doPostBack(eventTarget, eventArgument) {
   8:     if (!theForm.onsubmit || (theForm.onsubmit() != false)) {
   9:         theForm.__EVENTTARGET.value = eventTarget;
  10:         theForm.__EVENTARGUMENT.value = eventArgument;
  11:         theForm.submit();
  12:     }
  13: }
  14: //]]>
</script>

Niestety powyższe nie jest generowane przez kontrolki Button i ImageButton.

W przypadku gdy z jakiegoś powodu jest to wymagane to wtedy trzeba sobie radzić w inny sposób i np w funkcji Page_Load dopisać kod, który doda powyższe dwie ukryte kontrolki jak również javascript.

Przykład implementacji takiego rozwiązania można jest np tutaj.

Dlaczego o tym pisze? A to dlatego, że miałem cały dzień zepsuty przez to, że ktoś próbował wykorzystać EVENTARGUMENT w funkcji, która obługiwała zdarzenie OnClick dla ImageButton.

Wszystko byłoby w porządku, pod warunkiem gdy kod ten nie nadawał się do thedailywtf.com.

niedziela, 19 lutego 2012

Bez kluczy, bez indeksów

Poprzedni post dotyczący ograniczeń w bazie danych zakończyłem planem wykonania prostego zapytania aktualizującego jeden rekord w tabeli. Na planie tym pojawiło się 13 operacji w tym skanowanie indeksu. Niewątpliwie wszystkie ograniczenia, o których pisałem są bardzo przydatne i w 99% procentach przypadków mają one zastosowanie.

Pozostaje ten 1% kiedy optymalizacja zapisu jest bardziej istotna od integralności. Przykładem takim może być zapisywanie statystyk dostępu do serwisu internetowego. W przypadku dużej liczby odwiedzin, gdyby była potrzeba zapisania informacji o każdej sesji, czy czynności wykonanych przez użytkownika opercje zapisywania danych statystycznych mogłyby mieć negatywny wpływ na podstawową funkcjonalność.
Dane ta bardzo często w środowki produkcyjnym były by tylko danymi do zapisu a odczyt następowałby tylko w sytuacji importowania do jakiegoś magazynu danych. Choć i ta operacja mogłaby być robiona z kopi zapasowej. Oczywiście taka sytuacja miałaby miejce w przypadku zastosowania tylko jednej bazy danych. Alternatywą może być np osobna baza tylko dla danych statystycznych.

Tak więc o ile integralność danych jest bardzo często aspektem kluczowym, warto także na uwadze optymalizację.

piątek, 17 lutego 2012

Ograniczenia w bazie danych

Ograniczenia w bazie danych są najlepszym sposobem na zachowanie integralności danych. SQL Server oferuje kilka różnych typów ograniczeń i koszt ich użycia nie jest taki sam. Ograniczenia sprawdzane są w określonej kolejności. W poniższych testach zademonstruje działanie kilku z nich.
Przykładowe zapytania bądą wykonywane na bazie danych, do której skrypt można pobrać z tej lokalizacji.
Poniższy skrypt doda wyzwalacz, który będzie potrzebny do wykonania jednego z testów. Wszystkie testy będą wykonane na tabeli Subdivision.
   1: CREATE TRIGGER TRIG_SUBDIVISION_UPDATE ON Subdivision
   2: AFTER UPDATE
   3: AS
   4:  
   5: IF EXISTS(SELECT * FROM inserted WHERE SubdivisionId like 'XX-%')
   6:     RAISERROR('Invalid SubdivisionId', 1,16)
   7: GO
Poniżej dla przejrzystości deklaracja tabeli Subdivision. Znajduję się ona także w skrypcie, o którym wspomniałem wcześniej.
   1: IF NOT EXISTS(SELECT * FROM SYS.tables WHERE name = 'Subdivision')
   2: CREATE TABLE Subdivision(
   3:     SubdivisionID varchar(6) NOT NULL,
   4:     SubdivisionName nvarchar(50) NOT NULL,
   5:     CountryID  AS CAST(LEFT(SubdivisionID,(2)) AS CHAR(2)) PERSISTED,
   6:     Constraint FK_Subdivisions_Country FOREIGN KEY(CountryID) REFERENCES Country(CountryID),
   7:     Constraint PK_Subdivision PRIMARY KEY(SubdivisionID),
   8:     Constraint SubdivisionLength CHECK(LEN(SubdivisionID) >= 4)
   9: )
  10: GO
Istotnym elementów testów jest tabela Hotel
   1: if not exists(select * from sys.tables where name = 'Hotel')
   2: create table Hotel (
   3:     HotelID int identity not null,
   4:     Name nvarchar(100) not null,
   5:     Address1 nvarchar(100) not null,
   6:     Address2 nvarchar(100) null,
   7:     City nvarchar(255) not null,
   8:     SubdivisionID varchar(6) not null,
   9:     Postcode varchar(10) not null,
  10:     constraint PK_Hotel primary key(HotelID),
  11:     constraint FK_Hotel_Subdivision foreign key(SubdivisionID) references Subdivision (SubdivisionID)
  12: )
  13: go

 

Kolejności sprawdzania ograniczeń

Ograniczenia w bazie sprawdzane są w następującej kolejności
  • Wartości domyślne
  • Wartość NULL
  • Ograniczenie CHECK
  • Klucze obce w tabelach zależnych
  • Klucze tabeli aktualizowanej
  • Klucz podstawowy / wartość unikalna
  • Wyzwalacze

Sprawdzanie wartości NULL

W testach pominę pierwszy typ ograniczeń. Osobiście rzadko korzystam z tej funkcjonalności i staram się przenosić ustawianie wartości domyślnych do aplikacji bądź procedur składowych.

Sprawdzanie wartości NULL a raczej NOT NULL jest jednym z moich ulubionych. Ja lubie dane, a jeżeli tych danych nie ma w połowie tabeli to kolumna może powinna zostać przenesiona do innej tabeli.

Kolumna SubdivisionId będąca kluczem podstawowym nie może przyjmować wartości NULL.

Wykonując poniższą kwerendę w wynikach pojawi się błąd.
   1: BEGIN TRAN
   2: UPDATE Subdivision SET SubdivisionId = NULL WHERE SubdivisionName = 'York'
   3: ROLLBACK TRAN

Msg 515, Level 16, State 2, Line 2

Cannot insert the value NULL into column 'SubdivisionID', table 'SQLTest.dbo.Subdivision'; column does not allow nulls. UPDATE fails.


Warto zwrócić uwagę tutaj na fakt, że sprawdzane jest ograniczenie sprawdzające czy nowa wartością nie jest NULL a nie klucz podstawowy.

 

Organiczenie CHECK

Jest to prawdopodobnie jedno z rzadziej używanych ograniczeń w bazie danych, jednakże jest ono bardzo przydatne w kontekście optymalizacji bazy danych.

W przypadku kodów ISO dla jednostek administracyjnych, pierwsze dwie litery to jest oznaczenie państwa po którym następuje myślnik i kod właściwy jednostki administracyjnej. W przykładzie tym w tabeli Subdivision jest ograniczenie na minimalną długość tekstu, która musi wynosić 4 znaki.
   1: BEGIN TRAN
   2: UPDATE Subdivision SET SubdivisionId = 'XX-' WHERE SubdivisionName = 'York'
   3: ROLLBACK TRAN
Powyższe zapytanie zwróci następujący błąd:

Msg 547, Level 16, State 0, Line 2

The UPDATE statement conflicted with the CHECK constraint "SubdivisionLength". The conflict occurred in database "SQLTest", table "dbo.Subdivision", column 'SubdivisionID'.


Ograniczenie to, podobnie jak wartość domyślna i NOT NULL jest ograniczeniem własnym tabeli. Nie występują tutaj żadne odwołania do innych obiektów. Warto zwrócić tutaj uwagę na kolejność sprawdzania ograniczeń. Pomimo tego, że w tabeli Hotel istnieje rekord dla GB-YOR, który jest aktualizowany przez powyższe zapytanie, klucz obcy nie jest weryfikowany.

 

Sprawdzanie kluczy obcych w tabelach zależnych

W nastepnej kolejności sprawdzane są klucze obce
   1: BEGIN TRAN
   2: UPDATE Subdivision SET SubdivisionId = 'XX-ZZ' WHERE SubdivisionName = 'York'
   3: ROLLBACK TRAN

Powyższe zapytanie, gdyby zakończylo się sukcesem spowodowałoby utratę integralności danych. Z racji tego, że w tabeli Hotel znajduję się klucz obcy zapytanie to zwróci następujący błąd

Msg 547, Level 16, State 0, Line 2

The UPDATE statement conflicted with the REFERENCE constraint "FK_Hotel_Subdivision". The conflict occurred in database "SQLTest", table "dbo.Hotel", column 'SubdivisionID'.

 

Sprawdzanie kluczy obcych w tabeli aktualizowanej

Aby przeprowadzic kolejny test najpierw będzie trzeba zaktualizować tabelę Hotel. W tym celu klucz obcy do tabeli Subdivision będzie musiał zostać tymczasowo wyłączony.
   1: BEGIN TRAN
   2: ALTER TABLE Hotel NOCHECK CONSTRAINT FK_Hotel_Subdivision
   3: UPDATE Hotel SET Subdivisionid = 'XX-ZZ' WHERE HotelId=1
   4: ALTER TABLE Hotel CHECK CONSTRAINT FK_Hotel_Subdivision
   5:  
   6: UPDATE Subdivision SET SubdivisionId = 'XX-ZZ' WHERE SubdivisionName = 'York'
   7: ROLLBACK TRAN
Powyższe zapytanie aktualizujące tabelę Subdivision jest takie samo jak w poprzednim teście, jednakże tym razem zwrócony zostanie następujący błąd:

Msg 547, Level 16, State 0, Line 6

The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Subdivisions_Country". The conflict occurred in database "SQLTest", table "dbo.Country", column 'CountryID'.

 

Sprawdzanie kluczy podstawowych, wartości unikalnych

Przetestowanie sprawdzanie kluczy podstawowych w tabeli Subdivision, przy jednoczesnym zachowaniu sekwencji testów jest niemożliwe.

 

Wyzwalacze

Ostatnim etapem weryfikacji danych i sprawdzania integralności wbudowanej w SQL Server są wyzwalacze.

W calu wykonania tego testu najpierw trzeba wykonać poniże zapytanie, które spraw, że nie pojawi się błąd związany z kluczami obcymi.
   1: INSERT INTO Country SELECT 'XX', 'Dummy', 'XXX', 0
   2: GO
Teraz ponownie wykonując następujące zapytanie zostanie uruchomiony wyzwalacz
   1: BEGIN TRAN
   2: ALTER TABLE Hotel NOCHECK CONSTRAINT FK_Hotel_Subdivision
   3: UPDATE Hotel SET Subdivisionid = 'XX-ZZ' WHERE HotelId=1
   4: ALTER TABLE Hotel CHECK CONSTRAINT FK_Hotel_Subdivision
   5:  
   6: UPDATE Subdivision SET SubdivisionId = 'XX-ZZ' WHERE SubdivisionName = 'York'
   7: ROLLBACK TRAN
Sprawdza on czy nowa wartość zaczyna się od XX i jeżeli tak jest zwrócony jest błąd.

Invalid SubdivisionId
Msg 50000, Level 1, State 16

Powyższe przykłady pokazały kolejność sprawdzania ograniczeń w bazie danych.

Powyższe rozważania zakończę planem wykonania zapytania aktualizującego dane w tabeli Subdivision. Proste zapytanie a operacji jest nie tak mało. Na planie można między innymi zauważyć kolejność sprawdzania kluczy obcych.

OgraniczeniaPlanWykonania

Dalsze rozwaania na temat powyższego planu pozostawie sobie na kolejny post.

czwartek, 9 lutego 2012

Rozmiar tabel i indeksów w bazie danych

Ostatnio zajmowałem się projektem strony, która hostowana jest na serwerze, gdzie nie było pełnej kontroli nad serwerem baz danych. Jednym z wymagań była możliwość monitorowania wielkości bazy danych. SQL Server umożliwia wykonanie tego zadania poprzez odpytywanie widoków systemowych. Wszystkie dane jakie były potrzebe do wykonania tego zadania znajdują się w 4 widokach.
Poniżej znajduję się skrypt i trochę przykładowych danych do testowania.
   1: create database MyTest   
   2: go 

Baza danych lotnisk

Tutaj możecie pobrać bazę danych lotnisk. Skrypt SQL został stworzony na podstawie bazy, którą można pobrac z tej lokalizacji. Lista nie jest aktualna i nie ma np. na niej lotniska w Warszawa-Modlin.

Baza danych państw

Tutaj możecie pobrać bazę danych państw. Została ona stworzona na podstawie tego arykułu.

Po odpaleniu wcześniej wspomnianych skryptów w bazie danych pojawią się dwie nowe tabele.

Sprawzdanie rozmiaru tabel i indeksów

I teraz odpalając poniższy skrypt uzyskamy rozmiar obiektów w bazie.
   1: DECLARE @TABLENAME VARCHAR(126)   
   2: SET @TABLENAME = NULL   
   3:  
   4: SELECT
   5:     TABLE_NAME,
   6:     INDEX_NAME,
   7:     TYPE_DESC,
   8:     INDEX_ID,
   9:     PARTITION_NUMBER,
  10:     RESERVED_PAGES * 8 AS RESERVED_SPACE,
  11:     USED_PAGES * 8 AS USED_SPACE,
  12:     DATA_PAGES * 8 AS DATA,
  13:     ROWS,
  14:     SUM(RESERVED_PAGES * 8) OVER (PARTITION BY TABLE_NAME) AS TOTAL_SPACE_RESERVED,
  15:     SUM(RESERVED_PAGES * 8) OVER (PARTITION BY TABLE_NAME) - SUM(USED_PAGES * 8) OVER (PARTITION BY TABLE_NAME) AS TOTAL_SPACE_UNUSED
  16: FROM (
  17:     SELECT
  18:         T.NAME AS TABLE_NAME,
  19:         I.NAME AS INDEX_NAME,
  20:         I.TYPE_DESC,
  21:         I.INDEX_ID,
  22:         P.PARTITION_NUMBER,
  23:         SUM(PS.RESERVED_PAGE_COUNT) AS RESERVED_PAGES,
  24:         SUM(PS.USED_PAGE_COUNT) AS USED_PAGES,
  25:         SUM(CASE
  26:             WHEN (PS.INDEX_ID < 2) THEN (IN_ROW_DATA_PAGE_COUNT + LOB_USED_PAGE_COUNT + ROW_OVERFLOW_USED_PAGE_COUNT)
  27:             ELSE LOB_USED_PAGE_COUNT + ROW_OVERFLOW_USED_PAGE_COUNT
  28:         END) AS DATA_PAGES,
  29:         SUM (CASE
  30:                 WHEN (PS.INDEX_ID < 2) THEN PS.ROW_COUNT
  31:                 ELSE 0
  32:             END) AS ROWS
  33:         FROM sys.indexes AS I   
  34:         INNER JOIN sys.tables T
  35:         ON T.OBJECT_ID = I.OBJECT_ID
  36:         INNER join sys.partitions AS P
  37:             ON I.OBJECT_ID = P.OBJECT_ID
  38:             AND I.INDEX_ID = P.INDEX_ID
  39:         INNER JOIN sys.dm_db_partition_stats AS PS
  40:             ON P.PARTITION_ID = PS.PARTITION_ID
  41:         GROUP BY T.NAME, I.NAME, I.TYPE_DESC, I.INDEX_ID, P.PARTITION_NUMBER
  42: ) AS stats
  43: WHERE ISNULL(@TABLENAME, TABLE_NAME) = TABLE_NAME

Modyfikując zmienną @TABLENAME uzyskane wyniki będą dotyczyły tylko jednej tabeli.