SQL Pytania i Odpowiedzi: Spójność bazy danych, tabele tymczasowe i inne problemy

To dobrze, że polecenie DBCC CHECKDB jest uruchamiane jako część wykonywanych w nocy zadań administracyjnych oraz że włączone zostały sumy kontrolne stron. Opisane zachowanie może być bardo niepokojące i wygląda to prawie jak nieprawidłowe działanie polecenia DBCC CHECKDB. Sytuacja ta powtarza się jednak bardzo często - w trakcie używania bazy danych nie ma żadnych oznak uszkodzeń, w nocy polecenie DBCC CHECKDB znajduje uszkodzenia, a po powtórnym uruchomieniu w kilka godzin później polecenia DBCC CHECKDB uszkodzenia znikają. Przeanalizujmy więc całą sytuację krok po kroku.

Po pierwsze, raportowanie przez program DBCC CHECKDB uszkodzeń, które nie występują podczas zwykłego użytkowania bazy danych, jest dość powszechnym problemem. Wprawdzie sumy kontrolne stron są doskonałym sposobem wykrywania uszkodzeń powodowanych przez podsystem we/wy, ale są one efektywne tylko wówczas, gdy serwer SQL Server odczyta stronę danych już po wystąpieniu uszkodzenia.

Wyobraźmy sobie, że dla strony danych włączona została suma kontrolna, a następnie, że strona ta została uszkodzona przez podsystem we/wy. Pomimo tego, że uszkodzenie to zostanie wykryte dzięki zastosowaniu sumy kontrolnej, nastąpi to dopiero wtedy, kiedy uszkodzona strona zostanie wczytana do pamięci przez serwer SQL Server, który sprawdzi wartość sumy kontrolnej i w ten sposób wykryje uszkodzenie. Jeśli uszkodzona strona nie zostanie nigdy odczytana przez serwer SQL Server, to jej uszkodzenie nigdy nie zostanie wykryte. To właśnie dlatego tak ważne jest włączenie sum kontrolnych i regularne sprawdzenie spójności bazy danych. Proces sprawdzania spójności odczytuje bowiem wszystkie strony bazy danych, sprawdzając ich sumy kontrolne i odkrywając uszkodzenia tak wcześnie, jak to tylko możliwe.

W opisanym przypadku wygląda to tak, jak gdyby uszkodzenia miały miejsce na stronach danych, które nie są odczytywane w trakcie normalnego użytkowania bazy danych i dlatego pozostają one niewykryte aż do chwili odczytania uszkodzonych stron przez polecenie DBCC CHECKDB. Mogłoby się wydawać, że funkcja sum kontrolnych stron nie wykrywa uszkodzeń, tak jak powinna, ale tak nie jest.

Po drugie, uszkodzenia bazy danych mogą całkiem łatwo „znikać” pomiędzy dwoma kolejnymi uruchomieniami polecenia DBCC CHECKDB, ale tylko wtedy, kiedy baza danych była modyfikowana pomiędzy kolejnymi uruchomieniami polecenia DBCC. Załóżmy, że strona danych jest naprawdę uszkodzona i że uszkodzenie to zostało wykryte przez program DBCC CHECKDB . Załóżmy również, że strona ta została następnie dealokowana z tabeli (np. dlatego, że stała się pusta). Podczas kolejnego uruchomienia polecenia DBCC CHECKDB strona ta nie będzie już odczytywana, a więc nie zostanie wykryta jako uszkodzona. Polecenie DBCC CHECKDB odczytuje tylko alokowane strony (tzn. strony, które są aktualnie używane). Domyślam się, że w tym przypadku jednym z pozostałych, uruchamianych w nocy zadań administracyjnych jest przebudowa lub reorganizacja indeksu, a oba te zadania mogą mieć drastyczny wpływ na zbiór stron alokowanych przez konkretny obiekt lub indeks. To może wyjaśniać opisane przez Czytelnika zachowanie. Uszkodzone strony były dealokowane jako uboczny efekt operacji przebudowy indeksu i dlatego kolejne wykonanie polecenia DBCC CHECKDB nie zgłaszało żadnych błędów.

Aby ostatecznie wykryć uszkodzone strony, należy zmodyfikować wykonywane w nocy zadania administracyjne tak, by kończyły się one po nieudanym uruchomieniu polecenia DBCC CHECKDB. Pozwoli to na ręczną weryfikację uszkodzeń i podjęcie odpowiednich, dalszych działań.

Z zadanego pytania wnioskuję, że w Państwa bazie danych została zaimplementowana architektura bazy danych tempdb typu jeden-plik-na-każdy-procesor, która jest zwykle wymagana, jeśli w ramach wielu połączeń do bazy danych tworzonych jest wiele krótkoterminowych tabel tymczasowych. Być może konieczne było nawet włączenie flagi śledzenia numer 1118 (więcej informacji na ten temat znajduje się w artykule „Concurrency enhancements for the tempdb database" (Usprawnienia równoczesnego dostępu do bazy danych tempdb)).

Wprawdzie w wersji SQL Server 2005 i następnych istnieje potencjalna możliwość bardziej intensywnego korzystania z bazy tempdb, ale nie powinno to mieć miejsca, jeśli nie będzie się korzystać z funkcji, których działanie jest uzależnione od bazy danych tempdb. Należy podkreślić, że jeśli weźmiemy rozwiązanie, które w wersji SQL Server 2000 doświadczało już problemów wydajnościowych związanych z bazą danych tempdb, to po przeprowadzeniu na tej samej platformie sprzętowej aktualizacji do wersji SQL Server 2008, dzięki pewnym zmianom wprowadzonym w tej wersji do silnika magazynowania danych i dotyczącym bazy danych tempdb, w większości przypadków powinniśmy zaobserwować nawet pewne złagodzenie tych problemów.

Nowe funkcjonalności, które są dostępne w wersji SQL Server 2005 i SQL Server 2008 i które intensywnie korzystają z bazy danych tempdb to:

Wszystkie cztery z wymienionych funkcjonalności wykorzystują nową technologię nazywaną wersjonowaniem, używając jej do zapisywania wersji rekordów danych istniejących w różnych chwilach. Mówiąc wprost, te różne wersje rekordów zapisywane są w magazynie wersji, znajdującym się właśnie w bazie danych tempdb, a wszyscy użytkownicy bazy danych korzystają z tego samego magazynu wersji, zapisanego w pojedynczej bazie danych tempdb. Im więcej będziemy korzystać z tych nowych funkcjonalności, tym intensywniej używany będzie magazyn wersji, a w konsekwencji także baza danych tempdb, a więc zwiększy się potencjalna możliwość negatywnego wpływu tych funkcji na wydajność.

Kluczem do pomyślnego przeprowadzenia każdej aktualizacji jest odtworzenie, w używającym nowego schematu systemie testowym, obciążenia reprezentatywnego dla systemu produkcyjnego i sprawdzenie wydajności systemu testowego, co pozwoli na uniknięcie niespodzianek, z jakimi moglibyśmy się spotkać przeprowadzając aktualizację od razu w systemie produkcyjnym.

Niestety, szczegółowe omówienie tego zagadnienia wykraczałoby znacznie poza ramy tego działu, ale w sieci dostępnych jest wiele doskonałych zasobów, które polecam Czytelnikom do samodzielnego przestudiowania:

Współczynnik wypełniania rzeczywiście pozwala na częściową eliminację potrzeby wykonywania zadań administracyjnych związanych z utrzymywaniem indeksu, ale bardzo rzadko pozwala on na całkowite wyeliminowanie takiej potrzeby. Mówiąc krótko, skonfigurowanie współczynnika wypełniania stanowi instrukcję dla silnika magazynowania, aby podczas tworzenia lub przebudowy indeksów grupujących i niegrupujących pozostawiał na każdej stronie pewien procent wolnego miejsca (należy pamiętać, że skonfigurowany współczynnik wypełniania nie jest zachowywany podczas zwykłych operacji wstawiania, aktualizowania lub usuwania danych). Np. użycie współczynnika wypełniania o wartości 90 spowoduje pozostawienie 10% wolnego miejsca. Wartości współczynnika wypełniania 0 i 100 powodują niepozostawianie żadnego wolnego miejsca (co jest źródłem wielu nieporozumień).

Idea stosowania współczynnika wypełniania polega na pozostawieniu na każdej stronie pewnej ilości wolnego miejsca, która umożliwi powiększanie rekordów znajdujących się już na tej stronie lub wstawianie nowych, bez powodowania kosztowej operacji powodującej fragmentację indeksu i nazywanej podziałem strony (ang. page split). Dzięki określeniu procentu wolnego miejsca, strony indeksu mogą pozostawać bardziej równomiernie wypełnione, aż do następnej operacji przebudowy indeksu, która przywróci skonfigurowany współczynnik wypełniania. Cała sztuka polega na dobraniu takiej wartości procentowej, która zminimalizuje ilość podziałów strony wykonywanych pomiędzy kolejnymi operacjami przebudowy indeksu.

W przypadku baz danych typu OLTP (Online Transaction Processing - Przetwarzanie transakcji w trybie online) nie ma innego sposobu dobrania właściwej wartości współczynnika wypełniania, jak zastosowanie metody prób i błędów. W przypadku hurtowni danych, w których indeksy się nie zmieniają, należy stosować współczynnik wypełniania równy 100% (co oznacza, że na stronach nie będzie w ogóle pozostawiane żadne wolne miejsce). Przypadki zmiany współczynnika wypełniania dla całej bazy danych, który domyślnie ma wartość 100%, są naprawdę bardzo rzadkie, ponieważ optymalna wartość współczynnika wypełniania jest zwykle różna dla różnych indeksów. Więcej informacji na ten temat można znaleźć w dokumentacji SQL Server 2008 Books Online, w sekcji zatytułowanej "Fill Factor" (Współczynnik wypełniania).

Inna możliwość polega na takiej zmianie indeksu, by nie dochodziło w nim do podziałów strony. Może to wymagać zmiany klucza indeksu, tak by operacje wstawiania nie odbywały się w przypadkowym miejscu (np. poprzez zaniechanie stosowania jako klucza podstawowego przypadkowych wartości identyfikatorów GUID) albo zabronienia operacji zmieniających rozmiar kolumn o zmiennej długości.

Zawsze dobrze jest zapoznać się ze wszystkimi cechami nowej funkcjonalności, zanim zostanie ona uwzględniona przy projektowaniu nowego schematu lub aplikacji, zwłaszcza jeśli funkcjonalność ta wykorzystuje technologie spoza samego serwera SQL Server tak, jak ma to miejsce w przypadku funkcjonalności FILESTREAM. Większość potrzebnych informacji na temat tej funkcji można znaleźć w artykule typu „white paper”, który napisałem dla zespołu zajmującego się serwerem SQL Server i który nosi tytuł "FILESTREAM Storage in SQL Server 2008" (Magazynowanie danych przy użyciu funkcji FILESTREAM w wersji SQL Server 2008). Zachęcam do przeczytania tego artykułu, gdyż zawiera on wyczerpujące omówienie tej funkcjonalności, niemniej jednak w tym miejscu przedstawię krótkie podsumowanie głównych obszarów mogących wzbudzać największe obawy.

Po pierwsze należy pamiętać, że dane typu FILESTREAM są przechowywane w systemie plików NTFS, a nie wewnątrz plików danych serwera SQL Server. Zapewnienie dobrej współpracy systemu NTFS z bardzo dużą liczbą plików zapisywanych w jednym katalogu wiąże się z koniecznością wykonania różnych kroków konfiguracyjnych, takich jak wyłączenie funkcji generowania nazw plików w formacie 8.3, odpowiednie skonfigurowanie rozmiaru klastra systemu NTFS, a być może także odseparowanie danych typu FILESTREAM od pozostałych danych, poprzez przeznaczenie dla nich osobnego dysku.

Po drugie, należy zagwarantować, że średni rozmiar danych zapisywanych przy użyciu typu FILESTREAM wynosić będzie 1 MB lub więcej. Przeprowadzane badania ujawniły, że dla danych o rozmiarach mniejszych od 256 KB, a w niektórych przypadkach również dla danych o rozmiarach z przedziału 256KB-1MB lepszą wydajność można uzyskać zapisując te dane bezpośrednio wewnątrz bazy danych serwera SQL Server, zamiast korzystać z takich mechanizmów, jak FILESTREAM.

Po trzecie, należy także wziąć pod uwagę operacje, jakie będą wykonywane na danych typu FILESTREAM. Dane typu FILESTREAM nie obsługują możliwości częściowej aktualizacji, a więc nawet aktualizacja jednego bajtu z liczącej 200 MB wartości typu FILESTREAM spowoduje odtworzenie od nowa całych 200 MB danych. Pomijając fakt, że jest to bardzo kosztowna operacja, może ona prowadzić do fragmentacji systemu plików NTFS, co może jeszcze bardziej obniżyć wydajność systemu. Jeśli częściowe aktualizacje są częstym zjawiskiem w używanej aplikacji, to konieczne może być zaimplementowanie jakiegoś mechanizmu grupującego pojedyncze zadania aktualizacji, którego zadaniem będzie eliminowanie kilkukrotnego aktualizowania tej samej wartości typu FILESTREAM.

Ponadto należy także rozważyć kompatybilność funkcjonalności FILESTREAM z technologiami służącymi do zapewniania wysokiej dostępności. Funkcjonalność FILESTREAM w pełni obsługuje operacje tworzenia i przywracania kopii zapasowych (włącznie z operacją odtwarzania wybranego punktu w czasie), ekspediowania dzienników oraz replikacji. W wersji SQL Server 2008 nie jest ona jednak w żaden sposób kompatybilna z funkcją dublowania baz danych (ang. database mirroring) (otrzymałem informację, że ma się to zmienić w następnej wersji produktu SQL Server).

Poruszonekwestie dają zaledwie przedsmak rzeczy, które należy wziąć pod uwagę. Aby uzyskać pełen obraz, należy przeczytać wspomniany wcześniej artykuł typu „white paper”. Jak zawsze w przypadku każdej nowej funkcjonalności, należy dokładnie ją przemyśleć, zanim przystąpimy do projektowania opartej na niej aplikacji i koniecznie przeprowadzić dokładne testy sprawdzające, czy ta nowa funkcjonalność będzie spełniać nasze wymagania. Biorąc pod uwagę fakt, że funkcjonalność FILESTREAM opiera się na magazynowaniu danych w systemie plików NTFS, zalecałbym również przeprowadzenie wstępnych testów dla różnych scenariuszy całkowitej katastrofy, aby uzyskać pewność, że nic nas nie zaskoczy po wprowadzeniu tej nowej funkcji w życie.