Rozdział 13.
Indeksowanie dla poprawy wydajności

W rozdziale 12. zostały omówione rozszerzenia programowe SQL Servera i Transact-SQL, jak również transakcje. Transakcje pozwalają na logiczne grupowanie modyfikacji dokonywanych w bazie danych. Wymagane jest, aby te logiczne grupy były w całości zatwierdzone lub w całości wycofane jako pojedyncze wykonywane jednostki. Zostało również omówione blokowanie wykorzystane w ochronie relacyjnej bazy danych. Blokowanie pozwala stworzyć wrażenie, że baza danych wygląda jak system pojedynczego użytkownika.

Niniejszy rozdział omawia indeksy. Indeksy dostarczają zbioru logicznych wskaźników do danych, podobnie jak indeksy w książkach, które pomagają w znalezieniu potrzebnych wyrażeń. Chociaż wszystkie omówione wcześniej wyrażenia (SELECT, INSERT, UPDATE, DELETE) działały bez indeksów, na ogół działają szybciej z indeksami.

Niniejszy rozdział rozpoczyna się omówieniem przydatności indeksów, a następnie przedstawieniem podstaw działania indeksów o strukturze drzewa B+ (typ indeksu implementowany przez SQL Server). Następnie zostanie omówiona składnia polecenia CREATE INDEX. Przedstawionych zostanie kilka opcji i problemów związanych z wydajnością w kontekście indeksów. Kolejnym zagadnieniem będą indeksy na widokach i sytuacje, w których indeksy są bardzo potrzebne. Następnie podane zostaną niektóre z poleceń DBCC, używane z indeksami, jak również inne zagadnienia związane z pielęgnacją indeksów. Ostatnim zagadnieniem będzie przegląd możliwości indeksowania pełnotekstowego SQL Servera 2000.

Dlaczego używać indeksów?

Można wprowadzać indeksy z wielu przyczyn, ale najbardziej zwyczajna przyczyna była już wymieniana — szybkość. Bez stosowania indeksów SQL Server korzysta z danych czytając każdą stronę danych w każdej z tablicy, które zostały określone w poleceniu SQL. Takie skanowanie tablicy (czytanie każdej strony danych), może być świetną metodą pobierania danych. Przykładowo, jeżeli tablica jest mała lub gdy sięga się do dużej części tablicy, skanowanie tablicy może być najlepszym sposobem dostępu do danych. Jednak, bardzo często dostęp do danych jest znacznie szybszy z indeksowaniem. Indeksowanie może również przyspieszyć złączenia tablic.

Inną przyczyną tworzenia indeksu jest wymuszenie unikalności. Dwa identyczne wiersze w tablicy nie warunkują błędu. Jednak, nie jest to metoda przechowywania danych, którą chce wykorzystywać większość ludzi. Można sobie wyobrazić system śledzący klientów. Jeżeli nie można jednoznacznie określić klientów, można mieć problemy z ich utrzymaniem, gdy rachunki dla nich będą nieprawidłowe. Istnieje kilka opcji do unikalnej identyfikacji klientów. Można nadać im numery, używać łącznie ich imion i dat urodzenia, używać numerów ich kart kredytowych lub używać innych wartości lub zbiorów wartości. Bez względu na wybór, sposobem poinformowania SQL Server o wyborze jest zastosowanie unikalnego indeksu. W rozdziale 14 zostanie omówiony inny sposób wymuszenia unikalności — więzy unikalne — ale nawet wtedy SQL Server będzie nadal tworzyć unikalny indeks jako narzucony mechanizm.

Struktury indeksu

Na ogół indeks składa się ze zbioru stron zwanego drzewem B+. Drzewo B+ wygląda podobnie jak na pokazano na rysunku 13.1

--> Rysunek[Author:AK] 13.1. Drzewo B+.

Jak zostało wcześniej powiedziane, indeks pomaga w szybkim znalezieniu danych. Aby znaleźć odpowiedni wiersz danych, należy przeszukiwać B+ drzewo aż do znalezienia wiersza a następnie przesuwać się w strukturze drzewa do indywidualnego wiersza danych. Rozpoczyna się od strony głównej. Wskaźnik do strony głównej jest umieszczony w tablicy systemowej sysindexes (w kolumnie zwanej root dla indeksów niezgrupowanych). Strona główna zawiera wpisy indeksu (dane z indeksowanej kolumny lub kolumn), oraz wskaźniki do każdej strony poniżej strony głównej. Każdy indeks może mieć jeden lub więcej poziomów pośrednich. Każdy wpis posiada wartość indeksu i wskaźnik do następnej strony poniżej.

Na stronach liścia (najniższy poziom w drzewie), to co się tam znajduje zależy od tego, czy tablica ma indeks zgrupowany. Logicznie mówiąc, można znaleźć wpis dla każdego wiersza w tablicy, który został indeksowany, jak również wskaźnik do strony danych i numeru wiersza, który ma aktualny wiersz danych. Jeżeli tablica ma również indeks zgrupowany, wszelkie niezgrupowane indeksy zawierają raczej wartości klucza z indeksu zgrupowanego niż informacje o stronie danych i numerze wiersza. Indeksy zgrupowane i niezgrupowane zostaną wytłumaczone później, ale teraz należy pomyśleć o indeksach zgrupowanych jako o sposobie do wcześniejszego posortowania bieżących danych. Tablice bez indeksów zgrupowanych nazywane są stosem (heaps), a indeksy niezgrupowane są osobnymi strukturami indeksów, które nie sortują bezpośrednio danych.

Dane są przechowywane w stronach zwanych stronami danych. Każda strona ma rozmiar 8,192 bajtów z nagłówkiem o wielkości 96 bajtów. Czyli, każda strona ma 8,096 bajtów dostępnych na przechowywanie danych. Każda strona na taką samą strukturę podstawową.

Rysunek 13.2 pokazuje przykład jak może wyglądać indeks. Ten indeks jest założony na kolumnę imię (first_name).

--> Rysunek[Author:AK] 13.2. Przykładowy indeks B+.

Każdy poziom indeksu jest listą podwójnie łączoną. Każda strona ma informacje o stronie, która jest logicznie przed nią i po niej. Na poziomie głównym i poziomach pośrednich indeksu, każda wartość indeksu jest pierwszą wartością na stronie, na kolejnym niższym poziomie. Poziom liścia indeksu zawiera jeden wpis dla każdego wiersza w tablicy. Warto zauważyć, że indeks jest sortowany w oparciu o kolumnę (lub kolumny), która została wybrana jako klucz indeksu. Kolejność ta nie zmienia fizycznej kolejności sortowania danych.

Kiedy dane w tablicy są modyfikowane, modyfikowany jest również każdy indeks w tablicy. SQL Server gwarantuje spójność pomiędzy danymi w tablicach a ich indeksami. Jest to dobre, gdy chce się mieć doskonałą integralność danych. Jednak, oznacza to również, że operacje INSERT, UPDATE i DELETE, które powinny być szybkie, mogą mieć trochę więcej pracy i mogą działać nieznacznie wolniej. Dodawanie nowego wiersza przy pomocy polecenia INSERT, zajmuje normalnie dwie operacje wejścia/wyjścia (I/O) — jedną dla danych, jedną dla dziennika. Jeżeli w danej tablicy są dwa indeksy, dodawanie wiersza wymaga co najmniej dwóch kolejnych I/O, a być może więcej. Należy odpowiednio wyważyć swoje potrzeby związane z modyfikacją danych a realizacją szybszych zapytań.

Jak korzystać z indeksu? Na rysunku 13.2. należy znaleźć wiersz danych. Przyjmijmy, że indeks jest założony na kolumnę imię (first_name). Aby znaleźć wiersz z imieniem Donald, rozpoczyna się od strony głównej (korzeń drzewa). Ponieważ Donald jest „niżej” niż John, należy przejść do wpisu Andy na stronie 31. Na stronie 31 okazuje się, że Donald jest „wyżej” niż David ale „niżej” niż Hank, czyli następuje przejście do strony 22. Na stronie 22, można przeczytać, że wpis dla imienia Donald wskazuje na stronę 1 (która jest stroną danych ponieważ jest to najniższy poziom indeksu —liść). W SQL Serverze, można również znaleźć numer wiersza, który zawiera imię Donald, ale nie został przedstawiony na rysunku dla zachowania przejrzystości. Teraz pozostaje przeczytanie informacji na temat Donalda z odpowiedniego wiersza ze strony 1. SQL Server używa indeksów do wyszukiwania danych dokładnie w ten sam sposób.

Opcje indeksu

Dla indeksów dostępnych jest kilka opcji. Zanim utworzy się własny indeks należy zrozumieć poszczególne możliwości aby dokonać wyboru odpowiedniego indeksu.

Indeksy zgrupowane

Istnieją dwie opcje fizycznego przechowywania indeksów. Pierwszy typ jest znany jako zgrupowany. Indeks zgrupowany fizycznie sortuje dane. Inaczej niż w przypadku posiadania całkowicie odrębnej struktury (takiej jak opisana wcześniej), poziom liścia indeksu w tym przypadku to dane. Dostęp do danych przy pomocy indeksu zgrupowanego jest prawie zawsze szybszy niż używanie indeksu niezgrupowanego, ponieważ dodatkowe przeglądanie strony danych/wiersza z poziomu liścia indeksu nie jest potrzebne.

Ponieważ dane są fizycznie sortowane w kolejności klucza indeksu, można mieć tylko jeden indeks zgrupowany w tablicy (nie ma sensu trzymać wielu kopii danych). Ponieważ dostępny jest jedynie jeden indeks zgrupowany, należy go wybrać szczególnie starannie. Wybór takiego indeksu może być dość złożony, ale niektóre główne wskazówki zostaną tutaj przedstawione.

Z indeksami zgrupowanymi związana jest jedna zasadnicza kwestia — wolna przestrzeń. Utworzenie indeksu zgrupowanego wymaga, aby było dostępne przynajmniej 120% rozmiaru tablicy jako chwilowy obszar roboczy. Musi istnieć wolna przestrzeń w bazie danych, w której jest tworzony indeks. Aby utworzyć indeks, SQL Server kopiuje tablicę, sortuje kopię w kolejności wartości indeksu (w kolejności rosnącej), buduje struktury indeksu (stronę główna i wszelkie potrzebne strony pośrednie), a następnie usuwa oryginalną tablicę. Po zakończeniu operacji, indeks zgrupowany zajmuje jedynie około 5% więcej przestrzeni niż tablica. Narzut (strony nie zawierające danych) w indeksie tego typu jest relatywnie mały, ale zależy od rozmiaru wartości, które są indeksowane.

Narzut ten może nie wygląda niepokojąco ponieważ wolna przestrzeń jest potrzebna jedynie podczas tworzenia indeksu, ale jej wielkość może być trudna do oszacowania. W przypadku bazy danych o wielkości 500 MB, gdy jedna tablica ma 100MB i będzie potrzeba stworzenia indeksu zgrupowanego dla tej tablicy, będzie potrzeba co najmniej 120MB nieużywanej przestrzeni w bazie danych.

Inną, równie ważną sprawą w SQL Serverze 2000 jest to, że wartości klucza (kolumna indeksowana), które zostały wybrane do indeksu zgrupowanego, są „przenoszone” do niezgrupowanych indeksów. Dlatego, jeżeli zostanie wybrany duży indeks zgrupowany — przykładowo, Char(30) — nie tylko więcej czasu zajmuje przeszukanie tego indeksu ale wszystkie inne indeksy niezgrupowane muszą również posiadać wartość Char(30) klucza tego indeksu zgrupowanego w każdym z wierszy w indeksach niezgrupowanych. Jest to znaczący narzut, dlatego należy utrzymywać klucze indeksu zgrupowanego tak małe, jak to możliwe. Należy się również upewnić, czy klucz zgrupowany, który został wybrany, nie jest często uaktualniany, ponieważ wszystkie indeksy niezgrupowane muszą być uaktualnione gdy zmienią się wartości indeksu zgrupowanego.

Inną sprawą, której należy być świadomym, to kolejność w jakiej tworzy się indeksy. Ponieważ klucz indeksu zgrupowanego jest częścią kluczowych wartości dla każdego indeksu niezgrupowanego, każdy z tych indeksów musi zostać odbudowany. Dlatego należy zawsze tworzyć najpierw indeksy zgrupowane.

Rysunek 13.3 pokazuje przykład indeksu zgrupowanego. Dane są sortowane w kolejności klucza indeksu (ponownie jest to imię), a dane na najniższym poziomie indeksu (strona danych) są sortowane wg imienia.

--> Rysunek[Author:AK] 13.3. Przykład indeksu zgrupowanego.

Indeksy niezgrupowane

Indeks niezgrupowany jest ogólnie taki sam jak standardowy indeks B+ drzewa. Każdy z indeksów ma stronę główną, jedną lub więcej stron poziomów pośrednich oraz poziom liścia, zawierający jeden wiersz dla jednego wiersza z tablicy. Indeksy niezgrupowane wymagają w sumie więcej przestrzeni niż indeksy zgrupowane, ale zajmują znacznie mniej przestrzeni podczas procesu ich tworzenia.

Na pojedynczej tablicy można mieć do 249 indeksów niezgrupowanych. Kolejność, w jakiej są tworzone, nie jest istotna. Indeks niezgrupowany nie zmienia kolejności danych, tak jak to robi indeks zgrupowany. Wiersze na poziomie liścia indeksu są posortowane w kolejności kolumn wybranych jako część indeksu. Każdy wiersz zawiera wskaźnik do kombinacji, numer strony/numer wiersza danych w tablicy (jeżeli nie istnieje indeks zgrupowany) lub wartość klucza indeksu zgrupowanego, (jeżeli tablica posiada taki indeks). Przykład indeksu niezgrupowanego został przedstawiony na rysunku 13.2.

Indeksy unikalne/nie unikalne

Unikalność determinuje, czy wartości powtarzające się są dozwolone w indeksie. Przykładowo, we wcześniej prezentowanym indeksie dotyczącym imion, żadne dwie osoby nie mogły by mieć takich samych imion jeśli indeks byłby unikalny. Indeksy SQL Servera domyślnie nie są unikalne, co oznacza, że dozwolone jest duplikowanie wartości.

Jeżeli pozwalają na to dane, utworzenie unikalnego indeksu może znacznie poprawić wydajności podczas używania indeksu. Kiedy szukana wartość zostaje znaleziona, nie ma potrzeby wykonywania kolejnych wyszukiwań (ponieważ wartości się nie powtarzają).

Indeksy zgrupowane są szczególnie dobrymi kandydatami na unikalne indeksy ponieważ, SQL Server zawsze wewnętrznie wymusza unikalność indeksów zgrupowanych. Jeżeli nie zostanie utworzony unikalny indeks zgrupowany, SQL Server generuje dodatkową ukrytą wartość klucza, aby wymusić unikalność indeksu. Dlatego nie warto, aby SQL Server generował dodatkowy klucz skoro można skorzystać z unikalnego klucza tego indeksu.

Indeksy na pojedynczą kolumnę/wiele kolumn

Wiele indeksów obejmuje tylko jedną kolumnę, jednak można łatwo utworzyć indeks na wielu kolumnach. Indeksy wielokolumnowe mogą być całkiem pożyteczne, ponieważ pozwalają zredukować ilość indeksów używanych przez SQL Server i zapewnić lepszą wydajność. Jeżeli jakieś kolumny występują często razem w zapytaniach, są one doskonałymi kandydatami do indeksu złożonego (inna nazwa indeksu wielokolumnowego). Indeksy złożone mogą być zgrupowane lub niezgrupowane oraz zawierać od 2 do 16 kolumn i mogą mieć wielkość do 900 bajtów.

Problemem tutaj może być to, że jeżeli utworzy się zbyt duży indeks, nie będzie on użyteczny, ponieważ może się okazać, że przejrzenie bezpośrednio tablicy zajmuje mniej czasu niż używanie indeksu. Indeksowanie dotyczy niestety wiele zagadnień i rzadko prezentuje jeden oczywisty wybór dla wielu aplikacji.

Indeksy rosnące/malejące

We wszystkich wcześniejszych wersjach SQL Servera, indeksy były zawsze przyjmowane jako rosnące (naturalny sposób myślenia o indeksach). Czyli, sortując alfabet, A będzie na początku, a Z na końcu. Jednak, czasem może być potrzebna lista posortowana w malejącej kolejności (od Z do A). SQL Server 2000 w pełni obsługuje indeksy malejące.

Rysunek 13.4 pokazuje indeks niezgrupowany dotyczący imienia (ten sam indeks jak na rysunku 13.2), ale indeks jest teraz przechowywany w porządku malejącym. Wiersz można znaleźć dokładnie w ten sam sposób, zaczynając nawigację od strony głównej. Zaletą tej metody jest to, że jeżeli zapytanie ma zwrócić dane w kolejności malejącej, mogą być one zwrócone bez dodatkowego sortowania.

--> Rysunek[Author:AK] 13.4. Indeks niezgrupowany, malejący.

Indeksy SQL Servera

SQL Server do budowy indeksów implementuje drzewa B+. Aby utworzyć indeks należy zastosować polecenie CREATE INDEX:

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name

ON [owner.][table_name | view_name]

(column_name [ASC|DESC] [,...n]

[WITH

[PAD_INDEX][[,] FILLFACTOR = x]

[[,] IGNORE_DUP_KEY]

[[,] DROP_EXISTING]

[[,] STATISTICS_NORECOMPUTE]

[[,] SORT_IN_TEMPDB]]

[ON filegroup]

Znaczenie składni:

• UNIQUE określa, że dla tego indeksu nie są dozwolone wartości duplikowane. Unikalność nie jest domyślnie wybraną wartością. Są dozwolone powtarzające się wpisy indeksu.

• CLUSTERED określa, ze dane będą fizycznie sortowane i staną się najniższym poziomem indeksu (liść). Wartości indeksu zgrupowanego, muszą być unikalne. W przypadku tworzenia indeksu zgrupowanego UNIQUE nie ma żadnego problemu. Jednak, jeżeli jest tworzony nie unikalny indeks zgrupowany, do każdego klucza tego indeksu jest dodawany 4-bajtowy „unifikator”, gwarantujący unikalność.

• NONCLUSTERED określa, że będzie tworzony normalny indeks B+, jako zupełnie osobny obiekt. Jest to domyślny typ indeksu.

• index_name jest unikalną nazwą SQL Servera dla tego obiektu.

• table_name jest nazwą tablicy, przechowującej kolumny, które mają być indeksowane.

• view_name jest nazwą widoku zawierającego kolumny do indeksowania.

• column_name jest nazwą kolumny (lub kolumn), która ma być indeksowana. Można utworzyć indeks dla 16 kolumn o rozmiarze do 900 bajtów. Kolumny nie mogą być typu text, image, bit lub ntext. Nową rzeczą w SQL Server 2000 jest to, że kolumna może być kolumną wyliczeniową.

• ASC|DSC oznacza, czy kolumna jest posortowana rosnąco (ASC) lub w kolejności malejącej (DESC) — od Z do A. Wartością domyślną jest sortowanie rosnąco, co jest spójne z wcześniejszymi wersjami, które nie miały tej opcji.

• filegroup jest nazwą grupy plików na który indeks powinien być założony. Jeżeli parametr filegroup nie został określony, indeks jest tworzony na domyślnej grupie plików.

Przykładowo, następujący kod tworzy tablicę zwaną myauthors w bazie danych pubs, a następnie kopiuje wszystkie dane z tablicy authors. Następnie kod tworzy indeks na kolumnie au_id tablicy myauthors. Powstały indeks zgrupowany wymusza unikalność.

USE PUBS

-- CREATE THE TABLE

CREATE TABLE dbo.myauthors (

au_id id NOT NULL,

au_lname varchar (40) NOT NULL ,

au_fname varchar (20) NOT NULL ,

phone char (12) NOT NULL ,

address varchar (40) NULL ,

city varchar (20) NULL ,

state char (2) NULL ,

zip char (5) NULL ,

contract bit NOT NULL)

-- Copy the data from the authors table into myauthors

INSERT myauthors select * from authors

-- create the unique clustered index on the myauthors table

Create unique clustered index myauind on myauthors (au_id)

Następujący kod tworzy nie unikalny indeks niezgrupowany, malejący na kolumnie au_fname tej samej tablicy:

Use pubs

Create index mynamindex on myauthors (au_fname DESC)

Warto zauważyć, że to polecenie Transact-SQL jest identyczne jak poprzednie w zakresie wykonywanych działań i może być bardziej oczywiste w formie:

Use pubs

Create nonclustered index mynameindex on myauthors (au_fname)

Opcje fillfactor i pad_index

Opcja fillfactor określa, na ile powinna być wypełniona każda strona na poziomie liścia indeksu. Domyślnie stopień wypełnienia jest ustawiony na 0. Ponieważ fillfactor jest parametrem konfiguracyjnym, należy upewnić się, że nie będzie on zmieniany. Można sprawdzić to z SQL Server Enterprise Managera lub przy pomocy procedury sp_configure bez żadnych parametrów. Wynik powinien wyglądać podobnie do przedstawionego:

EXEC sp_configure

GO

name minimum maximum config_value run_value

------------------------ ------- ------- ------------ ---------

allow updates 0 1 0 0

default language 0 9999 0 0

fill factor (%) 0 100 0 0

language in cache 3 100 3 3

max async IO 1 255 32 32

max text repl size (B) 0 2147483647 65536 65536

max worker threads 10 1024 255 255

nested triggers 0 1 1 1

network packet size (B) 4096 65536 4096 4096

recovery interval (min) 0 32767 0 0

remote access 0 1 1 1

remote proc trans 0 1 0 0

show advanced options 0 1 0 0

user options 0 4095 0 0

(14 row(s) affected) ...

Należy znaleźć w zbiorze wyników nazwę fill factor i sprawdzić czy wartości kolumny config_value i run_value dla tego parametru ustawione są na 0.

Jeżeli w poleceniu CREATE TABLE nie zostanie określony parametr fillfactor, używana jest domyślna wartość (na ogół 0). Wartość 0 oznacza, ze strony na „najniższym” poziomie indeksu są prawie pełne ale strony innych poziomów (poziomy pośrednie i poziom główny) nadal mają miejsce na co najmniej dwa wiersze. Jeżeli wartość fillfactor wynosi 100, wszystkie strony liśca są zapełnione w 100 procentach, bez miejsca na dodatkowe wiersze. Z kolei, strony główna i pośrednie ciągle posiadają miejsce na dwa dodatkowe wiersze. Każda inna wartość oznacza procentowy stopień zapełnienia wierszami każdej ze stron liścia. SQL Server zaokrągla procent do rozmiaru najbliższego wiersza, czyli rzadko można uzyskać faktyczny stan procentowy, ale wynik jest najlepszy, jaki SQL Server może podać.

Jeżeli zostanie utworzony indeks zgrupowany o wartości fillfactor wynoszącej 50, każda strona będzie w 50 procentach zapełniona. Na rysunku 13.5 widać, że strona liścia indeksu zgrupowanego jest zapełniona jedynie w połowie. Przykładowy kod wygląda następująco:

CREATE INDEX aunameindex on authors (au_fname)

WITH FILLFACTOR = 50

GO

--> Rysunek[Author:AK] 13.5. Indeks zgrupowany o wartości fillfactor równej 50.

--> fillfactor[Author:AK] nie jest uaktualniany w indeksie. Jeżeli utworzy się indeks z poziomem wypełnienia 50 (co oznacza, że każda ze stron jest w połowie wypełniona), po pewnym czasie niektóre strony zapełnią się a niektóre będą prawie puste. Jeżeli potrzeba zobaczyć uaktualniony współczynnik wypełnienia, należy wykonać jedną z czynności:

• Usunąć i utworzyć ponownie indeks z ponownie określonym stopniem zapełnienia.

• Uruchomić polecenie DBCC DBREINDEX

• Uruchomić polecenie DBCC INDEXDEFRAG.

--> Każda[Author:AK] z opcji zmienia stan współczynnika wypełnienia. Można znaleźć narzędzie do pomocy określając potrzebę reindeksacji DBCC SHOWCONFIG, polecenie omówione później. Jednak, SQL Server sam dokonuje automatycznie nieznacznego wyczyszczenia.

Kiedy używana jest opcja pad_index ze stopniem zapełnienia, opcja ta określa, że do pozostałych stron nie-liścia indeksu stosuje się również współczynnik wypełniania, jak do stron liścia. Najłatwiejszym sposobem zrozumienia opcji pad_index jest analiza poniższego przykładu.

Jeżeli zostanie utworzony taki sam indeks jak na rysunku 13.5 ale z dodaną opcją pad_index, współczynnik wypełnienia będzie stosowany również do stron innych niż strony liścia. Warto zauważyć, że jeżeli wiersze nie pasują najlepiej, SQL Server stara się dopasować dane w indeksie możliwie najbliżej do wymaganego współczynnika wypełnienia. Przykładowo, następujący kod tworzy indeks przedstawiony graficznie na rysunku 13.6:

CREATE INDEX aunameindex on authors (au_fname)

WITH FILLFACTOR = 50, PAD_INDEX

GO

--> Rysunek[Author:AK] 13.6. Indeks zgrupowany z parametrem pad_index.

Do tej pory zostało omówione, jak działają parametry fillfactor i pad_index, ale nie zostało powiedziane dlaczego należy z nich korzystać. Określenie współczynnika wypełnienia bywa często przydatne. Aby zrozumieć dlaczego jest to potrzebne, należy znać kilka warunków. Po pierwsze jest to podział strony (page split). Podział strony występuje gdy strona SQL Servera jest pełna a potrzeba umieścić w niej kolejny wiersz. Do indeksu lub tablicy zostaje przydzielona nowa strona, 50% wierszy jest przesuwanych do nowej strony. Następnie nowy wiersz jest dodawany do odpowiedniej lokalizacji. Jak można sobie wyobrazić, operacja ta może być kosztowna jeśli zdarza się często. Jeżeli przy tworzeniu indeksu używany jest współczynnik wypełnienia, można dodać nowy wiersz bez wywoływania podziału strony. Ustawienie odpowiedniego współczynnika wypełnienia może poprawić wydajność. Jednak dobieranie prawidłowego współczynnika wypełnienia na ogół przychodzi wraz z nabieraniem doświadczenia.

Opcja ta może być szczególnie przydatna w przypadku indeksów zgrupowanych, ponieważ poziom liścia indeksu jest tworzony z bieżących stron danych. Należy mieć świadomość, że stosowanie współczynnika wypełnienia, takiego jak 50, do indeksów zgrupowanych w przybliżeniu podwaja rozmiar tablicy. Podejście to może mieć bardzo znaczący wpływ na wykorzystanie przestrzeni i należy stosować go po przemyśleniu.

Podzielenie strony na stosie (heaps - tablice bez indeksu zgrupowanego) może być szczególnie kosztowne w znaczeniu jego wpływu na sposób przechowywania niezgrupowanych indeksów. Każdy wiersz w niezgrupowanym indeksie posiada wskaźnik do kombinacji danych, numer strony /numer wiersza. Jeżeli wystąpi podział strony na stosie (heap), w przybliżeniu połowa wierszy przesunie się. Oznacza to, że połowa wierszy musi zmodyfikować każdy wpis dla każdej pojawiającej się zmiany, co wiąże się z wolniejszym czasem odpowiedzi. Dlatego dobrze jest mieć indeks zgrupowany na większości tablic. Jak pokazano wcześniej, indeksy niezgrupowane nie wskazują na fizyczne położenie wierszy jeżeli istnieje indeks zgrupowany, czyli podziały strony w tym indeksie nie mają wpływu na indeksy niezgrupowane.

Dodając opcję pad_index, można uniknąć podziałów stron nie liścia. Tym sposobem zostaje zwiększone wykorzystanie przestrzeni — ale nie tak znacznie jak w przypadku używania fillfactor.

Opcja DROP_EXISTING

Opcja DROP_EXISTING określa, czy indeks powinien być usunięty i ponownie utworzony (zasadniczo jest to „reorganizacja” indeksu). Jeżeli usuwany i ponownie tworzony jest indeks zgrupowany, można sądzić, że wpłynie to na indeksy niezgrupowane.

Jednak, SQL Server 2000 jest na tyle dobrym produktem, że po prostu usuwa i tworzy od nowa jedynie indeks zgrupowany. Ponieważ indeksy niezgrupowane posiadają wartość klucza indeksu zgrupowanego jako identyfikator wiersza i wartość ta nie zmienia się podczas operacji CREATE INDEX z opcją DROP_EXISTING, nie ma potrzeby zmian indeksów niezgrupowanych.

Opcja ta jest najbardziej przydatna dla indeksów zgrupowanych, ale może być również używana z indeksami niezgrupowanymi. Opcja DBCC DBREINDEX opisana później, ma podobną funkcjonalność, ale opcja DROP_EXISTING jest być może lepsza w dłuższym okresie. Jednak dla SQL Servera 2000, firma Microsoft przedstawiła narzędzie reorganizacji indeksów online, DBCC INDEXDEFRAG, które zostanie również omówione w tym rozdziale.

Opcja STATISTICS_NORECOMPUTE

SQL Server 2000 w razie potrzeby automatycznie przelicza ponownie statystyki. Można zablokować tę własność w razie potrzeby przy pomocy opcji STATISTICS_NORECOMPUTE. Aby ponownie włączyć korzystanie z tej funkcji SQL Servera, należy uruchomić UPDATE STATISTICS bez opcji NORECOMPUTE. Więcej informacji na temat UPDATE STATISTICS zostanie przedstawionych w dalszej części rozdziału. Wyłączenie automatycznego zbierania statystyk dla indeksów jest generalnie nie najlepszym pomysłem. Optymalizator SQL Servera 2000 jest bardzo zależny od aktualnych statystyk w zakresie optymalizacji zapytań.

Opcja SORT_IN_TEMPDB

SQL Server daje możliwość korzystania z bazy tempdb do tymczasowego przechowywania pośrednich struktur danych przy tworzeniu indeksu. Oznacza to, że tworzony indeks jest większy niż ilość pamięci dostępnej fizycznie dla SQL Servera 2000. Jeżeli występuje ten problem, SQL Server potrzebuje przenieść na dysk część tymczasowych struktur danych do tworzenia indeksów. Bez opcji SORT_IN_TEMPDB, struktury te są przenoszone do bazy danych, gdzie jest tworzony indeks. Gdy opcja ta jest włączona, tymczasowe struktury są tworzone w bazie danych tempdb. Jeżeli baza tempdb jest na osobnym dysku, proces ten może mieć lepszą wydajność w zakresie tworzenia indeksu, ale będzie wymagał zapewne więcej przestrzeni dyskowej, niż gdy tworzy się indeks bez używanis tempdb.

Opcja IGNORE_DUP_KEY

Opcja IGNORE_DUP_KEY określa, że podczas uaktualniania wielu wierszy tablicy z unikalnym indeksem zgrupowanym, wiersze powtarzające się w tej operacji wprowadzania danych są bezproblemowo odrzucane i operacja kończy się sukcesem, a SQL Server zwraca ostrzeżenie. Następujący przykład kodu pokazuje działanie tej operacji:

create table t1 (col1 int not null, col2 char(5) not null)

go

create unique clustered index myind on t1 (col1) with ignore_dup_key

go

create table t2 (col1 int not null, col2 char(5) not null)

go

insert t2 values (1, 'abcde')

insert t2 values (2, 'abcde')

insert t2 values (2, 'abcde')

insert t2 values (3, 'abcde')

insert t1 select * from t2

Po uruchomieniu powyższego kodu system zwróci następujący komunikat:

Server: Msg 3604, Level 16, State 1, Line 1

Duplicate key was ignored.

Jeżeli spróbuje się pobrać (poleceniem select) dane z tablicy t1, otrzyma się w wyniku trzy wiersze, tak jak można się było spodziewać.

Korzystanie z indeksu

Zostały omówione podstawy indeksów i tworzenie indeksów w SQL Serverze 2000. Jednak, nie było mowy o tym, jakie indeksy tworzyć lub kiedy powinny być używane.

Kiedy indeks będzie używany?

Można rozpocząć od dość oczywistego pytania: Skąd wiadomo, kiedy indeks będzie używany? Kiedy zapytanie jest wysyłane do SQL Servera, jest ono rozkładane na części i analizowane. W pojęciu optymalizacji indeksowania, najważniejszą częścią zapytania jest klauzula WHERE. Polecenie zawarte w klauzuli WHERE zapytania jest najbardziej prawdopodobną drogą, którą SQL Server dowie się jak zoptymalizować zapytanie do używania indeksu. Jednak to, że zapytanie zawiera klauzulę WHERE nie jest równoznaczne z tym, że będzie używany indeks. Jako przykład można rozważyć zapytanie:

SELECT au_id, au_fname, au_lname

FROM pubs..authors

WHERE state = 'CA'

GO

Jeżeli kolumna state posiada indeks, ale większość autorów pochodzi z Kalifornii (CA), nie ma sensu używać indeksu. Przeglądanie tablicy (czytanie każdej strony danych w tablicy) jest bardziej prawdopodobne im bardziej wydajny jest plan pobierania danych. Wyrażenie bardziej wydajny plan oznacza lepszą wydajność w pojęciu minimalizacji ilości przeczytanych stron przez SQL Server. Innym pojęciem określającym to jest minimalizacja odczytów I/O i odczytów logicznych stron.

W podobnym zapytaniu, zwracającym tylko jeden wiersz, jest większy sens używania indeksu. Przykładowo, jeżeli kolumna au_id ma indeks zgrupowany, zapytanie to będzie prawie na pewno korzystać z indeksu:

SELECT au_id, au_fname, au_lname

FROM pubs..authors

WHERE au_id = '341-22-1782'

Jak sprawdzić czy indeks jest używany?

Kolejnym pytaniem jest to, jak zweryfikować, który indeks jest używany przez zapytanie. SQL Server 2000 posiada wiele opcji do testowania, które indeksy są używane od obsługi zapytań. Można skorzystać z poleceń Transact-SQL SET SHOWPLAN_ALL ON lub SET SHOWPLAN_TEXT ON. Jeżeli do uruchamiania planu showplan używany jest SQL Server Query Analyzer, można uruchomić również opcje. Jednak używając narzędzia z wiersza poleceń, takiego jak isql.exe lub osql.exe, należy korzystać z opcji SHOWPLAN_TEXT. Aby zobaczyć różnicę, należy uruchomić skrypt przedstawiony na wydruku 13.1 do tworzenia tablicy i umieszczania w tablicy przykładowych danych. Skrypt musi umieszczać w tablicy pewien zakres danych, aby indeksy miały szanse być używane.

Opcje SET dla showplan muszą być umieszczone pojedynczo (osobno) w pliku wsadowym.

Wydruk 13.1 Monitorowanie indeksów

USE PUBS

CREATE TABLE PUBS..INDEXTAB

(col1 int not null,

col2 varchar(250) not null,

col3 varchar(250) not null,

col4 varchar(250) not null,

col5 varchar(250) not null)

insert indextab values (1,'adam','col3','col4','col5')

insert indextab values (2,'bob','col3','col4','col5')

insert indextab values (3,'charles','col3','col4','col5')

insert indextab values (4,'david','col3','col4','col5')

insert indextab values (5,'edward','col3','col4','col5')

insert indextab values (6,'frank','col3','col4','col5')

insert indextab values (7,'george','col3','col4','col5')

insert indextab values (8,'hank','col3','col4','col5')

insert indextab values (9,'ida','col3','col4','col5')

insert indextab values (10,'john','col3','col4','col5')

insert indextab values (11,'kim','col3','col4','col5')

insert indextab values (12,'loni','col3','col4','col5')

insert indextab values (13,'mike','col3','col4','col5')

insert indextab values (14,'nikki','col3','col4','col5')

insert indextab values (15,'oprah','col3','col4','col5')

insert indextab values (16,'paul','col3','col4','col5')

insert indextab values (17,'quan','col3','col4','col5')

insert indextab values (18,'richard','col3','col4','col5')

insert indextab values (19,'sam','col3','col4','col5')

insert indextab values (20,'tom','col3','col4','col5')

insert indextab values (21,'uma','col3','col4','col5')

insert indextab values (22,'vera','col3','col4','col5')

insert indextab values (23,'walter','col3','col4','col5')

insert indextab values (24,'xray','col3','col4','col5')

insert indextab values (25,'yuma','col3','col4','col5')

insert indextab values (26,'zane','col3','col4','col5')

insert indextab values (27,'ann','col3','col4','col5')

insert indextab values (28,'bill','col3','col4','col5')

insert indextab values (29,'cathy','col3','col4','col5')

insert indextab values (30,'dawn','col3','col4','col5')

insert indextab values (31,'ellen','col3','col4','col5')

insert indextab values (32,'fran','col3','col4','col5')

insert indextab values (33,'grant','col3','col4','col5')

insert indextab values (34,'helen','col3','col4','col5')

insert indextab values (35,'irwin','col3','col4','col5')

insert indextab values (36,'jack','col3','col4','col5')

insert indextab values (37,'kathy','col3','col4','col5')

insert indextab values (38,'lance','col3','col4','col5')

insert indextab values (39,'molly','col3','col4','col5')

insert indextab values (40,'nancy','col3','col4','col5')

CREATE CLUSTERED INDEX CL_MYINDEX on indextab (col1)

Na ogół użytkownik ustawia Query Analyzer aby zwrócił wyniki w postaci tekstu w celu interpretacji wyników kolejnych kilku poleceń. Analiza wyników jest trudna jeżeli zostaną przedstawione przy pomocy domyślnej opcji Results in Grid.

Po pierwsze należy uruchomić opcję SHOWPLAN_TEXT:

USE PUBS

GO

SET SHOWPLAN_TEXT ON

GO

select col1, col2 from indextab

where col2 = 'ann'

Wynik powyższego zapytania jest następujący:

StmtText

--------------------------------------------------------

select col1, col2 from indextab

where col2 = 'ann'

(1 row(s) affected)

StmtText

------------------------------------------------------------------

|--Clustered Index Scan(OBJECT:([pubs].[dbo].[INDEXTAB].[CL_MYINDEX]), WHERE:([INDEXTAB].[col2]=[@1]))

(1 row(s) affected)

Jak widać, SQL Server wybrał korzystanie z indeksu zgrupowanego do uruchomienia tego zapytania. Następnie, to samo zapytanie zostało przedstawione poniżej z opcją SHOWPLAN_ALL:

USE PUBS

GO

SET SHOWPLAN_ALL ON

GO

select col1, col2 from indextab

where col2 = 'ann'

Wyniki zostały przedstawione na rysunku 13.7.

Rysunek 13.7. Wynik zapytania w postaci tekstu —opcja SHOWPLAN_ALL w SQL Server Query Analyzer.

Warto zwrócić uwagę, że jest to ta sama informacja jak przy opcji SHOWPLAN_TEXT, ale z dodatkowymi szczegółami. Zaleca się korzystanie z opcji SHOWPLAN_TEXT, dopóki nie nabierze się wprawy w optymalizacji zapytań i zrozumieniu pojęć takich jak TotalSubtreeCost.

Aby zobaczyć, jakie indeksy zawiera tablica, można uruchomić następującą systemową procedurę składową sp_helpindex:

SET SHOWPLAN_ALL OFF -- or turn off any other showplan options first

GO

EXEC sp_helpindex indextab

GO

index_name index_description index_keys

--------------------------------------------------------

CL_MYINDEX clustered located on PRIMARY col1

Teraz należy utworzyć drugi indeks do tablicy, co pozwoli przestudiować plan dla innego indeksu niż indeks zgrupowany:

CREATE INDEX NONCL_MYINDEX on indextab (col2)

GO

SET SHOWPLAN_TEXT ON

GO

select col1, col2 from indextab

where col2 = 'ann'

StmtText

--------------------------------------------------------

select col1, col2 from indextab

where col2 = 'ann'

(1 row(s) affected)

StmtText

---------------------------------------------------------------

|--Index Seek(OBJECT:([pubs].[dbo].[INDEXTAB].[NONCL_MYINDEX]), SEEK:([INDEXTAB].[col2]=[@1]) ORDERED FORWARD)

(1 row(s) affected)

Warto zauważyć, ze SQL Server użył obecnie „przeszukiwania indeksu” (index seek) w indeksie noncl_myindex. Oznacza to po prostu, że indeks jest używany do znalezienia wszystkich wierszy zawierających imię ann.

Można również przetestować plany korzystając z opcji graficznej prezentacji planów SQL Server Query Analyzera. Obecnie można zobaczyć dwa plany: przybliżony plan wykonywania lub bieżący plan z wykonywanego zapytania. Różnica jest taka, że bieżący plan prezentuje dokładne liczby, podczas gdy przybliżony plan pokazuje to, co optymalizator SQL Servera podejrzewa, że zostanie wykonane. Należy uruchomić takie samo zapytanie jak poprzednio, ale włączając opcję Show the Execution Plan (z menu Query lub przy pomocy przycisku Execute ModePo kliknięciu zakładki Execution Plan w dolnej części okna, wyświetlą się informacje podobne do pokazanych na rysunku 13.8. Jeżeli przesunie się kursor myszy nad graficzny element okna, zostaną wyświetlone szczegółowe informacje na temat poszczególnych etapów zapytania. Informacje wyjściowe są w tym przypadku relatywnie proste, ale dla większych planów, plan graficzny może być z trudnością porównany z planem tekstowym.

Rysunek 13.8. Graficzny plan wykonywania w SQL Server Query Analyzer.

Dotychczas pokazano jakich wyborów dokona SQL Server. Należy do tego dodać jeszcze informacje o tym, w jaki sposób SQL Server dokonuje wyborów. W większej części przypadków optymalizacja jest dokonywana na podstawie ilości pracy wymaganej do odpowiedzi na zapytanie. Z założenia SQL Server ma odpowiedzieć na zapytanie tak szybko, jak to możliwe i wykonując jak najmniejszą ilość operacji. Oznacza to zwykle minimalizację ilości wykonanych logicznych odczytów I/O.

Logiczne I/O (Wejście/Wyjście) jest dostępem do danych przez SQL Server. Każdy logiczny odczyt I/O oznacza, że SQL Server przeczytał pojedynczą stronę 8KB. Całkowita ilość stron, do których wystąpił dostęp, jest zawsze równa ilości logicznych I/O. SQL Server rozróżnia fizyczne i logiczne I/O. Jeżeli zapytanie zajmuje 50 logicznych I/O i jedynie 20 fizycznych I/O oznacza to, że 30 z 50 potrzebnych stron jest już w pamięci. Potrzeba jedynie pobrać kolejnych 20 stron fizycznie z dysków i załadować do pamięci, aby mogły być czytane — czyli są to fizyczne I/O. SQL Server próbuje zminimalizować ilość fizycznych dostępów do wejścia/wyjścia (I/O). Można zobaczyć ilość I/O używanych przez zapytanie przy pomocy polecenia SET STATISTICS IO ON:

SET STATISTICS IO ON

GO

SELECT col1,col2

FROM indextab

where col1 < 3

GO

STEP 1

col1 col2

----------- ----

1 adam

2 bob

(2 row(s) affected)

Table 'INDEXTAB'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

Należy się upewnić czy została wcześniej wyłączona opcja NOEXEC przed użyciem polecenia STATISTICS IO (jeżeli była uprzednio włączona), ponieważ trzeba uruchomić zapytanie aby uzyskać informacje na temat statystyk I/O. Ponieważ opcje SHOWPLAN nie uruchamiają naprawdę zapytania, nie są również zgodne z pobieraniem informacji STATISTICS IO.

Warto zwrócić uwagę, że do wykonania zapytania potrzeba dwóch logicznych odczytów: jeden odczyt aby pobrać stronę główną indeksu zgrupowanego i jeden aby odczytać stronę liścia indeksu. Należy pamiętać, że ponieważ jest to indeks zgrupowany, strona liścia jest stroną danych. SQL Server normalnie preferuje używanie indeksu zgrupowanego.

Na rysunku 13.9 można zobaczyć, jak zostały wyliczone te dwa logiczne odczyty. SQL Server musi odczytać stronę główną indeksu a następnie poziom liścia indeksu aby zwrócić dane. Warto zauważyć, że ten indeks nie posiada stron pośrednich (jest zbyt mały).

--> Rysunek[Author:AK] 13.9. Indeks zgrupowany.

SQL Server używa również informacji o tym, ile pamięci jest dostępne, jak dużo czasu procesora (CPU) potrzeba na wykonanie każdej z opcji zapytania oraz jak bardzo system jest zajęty. Dlatego, może się okazać, że SQL Server wybrał plan, który zajmuje więcej I/O niż inny, ponieważ jeżeli przeliczy się wszystkie współczynniki razem, będzie on mniej kosztowny. Optymalizator w SQL Serverze 2000 jest znacznie bardziej skomplikowany niż był w wersjach przed 7.0 i bierze pod uwagę więcej informacji do podejmowania decyzji, których indeksów użyć. Jeżeli przeanalizuje się wyjściowy raport z SHOWPLAN_ALL, wiele z tych wartości jest tam podanych. Omówienie wykorzystania tych wartości jest poza zakresem tej książki.

Teraz, gdy wiadomo już jak SQL Server próbuje dostać się do tablic, co można zrobić w tej sprawie? Również, co zrobić jeżeli zostaną utworzone indeksy, które powinny być używane, ale SQL Server nie używa ich? Należy ominąć działanie optymalizatora.

Omijanie działań optymalizatora

Aby ominąć działanie optymalizatora, należy określić porady dla optymalizatora. Po pierwsze, należy założyć, że przez większość czasu optymalizator SQL Servera działa poprawnie. Jeżeli nadal użytkownik jest pewny, że ma rację a SQL Server działa nieprawidłowo, statystyki zostały uaktualnione i sprawdzony wynik SHOWPLAN, można rozważyć użycie podpowiedzi (hints) dla optymalizatora. Sekcja ta omawia każdą z opcji, dostarczającą poprawnej informacji dla optymalizatora SQL Servera.

Aby wybrać indeks, należy określić nazwę indeksu lub numer indeksu jako część polecenia select. Można znaleźć numer indeksu przy pomocy zapytania skierowanego do tablicy systemowej sysindexes w bazie danych:

SELECT col1, col2

FROM indextab (index = noncl_myindex)

SQL Server zawsze używa indeksów, które zostały wskazane, bez względu na to, co optymalizator uważa za odpowiedni wybór. SQL Server nigdy nie ocenia wyboru użytkownika, dlatego należy przetestować co pewien czas ten wybór. Jest to szczególnie istotne, gdy są stosowane pakiety serwisowe lub uaktualniania SQL Servera. Dlatego, używanie podpowiedzi dla optymalizatora jest rzadko potrzebne — i czasem bywa niekorzystne.

Powyższe zapytanie domyślnie korzysta z indeksu zgrupowanego, ale w tym przypadku, użytkownik narzucił korzystanie z indeksu niezgrupowanego, który został wcześniej stworzony.

Prawidłowe podejście do wymuszenia korzystania przez SQL Server ze skanowania tablicy wymaga zrozumienia numeracji indeksów w tablicy sysindexes. Indeksy niezgrupowane są numerowane od 2 do 250. Jeżeli istnieje indeks zgrupowany, ma on numer 1. Jeżeli nie istnieje indeks zgrupowany, wiersz z indeksem o numerze 0 pojawia się w tablicy sysindexes. Zaleca się raczej określenie numeru indeksu w podpowiedziach dla optymalizatora niż używanie nazwy indeksu. Poprzednie polecenie SQL może wyglądać następująco, przyjmując, że indeks noncl_myindex ma numer 3:

SELECT col1, col2

FROM indextab (INDEX = 3)

Aby wymusić przeglądanie indeksu zgrupowanego, należy napisać zapytanie podobne do poniższego. Z wyników zapytania widać, ze SQL Server wykonał zadanie i skorzystał przy tym z indeksu zgrupowanego:

SET SHOWPLAN_TEXT ON

GO

SELECT col1, col2

FROM indextab (INDEX = 0)

WHERE col1 < 3

StmtText

--------------------------------------------------------------

SELECT col1, col2

FROM indextab (INDEX = 0)

WHERE col1 < 3

(1 row(s) affected)

StmtText

--------------------------------------------------------------

|--Clustered Index Scan(OBJECT:([pubs].[dbo].[INDEXTAB].[CL_MYINDEX]), WHERE:([INDEXTAB].[col1]<3))

(1 row(s) affected)

Aby zobaczyć inne użycie tej opcji, należy usunąć indeks zgrupowany z tej tablicy. Tablica staje się z powrotem stosem (heap). Następnie należy uruchomić identyczne zapytania z zaleceniem dla optymalizatora INDEX=0:

set showplan_text off

go

drop index indextab.cL_MYINDEX

GO

SET SHOWPLAN_TEXT ON

GO

SELECT col1, col2

FROM indextab (INDEX = 0)

WHERE col1 < 3

StmtText

-------------------------------------------------------------

SELECT col1, col2

FROM indextab (INDEX = 0)

WHERE col1 < 3

(1 row(s) affected)

StmtText

-------------------------------------------------------------

|--Table Scan(OBJECT:([pubs].[dbo].[INDEXTAB]), WHERE:([INDEXTAB].[col1]<3))

(1 row(s) affected)

Warto zauważyć, że skanowanie tablicy zostało wykorzystane, ponieważ tablica nie miała indeksu zgrupowanego. Należy pamiętać, że skanowanie tablicy i indeks zgrupowany są zasadniczo operacjami tego samego typu. Poziom liścia indeksu zgrupowanego są to dane, czyli skanowanie tego poziomu jest tym samym co czytanie każdej pojedynczej strony danych (skanowanie tablicy).

Pielęgnacja indeksu

Indeksy muszą być pielęgnowane aby stale zachowywać efektywności i użyteczność. Należy ręcznie konserwować te indeksy (lub ustawić automatyczny plan do wykonywania tych czynności). Bez względu na sposób wykonywania, należy zrozumieć podstawy pielęgnacji indeksu.

Statystyki

SQL Server uzyskuje informacje statystyczne na temat danych poprzez specjalny obiekt w bazie danych (zwany statistics blob). Statistics blob zawiera informacje o tym, jak dane są rozmieszczane w tablicy oraz wylicza średnią liczbę powtórzonych wartości dla kolumn indeksowanych. Gdy tworzony jest indeks, jeżeli tablica zawiera już dane, obiekt statistics blob jest tworzony i wypełniany informacjami na temat kolumn, które zostały poindeksowane. Jeżeli na tablicy zostanie utworzony indeks a następnie dodane dane, ale nie zostanie wyłączone automatyczne zbieranie statystyk, generowany jest statistics blob. Jeżeli zostanie wyłączona możliwość automatycznego uaktualnienia statystyk na temat indeksu, obiekt statistics blob nie jest tworzony. Jeżeli informacja ta nie istnieje, SQL Server musi zgadywać jak są rozmieszczone dane. Jeżeli zapytanie zawiera klauzulę, jak np.: col1< 3, SQL Server sprawdza statistics blob, aby przewidzieć jak wiele wierszy danych zostanie zwróconych. Im bardziej aktualny jest statistics blob, tym lepiej SQL Server dobierze odpowiedni indeks (lub zdecyduje się na używanie skanowania tablicy). Dlatego nie należy wyłączać gromadzenia automatycznych statystyk.

Niektórzy użytkownicy (wliczając w to autorów tej książki) okresowo gromadzą statystyki również ręcznie. Aby uaktualnić statistics blob bieżącą informacją, należy uruchomić polecenie UPDATE STATISTICS:

UPDATE STATISTICS { table | view}

[index

| ([index_or_column [, ...])]

[WITH [ [FULLSCAN]

| SAMPLE number {PERCENT | ROWS}] ]

[[,] [ALL | COLUMNS | INDEX]

[[,] NORECOMPUTE] ]

Poniższy przykład pokazuje uaktualnienie statystyk tablicy authors:

UPDATE STATISTICS authors

Dopóki włączone jest automatyczne zbieranie statystyk, nie ma potrzeby częstego uruchamiania tej opcji. Na własnych serwerach autorzy uruchamiają UPDATE STATISTICS raz w tygodniu na każdej z tablic w bazie danych. Firma Microsoft dostarcza wygodnej systemowej procedury składowej do wykonywania tego zadania: sp_statistics. Aby uruchomić tę procedurę, należy wpisać w oknie zapytania exec sp_statistics. Nie ma żadnych parametrów i nie potrzeba robić nic więcej. Polecenie to automatycznie zbierze informacje statystyczne na temat indeksów każdej z tablic w bazie danych. Można określić wiele innych opcji, ale nie ma potrzeby ich teraz omawiać.

Wyłączanie statystyk automatycznych

Można wyłączyć statystyki automatyczne (zalecane jest włączenie ich z powrotem) przy pomocy systemowej procedury składowej sp_autostats:

sp_autostats 'tablename' [, 'statsflag'] [, 'indexname']

Znaczenie składni:

• tablename jest nazwą tablicy lub widoku, na którym ma być zmieniona opcja autostats

• statsflag może być włączona lub wyłączona (włączenie automatycznych statystyk lub wyłączenie ich, jeżeli opcja nie zostanie określona).

• indexname jest nazwą indeksu, który ma być zmieniony. Jeżeli nie określi się tego parametru, zmiany będą dotyczyły wszystkich indeksów w danej tablicy. Jeżeli nie zostaną określone obydwa parametry statsflag i indexname, sp_autostats zwróci informacje na temat bieżącego statusu statystyk automatycznych w indeksach danej tablicy.

Aby wyłączyć statystyki automatyczne w tablicy authors w bazie pubs, należy uruchomić:

use pubs

exec sp_autostats 'authors','Off'

W wyniku otrzyma się

(4 row(s) affected)

Automatic statistics maintenance turned OFF for 4 indices.

Statystyki kolumnowe

SQL Server 2000 może również śledzić statystyki kolumn nieindeksowanych. Własność ta jest zwana keeping statistics (utrzymywanie statystyk na kolumnach). Stosuje się polecenie UPDATE STATISTICS jak poprzednio, z wyjątkiem tego, że określa się kolumny, które mają mieć włączoną statystykę i używa się opcji WITH COLUMNS. Poniższy przykład uaktualnia statystyki kolumny city tablicy authors:

UPDATE STATISTICS authors (city) WITH COLUMNS

Do czego ta możliwość jest potrzebna? SQL Server Optymizer (kod, który decyduje, którego indeksu użyć) może testować te statystycznie informacje aby zadecydować jak najlepiej czytać tablicę — podobnie może używać kolumn indeksowanych ze statystykami. Jeżeli zostanie określona kolumna w klauzuli WHERE polecenia select, ale kolumna ta nie ma być indeksowana, może być nadal używana do celów optymalizacji z wykorzystaniem statystyk kolumnowych. Ponownie temat statystyk kolumnowych powróci przy omawianiu kreatora Index tunnig wizard.

Kiedy ostatnio były gromadzone statystyki?

Jeżeli zachodzi potrzeba zajęcia się nowym SQL Serverem, być może będzie potrzeba zebrania informacji o obecnym stanie baz danych SQL Servera. Należy określić jak i kiedy były uaktualniane statystyki. Na szczęście można znaleźć, kiedy ostatni raz były gromadzone statystyki przy pomocy funkcji STATS_DATE:

SELECT STATS_DATE (table_id, index_id)

Funkcja ta jest intuicyjna, ale wymaga podania table_id i index_id. Można uzyskać te parametry przy pomocy funkcji OBJECT_ID:

Use pubs

Go

Declare @tabid int

Declare @indid int

Select @tabid = object_id ('indextab')

Select @indid = 2 /* the non-clustered index */

SELECT STATS_DATE (@tabid, @indid)

Otrzyma się w wyniku:

-----------------------

2000-05-09 22:12:05.920

(1 row(s) affected)

--> Jeżeli [Author:AK] nie otrzyma się powyższych wyników, być może nadal jest włączona opcja showplan. Należy otworzyć nowe okno zapytań i wpisać następujący kod:

SET SHOWPLAN_TEXT OFF
GO
SET SHOWPLAN_ALL OFF
--> GO[Author:AK]

Polecenie DBCC UPDATEUSAGE

DBCC UPDATEUSAGE koryguje informacje w tablicy systemowej SQL Servera sysindexes. Informacje z tej tablicy mówią SQL Serverowi m.in., ile stron jest w tablicy i ile stron jest używanych przez każdy indeks. Informacje te są aktualizowane automatycznie, ale może się zdarzyć, że informacje w tablicy systemowej będą nieaktualne. Aby zaktualizować informacje należy uruchomić DBCC UPDATEUSAGE:

DBCC UPDATEUSAGE ({0 | database_name} [, {'table_name' |'viewname'}

[, index_id | 'indexname']])

[ WITH [COUNT_ROWS] [, NO_INFOMSGS]]

W tej składni:

• database_name jest nazwą bazy danych, w której wykonywane są działania. Jeżeli zostanie określona tylko nazwa bazy danych, w tablicy sysindexes są uaktualniane informacje dla stosu (heaps) i indeksów w bazie.

• table_name lub viewname jest nazwą tablicy lub widoku, dla którego ma być uaktualniona informacja w tablicy systemowej sysindexes.

• index_id jest numerem indeksu dla podanej tablicy, dla której mają być uaktualnione informacje w tablicy sysindexes. Aby znaleźć index_id dla indeksu, należy uruchomić systemową procedurę składową sp_helpindex. Można również określić nazwę indeksu.

Zapewne użytkownik nie będzie korzystał z DBCC UPDATEUSAGE, ale polecenie to zostało tutaj zamieszczone dla zachowania spójności tematu.

Polecenie DBCC SHOWCONTIG

Okresowo, należy reorganizować indeksy. Po pewnym czasie indeksy nie spełniają współczynnika wypełnienia lub stają się pofragmentowane i mniej użyteczne. Aby sprawdzić czy indeksy są pofragmentowane i wymagają reorganizacji, należy skorzystać z polecenia DBCC SHOWCONTIG:

DBCC SHOWCONTIG [ (tablename | table_id | viewname | view_id}

[, indexname | index_id] ) ]

[WITH {ALL_INDEXES | FAST [, ALL_INDEXES]

| TABLERESULTS [, {ALL_INDEXES}]

[, {FAST | ALL_LEVELS} ] } ]

Składnia:

• table_id lub view_id jest identyfikatorem ID tablicy lub widoku, który ma być testowany pod kątem fragmentacji. Można pobrać ID obiektu przy pomocy funkcji object_id ('tablename').

• tablename lub viewname jest nazwą tablicy lub widoku, który ma być testowany. Można określić nazwę zamiast ID. Opcja ta jest nowa w SQL Serverze 2000.

• index_id jest wewnętrznym numerem indeksu, który ma być testowany, bez podania wartości. Informacja jest pobierana z indeksu zgrupowanego (jeżeli istnieje) lub ze stosu (heap).

• indexname jest nazwą indeksu, który ma być testowany. Ponownie jest to alternatywa do index_id i jest to nowa opcja w SQL Serverze 2000.

Można uruchomić następujący skrypt aby przeglądnąć wyniki działania DBCC SHOWCONTIG. Należy uruchomić ten skrypt w nowym oknie zapytań (lub wyłączyć SHOWPLAN i wszelkie inne opcje, które zostały uprzednio włączone).

USE PUBS

DBCC SHOWCONTIG ('indextab')

GO

DBCC SHOWCONTIG scanning 'INDEXTAB' table...

Table: 'INDEXTAB' (1557580587); index ID: 0, database ID: 5

TABLE level scan performed.

- Pages Scanned................................: 1

- Extents Scanned..............................: 1

- Extent Switches..............................: 0

- Avg. Pages per Extent........................: 1.0

- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]

- Extent Scan Fragmentation ...................: 0.00%

- Avg. Bytes Free per Page.....................: 6438.0

- Avg. Page Density (full).....................: 20.46%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Nową dobrą własnością poleceń DBCC SQL Servera jest obsługa opcji tableresults. Obsługa tej opcji pozwala, aby polecenie DBCC zwróciło poprawny rowset SQL Servera. Praktycznie wszystkie polecenia DBCC zezwalają na używanie tej opcji, nawet jeżeli oficjalna składnia nie pokazuje jej jako poprawnej opcji w SQL Server Books Online. Obsługa ta pozwala na umieszczanie wyników polecenia DBCC w tablicy. Przydatne jest to do testowania dużej ilości wyników DBCC w sposób relacyjny. Przykładowo, należy uruchomić
USE PUBS
DBCC SHOWCONTIG ('indextab') with tableresults
GO

aby otrzymać wynik jako pojedynczy wiersz w wirtualnej tablicy (rowset).

Skrypt ten zawiera wiele znaczących informacji, ale wystarczy się skupić jedynie na dwóch, trzech elementach. Po pierwsze, w pierwszej linii, wartość index ID: 0 oznacza, że tablica nie ma indeksu zgrupowanego. Następną wartością do omówienia jest Scan Density. Im wyższy procent, tym lepszy kształt tablicy. Kolejnym parametrem jest Avg. Page Density (full). Wartość ta pozwala sprawdzić jak jest blisko wartości współczynnika wypełnienia.

Kiedy te liczby w jakiś sposób stają się niższe (nie określając konkretnej liczby) oznacza to, że należałoby reorganizować indeksy. Można je usunąć a następnie utworzyć je ponownie. W przypadku indeksu zgrupowanego, można użyć opcji DROP_EXISTING aby przyspieszyć operację. Podejście to przywraca indeks do oryginalnego kształtu włączając w to ponowne zastosowanie opcji współczynnika wypełnienia. Nie wpływa również na indeksy niezgrupowane, ponieważ kolejności i narzut struktury indeksu zgrupowanego nie są zmieniane podczas korzystania z opcji DROP_EXISTING. Jeżeli indeks zgrupowany zostanie usunięty i ponownie utworzony bez tej opcji, zostają przeorganizowane strony danych oraz struktura indeksu.

DBCC SHOWCONTIG może być uruchamiany przez członków ustalonej roli serwera sysadmin lub przez członków roli bazy danych db_owner lub db_ddladmin.

Polecenie DBCC DBREINDEX

Inną dostępną opcją, która jest lepszym wyborem jest polecenie DBCC DBREINDEX:

DBCC BDREINDEX (['database.owner.table_name' [, index_name

[, fillfactor ]]])

[WITH NOINFOMSGS]

Wiele z tych opcji jest takich samych jak w poleceniu CREATE INDEX, należy więc odwołać się do tego polecenia, aby przypomnieć sobie opcje dotyczące sortowania danych i współczynnika wypełnienia. Jeżeli jednak współczynnik wypełnienia zostanie określony jako 0, używany jest oryginalny współczynnik, który został określony podczas tworzenia indeksu. Warto zauważyć, że jeżeli ma być określony parametr opcjonalny, należy określić wszystkie poprzednie opcje. Przykładowo, aby określić współczynnik wypełnienia, należy określić nazwę indeksu, nawet jeżeli zostawi się pustą nazwę. Dla przykładu można użyć następującego polecenia do przebudowania indeksów w tablicy authors (wszystkich indeksów) z oryginalnym współczynnikiem wypełnienia:

DBCC DBREINDE ('authors','',0)

Jeżeli zostanie określona tablica, taka jak następująca, każdy indeks w tablicy jest usuwany i tworzony ponownie, jak pokazano w wyniku poprzedniego zapytania:

DBCC DBREINDEX ('indextab')

GO

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Można również określić indywidualny indeks, którego będą dotyczyły operacje. W tym przykładzie, na początku jest tworzony dodatkowy indeks niezgrupowany na tablicy authors. Następnie uruchamiane jest polecenie DBREINDEX i wskazywany jest właśnie utworzony indeks:

CREATE INDEX noncl_col2_indextab on indextab (col4)

GO

DBCC DBREINDEX ('indextab','noncl_col2_indextab')

Należy rozważnie określać współczynnik wypełnienia przy pomocy DBCC DBREINDEX. Jeżeli nie zostanie określony dany indeks, ale zostanie użyte ustawienie współczynnika wypełnienia, jak np.: DBCC DBREINDEX ('indextab','',50), to współczynnik wypełnienia zostanie zresetowany we wszystkich indeksach w tablicy (w tym przypadku, do wartości 50). Wynik ten nie zawsze jest tym, czego użytkownik oczekiwał.

DBCC DBREINDEX ma jedną znaczącą przewagę nad usuwaniem i ponownym tworzeniem indeksów. Zostanie to omówione w rozdziale 14. Integralność referencyjna i więzy American National Standards Institute (ANSI) mogą czasami tworzyć indeksy w tle. DBCC DBREINDEX może bezpiecznie usunąć i utworzyć ponownie indeksy utworzone z więzami, podczas gdy jedyną drogą do wykonania tego inaczej jest usunięcie i ponowne utworzenie więzów.

Polecenie DBCC DBREINDEX mogą uruchamiać członkowie roli serwera sysadmin, ról db_owner i db_ddladmin lub właściciel tablicy.

Polecenie DBCC INDEXDEFRAG

DBCC INDEXDEFRAG jest nowym poleceniem w SQL Serverze 2000. Na pierwszy rzut oka, opcja ta może wydawać się ograniczona w porównaniu do DBCC DBREINDEX, ale będzie zapewne wybierana do reorganizacji indeksów w większości przypadków. Używając DBCC DBREINDEX, tablica jest niedostępna dla innych użytkowników stosujących zapytania, do czasu ukończenia reindeksowania. Jednak, w przypadku DBCC INDEXDEFRAG, inni użytkownicy mają nadal dostęp do tablicy. Możliwość ta zwana jest online reorg. Składnia polecenia jest następująca:

DBCC INDEXDEFRAG

( { database_name | database_id | 0}

, { table_name | table_id | 'view_name' | view_id}

,{index_name | index_id}

) [WITH no_INFOMSGS]

Ponownie, opcje są bardzo podobne do omówionych wcześniej, dlatego nie zostaną tutaj przedstawione. Teraz należy zastanowić się jaka jest w takim razie różnica pomiędzy tym poleceniem a DBCC DBREINDEX.

DBCC INDEXDEFRAG reorganizuje jedynie poziomy liścia indeksów. Jeżeli celem jest stworzenie perfekcyjnie ułożonego indeksu od góry do dołu, ta opcja nie jest najlepsza. Jednak polecenie to jest bardzo dobre, jeżeli chodzi o zadanie uruchamiane w tle, które usunie fragmentację większości indeksów lub jeżeli tablica nie może być niedostępna (jak podczas uruchomienia DBREINDEX).

Widoki indeksowane

SQL Server 2000 prezentuje możliwość tworzenia indeksów na widokach. Wszystkie wcześniejsze wersje Microsoft SQL Servera pozwalały na tworzenie indeksów jedynie na tablicach. Widoki indeksowane są łatwe koncepcyjnie, ale należy zrozumieć kilka kwestii dotyczących konfiguracji. Jednak aby zrozumieć podstawy, nie należy na razie troszczyć się o te sprawy.

Jak można było zauważyć przy omawianiu indeksowania, wszędzie gdzie była określana tablica w poleceniu związanym z indeksowaniem, można było również określić nazwę widoku. Ponieważ tablice zawierają dane, tworzenie indeksów na nich działa bezproblemowo (i jest intuicyjne). Jednak jak to wygląda w przypadku widoków, które — jak zostanie pokazane w rozdziale 15 — są po prostu przechowywanym zapytaniem? Otóż gdy tworzony jest indeks widoku, wyniki zapytania są przeliczane a dopiero po tym zapisywane. Następnie, za każdym razem, gdy dane są zmieniane dla dowolnych wierszy mających odzwierciedlenie w wynikach widoku, indeksy widoku są uaktualniane.

Do wytłumaczenia tego scenariusza przyda się przykład. Przyjmijmy, że istnieje widok łączący w sobie dane z tablicy titles, titleauthor i authors w bazie danych pubs. Widok może wyglądać następująco:

Create view myauthorview

With schemabinding

As

Select a.au_lname, a.au_fname, t.title, ta.royaltyper

From dbo.authors a inner join dbo.titleauthor ta on a.au_id = ta.au_id inner join dbo.titles t on t.title_id = ta.title_id

Następnie uruchamiając poniższe zapytanie, ukaże się plan optymalizatora pokazany na rysunku 13.10.

select * from myauthorview

Rysunek 13.10. Graficzny plan bez użycia indeksu zgrupowanego.

Ponieważ tablice te są niewielkie, trudno jest aby optymalizator używał indeksu zgrupowanego. Dlatego można to wymusić przy pomocy podpowiedzi dla optymalizatora. Należy uruchomić:

select * from myauthorview (noexpand)

aby uzyskać showplan jak na rysunku 13.11 pokazujący, że został użyty indeks zgrupowany.

Rysunek 13.11. Graficzny plan z użyciem indeksu zgrupowanego.

Dla większych tablic z wieloma złączeniami można sobie wyobrazić, że jeżeli odpowiedź jest już przeliczona, wydajność zapytania znacząco się poprawi, gdy będzie używany widok indeksowany. Widok ten jest szczególnie użyteczny w środowisku raportowania, gdzie znane są wcześniej rodzaje zapytań.

Jak widok jest zarządzany z indeksem? Za każdym razem, gdy jest modyfikowany wiersz jednej z tablic związanej z widokiem (authors, titles lub titlesauthor), widok indeksowany również musi być uaktualniony. Czyli regularna pielęgnacja tego indeksu może być kosztowna (koszty uaktualniania tego indeksu były by duże). Jednak jeżeli poprawa wydajności zapytań do raportowania jest znacząca, jest to rozwiązanie warte zastosowania.

Ograniczenia widoków indeksowanych

Przy korzystaniu z widoków indeksowanych należy przestrzegać kilku ograniczeń:

• Zanim utworzy się indeksy niezgrupowane należy najpierw utworzyć dla widoku indeks zgrupowany.

• Należy utworzyć widok używając opcji SCHEMABINDING. Opcja zostanie omówiona szczegółowo w rozdziale 15.

• Własności ANSI_NULLS i QUOTED_IDENTIFIER musza być włączone dla połączenia z bazą danych, które tworzy widok (SQL Server 2000 Query Analyzer ma domyślnie włączone te opcje).

• Własność ANSI_NULLS musi być włączona dla połączenia, które tworzy tablice związane z tym widokiem.

• Widok może być zdefiniowany do przepytywania jedynie tablic, a nie innych widoków.

• Wszystkie obiekty używane w widoku muszą mieć tego samego właściciela i być w tej samej bazie danych co widok.

• Należy umieścić nazwę właściciela we wszystkich odniesieniach zapytań, jak pokazano wcześniej w przykładowym zapytaniu.

Więcej ograniczeń zawierają podręczniki Books Online, powyższej przedstawiono tylko najważniejsze z nich.

Widoki indeksowane są używane przez optymalizator zapytań jedynie w wersji Enterprise Edition SQL Servera 2000. Można tworzyć indeksy widoków również w innych wersjach SQL Servera 2000, ale jedynie Enterprise Edition faktycznie ich używa. Istnieje możliwość, że polecenia Data Definition Language (DDL) nie będą zawodzić w różnych wersjach SQL Servera, nawet jeżeli nie są całkowicie wykorzystywane w innych wersjach tego produktu.

Zarządzanie indeksami przy pomocy SQL Server Enterprise Managera

Można zarządzać indeksami przy pomocy okna właściwości z SQL Server Enterprise Managera. Po pierwsze, należy wiedzieć, że są dwa różne okna kontroli indeksów. Można rozwinąć odpowiednią bazę danych (w przykładzie jest to baza pubs), podświetlić folder Tables i kliknąć prawym klawiszem myszy odpowiednią tablicę w prawym panelu. Następnie wybrać z menu kontekstowego Desing Table, aby otworzyć okno Desing Table. Przykładowo, wybrano tablicę authors, przestawioną na rysunku 13.12.

Rysunek 13.12. Okno zarządzania tablicą — Design Table.

Jak widać, na rysunku 13.12, po kliknięciu drugiego przycisku na pasku narzędzi (Table and Index Properties) pojawi się okno właściwości tablicy. Warto zwrócić uwagę, że okno jest inne od okna dotyczącego głównych własności tablicy. Następnie należy kliknąć zakładkę Indexes/Keys (zobacz rysunek 13.13).

Rysunek 13.13. Własności okna Design Table.

Z tego okna można używać większości opcji indeksowania poznanych do tej pory. Można tworzyć, usuwać i zmieniać nazwę indeksu. Można określić unikalność indeksu, a jeśli indeks jest unikalny można zadecydować, czy jest to unikalne powiązanie czy unikalny indeks. W praktyce wybiera się zwykle unikalne więzy a nie unikalny indeks. Jeżeli zdecyduje się pozostawić powiązanie jako unikalny indeks, może ustawić opcję Ignore Duplicate Key, omówioną w sekcji „Opcja IGNORE_DUP_KEY”.

Można również określić czy jest to indeks zgrupowany oraz wyłączyć automatyczne zbieranie statystyk. Warto zauważyć, że statystyki automatyczne są włączone, chyba że zostały wcześniej wyłączone przez użytkownika. Dostępne są również opcje Fill Factor i Pad Index. Dostępna jest również opcja do grupowania plików. Jeżeli są używane grupy plików (jak opisano w rozdziale 4), można zmienić grupę plików, do której jest przypisany indeks.

Jednak dostępne jest lepsze okno dialogowe dotyczące opcji indeksu — zostało pokazane na rysunku 13.14. Korzystając z tego okna można łatwiej przejrzeć, które indeksy są dostępne i które kolumny są indeksowane. Można wywołać to okno klikając prawym klawiszem myszy tablicę i wybierając Wszystkie Zadania, Manage Indexes.

Rysunek 13.14. Zarządzanie indeksami — okno Manage Indexes.

Zastosowanie tej opcji widać dopiero przy tworzeniu nowego indeksu. Należy kliknąć przycisk New aby otworzyć okno pokazane na rysunku 13.15. Warto zauważyć, że każda z opcji dla SQL Servera 2000 ma możliwość graficznego określenia.

Rysunek 13.15. Tworzenie indeksu (Create New Index).

Dlaczego SQL Server ma dwa zupełnie różne okna do zarządzania indeksami? Okno z własności tablicy pochodzi ze składnika kodu, który jest wspólny dla wielu produktów Microsoft (jak np. Visual Studio). Jest tworzony do obsługi wielu baz danych, nie tylko SQL Servera 2000. Natomiast okno Manage Indexes jest dedykowane w całości dla SQL Servera 2000 i dlatego wszystkie opcje specyficzne dla SQL Servera 2000 mogą być dostępne.

Wybór indeksu

Po poznaniu dostępnych opcji oraz sposobu budowania indeksu, kolejnym pojawiającym się logicznym pytaniem powinno być: „ Które kolumny należy indeksować i jak należy to zrobić?”

Dobrymi kandydatami do indeksów są:

• Kolumny klucza podstawowego (omówione w rozdziale 14)

• Kolumny klucza obcego (omówione w rozdziale 14)

• Kolumny, do których stosowana jest klauzula ORDER BY

• Kolumny do których stosowana jest klauzula GROUP BY

• Kolumny, które zostają bezpośrednio określone w klauzuli WHERE

Zasadniczo, należy indeksować kolumny, które są ujęte w klauzuli WHERE przy pomocy warunków nie zawierających funkcji ani kalkulacji. Następujący przykład pokazuje, że kolumna emp_id jest dobrym kandydatem do indeksowania:

WHERE emp_id = 5

W przypadku kolejnego przykładu, nie będzie raczej stosowany indeks, więc nie ma sensu go tworzyć:

WHERE SUBSTRING(emp_id,1,5) = 'Annet'

Polecenia takie jak we wcześniejszym przykładzie klauzuli WHERE nazywane są searchable arguments. Searchable arguments (zwane również SARG) są typami argumentów, które mogą być używane przez SQL Server. Dodatkowo do dokładnego dopasowania, można czasem użyć przybliżonych wartości w stosunku do kolumn znakowych. Przykładowo:

WHERE emp_lname LIKE 'w%'

jest argumentem searchable argument, ale

WHERE emp_lname LIKE '%w%'

nie jest. Warto zwrócić uwagę, że symbol % oznacza znak wieloznaczny. W pierwszym przykładzie, kolumna emp_lname jest przeszukiwana pod kątem wartości rozpoczynających się literą w. W drugim przykładzie kolumna emp_lname jest przeszukiwana pod kątem wartości zawierających w sobie literę w . Można to porównać do przeszukiwania książki telefonicznej. W przypadku drugiego warunku przeszukiwanie będzie o wiele trudniejsze. SQL Server będzie miał ten sam problem; natomiast w przypadku gdy pierwsza litera jest określona, wiadomo od czego zacząć.

Należy również brać pod uwagę, że nie każda kolumna jest dobrym kandydatem do utworzenia z niej indeksu. Nie należy indeksować następujących kolumn:

• kolumn używających typów danych text, image i bit

• kolumn, które nie są bardzo różnorodne (np. kolumna płeć — mężczyzna, kobieta)

• kolumn, które są zbyt duże aby były użytecznymi indeksami

Ostatnia opcja zależy od aplikacji i od tablicy, ale na ogół typ char(200) nie jest dobrym kandydatem do stworzenia na jego podstawie indeksu.

Należy mieć świadomość, że każdy indeks wiąże się z jego pielęgnacją, zużyciem przestrzeni i sprawami wydajności. Zasadniczo zaleca się zakładanie nie więcej niż trzy, cztery indeksy w pojedynczej tablicy. Jak w przypadku każdej reguły, jest kilka wyjątków, ale jest to dobra reguła i należy się jej trzymać. Po zadecydowaniu, które kolumny indeksować, należy zadecydować, który typ indeksu będzie potrzebny: zgrupowany czy niezgrupowany.

Wybór indeksu zgrupowanego

Ponieważ można mieć w tablicy tylko jeden indeks zgrupowany, dobrze jest wybrać go na początku. Dobry kandydat na tego typu indeks zgrupowany charakteryzuje się własnościami:

• bardzo specyficzne zapytania (np.: where col1 = 5)

• zapytania z zakresem danych (np.: WHERE col1 BETWEEN 5 AND 30 oraz WHERE col1 > 20)

• Zapytania na kolumnach, do których często stosuje się klauzule order by lub group by.

Bardzo ważną sprawą w SQL Serverze 2000 jest to, że wartości klucza (kolumna indeksowana), które zostały wybrane do indeksu zgrupowanego, są „przenoszone” do niezgrupowanych indeksów. Dlatego, jeżeli zostanie wybrany duży indeks zgrupowany — przykładowo Char(30) — nie tylko więcej czasu zajmuje przeszukanie tego indeksu, ale wszystkie inne indeksy niezgrupowane muszą również zawierać wartość Char(30) klucza tego indeksu zgrupowanego na każdym z wierszy w tych indeksach. Jest to znaczący narzut, dlatego należy utrzymywać klucze indeksu zgrupowanego tak małe jak to możliwe. Należy się również upewnić, czy klucz zgrupowany, który został wybrany, nie jest często uaktualniany, ponieważ wszystkie indeksy niezgrupowane muszą być uaktualnione gdy zmienią się wartości indeksu zgrupowanego.

Innym wyborem może być indeks na kolumnach klucza obcego, które zostaną omówione w rozdziale 14.

Wybór indeksu niezgrupowanego

Po wybraniu indeksu zgrupowanego, pozostałe utworzone indeksy muszą być typu niezgrupowanego. Dobry kandydat spełnia warunki:

• specyficzne zapytania (są dobre dla obydwu typów indeksów).

• zapytania, które mogą być w całości wykonane przy pomocy indeksu (zwane covering index queries). Przykładowo, jeżeli na kolumnach au_lname i au_fname założony jest indeks w tablicy authors w bazie danych pubs, następujące zapytanie może być w całości zrealizowane poprzez indeks, bez potrzeby dostępu do danych:

SELECT au_fname from pubs..authors where au_lname = "White"

• kolumny wg których się sortuje (order by) lub grupuje (group by).

• kolumny, na których używa się funkcji (takich jak MIN, MAX lub COUNT).

Kluczową sprawą w tym przypadku jest to, że indeksuje się jedynie kolumny, które będą używane w klauzuli WHERE zapytań oraz to, że zostało zweryfikowane (przy pomocy opcji SHOWPLAN lub opcji graficznych SQL Server Query Analyzera), iż wybrane indeksy będą używane przez SQL Server.

SQL Server Index Tunning Wizard

Index Tunnig Wizard zostanie omówiony w rozdziale 20. Tutaj kreator ten został jedynie wymieniony dla zachowania całości tematu. Jednak, najpierw należy zaznajomić się z narzędziem SQL Server Profiler zanim będzie się z powodzeniem korzystać z tego kreatora.

Indeksowanie pełnotekstowe

Indeksowanie pełnotekstowe pozwala na tworzenie indeksu na kolumnie typu text, jak również na innych kolumnach typu znakowego. Własność ta jest instalowana domyślnie poczynając od SQL Servera 2000 (chyba, że serwer jest uruchamiany w systemie Windows 9x). Indeksowanie pełnotekstowe jest możliwe z powodu przeniesienia technologii Microsoft Index Server do SQL Servera 2000. Własność ta wymaga uruchomienia usługi Microsoft Search (która jest instalowana domyślnie podczas instalacji indeksowania pełnotekstowego). Wymaga również unikalnego indeksu na każdej z tablic, które mają być używane do indeksowania pełnotekstowego. Jedynie właściciel tablicy może tworzyć indeks pełnotekstowy.

Można mieć tylko jeden indeks pełnotekstowy w tablicy, indeks ten jest fizycznie przechowywany poza SQL Serverem (w katalogu \mssql\FTData). Inaczej niż w przypadku normalnych indeksów SQL Servera, indeksy pełnotekstowe nie są samodzielnie zarządzane. Należy ustawić okresowe zadanie, które będzie uaktualniało indeks pełnotekstowy. Zadania (jobs) zostaną omówione w rozdziale 18. Proces konfiguracji i używania indeksowania pełnotekstowego jest inny niż w przypadku normalnych indeksów, ale firma Microsoft dostarczyła kolejny kreator do wspomagania pracy użytkownika.

W SQL Server Enterprise Managerze należy podświetlić tablicę, w której ma być tworzony indeks pełnotekstowy, następnie należy wybrać z menu Tools opcję Full-Text Indexing. Uruchomi się Full-Text Indexing Wizard, który poprowadzi przez proces tworzenia nowego indeksu. Pokazany tutaj przykład dotyczy tablicy pub_info z bazy danych pubs. Wybór ten ma na celu pokazanie, że można indeksować kolumnę typu text, nawet jeżeli nie jest to możliwe z normalnymi indeksami SQL Servera. Po uruchomieniu kreatora ukazuje się ekran powitalny. Po przeczytaniu wprowadzenia należy kliknąć Dalej aby zobaczyć okno pokazane na rysunku 13.16.

Rysunek 13.16. Wybór unikalnego indeksu tablicy.

Jak zostało powiedziane, do tworzenia indeksu pełnotekstowego potrzebny jest unikalny indeks (jako row locator). Należy wybrać odpowiedni unikalny indeks (dla pub_info jest dostępny tylko jeden), i kliknąć Dalej aby zobaczyć okno pokazane na rysunku 13.17.

Rysunek 13.17. Wybór kolumn tablicy.

Jak widać z rysunku, została wybrana kolumna pr_info typu text. Inne opcje w tym oknie są nowymi własnościami SQL Servera 2000: można indeksować dokumenty, które mają dostępne filtry dokumentów. Przykłady uwzględniają dokumenty Microsoft Word. Można ustawić nawet język dokumentu, czyli filtry indeksu są odpowiednie do lokalnego języka. Należy kliknąć Dalej aby zobaczyć okno pokazane na rysunku 13.18, w którym można wybrać katalog. Dla uproszczenia sprawy, można wybrać nowy (ponieważ użytkownik zapewne nie posiada odpowiedniego w systemie), i nazwać go pub_info (jak pokazano na rysunku 13.18).

Rysunek 13.18. Wybór katalogu do przechowywania indeksu pełnotekstowego.

Należy kliknąć Dalej i w oknie wynikowym pojawia się opcja do ustawienia planu wypełniania i uaktualniania indeksu pełnotekstowego. Zamiast wchodzenia w harmonogram zadań należy opuścić tę opcję klikając Dalej. Można nadal ręcznie uaktualniać indeks pełnotekstowy. Należy kliknąć Finish. Ukaże się okno pokazane na rysunku 13.19 informujące o poprawnym zakończeniu konfigurowania indeksu pełnotekstowego. Informuje ono także o tym, ze indeks pełnotekstowy nie został jeszcze wypełniony.

Rysunek 13.19. Ostatnie okno po ukończeniu działań kreatora.

Należy kliknąć OK a następnie ponownie rozwinąć bazę danych pubs, jeżeli nie zostało to zrobione wcześniej w SQL Server Enterprise Managerze. Należy podświetlić Full-Text Catalogs, kliknąć prawym klawiszem myszy katalog, który został właśnie utworzony (nazwany pub_info) w prawym panelu Enterprise Managera i wybrać z menu kontekstowego Start Full Population (zobacz rysunek 13.20).

Rysunek 13.20. Rozpoczęcie wypełniania Full-Text Catalog.

Po zakończeniu tej operacji można już korzystać z tego indeksu w zapytaniach. W tym momencie zapytania stają się nieznacznie trudniejsze. Zapytania z zastosowaniem indeksów pełnotekstowych nie są bardzo łatwe w SQL Serverze 2000 w porównaniu do tworzenie zwykłych zapytań ponieważ należy się nauczyć odpowiedniej składni dla indeksów pełnotestowych. Przykładowo, uruchamiając następujące zapytanie w Query Analyzer, zwrócone zostaną wszystkie wiersze zawierające słowo sample:

select pub_id, substring(pr_info,1,100) from pub_info

where CONTAINS (pr_info, 'sample')

Zapytania pełnotekstowe mogą być skomplikowane, dlatego należy odwołać się do podręczników SQL Server Books Online, (w przypadku uruchamiania znacznie bardziej złożonych zapytań z indeksami pełnotekstowymi).

2 Część I ♦ Podstawy obsługi systemu WhizBang (Nagłówek strony)

2 C:\moj dysk\studia\pracamag\materiały\helion\ksiazki\SQL Server 2000 dla kazdego\r13-05.doc

Brak rysunku

Brak rysunku

Brak rysunku

Brak rysunku

Brak rysunku

Początek wskazówki

Koniec wskazówki

Brak rysunku

Brak rysunku

Początek wskazówki

Koniec wskazówki

---