Lekcja 39. Indeksy

SQL Serwer organizuje utrwalone dane na stronach które są 8 KB jednostką należącą do pojedynczego obiektu. Strona jest najmniejszą jednostką zapisu i odczytu. Strony są grupowane w logiczne struktury po 8 stron zwane zakresami. Zakresy mogą zawierać strony z jednego (zakres jednolity) lub z wielu obiektów (zakres mieszany). SQL Serwer zapisuje pierwsze 8 stron obiektu w zakresie mieszanym. Jeżeli obiekt jest większy niż 8 stron to serwer alokuje dodatkowe zakresy jednolite dla tego obiektu. Dzięki tej organizacji małe obiekty marnują mniej przestrzeni pamięci a duże są mniej rozdrobnione.

Sterty i drzewa zrównoważone

Strony są częścią struktury fizycznej. SQL Serwer organizuje dane w stronach w strukturach logicznych. Tabele są organizowane jako sterta (ang. heap) lub drzewo zrównoważone (ang. balanced tree). Tabele zorganizowane jako drzewo są tabelami posiadającymi indeks klastrowany. Indeksy są zawsze zorganizowane jako drzewo zrównoważone. Inne indeksy które służą do szybkiego przeszukiwania wierszy są zwane indeksami nieklastrowymi.

Sterta

Sterta jest prostą strukturą. Dane na stercie nie są zorganizowane w żadnej logicznej kolejności. Sterta jest po prostu wiązką stron i zakresów. SQL Serwer śledzi strony i zakresy należące do obiektu poprzez specjalną stronę systemową zwaną Index Allocation Map (IAM). Każda tabela lub indeks posiada przynajmniej jedną stronę IAM zwaną pierwszą stroną IAM. Jeden IAM może wskazywać na około 4 GB pamięci. Duże obiekty mogą mieć więcej niż jedną stronę IAM. Strony IAM dla jednego obiektu są zorganizowane jako listy dwukierunkowe, każda strona ma wskaźnik na potomka i przodka. SQL Serwer przechowuje wskaźnik do pierwszej strony IAM w swoich wewnętrznych tabelach systemowych. Poniższy rysunek (źródło: SQL Server books online) przedstawia jak SQL Serwer używa stron IAM do pobrania wierszy z pojedynczej partycji. SQL Serwer potrafi znaleźć dane w stercie tylko poprzez przeskanowanie całej sterty. SQL Serwer używa stron IAM do skanowania sterty. Nawet jeżeli zapytanie chce pobrać tylko jeden wiersz to SQL Serwer skanuje całą stertę. Nowe wiersze są dodawane gdziekolwiek na stercie. Nowy wiersz może zostać dodany na istniejącej stronie lub utworzyć nową. Oznacza to, że sterta może być bardzo rozdrobniona. Aby lepiej zrozumieć stertę przeanalizujmy przykład. Poniższy kod tworzy tabelę zorganizowaną jako sterta:

CREATE TABLE dbo.TestStructure
(
id INT NOT NULL,
filler1 CHAR(36) NOT NULL,
filler2 CHAR(216) NOT NULL
);

Jeżeli nie utworzymy indeksu klastrowanego bezpośrednio lub poprzez nadanie klucza głównego lub ograniczenia unikalności to tabela jest zorganizowana jako sterta. SQL Serwer nie alokuje żadnych stron dla tabeli w momencie jej tworzenia. Alokuje pierwszą stronę i pierwszą stronę IAM kiedy dodany zostaje pierwszy wiersz. Informacje o tabelach i indeksach można odczytać z widoku sys.indexes. Poniższy kod poobiera informacje na temat utworzonej tabeli:

SELECT OBJECT_NAME(object_id) AS table_name,
name AS index_name, type, type_desc
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.TestStructure', N'U');

Wynik:

table_name index_name type type_desc ------------- ---------- ---- --------- TestStructure NULL 0 HEAP

Kolumna type przechowuje wartość 0 dla sterty, 1 dla tabeli z indeksem klajstrowanym i 2 dla indeksów nieklastrowanych. Aby zobaczyć ile stron jest przydzielonych dla obiektu używamy widoku sys.dm_db_index_physical_stats lub procedury dbo.sp_spaceused system. Ich użycie pokazane jest w skrypcie poniżej. Ponieważ będziemy wykorzystywać ten skrypt kilkukrotnie w tej lekcji to nazwiemy go "sprawdzeniem alokacji sterty".

SELECT index_type_desc, page_count,
record_count, avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats
(DB_ID(N'tempdb'), OBJECT_ID(N'dbo.TestStructure'), NULL, NULL , 'DETAILED');
EXEC dbo.sp_spaceused @objname = N'dbo.TestStructure', @updateusage = true;

Wynik:

index_type_desc page_count record_count avg_page_space_used_in_percent --------------- ---------- ------------ ------------------------------ HEAP 0 0 0 name rows reserved data index_size unused ------------- ---- -------- ---- ---------- ------ TestStructure 0 0 KB 0 KB 0 KB 0 KB

Widzimy, że tabela jest pusta i nie została przydzielona dla niej żadna strona. Kolumna avg_space_used_in_percent pokazuje wewnętrzną fragmentację. Wewnętrzna fragmentacja (ang. internal fragmentation) oznacza, że strony nie są pełne. Im więcej wierszy jest przechowywanych na jednej stronie tym mniej stron SQL Serwer musi odczytać aby je pobrać. W stercie nie ma dużej wewnętrznej fragmentacji ponieważ SQL Serwer przechowuje nowe wiersze w istniejących stronach jeżeli posiadają one wystarczającą ilość pamięci. Wstawmy pierwszy wiersz:

INSERT INTO dbo.TestStructure
(id, filler1, filler2)
VALUES
(1, 'a', 'b');

Teraz sprawdźmy alokację sterty:

index_type_desc page_count record_count avg_page_space_used_in_percent --------------- ---------- ------------ ------------------------------ HEAP 1 1 3.24932048430937 name rows reserved data index_size unused ------------- ---- -------- ---- ---------- ------ TestStructure 1 16 KB 8 KB 8 KB 0 KB

Tabela zajmuje jedną stronę dla jednego wiersza. Średnia przestrzeń strony jest mała ponieważ istnieje tylko jeden wiersz na stronie. Wynik procedury dbo.sp_spaceused pokazuje, że tabela posiada dwie strony zarezerwowane, jedną dla danych i jedną dla pierwszej strony IAM. Widać, że SQL Serwer alokuje jedną stronę i żadnego zakresu dla tabeli. Wstawmy teraz więcej wierszy wykonując poniższy kod:

DECLARE @i AS int = 1;
WHILE @i < 30
BEGIN
SET @i = @i + 1;
INSERT INTO dbo.TestStructure
(id, filler1, filler2)
VALUES
(@i, 'a', 'b');
END;

Sprawdźmy alokację sterty:

index_type_desc page_count record_count avg_page_space_used_in_percent --------------- ---------- ------------ ------------------------------ HEAP 1 30 98.1961947121324 name rows reserved data index_size unused ------------- ---- -------- ---- ---------- ------ TestStructure 30 16 KB 8 KB 8 KB 0 KB

Nadal zajęta jest tylko jedna strona. Strona ta nie zawiera żadnego rozdrobnienia ponieważ nie można wstawić do niej dodatkowego wiersza. Wstawmy dodatkowy wiersz:

INSERT INTO dbo.TestStructure
(id, filler1, filler2)
VALUES
(31, 'a', 'b');

Sprawdźmy alokację sterty:

index_type_desc page_count record_count avg_page_space_used_in_percent --------------- ---------- ------------ ------------------------------ HEAP 2 31 50.7227575982209 name rows reserved data index_size unused ------------- ---- -------- ----- ---------- ------ TestStructure 31 24 KB 16 KB 8 KB 0 KB

Teraz widać, że pojedyncza dodatkowa strona została dodana. Fragmentacja wzrosła ponieważ druga strona jest prawie pusta. Wypełnijmy 8 stron używając poniższego kodu:

DECLARE @i AS int = 31;
WHILE @i < 240
BEGIN
SET @i = @i + 1;
INSERT INTO dbo.TestStructure
(id, filler1, filler2)
VALUES
(@i, 'a', 'b');
END;

Sprawdźmy alokację sterty:

index_type_desc page_count record_count avg_page_space_used_in_percent --------------- ---------- ------------ ------------------------------ HEAP 8 240 98.1961947121324 name rows reserved data index_size unused ------------- ---- -------- ----- ---------- ------ TestStructure 240 72 KB 64 KB 8 KB 0 KB

Osiem stron jest zajętych. Co stanie się po dodaniu jeszcze jednego wiersza:

INSERT INTO dbo.TestStructure
(id, filler1, filler2)
VALUES
(241, 'a', 'b');

Sprawdźmy alokację sterty:

index_type_desc page_count record_count avg_page_space_used_in_percent --------------- ---------- ------------ ------------------------------ HEAP 9 241 87.6465530022239 name rows reserved data index_size unused ------------- ---- -------- ----- ---------- ------ TestStructure 241 136 KB 72 KB 8 KB 56 KB

Teraz widać, że tabela zajmuje 9 stron, 16 stron danych plus pierwszą stronę IAM są zarezerwowane dla tabeli. Procedura dbo.sp_spaceused pokazuje, że serwer zarezerwował 136 KB dla tabeli co oznacza 17 stron. 56 KB jest nadal nieużywane. Nieużywane 56 KB oznacza, że 7 stron z jednolitego zakresu nadal jest puste. pierwsze 8 stron pozostaje w zakresie mieszanym. Ponieważ tabela jest większa niż 8 stron to SQL Serwer za alokował jednolity zakres dla dodatkowych danych.

Indeks klastrowany

Tabela jest zorganizowana jako drzewo zrównoważone jeżeli ma utworzony indeks klastrowany. Każde drzewo posiada pojedynczą stronę zwaną korzeniem (ang. root) która jest węzłem głównym , wiele stron węzłów pośrednich i jedną lub więcej stron liści. Liście nie mają żadnych węzłów podrzędnych. Wszystkie dane w tabeli klastrowanej są przechowywane na stronach liści. Dane są rozmieszczone w kolejności logicznej wg klucza klastrowanego. Taki klucz może być założony na pojedynczej kolumnie lub na kilku kolumnach. Jeżeli klucz jest założony na wielu kolumnach to jest to klucz złożony (ang. composite key). Klucz może posiadać maksymalnie 16 kolumn a ich łączny rozmiar nie może przekraczać 900 bajtów. Ważne jest żeby zapamiętać, że dane są posortowane logicznie a nie fizycznie. SQL Serwer nadal używa stron IAM do rozmieszczenia danych fizycznie.

Klucz klastrowany jest tabelą

Kiedy tworzymy indeks klastrowany to nie kopiujemy danych w tabeli. Zamiast tego reorganizujemy dane w tabeli. Strony nad poziomem liści wskazują na strony liści. Wiersz na stronie powyżej poziomu liścia zawiera wartość klucza klastrowanego i wskaźnik do strony gdzie ta wartość się rozpoczyna na poziomie liści w porządku logicznym. Jeżeli do wszystkich stron liści można odnieść się z jednej strony to przydzielona jest tylko strona węzła głównego. Jeżeli wymagane jest więcej niż jedna strona do odniesienia się do stron poziomu liści, SQL Serwer tworzy stronę poziomu pośredniego która wskazuje na strony liści. Strona węzła głównego wskazuje wtedy na strony poziomów pośrednich. Jeżeli strona węzła głównego nie może wskazywać na wszystkie strony poziomu pośredniego to SQL Serwer tworzy nowy poziom pośredni. Strony na tym samym poziomie są zorganizowane jako dwukierunkowe listy. Dzięki temu SQL Serwer może znaleźć poprzednią i następną stronę w porządku logicznym dla każdej strony. SQL Serwer używa stron IAM to śledzenia zmian fizycznej alokacji stron drzewa. Do utworzenia indeksu klastrowanego można użyć kloumn gwarantujących unikalność lub nie. Jeżeli kolumny nie gwarantują unikalności to SQL Serwer zawsze wymusi taką unikalność klucza klastrowanego poprzez dodanie własnego identyfikatora liczbowego (ang. uniquifier) do powtarzających się wartości. Pierwsza wartość nie będzie posiadała dodatkowego identyfikatora lecz każda następna powtórzona wartość tak. Poniższy rysunek (źródło: SQL Server books online) przedstawia strukturę indeksu klastrowanego. SQL Serwer może użyć indeksu klastrowanego do wyszukania wiersza. Aby znaleźć wiersz w indeksie klastrowanym na rysunku SQL Serwer musi odczytać tylko trzy strony. Jeżeli tabela byłaby zorganizowana jako sterta to serwer musiałby przeszukać całą tabelę. Oczywiście jeżeli pobieramy wszystkie wiersze SQL Serwer skanuje również wszystkie strony na poziomie liści. Skan indeksu klastrowanego może być wykonany w porządku logicznym lub gdy nie jest potrzebny to w porządku alokacji lub fizycznym. Ponadto SQL Server może wykonać częściowe skanowanie jeśli sekwencyjne wiersze w porządku klucza klastrowanego są wymagane przez zapytanie. Są to niektóre zalety indeksu klastrowanego w porównaniu do sterty. Indeksy klastrowane mają również wady w porównaniu do sterty. Kiedy wstawiamy nowy wiersz do pełnej strony, SQL Serwer dzieli stronę na dwie i przenosi połowę wierszy do drugiej strony. Dzieje się tak ponieważ SQL Serwer musi zarządzać logicznom kolejnością wierszy. Powoduje to fragmentację wewnętrzną której nie ma w przypadku użycia sterty. Ponadto nowa strona lub nowy jednolity zakres dla dużej tabeli może być zarezerwowany gdziekolwiek w pliku danych. Fizyczna kolejność stron i zakresów tabeli klastrowanej nie musi być taka sama jak kolejność logiczna. Jeżeli strony są fizycznie nieuporządkowane to indeks klastrowany jest logicznie pofragmentowany. Nosi to nazwę fragmentacji zewnętrznej. Może ona spowolnić pełne lub częściowe skanowanie w porządku logicznym. W większości przypadków zalety indeksu klastrowanego przewyższają jego wady. Wewnętrzną fragmentację można kontrolować poprzez opcję FILEFACTOR dla stron poziomu liści oraz PAD_INDEX dla stron wyższych poziomów przy tworzeniu indeksu za pomocą polecenia CREATE INDEX. Indeksy można przebudowywać lub reorganizować aby pozbyć się fragmentacji zewnętrznej. Służą do tego polecenia ALTER INDEX...REORGANIZE oraz ALTER INDEX...REBUILD. Krótki klucz klastrowany oznacza, że można zmieścić więcej wierszy na stronach powyżej poziomu liści. Dlatego potencjalnie wymagane jest mniej poziomów. Mniej poziomów to bardziej efektywny indeks ponieważ SQL Serwer odczytuje mniej wierszy aby odnaleźć wiersz. Dodanie przez SQL Serwer identyfikatora gwarantującego unikalność klucza klastrowanego rozszerza klucz dlatego krótki i unikalny klucz jest preferowany do wyszukiwania. Jest to typowe dla aplikacji OLTP. W takich aplikacjach wybranie sekwencyjnego numeru jako klucz klastrowany jest dobrą decyzją. jednakże w hurtowniach danych wiele zapytań odczytuje wielkie ilości danych, zwykle uporządkowanych. Przykładowo wiele hurtowni danych szuka wierszy w kolejności kolumn zawierających daty. W takich przypadkach lepszym rozwiązaniem może być nadanie indeksu klastrowanego na kolumnie daty. Aby nauczyć się więcej o indeksie klastrowanym wykonamy kilka przykładów. Poniższy kod czyści tabelę stworzoną w sekcji omawiającej stertę oraz reorganizuje tabelę w drzewo zrównoważone poprzez użycie kolumny id jako klucz klastrowany.

TRUNCATE TABLE dbo.TestStructure;
CREATE CLUSTERED INDEX idx_cl_id ON dbo.TestStructure(id);

Sprawdźmy czy indeks został założony w widoku sys.indexes:

SELECT OBJECT_NAME(object_id) AS table_name,
name AS index_name, type, type_desc
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.TestStructure', N'U');

Wynik:

table_name index_name type type_desc ------------- ---------- ---- --------- TestStructure idx_cl_id 1 CLUSTERED

Kolumna typ zmieniła się na 1 czyli sterta została zreorganizowana w indeks klastrowany. Wstawmy 621 stron do tabeli poprzez wstawienie unikalnych wartości klucza klastrowanego:

DECLARE @i AS int = 0;
WHILE @i < 18630
BEGIN
SET @i = @i + 1;
INSERT INTO dbo.TestStructure
(id, filler1, filler2)
VALUES
(@i, 'a', 'b');
END;

Jeżeli chcemy aby wartości były unikalne powinniśmy stworzyć klucz główny lub ograniczanie unikalności na wybranych polach tabeli. Można również stworzyć indeks unikalny, jednakże ponieważ unikalność jest ograniczeniem wartości to powinniśmy użyć ograniczenia. Podstawowe informacje o indeksie możemy pobrać odpytując funkcję sys.dm_db_index_physical_stats. Poniższy kod będzie używany wielokrotnie w przykładach więc nazwiemy go "sprawdzeniem alokacji indeksu klastrowanego".

SELECT index_type_desc, index_depth, index_level, page_count,
record_count, avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats
(DB_ID(N'tempdb'), OBJECT_ID(N'dbo.TestStructure'), NULL, NULL , 'DETAILED');

Wynik zapytania:

index_type_desc index_depth index_level page_count record_count avg_pg_spc_used_in_pct --------------- ----------- ----------- ---------- ------------ ---------------------- CLUSTERED INDEX 2 0 621 18630 98.1961947121324 CLUSTERED INDEX 2 1 1 621 99.7158388930072

Jak widać, indeks ma dwa poziomy, poziom liści i stronę węzła głównego. Strona węzła głównego ma 621 wierszy które wskazują 621 stron liści. W tej chwili nie ma wewnętrznej fragmentacji. Wstawmy dodatkowy wiersz:

INSERT INTO dbo.TestStructure
(id, filler1, filler2)
VALUES
(18631, 'a', 'b');

Wynik sprawdzenia alokacji indeksu klastrowanego:

index_type_desc index_depth index_level page_count record_count avg_pg_spc_used_in_pct --------------- ----------- ----------- ---------- ------------ ---------------------- CLUSTERED INDEX 3 0 622 18631 98.0435507783543 CLUSTERED INDEX 3 1 2 622 49.9258710155671 CLUSTERED INDEX 3 2 1 2 0.296515937731653

Teraz indeks ma trzy poziomy. ponieważ nowa strona została przydzielona do poziomu liści to strona węzła głównego nie może wskazywać wszystkich stron liści. SQL Serwer dodał poziom pośredni z dwoma stronami wskazującymi 662 strony liści. teraz węzeł główny wskazuje na dwie strony warstwy pośredniej. Aby pokazać wpływ dodatkowego identyfikatora dodawanego do nieunikalnych wartości klucza klastrowanego wyczyścimy tabelę i wstawimy do niej 423 strony używając nie unikalnych wartości klucza:

TRUNCATE TABLE dbo.TestStructure;
DECLARE @i AS int = 0;
WHILE @i < 8908
BEGIN
SET @i = @i + 1;
INSERT INTO dbo.TestStructure
(id, filler1, filler2)
VALUES
(@i % 100, 'a', 'b');
END;

Wynik sprawdzenia alokacji indeksu klastrowanego:

index_type_desc index_depth index_level page_count record_count avg_pg_spc_used_in_pc --------------- ----------- ----------- ---------- ------------ ---------------------- CLUSTERED INDEX 2 0 423 8908 70.9815171732147 CLUSTERED INDEX 2 1 1 423 99.8393872003954

Strona węzła głównego może wskazywać tylko 423 strony. Aby wypełnić dwa poziomy indeksu trzeba 8,908 wierszy gdzie dla klucza unikalnego SQL Serwer mógł wstawić 18,630 wierszy w dwóch poziomach. Dodajmy kolejny wiersz:

INSERT INTO dbo.TestStructure
(id, filler1, filler2)
VALUES
(8909 % 100, 'a', 'b');

Wynik sprawdzenia alokacji indeksu klastrowanego:

index_type_desc index_depth index_level page_count record_count avg_pg_spc_used_in_pc --------------- ----------- ----------- ---------- ------------ ---------------------- CLUSTERED INDEX 3 0 424 8909 70.8220039535458 CLUSTERED INDEX 3 1 2 424 50.0370644922165 CLUSTERED INDEX 3 2 1 2 0.395354583642204

Jak widać SQL Serwer musiał dodać dodatkowy poziom do indeksu. Do tej pory wartości indeksu były sekwencyjne. Co stanie się gdy nie będą? Poniższy kod czyści tabelę, usuwa indeks klastrowany i tworzy nowy używający kolumny filter1 jako klucz klastrowany po czym dodaje 9000 wierszy do tabeli z unikalnymi sekwencyjnymi wartościami klucza:

TRUNCATE TABLE dbo.TestStructure;
DROP INDEX idx_cl_id ON dbo.TestStructure;
CREATE CLUSTERED INDEX idx_cl_filler1 ON dbo.TestStructure(filler1);
DECLARE @i AS int = 0;
WHILE @i < 9000
BEGIN
SET @i = @i + 1;
INSERT INTO dbo.TestStructure
(id, filler1, filler2)
VALUES
(@i, FORMAT(@i,‘0000’), 'b');
END;

Sprawdźmy fragmentację poprzez użycie skryptu który będziemy nazywali "sprawdzeniem fragmentacji". kolumna avg_page_space_used_in_percent oznacza wewnętrzną fragmentację a kolumna avg_fragmentation_in_percent oznacza fragmentację zewnętrzną:

SELECT index_level, page_count,
avg_page_space_used_in_percent, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(DB_ID(N'tempdb'), OBJECT_ID(N'dbo.TestStructure'), NULL, NULL , 'DETAILED');

Wynik sprawdzenia fragmentacji:

index_level page_count avg_page_space_used_in_percent avg_fragmentation_in_percent ----------- ---------- ------------------------------ ---------------------------- 0 300 98.1961947121324 1.66666666666667 1 3 55.5720286632073 0 2 1 1.64319248826291 0

jak widać indeks ma trzy poziomy. nie ma fragmentacji wewnętrznej na poziomie liści oraz prawie żadnej fragmentacji zewnętrznej. Strony na poziomie liści są pełne i porządek logiczny jest prawie taki sam jak porządek fizyczny. Teraz wyczyśćmy tabelę i wypełnijmy ją wartościami losowymi w kolumnie klucza indeksu. Poniższy skrypt używa funkcji NEWID() która generuje losowe GUIDy dla kolumny filler1:

TRUNCATE TABLE dbo.TestStructure;
DECLARE @i AS int = 0;
WHILE @i < 9000
BEGIN
SET @i = @i + 1;
INSERT INTO dbo.TestStructure
(id, filler1, filler2)
VALUES
(@i, CAST(NEWID() AS CHAR(36)), 'b');
END;

Wynik sprawdzenia fragmentacji:

index_level page_count avg_page_space_used_in_percent avg_fragmentation_in_percent ----------- ---------- ------------------------------ ---------------------------- 0 432 68.1842599456387 98.6111111111111 1 4 60.0197677291821 50 2 1 2.19915987150976 0

jak widać poziom liści ma tylko 68% wypełnienia przez wiersze. Dzieje się tak ponieważ SQL Serwer dokonał wielu podziałów stron. Fragmentacja zewnętrzna jest na poziomie 99%, prawie żadne strony nie są zgodne w porządku logicznym w porównaniu do porządku fizycznego. Jak widać użycie GUID dla klucza klastrowanego oże prowadzić do dosyć nieefektywnych indeksów. Zewnętrzna fragmentacja zwykle spowalnia skanowanie, które nie powinny być tak często w srodowisku OLTP lecz w hurtowniach danych jest bardzo istotne. Wewnętrzna jest problemem w obu scenariuszach ponieważ tabele są o wiele większe w porównaniu do tabel z kluczem sekwencyjnym. Aby poradzić sobie z fragmentacją można przebudowywać lub reorganizować indeksy. Reorganizacja jest wolniejsza lecz mniej inwazyjna niż przebudowanie indeksu. Ogólne wytyczne mówią o reorganizowaniu indeksów kiedy fragmentacja zewnętrzna jest mniejsza niż 30% i przebudowanie jeżeli jest większa. Poniższy kod przebudowuje indeks:

ALTER INDEX idx_cl_filler1 ON dbo.TestStructure REBUILD;

Wynik sprawdzenia fragmentacji:

index_level page_count avg_page_space_used_in_percent avg_fragmentation_in_percent ----------- ---------- ------------------------------ ---------------------------- 0 300 98.1961947121324 0.666666666666667 1 2 83.3703978255498 0 2 1 1.08722510501606 0

Indeks nieklastrowany

Indeks nieklastrowany ma bardzo podobną strukturę do indeksu klastrowanego. Węzeł główny i poziomy pośrednie wyglądają tak samo jak w indeksie klastrowanym. Poziom liści jest inny ponieważ nie przechowuje wszystkich danych jak to ma miejsce w indeksie klastrowanym. To co jest przechowywane na poziomie liści indeksu nieklastrowanego zależy od organizacji tabeli. Jedna tabela może mieć do 999 indeksów nieklastrowanych. Poziom liści indeksu nieklastrowanego zawiera klucze indeksu i lokalizatory wierszy (ang. row locators). W kluczu można mieć do 16 kolumn i ich rozmiar musi być mniejszy niż 900 bajtów. Lokalizator wiersza wskazuje na wiersz w tabeli. Jeżeli tabela jest stertą to lokalizator wiersza jest zwany identyfikatorem wiersza (ang. row identifier), w skrócie RID. Jest on 8 bajtowym wskaźnikiem zawierającym identyfikator pliku bazy danych (ang. database file ID) , identyfikator strony w której leży wiersz (ang. page ID) oraz identyfikator wiersza na stronie (ang. target row ID). W celu odnalezienia wiersza, SQL Serwer musi przejść przez indeks do poziomu liści i odczytać odpowiednią stronę ze sterty po czym przejść do wiersza na tej stronie. Operacja pobrania wiersza ze sterty zwana jest wyszukaniem RID (ang. RID lookup). Jeżeli zapytanie jest selektywne i wyszukuje jeden wiersz lub małą ilość wierszy to przeszukanie indeksu za pomocą wyszukiwania RID jest bardzo wydajne. Ponieważ strony na tym samym poziomie indeksu są połączone w listy dwukierunkowe to SQL Serwer może przeprowadzić częściowe lub pełne skanowanie indeksu nieklastrowanego a następnie przeprowadzić wyszukiwanie RID bez startowania od węzła głównego dla każdego wiersza. Jednakże wraz ze wzrostem liczby wierszy pobieranych przez zapytanie wyszukiwanie RID staje się coraz bardziej kosztowne ponieważ koszt wyszukania RID jest zazwyczaj jedna strona na wiersz. Jeżeli tabela posiada indeks klastrowany to lokalizatorem wiersza jest klucz klastrowany. Oznacza to, że SQL Serwer wyszukując wiersz musi przejść wszystkie poziomy indeksu nieklastrowanego po czym przejść wszystkie poziomy indeksu klastrowanego. Operacja ta jest nazywana wyszukiwaniem klucza (ang. key lookup). Na pierwszy rzut oka może wydawac się to gorszym rozwiązaniem niż pobieranie strony ze sterty. Jednakże ponieważ lokalizator wiersza wskazuje na logiczną strukturę a nie na fizyczną to nie ma znaczenia gdzie wiersz jest przechowywany fizycznie. Oznacza to, że można dowolnie reorganizować lub przebudowywać indeks klastrowany. Dopuki nie zmienimy klucza klastrującego SQL Serwer nie musi aktualizować indeksu nieklastrowanego. Jeżeli wiersz na stercie jest przeniesiony to SQL Serwer musi zaktualizować wszystkie indeksy nieklastrowane tak aby znały nowe położenie wiersza. SQL Serwer posiada optymalizację uwzględniającą aktualizację sterty. Jeżeli wiersz zostaje przeniesiony na inną stronę to SQL Serwer pozostawia po nim wskaźnik do nowej lokalizacji dzięki czemu nie musi aktualizować indeksów nieklastrowanych. Jednakże nawet z tą optymalizacją lepszą praktyką jest organizowanie tabel z indeksem klastrowanym. jeżeli klucz klastrujący jest wąski (np 4 bajtowy int) to SQL Serwer może pomieścić więcej wierszy na poziomie liści w porównaniu do użycia RID jako lokalizatora wierszy. Poniższa ilustracja przedstawia strukturę indeksu nieklastrowanego w jednej partycji (źródło: SQL Server books online): Indeks klastrowany powinien być krótki i unikalny ponieważ pojawia się w każdym indeksie nieklastrowanym. jednakże nie jest to ogólna zasada, w przypadku hurtowni danych często preferuje się klucze klastrowane obsługujące częste skanowania częściowe. Klucz klastrowany nie powinien być zbyt często aktualizowany a najlepiej wcale. Jeżeli aktualizujemy klucz klastrowany to SQL Serwer musi zaktualizować wszystkie klucze nieklastrowane. Klucz klastrowany powinien być stworzony przed kluczami nieklastrowanymi. Jeżeli zmienimy organizację tabeli ze sterty na indeks klastrowany lub odwrotni poprzez stworzenie lub usunięcie indeksu klastrowanego to SQL Serwer będzie musiał stworzyć od nowa wszystki indeksy nieklastrowane. Indeksy nieklastrowane mogą być indeksami filtrującymi. Indeks filtrujący obejmuje tylko podzbiór wartości kolumny a więc odnosi się do podzbioru wierszy tabeli. Indeksy filtrujące są użyteczne kiedy pewne wartości w kolumnie występują rzadko a inne częściej. W takim przypadku możemy utworzyć indeks tylko dla rzadkich wartości. SQL Serwer użyje takiego indeksu do wyszukania rzadkich wartości lecz wykona skan dla częstych wartości. Indeksy filtrowane są tanie w utrzymaniu ponieważ SQL Serwer musi je zaktualizować tylko dla zmian rzadkich wartości. Indeks filtrowany tworzymy poprzez dodanie klauzuli WHERE do polecenia CREATE INDEX. Przykładowo można użyć indeksu filtrowanego do wymuszenia filtrowanej unikalności. Przykładowo mamy kolumnę która dopuszcza NULL dla wielu wierszy lecz pozostałe wartości muszą być unikalne. Nie można utworzyć filtrowanego klucza głównego lub ograniczenia unikalności lecz mozna stworzyć unikalny nieklastrowany indeks filtrujący wszystkie wartości oprócz NULL.

COLUMNSTORE INDEKS

SQL Serwer 2012 posiada specjalny sposób przechowywania indeksów nieklastrowanych. W przeciwiwństwie do przechowywnia wierszami, SQL Serwer 2012 potrafi przechowywać dane indeksów kolumnami. Indeks ten potrafi przyspieszyć zapytania hurtowni danych z dużym współczynnikiem, od 10 do nawet 100 razy. COLUMNSTORE indeks jest indeksem nieklastrowanym na tabeli. Optymalizator zapytań rozważa użycie tego indeksu w czasie fazy optymalizacji zapytania tak jak robi to z innymi indeksami. COLUMNSTORE indeks jest przechowany jako skompresowany. Współczynnik kompresji może być do 10 razy w porównaniu z rozmiarem oryginalnego indeksu. Kiedy zapytanie odnosi się do kolumny która jest częścią indeks COLUMNSTORE to SQL Serwer pobierze tylko tę kolumnę z dysku. Zmniejszza to I/O i zużycie pamięci podręcznej. COLUMNSTORE indeks używa własnego algorytmu kompresji, nie można użyć dla niego kompresji typu ROW lub PAGE. Z drugiej strony SQL Serwer zwraca w wyniku wiersze które muszą być zrekonstruowane w czasie wykonywania zapytania. Zajmuje to czas i zasoby. Bardzo selektywne zapytania pobierające tylko kilka wierszy mogą nie odnieść korzyści z użycia indeksu COLUMNSTORE. COLUMNSTORE indeks przyspiesza zapytania hurtowni danych a nie OLTP. Z powodu rekonstrukcji wierszy i rzeczy związanych z aktualizacją danych tabele zawierają COLUMNSTORE indeks są tylko do odczytu. jeżeli czcemy zaktualizować dane w takiej tabeli to musimy pierw usunąć indeks COLUMNSTORE. COLUMNSTORE indeks jest podzielony na jednostki zwane segmentami. Segmenty są przechowywane jako wielkie obiekty i składają się z wielu stron. Segmenty są jednostką transferu z dysku do pamięci. Każdy segment posiada metadane które zawierają minimalna i maksymalną wartość dla każdej kolumny segmentu. Pozwala to na wczesną eliminację segmentów w czasie ładowania do pamięci.

Widoki indeksowane

Zapytania agregujące dane i używające wielu złączeń można zoptymalizować. Przykładowo można stworzyć nową tabelę używając tych danych i utrzymywać tę tabelę w procesie ETL. Jednakże tworzenie dodatkowej tabeli dla połączonych i agregowanych danych nie jest najlepszą praktyką, ponieważ użycie tej tabeli oznacza zmiany w zapytaniach raportujących. Można użyć innej opcji do przechowywania połączonych i z agregowanych danych. Można stworzyć widok łączący tabele i agregujący dane. Później można założyć indeks na tym widoku i stworzyć widok indeksowany. Podczas indeksowania dane zostają utrwalone. W wersji Enterprise SQL Serwer 2012 optymalizator używa widoków indeksowanych automatycznie - bez potrzeby zmiany zapytania. SQL Serwer również zarządza automatycznie widokami indeksowanymi. jednakże aby przyspieszyć ładowanie danych można usunąć lub dezaktywować indeks i odtworzyć lub przebudować po zakończeniu ładowania danych. Widoki indeksowane mają wiele ograniczeń, restrykcji i wstępnych wymagań i aby zapoznać się z nimi dokładnie należy przestudiować Books Online. Przykładowo wykonamy prosty test pokazujący jak widoki indeksowane działają i jak mogą być użyteczne. poniższe zapytanie agreguje kolumnę ilości z tabeli Sales.OrderDetails względem kraju z tabeli Sales.Orders. Kod bada również użycie I/O:

USE TSQL2012;
SET STATISTICS IO ON;
-- Aggregate query with a join
SELECT O.shipcountry, SUM(OD.qty) AS totalordered
FROM Sales.OrderDetails AS OD
INNER JOIN Sales.Orders AS O
ON OD.orderid = O.orderid
GROUP BY O.shipcountry;

Zapytanie wykonało 11 odczytów logicznych na tabeli Sales.OrderDetails i 21 na tabeli Sales.Orders. Stwórzmy widok indeksowany używając kodu poniżej:

-- Create a view from the query
CREATE VIEW Sales.QuantityByCountry
WITH SCHEMABINDING
AS
SELECT O.shipcountry, SUM(OD.qty) AS total_ordered,
COUNT_BIG(*) AS number_of_rows
FROM Sales.OrderDetails AS OD
INNER JOIN Sales.Orders AS O
ON OD.orderid = O.orderid
GROUP BY O.shipcountry;
GO
-- Index the view
CREATE UNIQUE CLUSTERED INDEX idx_cl_shipcountry
ON Sales.QuantityByCountry(shipcountry);
GO

Ważną rzeczą przy tworzeniu widoku indeksowanego jest opcja SCHEMABINDING. Ponadto należy użyć funkcji COUNT_BIG. Po utworzeniu indeksu możemy wykonać zapytanie raz jeszcze i przekonać się, że wykonano tylko dwa odczyty logiczne na widoku Sales.QuantityByCountry. Wyczyśćmy zmiany uruchamiając kod:

SET STATISTICS IO OFF;
DROP VIEW Sales.QuantityByCountry;

Ćwiczenia

I. Analiza indeksów nieklastrowanych na stercie

Użyj bazy danych TEMPDB. Ustaw opcję NOCOUNT na ON:
```
USE tempdb;
SET NOCOUNT ON;
```

Stwórz tabelę dbo.TestStructure używając poniższego kodu:

CREATE TABLE dbo.TestStructure
(
id INT NOT NULL,
filler1 CHAR(36) NOT NULL,
filler2 CHAR(216) NOT NULL
);
GO

Ponieważ nie utworzono indeksu klastrowanego to tabela jest zorganizowana jako sterta. Utwórz indeks nieklastrowany na kolumnie filler1 używając kodu:
```
CREATE NONCLUSTERED INDEX idx_nc_filler1 ON dbo.TestStructure(filler1);
```

Sprawdź w widoku sys.indexes czy utworzona tabela jest stertą i posiada indeks nieklastrowany:

SELECT OBJECT_NAME(object_id) AS table_name,
name AS index_name, type, type_desc
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.TestStructure', N'U');

Wstaw 24,472 wiersze do tabeli z wartościami sekwencyjnymi w kolumnie filler1:

DECLARE @i AS int = 0;
WHILE @i < 24472
BEGIN
SET @i = @i + 1;
INSERT INTO dbo.TestStructure
(id, filler1, filler2)
VALUES
(@i, FORMAT(@i,’0000’), 'b');
END;

Użyj funkcji sys.dm_db_index_physical_stats do sprawdzenia ilości poziomów indeksu nieklastrowanego i ilości zapisanych stron na każdym poziomie:

SELECT index_type_desc, index_depth, index_level,
page_count, record_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'tempdb'), OBJECT_ID(N'dbo.TestStructure'), NULL, NULL , 'DETAILED');

Dodajmy kolejny wiersz:

INSERT INTO dbo.TestStructure
(id, filler1, filler2)
VALUES
(24473, '24473', 'b');

Sprawdź ilość poziomów i stertę jeszcze raz:

SELECT index_type_desc, index_depth, index_level,
page_count, record_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'tempdb'), OBJECT_ID(N'dbo.TestStructure'), NULL, NULL , 'DETAILED');

II. Analiza indeksów nieklastrowanych w tabeli klastrowanej

Wyczyść tabelę utworzoną w poprzednim ćwiczeniu i stwórz indeks klastrowany na kolumnie id:

TRUNCATE TABLE dbo.TestStructure;
CREATE CLUSTERED INDEX idx_cl_id ON dbo.TestStructure(id);
GO

Sprawdź w widoku sys.indexes czy utworzona tabela jest drzewem zrównoważonym i posiada indeks nieklastrowany:

SELECT OBJECT_NAME(object_id) AS table_name,
name AS index_name, type, type_desc
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.TestStructure', N'U');

Wstaw 28,864 wiersze do tabeli z sekwencyjną wartością kolumny filler1:

DECLARE @i AS int = 0;
WHILE @i < 28864
BEGIN
SET @i = @i + 1;
INSERT INTO dbo.TestStructure
(id, filler1, filler2)
VALUES
(@i, FORMAT(@i,’0000’), 'b');
END;

Sprawdź ilość poziomów indeksu nieklastrowanego i klastrowanego:

SELECT index_type_desc, index_depth, index_level,
page_count, record_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'tempdb'), OBJECT_ID(N'dbo.TestStructure'), NULL, NULL , 'DETAILED');

Dodaj kolejny wiersz:

INSERT INTO dbo.TestStructure
(id, filler1, filler2)
VALUES
(28865, '28865', 'b');

Sprawdź jeszcze raz ilość poziomów indeksu nieklastrowanego i klastrowanego:

SELECT index_type_desc, index_depth, index_level,
page_count, record_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'tempdb'), OBJECT_ID(N'dbo.TestStructure'), NULL, NULL , 'DETAILED');

Wyczyść bazę TEMPDB:
```
DROP TABLE dbo.TestStructure;
```

Podsumowanie

Struktura tabeli może być zorganizowana jako sterta lub drzewo zrównoważone. Jeżeli jest zorganizowana jako drzewo zrównoważone oznacza, że posiada indeks klastrowany.
Indeks nieklastrowany można utworzyć na stercie lub drzewie zrównoażonym.
Można również dodać indeks do widoku.