Wydajność kwerend hurtowni danych

Autor: Sunil Agarwal, Torsten Grabs i Dr Joachim Hammer

Opublikowano: 5 września 2008

Zawartość strony

	Logiczny projekt bazy danych: Modelowanie wymiarowe
	Projekt fizyczny
	Tabela faktów
	Tabele wymiarów
	Optymalizacja kwerend ze złączeniami w schemacie gwiazdy
	Wybór planu w oparciu o selektywność
	Potok optymalizacji złączeń w schemacie gwiazdy
	Heurystyki złączeń w schemacie gwiazdy
	Wyniki wydajności złączeń w schemacie gwiazdy
	Funkcja Partitioned Table Parallelism
	Kompresja danych
	Indeksowane widoki dostosowane do partycjonowania
	Eskalacja blokad na poziomie partycji
	Zakończenie

Wersja SQL Server 2008 zaoferuje dużo więcej możliwości z zakresie obsługi relacyjnych hurtowni danych niż jej poprzedniczka. Jednak nadal mogą pojawiać się wątpliwości, jak wykorzystywać wszystkie nowe technologie do budowania wydajnych magazynów danych wspierających podejmowanie decyzji na podstawie miliardów wierszy.

Niektórzy czytelnicy mogą również zastanawiać się, które funkcje pomogą im w osiągnięciu najlepszej wydajności kwerend i raportów wspierających podejmowanie decyzji oraz jakiego typu ulepszeń wydajności można w rzeczywistości oczekiwać od nowej wersji SQL Server®.

W miarę zbliżania się do faktycznej publikacji pojawia się coraz więcej pytań. Mamy nadzieję, że ten wnikliwy wgląd w najważniejsze związane z wydajnością funkcje hurtowni danych w SQL Server 2008 pomoże czytelnikom w przygotowaniach.

Logiczny projekt bazy danych: Modelowanie wymiarowe

Transakcyjne aplikacje Line-of-business posiadają zazwyczaj znormalizowany schemat bazy danych. Logiczny projekt schematu bazy danych dla relacyjnych hurtowni danych (ang. relational data warehouse) kładzie mniejszy nacisk na normalizację. Obecnie wiele projektów relacyjnych hurtowni danych podąża za zaleceniami modelowania wymiarowego, które zostały spopularyzowane przez Ralpha Kimball oraz Margy Ross w ich książce „The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling”.

Czytelnicy, którzy poświęcają wiele czasu obsłudze hurtowni danych, prawdopodobnie znają już typowe wzorce schematu dla relacyjnych hurtowni danych (takie jak schematy gwiazdy lub płatka śniegu). Modelowanie wymiarowe wprowadza rozróżnienie między tabelami wymiarów, a tabelami faktów. Tabele wymiarów przechowują kluczowe dane (takie jak produkty, klienci, sklepy oraz kraje), podczas gdy tabela faktów przechowuje dane transakcyjne (takie jak sprzedaż, zamówienia, zakupy oraz zwroty).

Tabele wymiarów oraz tabele faktów są powiązane relacjami klucz główny (PK)/klucz obcy (FK). W rzeczywistości wiele hurtowni danych nie wymusza ograniczeń FK, aby w ten sposób utrzymać niskie wymagania dotyczące magazynowania. To pozwala zmniejszyć ciężar przechowywania zastosowanych indeksów i utrzymywać niskie koszty konserwacji tabeli faktów. Tabele wymiarów w hurtowni danych są zwykle dość małe i zazwyczaj zawierają tysiące wierszy (maksymalnie kilka milionów). Natomiast tabela faktów może być bardzo duża, zawierając setki milionów, a nawet miliardy wierszy. A zatem projekt logiczny musi tak naprawdę koncentrować się na dotyczących magazynowania wymaganiach tabeli faktów.

Czynnik rozmiaru ma również znaczenie podczas określania, jaki klucz należy wybrać z tabeli wymiarów do utrzymywania relacji tabela faktów/tabela wymiarów. Klucze złożone bazujące na kluczu biznesowym wymiaru, czyli na rzeczywistym identyfikatorze obiektu reprezentowanego przez dany wymiar, zazwyczaj obejmują kilka kolumn. Należy mieć świadomość, że problem ten dotyczy odpowiadających im kluczy obcych w tabeli faktów, ponieważ wielokolumnowy klucz złożony będzie powtarzany w każdym wierszu tabeli faktów.

W związku z tym często spotykaną praktyką jest wykorzystanie małych kluczy zastępczych w celu zaimplementowania relacji między tabelą faktów, a jej wymiarami. Klucz zastępczy stanowi kolumnę tożsamości typu integer, która służy jako sztuczny klucz główny tabeli wymiarów. Gdy tabela faktów odwołuje się do mniejszego klucza zastępczego, następuje znacząca redukcja wymagań dotyczących magazynowania dużych tabel faktów. Rysunek 1 ilustruje schemat hurtowni danych, która bazuje na modelu wymiarowym i wykorzystuje tabele wymiarów oraz faktów z kluczami zastępczymi.

Rysunek 1: Przykład schematu gwiazdy z tabelą faktów i dwoma tabelami wymiarów.

Schemat płatka śniegu rozmieszcza jeden lub więcej wymiarów na wielu poziomach (na przykład klient, kraj oraz region dla wymiaru klienta), w konsekwencji normalizując większe wymiary, które mogą ucierpieć w wyniku nadmiernej redundancji danych. Poziomy są reprezentowane przez osobne tabele, nadając schematowi formę płatka śniegu. Natomiast projekt o schemacie gwiazdy nie rozprzestrzenia swoich wymiarów po tabelach. Schemat gwiazdy ma postać gwiazdy, w której tabele wymiarów są zgrupowane wokół znajdującej się w środku tabeli faktów.

W przypadku wymiarowo zamodelowanych schematów gwiazdy lub płatka śniegu kwerendy wspierające podejmowanie decyzji postępują zgodnie z typowym wzorcem: kwerenda wybiera kilka interesujących miar z tabeli faktów, złącza wiersze faktów z jednym lub kilkoma wymiarami według kluczy zastępczych, umieszcza predykaty filtrów w kolumnach biznesowych tabel wymiarów, grupuje według jednego lub kilku kolumn biznesowych i agreguje miary pobrane z tabeli faktów przez dany okres czasu. Następująca kwerenda demonstruje ten wzorzec i jest czasem nazywana kwerendą ze złączeniem w schemacie gwiazdy:

select ProductAlternateKey,

CalendarYear,sum(SalesAmount)

from FactInternetSales Fact

     join DimTime 

on Fact.OrderDateKey = TimeKey

     join DimProduct 

on DimProduct.ProductKey =

   Fact.ProductKey

where CalendarYear between 2003 and 2004

      and ProductAlternateKey like 'BK%'

group by ProductAlternateKey,CalendarYear

 Do początku strony

Projekt fizyczny

Wiele kwerend SQL w relacyjnej hurtowni danych będzie posiadało strukturę analogiczną do kwerendy ze złączeniem w schemacie gwiazdy. Pomimo tego kwerendy wspierające podejmowanie decyzji zwykle zmieniają się w miarę upływu czasu, ponieważ osoby podejmujące decyzje stale starają się lepiej zrozumieć fundamentalne dane biznesowe na nowe sposoby. To dlatego obciążenia robocze w hurtowniach danych mają tendencję do posiadania wysokiego współczynnika kwerend ad hoc. To sprawia, że tworzenie projektu fizycznego kwerend wspierających podejmowanie decyzji oraz schematu hurtowni danych bazującej na modelu wymiarowym stanowi nie lada wyzwanie.

W SQL Server projektanci hurtowni danych zazwyczaj rozpoczynają od szkicowego lub fizycznego projektu, który z czasem dostosowują i udoskonalają w miarę rozwoju obciążeń roboczych. Można swobodnie dostosowywać i zmieniać ten projekt we własnych środowiskach hurtowni danych. Jednak czyniąc to, należy oczywiście pamiętać o najlepszych praktykach projektowania struktury bazy danych, takich jak np. wpływ konserwacji indeksu na wydajność w kontekście modyfikacji i wymagań dotyczących przechowywania indeksów.

 Do początku strony

Tabela faktów

Szkicowy projekt ma na celu przewidzenie typowego kształtu kwerendy o schemacie gwiazdy i zbudowanie indeksów na tabeli faktów. Indeks klastrowy tabeli faktów w roli kluczy indeksów wykorzystuje kilka kolumn kluczy zastępczych wymiaru (kolumn kluczy obcych). Na liście kluczy indeksów powinny pojawić się najczęściej wykorzystywane kolumny. Warto poświęcić chwilę czasu na sprawdzenie, czy faktycznie zapewniają one dobrą ścieżkę dostępu do najczęściej wykonywanych kwerend w obciążeniu roboczym.

Ponadto projekt szkicowy tworzy jednokolumnowy, nieklastrowy indeks dla każdej kolumny zastępczej wymiaru (klucza obcego) w tabeli faktów. To zapewnia ścieżkę dostępu o wysokiej dostępności dla kwerend, które są bardzo selektywne w jednym ze swoich wymiarów.

Celem indeksów klastrowych jest zapewnienie wysokiej wydajności większości kwerend w obciążeniu roboczym. Zestaw indeksów nieklastrowych jest dedykowany dla tych kwerend, które pobierają miary tabeli faktów dla określonego klienta lub produktu. Te indeksy nieklastrowe zapewniają, że nie trzeba na przykład skanować tabeli faktów w celu pobrania danych sprzedaży dla pojedynczego klienta.

 Do początku strony

Tabele wymiarów

Stosując projekt szkicowy w tabelach wymiarów, należy stworzyć indeksy dla każdej tabeli wymiarów. Między innymi nieklastrowy indeks z ograniczeniami klucza głównego na kolumnie klucza zastępczego wymiaru oraz indeks klastrowy na kolumnach klucza biznesowego encji wymiaru. W większych tabelach wymiarów należy również rozważyć dodanie nieklastrowych indeksów na kolumnach, które są często wykorzystywane w wysoce selektywnych predykatach.

Indeksy klastrowe ułatwiają efektywną realizację procesów Ekstrakcji, Transformacji i Ładowania (ETL) w ramach przerwy konserwacyjnej hurtowni danych, gdzie czas jest często bardzo istotny. Na przykład w sytuacji, gdy wymiary zmieniają się powoli, istniejące wiersze mogą być aktualizowane w miejscu, podczas gdy wiersze jeszcze nieobecne w wymiarze są dołączane do tabeli wymiarów. Ten wzorzec dostępu wymaga wydajnych operacji wyszukiwania i aktualizacji tabeli wymiarów w czasie ETL.

Zaprezentowany szkicowy projekt stanowi dobry fundament dla fizycznych struktur relacyjnych hurtowni danych zbudowanych przy użyciu SQL Server. Bazując na tej typowej konfiguracji relacyjnej hurtowni danych, możemy przeanalizować kluczowe, nowe funkcje SQL Server 2008.

 Do początku strony

Optymalizacja kwerend ze złączeniami w schemacie gwiazdy

Przetwarzanie tabeli faktów stanowi z reguły najbardziej kosztowną fazę wykonywania kwerendy ze złączeniem w schemacie gwiazdy w relacyjnej hurtowni danych bazującej na modelu wymiarowym. Łatwo to zauważyć, ponieważ nawet wysoko selektywne kwerendy pobierają o rząd więcej wierszy z tabeli faktów niż z jakiegokolwiek wymiaru. A zatem wykorzystanie najlepszej ścieżki dostępu do tabeli faktów jest niezbędne do uzyskania wysokiej wydajności kwerendy.

W SQL Server optymalizator kwerend automatycznie wybierze ścieżkę dostępu z najniższym oszacowanym kosztem z zestawu alternatywnych opcji. W kontekście hurtowni danych głównym celem jest zapewnienie, że optymalizator kwerend rozważy atrakcyjne alternatywne opcje ścieżek dostępu dla planu wykonania kwerendy ze złączeniem w schemacie gwiazdy. Optymalizator kwerend w SQL Server zawiera różne funkcje, które automatycznie oferują najbardziej wydajne plany wykonania kwerendy ze złączeniem w schemacie gwiazdy.

Kwerendy ze złączeniem w kształcie gwiazdy można podzielić na trzy różne klasy, co pokazano na Rysunku 2. Te szerokie klasy pomagają aparatowi SQL Server w identyfikowaniu opcji planów dla tych kwerend. Główną koncepcją, na której bazuje SQL Server, jest selektywność kwerend w odniesieniu do tabeli faktów. Kwerenda jest uważana za tym bardziej selektywną, im niższa jest liczba wykorzystywanych przez nią wierszy tabeli faktów. Wyrażona w procentach liczba wierszy pobieranych z tabeli faktów stanowi kryterium podziału na klasy. Procenty reprezentują wartości pochodzące z typowych wdrożeń u klienta i nie stanowią ścisłych granic służących do generowania definicji ścieżek dostępu.

Rysunek 2: Zakresy selektywności dla kwerend ze złączeniem w kształcie gwiazdy.

Pierwsza klasa obejmuje wysoko selektywne kwerendy, które przetwarzają do 10 procent wierszy w tabeli faktów. Druga kasa o średniej selektywności składa się z kwerend, które przetwarzają więcej niż 10 i mniej niż 75 procent wierszy tabeli faktów. Kwerendy w trzeciej klasie z niską selektywnością wymagają przetworzenia ponad 75 procent wierszy składowanych w tabeli faktów. Prostokąty przedstawione na rysunku ilustrują podstawowe opcje planów wykonania kwerend w każdej z klas selektywności.

 Do początku strony

Wybór planu w oparciu o selektywność

Ponieważ wysoko selektywne kwerendy o schemacie gwiazdy zazwyczaj pobierają nie więcej niż 10 procent wierszy tabeli faktów, kwerendy te mogą pozwolić sobie na losowy dostęp do tabeli faktów. W związku z tym plany kwerend dla tej klasy polegają w znacznym stopniu na złączeniach pętli zagnieżdżonej w połączeniu z operacjami Index Seek (na indeksie nieklastrowym) oraz Bookmark Lookup w tabeli faktów. Ponieważ przeprowadzają one losowe operacje we/wy na tabeli faktów, wydajność sekwencyjnych operacji we/wy jest wyższa, gdy pobierane są większe porcje tabeli faktów. To powoduje stosowanie innych planów kwerend, gdy liczba wierszy z tabeli faktów wykracza poza określoną wartość.

Ponieważ średnio selektywne kwerendy o schemacie gwiazdy przetwarzają znaczącą część wierszy w tabeli faktów, złączenia typu Hash Join wraz z operacją Scan lub Range Scan w tabeli faktów stanowią zazwyczaj preferowaną opcję ścieżki dostępu do tabeli faktów. SQL Server wykorzystuje filtry bitmapowe w celu podniesienia wydajności złączeń typu Hash Join.

Rysunek 3 ilustruje, w jaki sposób SQL Server wykorzystuje te filtry bitmapowe do poprawienia wydajności złączeń podczas wykonywania kwerendy ze złączeniem w schemacie gwiazdy. Rysunek prezentuje plan dla kwerendy wykorzystującej dwie tabele wymiarów Product oraz Time, które są złączane z tabelą faktów na podstawie kluczy zastępczych. Kwerenda wykorzystuje predykaty filtrów, takie jak klauzule WHERE, w obu tabelach wymiarów, w wyniku czego tylko jeden wiersz kwalifikuje się dla każdego z wymiarów. Wskazują na to małe czerwone tabele znajdujące się obok dwóch operatorów złączenia.

Rysunek 3: Plan kwerendy ze złączeniem w schemacie gwiazdy z przetwarzaniem redukcji złączeń.

Implementacją dla każdego złączenia jest złączenie typu hash join, które umożliwia serwerowi SQL Server wykorzystywanie informacji o kwalifikujących się wierszach z tabel wymiarów, inaczej informacji do redukcji złączenia dla obu tabel wymiarów. Zielone kształty na Rysunku reprezentują struktury danych dla informacji do redukcji złączenia. SQL Server wypełnia te struktury danych na podstawie odpowiednich tabel wymiarów, a następnie automatycznie przenosi je podczas wykonywania kwerendy do operatora, który przetwarza tabelę faktów np. skanuje tabelę. Ten operator wykorzystuje informacje o wierszach tabeli wymiarów do wyeliminowania wierszy tabeli faktów, które nie spełniają warunków złączenia z wymiarami.

SQL Server odsiewa wiersze tabeli faktów w bardzo wczesnej fazie przetwarzania kwerendy, po pobraniu wiersza z tabeli faktów. To pozwala na redukcję zużycia procesora i potencjalną redukcję dyskowych operacji we/wy, ponieważ odsiane wiersze nie muszą być przetwarzane przez kolejne operatory planu kwerendy. SQL Server wykorzystuje reprezentację bitmapy do efektywnego implementowania struktur danych uwzględniających informacje do redukcji złączeń w czasie wykonania kwerendy.

 Do początku strony

Potok optymalizacji złączeń w schemacie gwiazdy

W proces optymalizacji zaangażowane są standardowe heurystyki, które sprawiają, że mechanizm optymalizacji kwerend ze złączeniami generuje wstępny zestaw alternatywnych planów wykonania kwerendy. Następnie uruchamiane są specjalistyczne rozszerzenia, które generują dodatkowe alternatywne plany wykonania.

W przypadku hurtowni danych rozszerzenie wykrywa schematy gwiazd, schematy płatków ściegu i wzorce kwerend w kształcie gwiazdy oraz szacuje selektywność kwerendy w kontekście tabeli faktów. Jeśli schemat i kształt kwerendy odpowiadają wzorcom, SQL Server automatycznie dodaje kolejne plany kwerend, które są następnie obejmowane optymalizacją pod względem kosztu, w celu wybrania najbardziej obiecującego planu wykonania kwerendy.

Podczas wykonania kwerendy SQL Server monitoruje rzeczywistą selektywność redukcji złączeń w czasie uruchomienia. Gdy selektywność ulega zmianie, SQL Server dynamicznie dostosowuje struktury danych zwierające informacje o redukcji złączeń tak, aby najbardziej selektywne z nich zostały zastosowane na początku.

 Do początku strony

Heurystyki złączeń w schemacie gwiazdy

Wiele projektów fizycznych hurtowni danych wzoruje się na schemacie gwiazdy, ale nie określa całkowicie relacji między tabelą faktów a tabelami wymiarów, jak już wspominano na przykład dla ograniczeń kluczy obcych. Bez jasno określonych ograniczeń kluczy obcych SQL Server musi polegać na heurystykach w celu wykrycia wzorców kwerend o schemacie gwiazdy. Do wykrywania wzorców kwerend ze złączeniem w schemacie gwiazdy stosowane są następujące heurystyki:

  1.  Największa z tabel uczestniczących w n-arnym złączeniu jest uznawana za tabelę faktów. Istnieją dodatkowe ograniczenia dotyczące minimalnego rozmiaru tabeli faktów, na przykład jeśli nawet największa tabela nie przekracza określonego rozmiaru, złączenie n-arne nie jest traktowane jako złączenie w schemacie gwiazdy.

  2.  Wszystkie warunki złączenia w złączeniach binarnych w kwerendzie ze złączeniem w schemacie gwiazdy muszą stanowić jednokolumnowe predykaty równości. Złączenia muszą być złączeniami typu inner join. Choć warunki te mogą wydawać się restrykcyjne, są spełniane przez znakomitą większość złączeń między tabelą faktów a tabelami wymiarów według klucza zastępczego w typowym schemacie gwiazdy. Jeśli złączenie posiada bardziej złożony warunek złączenia, który nie odpowiada powyżej opisanemu wzorcowi, złączenie jest wykluczane ze złączenia w schemacie gwiazdy. Pięcioelementowe złączenie może na przykład prowadzić do trzyelementowego złączenia w schemacie gwiazdy (z dwoma dodatkowymi złączeniami w przyszłości), jeśli dwa złączenia posiadają bardziej złożone predykaty złączeń.

Należy podkreślić, że są to zasady heurystyczne. Istnieje kilka rzeczywistych sytuacji, które mogą doprowadzić heurystyki do wybrania tabeli wymiarów jako tabeli faktów. To wpływa na wybór planu, ale nie ma wpływu na prawidłowość wykonania wybranego planu. Złączenia binarne zaangażowane w złączenie w schemacie gwiazdy są następnie porządkowane według malejącej selektywności. Selektywność złączenia w tym kontekście jest definiowana jako stosunek liczności wejściowej tabeli faktów do liczności wyniku złączenia. Selektywność złączenia wskazuje, w jakim stopniu określony wymiar redukuje liczność tabeli faktów. Generalna zasada jest taka, że złączenia o wyższej selektywności powinny być rozpatrywane jako pierwsze.

Procesor kwerend w SQL Server automatycznie stosuje optymalizację na kwerendach zgodnych z wzorcem złączenia w schemacie gwiazdy i wspomnianymi powyżej warunkami, gdy koszt wykonania oszacowany dla wynikowych planów kwerend wydaje się atrakcyjny. A zatem nie musimy dokonywać żadnych zmian w aplikacji, aby mogła ona korzystać z tych znacznych ulepszeń wydajności. Jednak należy mieć świadomość, że niektóre optymalizacje złączeń w schemacie gwiazdy, takie jak redukcja złączeń, są dostępne jedynie w edycji SQL Server Enterprise.

 Do początku strony

Wyniki wydajności złączeń w schemacie gwiazdy

W ramach prac nad optymalizacją złączeń w schemacie gwiazdy w SQL Server 2008 przeprowadzonych zostało wiele analiz wydajności bazujących na danych testowych oraz rzeczywistych obciążeniach roboczych klientów. Warto spojrzeć na trzy wybrane obciążenia robocze.

Hurtownia danych w dziale sprzedaży firmy Microsoft To obciążenie robocze śledzi wydajność hurtowni danych, która jest wykorzystywana wewnętrznie do podejmowania decyzji w dziale sprzedaży w firmie Microsoft. Stworzono przykładową migawkę bazy danych o rozmiarze około 750GB (łącznie z indeksami). Kwerendy w tym obciążeniu roboczym stanowią wyzwanie dla mechanizmu przetwarzania kwerend, ponieważ wiele z nich zawiera ponad 10 złączeń.

Klient – sprzedawca detaliczny Te serie eksperymentów bazują na kliencie hurtowni danych zajmującym się sprzedażą detaliczną (posiadającym zarówno tradycyjny sklep, jak i działalność online). Hurtowania klienta charakteryzuje się schematem płatka śniegu bazującym na modelu wymiarowym oraz kanonicznymi kwerendami ze złączeniami w schemacie gwiazdy. W ramach eksperymentów migawka hurtowni została wypełniona 100GB "czystych" danych.

Obciążenie robocze wspierające podejmowanie decyzji Te serie eksperymentów służą do zbadania wydajności obciążenia roboczego podejmowania decyzji na podstawie 100GB bazy danych bazującej na modelu wymiarowym. Rysunek 4 ilustruje wyniki dla tych trzech obciążeń roboczych. Prezentuje znormalizowane geometryczne średnie czasów odpowiedzi dla wszystkich kwerend w obciążeniu roboczym. Ten pomiar stanowi dobry wskaźnik tego, jaka będzie oczekiwana wydajność kwerendy, gdy uruchamiana zostanie losowa kwerenda z obciążenia roboczego. Słupki na rysunku porównują bazową wydajność (1.0), gdy optymalizacje złączenia w schemacie gwiazdy nie są stosowane, kontra wydajność zoptymalizowanego złączenia w schemacie gwiazdy. Wszystkie one były uruchamiane w SQL Server 2008.

Rysunek 4: Ulepszenia wydajności w wyniku optymalizacji złączenia w schemacie gwiazdy.

Jak wskazuje rysunek, wszystkie obciążenia robocze uległy znacznej poprawie, która wynosiła od 12 do 30 procent. Chociaż poszczególne wartości mogą się różnić, specjaliści z firmy Microsoft spodziewają się, że obciążenia robocze wspierające podejmowanie decyzji w aparacie SQL Server ulegną około 15–20 procentowej poprawie w wyniku zastosowania optymalizacji specyficznych dla złączenia w schemacie gwiazdy, które stanowią nową funkcję SQL Server 2008.

 Do początku strony

Funkcja Partitioned Table Parallelism

Aby przyspieszyć przetwarzanie kwerend w dużych hurtowniach danych, administratorzy baz danych często dzielą duże tabele faktów na partycje według daty. To powoduje umieszczenie danych w różnych grupach plików, tym samym redukując ilość danych, które muszą być przeszukiwane podczas przetwarzania wierszy w określonym zakresie danych. Dodatkowo takie podejście pozwala korzystać ze współbieżnej wydajności systemu dyskowego, gdy grupy plików rozmieszczone są na wielu dyskach fizycznych.

W SQL Server 2005 wprowadzono możliwość partycjonowania dużych relacji na mniejsze logiczne porcje w celu usprawnienia administracji i zarządzania dużymi tabelami. Mechanizm ten był również z sukcesem wykorzystywany do udoskonalania procesu przetwarzania kwerend, w szczególności w przypadku dużych aplikacji wspierających podejmowanie decyzji.

Niestety niektórzy klienci wykorzystujący wersję SQL Server 2005 doświadczyli problemów z wydajnością związanych z kwerendami wykonywanymi na partycjonowanych tabelach — w szczególności, gdy były one uruchamiane na wieloprocesorowych systemach współbieżnych z współdzielona pamięcią. W przypadku przetwarzania równoległych kwerend na partycjonowanych tabelach w SQL Server 2005 mogą zdarzać się sytuację, gdy do wykonywania kwerendy przydzielony zostanie jedynie podzbiór dostępnych wątków.

Wyobraźmy sobie 64-rdzeniową maszynę, na której kwerendy mogłyby wykorzystywać równolegle do 64 wątków oraz kwerendę, która obejmuje dwie partycje. W wersji SQL Server 2005 otrzymałaby ona jedynie 2 z 64 wątków i dlatego mogłaby użyć jedynie 2/64 (3.1 procent) mocy procesora na tej maszynie. Według doniesień dla niektórych kwerend wydajność w przypadku zastosowania partycji może być 10 (lub więcej) razy gorsza niż w przypadku, gdy ta sama kwerenda jest uruchomiona na tej samej maszynie, ale z wykorzystaniem niezawierającej partycji wersji tej samej tabeli faktów.

Należy zaznaczyć, że wersja SQL Server 2005 została specjalnie zoptymalizowana z myślą o kwerendach obejmujących pojedynczą partycję. W tym przypadku procesor kwerend przypisze wszystkie dostępne wątki do wykonania operacji skanowania. Ta specjalna optymalizacja zaowocowała znaczącym wzrostem wydajności kwerend, które obejmują jedną partycję i są wykonywane na wielordzeniowych maszynach. Naturalnie klienci oczekiwali, że mechanizm ten będzie działał również w przypadku kwerend obejmujących wiele partycji.

Wprowadzenie nowej funkcji Partitioned Table Parallelism (PTP) w wersji SQL Server 2008 poprawiło wydajność kwerend w przypadku zastosowania partycji poprzez lepsze wykorzystanie mocy przetwarzania istniejącego sprzętu, niezależnie od liczby partycji objętych przez kwerendę oraz względnego rozmiaru poszczególnych partycji. W typowym scenariuszu z wykorzystaniem hurtowni danych zawierającej podzieloną na partycje tabelę faktów, użytkownicy mogą zauważyć znaczącą poprawę w przypadku kwerend wykonywanych w planach równoległych, w szczególności jeśli liczba dostępnych rdzeni procesora jest większa niż liczba partycji objętych przez kwerendę. Ponadto nowa funkcja działa automatycznie, nie trzeba jej dostosowywać ani konfigurować.

Powiedzmy, że mamy tabelę faktów reprezentującą dane sprzedaży podzielone według daty na cztery partycje. Diagram na Rysunku 5 stanowi pomocną wizualizację tego przykładu. Warto zauważyć, że zamiast pojedynczego indeksu klastrowego dla całego zakresu dat (jak w przypadku braku partycji), istnieje zazwyczaj klastrowy indeks na kolumnie daty dla każdej partycji w tabeli faktów. A teraz załóżmy, że kwerenda Q podsumowuje sprzedaż z ostatnich siedmiu dni. Ponieważ nowe dane sprzedaży są stale wstawiane do tabeli faktów za pośrednictwem ostatniej partycji (z etykietą P4), kwerenda obejmie prawdopodobnie różne partycje w zależności od tego, kiedy zostanie wykonana. Mechanizm ten został zilustrowany w pierwszym wierszu diagramu, w którym kwerenda Q1 obejmuje tylko jedną partycję, podczas gdy kwerenda Q2 obejmuje dwie partycje, ponieważ odpowiedni zakres danych w czasie wykonania rozciąga się między partycjami P3 oraz P4.

Rysunek 5: Nowa funkcja PTP w działaniu.

A teraz załóżmy, że dostępnych jest osiem wątków. Wykonanie kwerend Q1 oraz Q2 na serwerze SQL Server 2005 może prowadzić do pewnych nieoczekiwanych zachowań. SQL Server 2005 stosuje optymalizację, w której jeśli optymalizator w czasie kompilacji uzna, że kwerenda obejmie tylko jedną partycję, partycja ta będzie traktowana jak pojedyncza tabela bez partycji i wygenerowany zostanie plan, który uzyskuje dostęp do tej tabeli przy użyciu wszystkich dostępnych wątków.

Efekt będzie taki, że kwerenda Q1 obejmująca pojedynczą partycję (P3) zostanie powiązana z planem, który jest przetwarzany przez osiem wątków (co nie zostało pokazane). W przypadku kwerendy Q2, która obejmuje dwie partycje, w czasie wykonania do każdej partycji przypisany zostanie pojedynczy wątek, nawet na wykorzystywanej maszynie dostępne są dodatkowe wątki. A zatem kwerenda Q2 wykorzysta jedynie mały ułamek dostępnej mocy procesora i prawdopodobnie zostanie wykonana dużo wolniej niż kwerenda Q1.

Wykonanie kwerend Q1 oraz Q2 na serwerze SQL Server 2008 zaowocuje lepszym wykorzystaniem dostępnego sprzętu, wyższą wydajnością oraz bardziej przewidywalnym zachowaniem. W przypadku kwerendy Q1 w czasie wykonania wszystkie osiem wątków zostanie przypisanych do przetwarzania danych w partycji P2 (co nie zostało pokazane). Natomiast kwerenda Q2 zaowocuje równoległym planem, w którym wszystkie dostępne wątki będą przypisywane do partycji P3 oraz P4 w sposób cykliczny, generując efekt zaprezentowany w dolnym wierszu diagramu, w którym każda z dwóch partycji otrzymuje po cztery wątki. Moc procesora jest w pełni wykorzystywana, a wydajność kwerend Q1 oraz Q2 jest porównywalna.

Dzięki tej cyklicznej alokacji wątków, kwerendy mają tym lepszą wydajność, im większy jest stosunek rdzeni procesora do liczby partycji, do których kwerendy te uzyskują dostęp. Niestety zdarzają się sytuacje, w których przydzielanie wątków do partycji nie jest tak proste jak w tym przykładzie.

Wzrost wydajności między wersją SQL Server 2005 a SQL Server 2008 w scenariuszu z wykorzystaniem partycjonowanej tabeli na maszynie z wielordzeniowym procesorem został zobrazowany na Rysunku 6. Zaprezentowany wykres ilustruje wydajność operacji skanowania dla partycjonowanych tabeli. W tym określonym teście, który został przeprowadzony w systemie z 64 rdzeniami oraz 256GB pamięci RAM, podzieliliśmy pojedynczą tabelę o rozmiarze 121GB na 11 partycji o rozmiarze 11GB. Do zestawu testów pokazanych na tym rysunku użyliśmy plików zorganizowanych na stercie, rozpoczynając zarówno od "zimnego", jak i od "ciepłego" bufora. Wszystkie kwerendy przeprowadzają proste operacje skanowania danych.

Rysunek 6: Wydajność operacji skanowania w SQL Server z włączoną nową funkcją PTP.

Oś y pokazuje czas odpowiedzi (sec), a oś x stopień równoległości (Degree of Parallelism - DOP), który odpowiada liczbie wątków przypisanych do kwerendy. Jak można zauważyć, zarówno w przypadku zimnego, jak i ciepłego bufora, czasy odpowiedzi stale maleją, dopóki DOP nie osiągnie poziomu 22. W tym momencie przy zastosowaniu zimnego bufora system we/wy zaczyna się przepełniać. Wynika to z faktu, iż kwerenda wykorzystana w tym przykładzie jest związana z we/wy. Dla obciążeń roboczych związanych bardziej z procesorem ograniczenie to może nie istnieć lub może pojawić się jedynie przy wyższych wskaźnikach DOP.

Jednak krzywa reprezentująca uruchamianie z wykorzystaniem ciepłego bufora nadal pokazuje skracanie się czasu odpowiedzi, gdy poziomy DOP wzrastają. W SQL Server 2005 obie krzywe zaczęłyby wyrównywać się na poziomie DOP 11, ponieważ w przypadku przetwarzania wielu partycji liczba wątków przypisywanych każdej z nich jest ograniczona do jednego.

Należy podkreślić, że w praktyce zysk polegający na skróceniu czasu odpowiedzi w wyniku zwiększenia liczby DOP nie jest linią ciągłą. Oczekiwany rezultat bardziej przypomina wynik funkcji skokowej, co odzwierciedla rzeczywistość, w które kwerenda zasadniczo oczekuje na najwolniejszą z części. A zatem na przykład zwykłe dodanie jednego dodatkowego wątku do operacji skanowania nie spowoduje poprawy czasu ukończenia wykonywania kwerendy, o ile wszystkie pozostałe operacje skanowania nie otrzymają dodatkowych wątków, które pozwolą im również szybciej zakończyć pracę.

Przeprowadziliśmy dodatkowe eksperymenty w celu przetestowania nowego mechanizmu PTP w różnych konfiguracjach sprzętu i plików. W trakcie tych testów zaobserwowaliśmy podobne efekty w zakresie wzrostu wydajności w miarę zwiększania wskaźnika DOP ponad jeden wątek/partycja.

Na zakończenie warto nadmienić, iż nowa funkcja PTP w SQL Server 2008 poprawia również czytelność planów kwerend i pozwala na lepszy wgląd w proces realizowania pewnych obciążeń roboczych. Na przykład w ramach funkcji PTP poprawiony został sposób reprezentowania równoległych i sekwencyjnych planów w wyniku Showplan XML. A także wzbogacone zostały informacje o partycjach, które są dostarczane zarówno w planie czasu kompilacji, jak i w planie czasu wykonania.

 Do początku strony

Kompresja danych

W miarę wzrostu popularności analizy biznesowej firmy wprowadzają do swoich hurtowni danych coraz więcej i więcej danych podawanych analizie. Wynikiem jest ekspotencjalny wzrost rozmiaru zarządzanych danych. W 1995 roku pierwsze badanie rozmiaru baz danych przeprowadzone przez firmę Winter Corporation wykazało, że największy system na świecie zawierał terabajt danych. Dziesięć lat później największa baza danych jest około 100 razy większa. Jeszcze bardziej zdumiewający jest fakt, iż rozmiar hurtowni danych potraja się w ciągu każdych dwóch lat. To rodzi nowe wyzwania w zakresie zarządzania tak ogromną ilością danych oraz zapewniania akceptowalnego poziomu wydajności kwerend hurtowni danych. Kwerendy te są zazwyczaj złożone, zawierają wiele złączeń i agregacji oraz uzyskują dostęp do dużej ilości danych. Duża część kwerend w obciążeniu roboczym jest również związana z operacjami we/wy.

Wewnętrzna kompresja danych ma za zadanie pomóc w radzeniu sobie z tym problemem. Z myślą o danych liczbowych wprowadzono w wersji SQL Server 2005 SP2 nowy format magazynowania danych o zmiennej długości, czyli format vardecimal. Ten nowy format magazynowania powala znacznie zredukować rozmiar bazy danych. A oszczędność miejsca pozwala z kolei poprawić wydajność kwerend związanych z we/wy na dwa sposoby. Po pierwsze, konieczne jest odczytanie mniejszej liczby stron, a po drugie, ponieważ dane są przechowywane w postaci skompresowanej w puli buforów, oczekiwany czas życia strony jest dłuższy (innymi słowy, istnieje większa szansa, iż żądana strona znajduje się w buforze). Oczywiście oszczędność miejsca osiągnięta poprzez kompresję danych pociąga za sobą dodatkowe obciążenie procesora ze względu na proces kompresji i dekompresji danych.

SQL Server 2008 rozwija format magazynowania vardecimal, oferując dwa rodzaje kompresji: kompresję ROW oraz kompresję PAGE. Kompresja ROW rozszerza format magazynowania vardecimal poprzez przechowywanie wszystkich typów danych o stałej szerokości w formacie magazynowania o zmiennej długości.

Przykładowymi typami danych o stałej długości są integer, char oraz float. Pomimo iż SQL Server przechowuje te typy danych w formacie o zmiennej długości, semantyka tych typów danych pozostanie niezmieniona (z punktu widzenia aplikacji typ danych nadal stanowi typ o stałej długości). A to oznacza, że możliwe jest czerpanie korzyści z kompresji danych bez konieczności dokonywania jakichkolwiek zmian w aplikacji.

Kompresja PAGE minimalizuje redundancję danych w kolumnach w co najmniej jednym wierszu na danej stronie. Wykorzystuje specyficzną implementację algorytmu LZ78 (Lempel-Ziv), przechowując redundantne dane jedynie raz na stronie, a następnie odwołując się do nich w pozostałych kolumnach. Warto zauważyć, że gdy stosowana jest kompresja PAGE, obejmuje ona w rzeczywistości kompresję ROW.

Kompresja ROW oraz PAGE może zostać uaktywniona dla tabeli lub indeksu lub dla przynajmniej jednej partycji w partycjonowanych tabelach oraz indeksach. To stwarza pełną elastyczność w zakresie wyboru tabel, indeksów oraz partycji poddawanych kompresji i pozwala na określenie odpowiedniego balansu między oszczędnością miejsca a wpływem na procesor.Rysunek 7 ilustruje ten mechanizm przy pomocy tabeli sprzedaży, która została na różne sposoby podzielona na partycje z indeksami dopasowanymi do partycjonowania.

Rysunek 7: Partycjonowana tabela z różnymi ustawieniami kompresji.

Każda partycja reprezentuje kwartał, przy czym Oct-Dec stanowi ostatni kwartał. Przypuśćmy, że pierwsze dwie partycje nie są często używane, trzecia partycja jest średnio aktywna, a ostatnia partycja jest najbardziej aktywna. W tym przypadku jedną z możliwych konfiguracji, która pozwala uzyskać maksymalną oszczędność miejsca z minimalnym negatywnym wpływem na wydajność obciążenia roboczego, jest włączenie kompresji PAGE w dwóch pierwszych partycjach, kompresji ROW w trzeciej partycji oraz niestosowanie kompresji w ostatniej partycji.

Można uaktywnić kompresję zarówno online, jak i offline przy użyciu instrukcji DDL Alter Table lub Alter Index. SQL Server oferuje również procedurę składowaną służącą do oszacowania oszczędności miejsca. Osiągana oszczędność miejsca zależy od rozmieszczenia danych oraz schematu obiektu poddawanego kompresji.

W oparciu o wyniki testów przeprowadzanych na wielu klienckich bazach danych wydaje się, że większość klientów będzie mogła zredukować rozmiar baz danych o 50–65 procent i znacznie poprawić wydajność kwerend związanych z we/wy. Jednak oszacowanie wpływu kompresji na wydajność kwerend związanych z procesorem jest trudniejsze i zależy od złożoności kwerend. W SQL Server koszt dekompresji ponoszony jest tylko podczas uzyskiwania dostępu do indeksów lub tabel. Jeśli relatywne obciążenie procesora wynikające z operacji skanowania jest niskie w porównaniu z ogólnym obciążeniem procesora wynikającym z wykonania kwerendy, co ma zazwyczaj miejsce w przypadku hurtowni danych, wpływ na zużycie procesora powinien być mniejszy niż 20-30 procent.

 Do początku strony

Indeksowane widoki dostosowane do partycjonowania

W SQL Server 2008 indeksowane widoki dostosowane do partycjonowania pozwalają na bardziej efektywne tworzenie i zarządzanie agregatami podsumowującymi w relacyjnej hurtowni danych i umożliwiają posługiwanie się nimi w sytuacjach, w których kiedyś nie mogły być one efektywnie stosowane. To pozwala poprawić wydajność kwerend. W typowym scenariuszu istnieje tabela faktów, która jest podzielona na partycje według daty. Na tej tabeli definiowane są indeksowane widoki (lub agregaty podsumowujące) służące do skrócenia czasu wykonywania kwerend. Gdy włączamy nową partycję tabeli, odpowiadająca jej partycja jest również automatycznie włączana w dostosowanych do partycjonowania indeksowanych widokach, które są zdefiniowane na partycjonowanej tabeli.

Jest to znaczące ulepszenie w porównaniu z wersją SQL Server 2005, w której przed wykorzystaniem operacji ALTER TABLE SWITCH do włączenia lub wyłączenia partycji, trzeba było usunąć wszystkie indeksowane widoki zdefiniowane na partycjonowanej tabeli. Dzięki funkcji indeksowanych widoków dostosowanych do partycjonowania w SQL Server 2008, można czerpać korzyści z indeksowanych widoków obejmujących duże partycjonowane tabele, unikając jednocześnie kosztu przebudowywania agregatów w całej partycjonowanej tabeli. Korzyści te obejmują również automatyczną konserwację agregacji i dopasowywanie indeksowanych widoków.

 Do początku strony

Eskalacja blokad na poziomie partycji

SQL Server wspiera partycjonowanie zakresowe, które pozwala na partycjonowanie danych na potrzeby zarządzania lub na grupowanie danych zgodnie z wzorcem ich wykorzystania. A zatem można na przykład podzielić dane sprzedaży na partycje według zakresów miesięcznych lub kwartalnych. Można mapować partycję do jej własnej grupy plikowej, a grupę plikową można z kolei mapować do zbioru plików. To przynosi dwie kluczowe korzyści. Po pierwsze można tworzyć i przywracać kopie zapasowe partycji w postaci niezależnych jednostek. Po drugie można mapować grupę plikową do wolnego lub szybkiego podsystemu we/wy w zależności od wzorca wykorzystania lub obciążenia kwerendy.

Interesującym aspektem jest wzorzec dostępu do danych. Kwerendy oraz operacje DML mogą potrzebować dostępu lub manipulować jedynie podzbiorem partycji. A zatem w ramach na przykład analizowania danych sprzedaży z roku 2004, potrzebny jest dostęp jedynie do odpowiednich partycji i w idealnej sytuacji kwerendy, które w tym samym czasie uzyskują dostęp do danych w innych partycjach, nie powinny mieć żadnego wpływu na ten proces (poza dostępnością zasobów systemowych). W SQL Server 2005 równoczesny dostęp do danych w innych partycjach może prowadzić do blokady na poziomie tabeli, która może wpływać na dostęp do innych partycji.

W celu zminimalizowania tych konfliktów w wersji SQL Server 2008 wprowadzono (na poziomie tabeli) opcję kontrolowania eskalacji blokad do poziomu partycji lub tabeli. Domyślnie włączona jest eskalacja blokady do poziomu tabeli, jak w przypadku SQL Server 2005. Jednak można nadpisać zasadę eskalacji blokady dla tabeli, przykład ustawiając eskalację blokady w sposób następujący:

Alter table <mojatabela> set (LOCK_ESCALATION = AUTO)

Polecenie to instruuje SQL Server, aby wybrał on szczegółowość eskalacji blokady, która jest odpowiednia dla schematu tabeli. Jeśli tabela nie jest partycjonowana, blokada zostanie eskalowana do poziomu TABLE. Jeśli tabela jest partycjonowana, blokada zostanie eskalowana do poziomu szczegółowości partycji. Opcja ta jest również wykorzystywana przez SQL Server jako wskazówka w celu zniesienia faworyzacji blokad na poziomie tabeli.

 Do początku strony

Zakończenie

Był to jedynie krótki przegląd ulepszonych funkcji, które można znaleźć w SQL Server 2008 i które pomagają w osiągnięciu lepszej wydajności kwerend, które wspierają podejmowanie decyzji w relacyjnych hurtowniach danych. Jednak należy pamiętać, że choć bardzo ważne jest uzyskanie krótkich czasów odpowiedzi dla kwerend wspierających podejmowanie decyzji, istnieją także inne kluczowe wymagania, które wykraczają poza zakres niniejszego artykułu.

Dodatkowe funkcjonalności związane z relacyjnymi hurtowniami danych to między innymi:

  •  Wsparcie dla składni MERGE w języku T-SQL służącej do modyfikacji, usuwania lub wstawiania danych (wymiaru) przy użyciu jednej instrukcji oraz jednego odwołania do bazy danych.

  •  Zoptymalizowane rejestrowanie aparatu SQL Server w celu poprawy wydajności operacji ETL.

  •  Grupowanie zestawów w celu ułatwienia tworzenia agregujących dane kwerend T-SQL wspierających podejmowanie decyzji.

  •  Kompresja kopii zapasowych służąca do zredukowania wymagań we/wy zarówno dla pełnych, jak i przyrostowych kopii zapasowych.

  •  Zarządzanie zasobami służące do kontrolowania alokacji zasobów systemowych na potrzeby różnych obciążeń roboczych.

Autorzy niniejszego artykułu zachęcają czytelników do zapoznania się z dodatkowymi informacjami na temat wszystkich tych fascynujących funkcji, które są dostępne w witrynie SQL Server o adresie microsoft.com/sql.

Autorzy chcieliby także podziękować Borisowi Baryshnikov, Premowi Mehra, Peterowi Zabback oraz Shinowi Zhang za merytoryczną współpracę.

O autorach

Sunil Agarwal pełni stanowisko Senior Program Manager w grupie SQL Server Storage Engine w firmie Microsoft. Jest odpowiedzialny za współbieżność, indeksy, bazę danych tempdb, typ LOB, możliwości wsparcia oraz masowy import/eksport.

Torsten Grabs pełni stanowisko Senior Program Manager Lead do spraw Core Storage Engine w zespole Microsoft SQL Server. Posiada tytuł PhD w zakresie systemów bazodanowych oraz 10-letnie doświadczenie w pracy z SQL Server.

Dr Joachim Hammer pełni stanowisko Program Manager w grupie Query Processing w firmie Microsoft. Specjalizuje się w optymalizacji kwerend w dużych aplikacjach hurtowni danych oraz obsłudze kwerend rozproszonych, ETL oraz integracji informacji.

Do początku strony

---