Nowości w aparacie składowania danych w SQL Server 2008 – filtrowane indeksy i statystyki

Autor: Damian Widera

Opublikowano: 17 czerwca 2008

Zawartość strony

	Wstęp
	Filtrowane indeksy
	Silnik baz danych a indeksy filtrowane
	Indeksy filtrowane a pełnotablicowe – krótkie porównanie
	Indeksy filtrowane a widoki
	Rekomendacje podczas tworzenia indeksu filtrowanego
	Indeks filtrowany – kiedy go użyć?
	Filtrowane statystyki
	Podsumowanie

Wstęp

Microsoft SQL Server 2008 February CTP (CTP6) przyniósł wiele istotnych nowości, które zainteresują każdego administratora czy dewelopera baz danych. Artykuł kontynuuje rozpoczęte rozważania na temat najistotniejszych zmian w aparacie składowania danych, które wprowadza cytowany CTP6. W poprzednim artykule omówiłem już sparse columns oraz colums set. Z punktu widzenia administratora baz danych filtrowane indeksy oraz statystyki pozwolą w pełni skorzystać z zalet opisywanych wcześniej nowych funkcjonalności. Nie tylko zresztą – filtrowane indeksy i statystyki mają szansę stać się swego rodzaju przełomem w tworzeniu aplikacji bazodanowych.

 Do początku strony

Filtrowane indeksy

Indeks filtrowany jest zoptymalizowanym indeksem niezgrupowanym, który najlepiej sprawdza się dla zapytań pokrywających, dla których dobrze zdefiniowano podzbiór danych. Dobrze skonstruowany indeks filtrowany może znacząco poprawić wydajność zapytań oraz zredukować koszty związane zarówno z jego obsługą (rozumianą w szerszym zakresie, niesprowadzającą się tylko do sprawdzania poziomu fragmentacji indeksu, ale także operacji nakładanych na silnik baz danych i związanych np. z aktualizacją statystyk) oraz zajmowanym miejscem na dysku.

Można rozwinąć powyższe zdanie i pokusić się o krótkie porównanie indeksów pełnotablicowych (znanych z wcześniejszych wersji systemu) z nowymi, filtrowanymi indeksami i stwierdzić, iż nowe indeksy rzeczywiście mają następujące zalety:

• Dobrze skonstruowany indeks filtrowany poprawia wydajność zapytań oraz jakość planów zapytań, ponieważ jest mniejszy (ma mniejszy rozmiar, zawiera mniej wierszy) oraz korzysta z filtrowanych statystyk, które są dokładniejsze niż statystyki oparte na danych z całej tablicy, ponieważ zawierają tylko wiersze wykorzystywane w indeksie filtrowanym.
• Indeks filtrowany, jak zresztą każdy indeks, wymaga pewnych operacji zarządzania jedynie wtedy, kiedy operacje języka DML (data manipulation language) modyfikują dane wchodzące w skład indeksu. Mniejszy koszt zarządzania indeksu filtrowanego wynika z faktu, i z taki indeks jest mniejszy niż indeks oparty na danych z całej tablicy. Można więc utworzyć wiele indeksów filtrowanych zawierających małe podzbiory danych, a efekt zmniejszonego kosztu zarządzania będzie najsilniej widoczny, kiedy dane zawarte w indeksie będą aktualizowane rzadko. Z drugiej jednak strony, duża liczba indeksów filtrowanych zwierających często aktualizowane dane przyczyni się do wzrostu kosztów zarządzania, niż miałoby to miejsce w przypadku korzystania z jednego indeksu pełnotablicowego, a wiąże się to z koniecznością aktualizacji odpowiednich statystyk.
• Dobrze zbudowany indeks filtrowany zajmuje mniej miejsca aniżeli indeks pełnotablicowy. Można w taki sposób zastąpić jeden duży niezgrupowany indeks, oparty na całej tablicy kilkoma mniejszymi indeksami filtrowanymi bez zauważalnej różnicy miejsce, które one zajmują.

 Do początku strony

Silnik baz danych a indeksy filtrowane

Trudno oczekiwać w tym miejscu innego stwierdzenia, niż to, że silnik baz danych traktuje indeksy filtrowane dokładnie w taki sam sposób, jak robi to z niezgrupowanymi indeksami pełnotablicowymi. Precyzując – indeksy filtrowane traktowane są jako szczególna odmiana indeksu niezgrupowanego.

Istnieje jednak kilka różnic pomiędzy „zwykłymi” a filtrowanymi indeksami – a mianowicie modyfikacja indeksu filtrowanego odbywa się za pomocą polecenia ALTER INDEX, ale trzeba pamiętać, iż zmieniając warunek filtra należy skorzystać z polecenia CREATE INDEX … WITH DROP EXISTING. Druga istotną różnicą jest fakt, iż poszukując brakujących indeksów za pomocą widoku katalogowego sys.dm_db_missing_index_details nie znajdziemy tam żadnego indeksu filtrowanego, co prowadzi do wniosku, iż silnik baz danych nie poszukuje brakujących indeksów wśród indeksów filtrowanych. Jest to niestety zachowanie niekonsekwentne, gdyż narzędzie Database Engine Tuning Advisor (dta.exe) rekomenduje indeksy filtrowane. Warto pamiętać również, iż można tworzyć indeksy filtrowane w trybie ONLINE a także można używać hintów tablicowych – pod pewnymi warunkami (niemającymi zastosowania dla indeksów niezgrupowanych pełnotablicowych), które zostały szczegółowo opisane w Books Online.

SQL Server Management Studio (SSMS) ułatwia tworzenie indeksów filtrowanych umieszczając odpowiedni odnośnik w oknie własności, jak pokazano na rysunku poniżej:

Strona Filter umożliwia wprowadzenie warunku filtra, który posłuży do zbudowania indeksu filtrowanego:

Oczywiście nic nie stoi na przeszkodzie, aby indeks filtrowany utworzyć również za pomocą kodu TSQL:

use AdventureWorks

go

CREATE NONCLUSTERED INDEX [idx_ncl_FICarrierTrackingNumber]

ON [Sales].[SalesOrderDetail]

(

[SalesOrderID] ASC

)

INCLUDE ( [ProductID],[UnitPrice],[UnitPriceDiscount])

WHERE ([CarrierTrackingNumber] IS NOT NULL)

ON [PRIMARY]

GO

Na powyższym przykładzie oznaczyłem pogrubioną czcionką, w jaki sposób definiować filtr tworzący indeks. W dalszej części artykułu przytoczę informację, jakie operatory logiczne są dozwolone w warunku filtra.

Tak naprawdę jest to jedyne miejsce, w którym tworzenie indeksu filtrowanego różni się od tworzenia indeksu niezgrupowanego w aparacie SQL Server 2005. Podobnie jak w starszej wersji systemu mamy możliwość (i obowiązek) wskazania pola lub pól będących kluczami indeksu (SalesOrderID), kolumn dołączanych (ProductID, UnitPrice, UnitPriceDiscount) oraz grupy plików, w której indeks będzie przechowany (PRIMARY). Omawiany przykład nie uwzględnia innych opcji, które znane są z silnika baz danych SQL Server 2005, takich jak możliwość utworzenia indeksu w trybie online, ponieważ nie uległy one zmianom.

Warto również zapamiętać, iż można utworzyć maksymalnie 249 indeksów (filtrowanych czy pełnotablicowych) na danej tablicy.

 Do początku strony

Indeksy filtrowane a pełnotablicowe – krótkie porównanie

Nadszedł czas, aby przyjrzeć się bliżej indeksom filtrowanym. Jeden z takich indeksów utworzyliśmy w poprzednim rozdziale artykułu:

CREATE NONCLUSTERED INDEX [idx_ncl_FICarrierTrackingNumber]

ON [Sales].[SalesOrderDetail]

(

[SalesOrderID] ASC

)

INCLUDE ( [ProductID],[UnitPrice],[UnitPriceDiscount])

WHERE ([CarrierTrackingNumber] IS NOT NULL)

ON [PRIMARY]

GO

Dla poprawnego wykonania przykładu utworzyłem drugi indeks niezgrupowany – tym razem pełnotablicowy – aby odzwierciedlić różnice pomiędzy nimi:

CREATE NONCLUSTERED INDEX [idx_ncl_CarrierTrackingNumber]

ON [Sales].[SalesOrderDetail]

(

[CarrierTrackingNumber] ASC,

[SalesOrderID] ASC

)

INCLUDE ( [ProductID],[UnitPrice],[UnitPriceDiscount])

ON [PRIMARY]

GO

Dodatkowo – tablica Sales.SalesOrderDetail została zmodyfikowana tak, aby bardziej odpowiadała rzeczywistym warunkom, z którymi zazwyczaj mamy do czynienia na co dzień. Modyfikacja polegała na wstawieniu miliona wierszy zawierających wartość NULL w kolumnie CarrierTrackingNumber. W tej sytuacji omawiana tablica zawierała 1121317 wierszy. Indeks filtrowany składał się z trzech poziomów, na poziomie liści miał 60919 wierszy, co dawało 490 stron. Z kolei indeks pełnotablicowy zawierał tyle wierszy ile tabela, zapisanych na 4925 stronach. Proste porównanie wystarcza, żeby zauważyć, że indeks filtrowany jest 10 razy mniejszy od niezgrupowanego indeksu pełnotablicowego.

Wykonałem następnie jedno testowe zapytanie, które zostało sformułowane następująco:

SELECT [SalesOrderID]

      ,[ProductID]

      ,[UnitPrice]

      ,[UnitPriceDiscount]

FROM  [Sales].[SalesOrderDetail]

WHERE [CarrierTrackingNumber] IS NOT NULL

Zapytanie uruchamiane było dla obydwu indeksów, tzn. korzystałem z hintów tablicowych i wymagałem, aby optymalizator uwzględnił raz jeden a raz drugi indeks. Plany zapytania przedstawiały się następująco:

Koszt wykonania zapytania (na rysunku powyżej znajduje się rzeczywisty plan wykonania zapytania) dla indeksu pełnotablicowego był trzykrotnie większy w porównaniu z kosztem wykonania zapytania opartego na indeksie filtrowanym (75% - 25%). Dla indeksu pełnotablicowego optymalizator wybrał przeszukiwanie indeksu (Indeks scan), natomiast w przypadku indeksu filtrowanego odbyło się jego skanowanie. Warto również popatrzeć na inne wskaźniki, które można odczytać dla obydwu operatorów:

Dla indeksu filtrowanego poprawnie został odczytana tzw. kardynalność tabeli, co pozwala wysunąć wniosek, iż plany wykonania zapytania zostały wygenerowane poprawnie i optymalizator nie przyjął fałszywych założeń związanych z rozmiarem tabeli źródłowej. Czynnikiem, który zdecydowanie przemawia za użyciem indeksu filtrowanego jest oznaczony na rysunki przewidywany koszt operacji I/O związany z odczytem danych zawartych w tym indeksie – Estimated I/O Cost. Kost ten jest około dziesięciokrotnie mniejszy dla indeksu filtrowanego (dla indeksu pełnotablicowego został on określony na poziomie 0.2681), co jest zgodne z wcześniejszą obserwacją na temat jego rozmiaru (chociaż nie należy wprost łączyć tych dwóch wielkości).

Różnice pomiędzy szybkością działania indeksów stają się dramatyczne na korzyść indeksu filtrowanego, gdy zapytanie testowe zostanie nieco tylko zmodyfikowane do postaci:

SELECT [SalesOrderID]

      ,[ProductID]

      ,[UnitPrice]

      ,[UnitPriceDiscount]

FROM [Sales].[SalesOrderDetail]

WITH ( INDEX ( idx_ncl_CarrierTrackingNumber) )

WHERE [CarrierTrackingNumber] IS NOT NULL

AND SalesOrderID='63155'

Kolumna SalesOrderID znajduje się w kluczu każdego z tych dwóch indeksów. Aktualne plany wykonania zapytań prezentują jednak zaskakujące informacje:

Okazuje się, iż koszt wykonania zapytania korzystającego z indeksu filtrowanego to tylko 1% w stosunku do obydwu zapytań. Zwróćmy uwagę, iż dla uzyskania wyników dla tego zapytania indeks filtrowany jest przeszukiwany a nie skanowany, jak miało to miejsce w pierwszym zapytaniu. Po włączeniu statystyk I/O oraz czasowych dla tych zapytań otrzymałem następujące wyniki:

• Dla indeksu niezgrupowanego pełnotablicowego – statystyki I/O

(20 row(s) affected)

Table 'SalesOrderDetail'. Scan count 1, logical reads 475, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

• Dla indeksu filtrowanego – statystyki I/O

(20 row(s) affected)

Table 'SalesOrderDetail'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

• Dla indeksu niezgrupowanego pełnotablicowego – statystyki czasowe

(20 row(s) affected)

SQL Server Execution Times:

CPU time = 0 ms,  elapsed time = 19 ms.

• Dla indeksu filtrowanego – statystyki czasowe

(20 row(s) affected)

SQL Server Execution Times:

CPU time = 0 ms,  elapsed time = 1 ms.

Proste testy przeprowadzone na stosunkowo reprezentacyjnej pod względem ilości wierszy tablicy pozwalają odkryć zalety używania dobrze zbudowanego indeksu filtrowanego nad pełnotablicowym indeksem niezgrupowanym.

Na zakończenie tej części artykułu chciałbym wspomnieć o konieczności konwersji predykatu filtra do odpowiedniego typu danych. Sytuacja taka nie jest częsta i ma miejsce tylko wówczas, gdy konwersja musiałaby nastąpić po lewej stronie predykatu filtra. Omawianą ideę na pewno przybliży przykład:

CREATE TABLE Tabela

(

a  int

,b int

)



CREATE NONCLUSTERED INDEX Index1

ON dbo.Tabela(a)

WHERE b = '1';

Silnik baz danych nie zezwoli na utworzenie takiego indeksu i poinformuje administratora za pomocą komunikatu błędu 10611, który oznacza, iż nieodpowiednia konwersja nastąpiła po lewej stronie warunku filtrującego:

Msg 10611, Level 16, State 1, Line 7

Filtered index 'Index1' cannot be created on table 'dbo.Tabela'

because the column 'b' in the filter expression is compared with a

constant of higher data type precedence or of a different collation.

Converting a column to the data type of a constant is not supported for

filtered indexes. To resolve this error, explicitly convert the constant

to the same data type and collation as the column 'b'.

W takiej sytuacji jednym wyjściem jest przepisanie warunku filtra tak, aby konwersja czy rzutowanie na odpowiedni typ danych nastąpiło po prawej stronie warunku filtra:

CREATE NONCLUSTERED INDEX Index1

ON dbo.Tabela(a)

WHERE b = CAST(‘1’ as int)

 Do początku strony

Indeksy filtrowane a widoki

Niniejszy fragment artykułu poświęcony jest widokom i ich podobieństwu do indeksów filtrowanych. Patrząc na definicję indeksu filtrowanego nie sposób oprzeć się wrażeniu, iż patrzymy na widok zbudowany na jednej tablicy. Pamiętajmy jednak, że widok jest wirtualną tablicą przechowującą definicję zapytania i jest wypełniany danymi w momencie odwołania się do niego.

Wiadomo jednak, iż można wykonać widok zmaterializowany – tzn. taki, który posiada indeks niezgrupowany i w ten sposób przechowuje dane trwale (fizycznie na dysku twardym). SQL Server 2008 nie pozwala jednak na utworzenie indeksów filtrowanych na żadnego typu widokach. Zamiast tego można utworzyć odpowiedni indeks filtrowany na tablicy, do której potem odwołuje się widok. Takie podejście – emulujące w pewien sposób widok materializowany - zadziała tylko wtedy, gdy widok będzie odwoływał się tylko do tej jednej tabeli, zapytanie nie będzie zwracało kolumn wyliczanych a warunek filtra indeksu oparty będzie tylko na prostej logice, która uwzględnia operatory porównania i logiczne, takie jak:

• =
• <, >, >=, <=
• <>
• AND
• OR

Reasumując, indeks filtrowany nie może być traktowany jak widok, ponieważ posiada następujące ograniczenia:

• nie może zawierać kolumn wyliczanych
• nie pozwala używać klauzuli JOIN a więc odwoływać się do wielu tabel
• nie pozwala korzystać z rozbudowanej logiki w warunku filtra

Do rozbudowanej logiki w warunku filtra możemy zaliczyć między innymi taki warunek jak:

WHERE [Kolumna1] LIKE 'MOTO%'

Więcej informacji wraz na ten temat można znaleźć w Books Online.

 Do początku strony

Rekomendacje podczas tworzenia indeksu filtrowanego

W zasadzie rekomendacje dotyczące tworzenia indeksu filtrowanego są takie samie, jak przy tworzeniu pełnotablicowych indeksów niezgrupowanych. Warto sobie jednak przypomnieć część dobrych praktyk i zobaczyć, w jaki sposób i w jakich sytuacjach optymalizator zapytań będzie mógł z takiego indeksu skorzystać.

Pierwszą i zdecydowanie najważniejszą rekomendacją dotyczącą budowania indeksu w ogólnym przypadku jest, aby zawierał jak najmniejszą ilość kolumn kluczowych czy dołączanych. Takie podejście gwarantuje, że optymalizator zapytań (który jest optymalizatorem kosztowym) będzie chętniej z indeksu korzystał, o ile tylko spełnia on wymagania postawione w zapytaniu.

W silniku SQL Server 2008 optymalizator skorzysta z indeksu filtrowanego nie tylko w przypadku, gdy będzie on pokrywał zapytanie – jednakże w takiej sytuacji prawdopodobieństwo jego użycia będzie bardzo duże. Indeks nie pokrywający zapytania może zostać użyty (skanowany lub przeszukiwany) we wstępnym przetwarzaniu zapytania – przekazując do operatora Key Lookup poszukiwane wartości kluczy oraz zestaw kolumn, które mają być wyświetlone w wyniku wykonania tego zapytania; Operator Key Lookup przeszuka następnie indeks zgrupowany i doczyta wartości z kolumn, których nie zawiera indeks filtrowany. Będzie to miało miejsce jedynie wtedy, gdy operacja przejścia z indeksu niezgrupowanego na zgrupowany będzie stosunkowo mało kosztowna i będzie dotyczyła około 5% wierszy w tablicy. Powyżej tej liczby wierszy mniej kosztowne staje się przeskanowanie całego indeksu zgrupowanego.

Modyfikując nieco zapytanie użyte we wcześniejszym rozdziale niniejszego artykułu, możemy taką sytuację zobrazować następująco:

SELECT [SalesOrderID],

       [ProductID],

       [UnitPrice],

       [UnitPriceDiscount],

       ModifiedDate –- TA KOLUMNA NIE JEST ZAWARTA W INDEKSIE FILTROWANYM

FROM [Sales].[SalesOrderDetail]

WITH ( INDEX ( idx_ncl_FICarrierTrackingNumber) )

WHERE [CarrierTrackingNumber] IS NOT NULL

AND SalesOrderID='63155'

W niektórych przypadkach indeks filtrowany pokrywa zapytanie nawet jeżeli nie zawiera poszukiwanych kolumn w kluczu czy kolumnach dołączanych. Kolumna w indeksie filtrowanym nie musi być częścią klucza, gdy warunek filtra indeksu pokrywa się z warunkiem klauzuli WHERE zapytania. Pamiętając definicję indeksu użytego we wcześniejszym przykładzie:

CREATE NONCLUSTERED INDEX [idx_ncl_FICarrierTrackingNumber]

ON [Sales].[SalesOrderDetail]

(

[SalesOrderID] ASC

)

INCLUDE ( [ProductID],[UnitPrice],[UnitPriceDiscount])

WHERE ([CarrierTrackingNumber] IS NOT NULL)

ON [PRIMARY]

GO

można powiedzieć, że zapytanie:

SELECT [SalesOrderID]

      ,[ProductID]

      ,[UnitPrice]

      ,[UnitPriceDiscount]

FROM [Sales].[SalesOrderDetail]

WHERE [CarrierTrackingNumber] IS NOT NULL

z dużym prawdopodobieństwem skorzysta z pokazanego wcześniej indeksu pomimo, iż kolumna [CarrierTrackingNumber] nie jest zawarta jako kolumna klucza tego indeksu. Kolumną tworzącą klucz indeksu jest kolumna [SalesOrderID].

Kolumna występująca w warunku budującym filtr indeksu powinna być także częścią jego klucza, gdy warunek występujący w zapytaniu nie jest dokładnie zgodny z warunkiem filtra indeksu, np.:

WHERE ([CarrierTrackingNumber] IS NOT NULL) – WARUNEK FILTRA INDEKSU



WHERE ([CarrierTrackingNumber] ='000A-434D-BC') – WARUNEK W ZAPYTANIU

W takiej sytuacji optymalizator nie mógłby skorzystać z indeksu filtrowanego i jedynym wyjściem jest zmiana definicji indeksu poprzez wstawienie kolumny [CarrierTrackingNumber] jako kolumny – klucza, np. w taki sposób:

CREATE NONCLUSTERED INDEX [idx_ncl_FICarrierTrackingNumber]

ON [Sales].[SalesOrderDetail]

(

  [SalesOrderID] ASC

  ,[CarrierTrackingNumber]

)

INCLUDE ( [ProductID],[UnitPrice],[UnitPriceDiscount])

WHERE ([CarrierTrackingNumber] IS NOT NULL)

WITH (DROP_EXISTING = ON)

ON [PRIMARY]

GO

Ostatnia rekomendacja mówi, iż kolumna tworząca warunek filtra powinna być kolumną kluczową bądź dołączaną w przypadku, gdy kolumna ta jest zwracana jako wynik wykonania zapytania. Dla przykładu w poniższym zapytaniu:

SELECT [SalesOrderID]

      ,[ProductID]

      ,[UnitPrice]

      ,[UnitPriceDiscount]

      ,[CarrierTrackingNumber]

FROM [Sales].[SalesOrderDetail]

WHERE [CarrierTrackingNumber] IS NOT NULL

optymalizator nie użyje indeksu filtrowanego, o ile kolumna [CarrierTrackingNumber] nie zostanie wstawiona na liste kolumn kluczowych lub dołączanych.

Na zakończenie pamiętajmy, iż kolumn – kluczy tworzących indeks zgrupowany nie musimy zawierać w indeksach niezgrupowanych, ponieważ i tak są one tam obecne niejawnie. Gdyby tak nie było, to silnik baz danych nie miałby np. możliwości przechodzenia pomiędzy indeksem niezgrupowanych a zgrupowanych (o czym wspominałem powyżej przy okazji operacji Key Lookup).

 Do początku strony

Indeks filtrowany – kiedy go użyć?

Wspomniałem na początku artykułu, iż indeks filtrowany najlepiej sprawdza się w przypadku, gdy zbudowany jest od dobrze zdefiniowany podzbiór danych, który to podzbiór jest potem otrzymywany w wyniku działania operacji SELECT. Indeksy te są tworzone z myślą o wykonywaniu zapytań pokrywających ten podzbiór danych. Indeksy filtrowane najlepiej sprawdzają się dla kolumna posiadających atrybut sparse (opisanych w artykule Co nowego w silniku bazodanowym SQL Server – February CTP), ponieważ wtedy warunek filtra zdefiniować można następująco:

CREATE NONCLUSTERED INDEX IndexSPARSE

ON TABELA(kolumna1, kolumna2, ..., kolumnaN)

WHERE ([Kolumna SPARSE] IS NOT NULL)

ON [GRUPA PLIKÓW]

GO

Innym zastosowanie dla indeksu filtrowanego będą takie podzbiory danych, które są heterogeniczne, tzn. zawierają pewne kategorie danych:

CREATE NONCLUSTERED INDEX IndexTYP

ON TABELA(kolumna1, kolumna2, ..., kolumnaN)

WHERE ([Kolumna Typ] = 'ROWER' OR [Kolumna Typ] = 'MOTOR')

ON [GRUPA PLIKÓW]

GO

Trzecim przypadkiem wartym rozważenia dla indeksu filtrowanego są kolumny zawierające zakresy danych, takie jak data czy czas:

CREATE NONCLUSTERED INDEX IndexZAKRES

ON TABELA(kolumna1, kolumna2, ..., kolumnaN)

WHERE ([Kolumna Data] BETWEEN '20080501' AND '20080531')

ON [GRUPA PLIKÓW]

GO

Indeksy filtrowane mogą być także tworzone na tablicach partycjonowanych, ale tylko w przypadku, gdy partycje są zdefiniowane poprzez proste porównanie wartości w kolumnach.

Kończąc część artykułu poświęconą indeksom chciałbym przypomnieć i zaakcentować fakt, iż indeks filtrowany zawierać niewielką ilość wierszy w stosunku do całej tablicy, bo wtedy naprawdę zostaną zauważone korzyści z jego użycia. Pamiętajmy także, iż indeks filtrowany nie jest widokiem i nie powinien być tak traktowany, ponieważ umożliwia tworzenie referencji tylko do jednej tablicy oraz w warunku filtrującym pozwala na wykorzystanie prostej logiki.

Druga część artykułu zostanie poświęcona filtrowanym statystykom, które są tworzone automatycznie w momencie utworzenia indeksu filtrowanego.

 Do początku strony

Filtrowane statystyki

Filtrowane statystyki są zoptymalizowanymi statystykami, które poprawiają dokładność statystyk dla zapytań skonstruowanych na dobrze zdefiniowanym podzbiorze danych. Filtrowane statystyki używają – podobnie jak filtrowane indeksy - predykatu do określenia podzbioru danych, który będzie zawarty w tej statystyce. Filtrowane statystyki mogą poprawić jakość planu zapytań w następujących sytuacjach:

• dla zapytań korzystających z indeksu filtrowanego – dla takiego indeksu aparat SQL Server 2008 automatycznie tworzy i zarządza odpowiednimi statystykami z właściwym predykatem. Tego typu statystyki są zdecydowanie bardziej dokładne od statystyk opartych na danych z całej tabeli
• dla zapytań na podzbiorze danych zawierających kolumny nieindeksowane – dla przypadku, gdy do określonej kolumny nie odwołuje się żaden indeks a kolumna jest wywoływana w zapytaniu, to silnik baz danych może utworzyć dla niej pełnotablicowe statystyki. Gdyby taka kolumna zawierała podzbiór danych o unikalnym rozkładzie to z powodzeniem, poprawiając wydajność wykonywania zapytań, można utworzyć na niej filtrowane statystyki
• dla zapytań pobierających dane ze skorelowanych kolumn – w przypadku, gdy zapytanie wybiera dane z nieindeksowanej kolumny, która jest skorelowana z wartościami w innej kolumnie a liczba odwołań do nieindeksowanych kolumn jest mała, to wydajność filtrowane indeksu nie będzie zadowalająca, z kolei filtrowane statystyki będą bardzo przydatne.

Wspomniałem już powyżej, iż system automatycznie tworzy statystyki dla indeksów – tak jak miało to miejsce do tej pory – w tym także dla indeksów filtrowanych. Dla indeksu wykorzystywanego w pierwszym przykładzie niniejszego artykułu SQL Server 2008 utworzył statystykę, jak pokazano poniżej:

CREATE STATISTICS idx_ncl_FICarrierTrackingNumber (Filtered)

ON Sales.SalesOrderDetail (SalesOrderID)

WHERE ([CarrierTrackingNumber] IS NOT NULL)

Tak jak to miało miejsce w przypadku indeksów, dla statystyk filtrowanych zmieniła się także okno własności, gdyż musiało pojawić się miejsce, w którym można by zdefiniować filtr:

Warto przy tej okazji nadmienić, iż każda statystka będzie automatycznie aktualizowana chyba, że administrator ręcznie tę możliwość wyłączy. Stwierdzenie to jest także prawdziwe dla statystyk – filtrowanych czy pełnotablicowych – tworzonych ręcznie.

Do dyspozycji administratora baz danych nadal pozostają dwa widoki katalogowe sys.stats oraz sys.stats_columns.

Widok sys.stats zawiera obecnie dodatkowe informacje, które pozwalają łatwo odnaleźć pośród wszystkich statystki tylko statystyki filtrowane. Odpowiada za to kolumna has_filter w widoku sys.stats.

Obydwa widoki można wykorzystać w codziennej pracy do wyświetlania np. informacji o autogenerowanych statystykach (zapytanie poniżej), w których nazwa rozpoczyna się od słów _WA.

SELECT OBJECT_NAME(s.object_id) AS [Obiekt]

,COL_NAME(sc.object_id, sc.column_id) AS [Kolumna]

,s.name AS [Statystyka]

FROM sys.stats AS s JOIN

sys.stats_columns AS sc

ON s.stats_id = sc.stats_id

WHERE s.name like '_WA%'

ORDER BY s.name;

GO

Nietrudno dostosować powyższe zapytanie do swoich potrzeb wyświetlić podobne informacje tylko dla statystyk filtrowanych, niezależnie od sposobu ich utworzenia:

SELECT OBJECT_NAME(s.object_id) AS [Obiekt]

,COL_NAME(sc.object_id, sc.column_id) AS [Kolumna]

,s.name AS [Statystyka]

,s.filter_definition as[Definicja filtra]

FROM sys.stats AS s JOIN

sys.stats_columns AS sc

ON s.stats_id = sc.stats_id

WHERE s.has_filter=1

ORDER BY s.name;

GO

W celach informacyjnych wspomnę, iż widok katalogowy sys.sql_expression_dependencies zawiera informację o każdej kolumnie zawartej w statystyce filtrowanej. Jest to o tyle istotne, gdyż nie można zmienić definicji kolumny w tabeli, czyli zmodyfikować zmienić jej nazwę lub usunąć, jeżeli kolumna jest częścią predykatu statystyki filtrowanej.

 Do początku strony

Podsumowanie

SQL Server 2008 proponuje administratorom i programistom baz danych możliwość skorzystania ze zoptymalizowanych indeksów niezgrupowanych oraz statystyk. Wykonane przeze mnie proste testy pokazują, iż rozważenie tych dwóch nowych funkcjonalności – razem czy w niektórych sytuacjach osobno – niesie za sobą realne korzyści objawiające się mniejszym kosztem zarządzania indeksem, lepszymi planami zapytań oraz oszczędnością miejsca zajmowanego przez indeks filtrowany. Z drugiej strony statystyki filtrowane są znacznie dokładniejsze od statystyk pełnotablicowych.

Pełnię możliwości odkryjemy jednak dopiero wtedy, gdy naprawdę dobrze przemyślimy i zaprojektujemy czy to filtrowany indeks czy statystykę.

---

Damian Widera, Project Manager & Team Lead (MCT, MCITP – DBA, MCSD.NET) Od 8 lat zajmuje się projektowaniem, tworzeniem i wdrażaniem aplikacji wykorzystujących platformę .NET, SQL Server oraz Oracle. Obecnie pracuje jako project manager dla LGBS Polska. Pracował także jako trener, programista, administrator baz danych, twórca domumentacji oraz analityk biznesowy. Aktywnie współpracuje z polskim oddziałem Microsoft publikując atykuły, webcasty oraz porady z zakresu SQL Server na stronach TechNet. Jest współautorem książki „Serwer SQL 2008. Administracja i programowanie”. Speaker na wielu konferencjach, m.in. Microsoft Heroes Happen Here, C2C, European PASS Conference, Microsoft Technology Summit, Energy Launch, TechED. Od 2004 r. posiada certyfikaty firmy Microsoft: MCT, MCITP–DBA oraz MCSD.NET. Jest współtwórcą oraz liderem jednej z najwiekszych grup pasjonatów SQL Server w Polsce – Śląskiej Regionalnej Grupy Microsoft (PLSSUG Katowice). Od listopada 2008 jest prezesem Polish SQL Server Users Group (PLSSUG) w Polsce. W styczniu 2009 nagrodzony tytułem MVP w kategorii SQL Server.

Do początku strony

---