.png "Microsoft SQL Server 2008")
SQL Pytania i Odpowiedzi: Defragmentacja indeksów, różnica pomiędzy stanem „Synchronizing” i „Synchronized” i inne .png "Udostępnij na: Facebook")
Przez Paul S. Randal
Opublikowano: 28 sierpnia 2008 | Zaktualizowano: 28 sierpnia 2008
.gif){kind=icon} |
Pyt. Przebudowa indeksu a tworzenie statystyk |
|
Pyt. Kompresja danych a niepowodzenie podczas odtwarzania bazy |
|
Pyt. Stan „Synchronizing" po implementacji funkcji synchronicznego dublowania bazy danych |
|
Pyt. Tworzenie raportów oraz sprawdzanie spójności w lustrzanej bazie danych |
Przebudowa indeksu a tworzenie statystyk |
| Jestem nieco zdezorientowany w kwestii wpływu, jaki ma na statystyki operacja defragmentacji indeksu. Słyszałem, że czasami po przebudowie indeksu należy utworzyć statystyki od nowa, a czasami nie oraz że przebudowa indeksu grupującego ma wpływ także na inne indeksy. Czy mógłby Pan wyjaśnić moje wątpliwości, ponieważ chciałbym mieć pewność, że niechcący nie pogorszę wydajności bazy danych? |
Odp. Ta kwestia z pewnością jest źródłem wielu nieporozumień, ale ma Pan rację, że pełna strategia utrzymywania bazy danych obejmuje defragmentacje indeksów oraz aktualizowanie statystyk. Nie będę teraz wdawać się w szczegóły związane z tym, kiedy i dlaczego należy przeprowadzać defragmentację indeksów - informacje te są dostępne w moim artykule z sierpnia 2008 roku, zatytułowanym „Top Tips for Effective Database Maintenance” (Najważniejsze wskazówki związane z efektywnym utrzymywaniem bazy danych). Zamiast tego przyjmę założenie, że Czytelnicy wiedzą już, które indeksy wymagają przebudowy, i skoncentruję się na opisie sposobu działania tej funkcjonalności. Pierwsza część narosłych wokół tej kwestii nieporozumień związana jest z tym, które spośród dostępnych operacji defragmentacji indeksu mają wpływ na statystyki. Przebudowa indeksu (za pomocą polecenia ALTER INDEX … REBUILD , DBCC DBREINDEX lub CREATE INDEX … WITH DROP_EXISTING ) powoduje aktualizację indeksu w sposób równoważny pełnemu skanowaniu, ale za to operacja reorganizacji indeksu (za pomocą polecenia ALTER INDEX … REORGANIZE lub DBCC INDEXDEFRAG) w ogóle nie powoduje aktualizacji statystyk, mimo że obie te operacje powodują defragmentację indeksu. Fakt, że operacja przebudowy indeksu powoduje aktualizację statystyk, a operacja reorganizacji nie, wynika z działania algorytmów używanych do wykonywania tych operacji. Operacja przebudowy indeksu dysponuje pełnym wglądem w zawartość indeksu, a więc może poprawnie zaktualizować statystyki. Natomiast operacja reorganizacji wykonywana jest zawsze tylko na kilku stronach indeksu i nie może poprawnie zaktualizować statystyk dla całego indeksu. Druga część nieporozumień związana jest z tym, które statystyki są aktualizowane podczas operacji przebudowy indeksu. Generalnie istnieją dwa rodzaje statystyk dla tabel - statystyki dla indeksowanych kolumn tabeli oraz statystyki dla kolumn nieindeksowanych. Operacja przebudowy indeksu aktualizuje statystyki tylko dla przebudowywanego indeksu. Statystyki dla kolumn nieindeksowanych muszą natomiast być aktualizowane ręcznie jako część zaplanowanych zadań administracyjnych. Co więcej, zgodnie z tym, co napisałem w artykule „Top Tips for Effective Database Maintenance”, należy unikać ręcznego aktualizowania statystyk po operacji przebudowy indeksu, ponieważ operacja ręcznej aktualizacji może używać mniejszej częstotliwości próbkowania niż 100%, podczas gdy operacja przebudowy indeksu wykorzystuje próbkowanie równoważne operacji pełnego skanowania (100%). Czyli inaczej mówiąc, mogłoby dojść do zastąpienia statystyki uzyskanej na drodze pełnego skanowania statystyką zbudowaną w oparciu o niepełne próbkowanie. Ostatnia część nieporozumień dotyczy tego, jaki jest wpływ operacji przebudowy indeksu na pozostałe indeksy. Prawda jest taka, że przebudowa indeksu zawsze wpływa tylko na ten konkretny indeks, który jest przebudowywany oraz na jego statystyki. Wyjątek stanowi nieunikalny indeks grupujący z wersji SQL Server 2000, którego przebudowa powoduje także przebudowę wszystkich niegrupujących indeksów z danej tabeli. Zachowanie to zostało jednak poprawione w wersji SQL Server 2005 oraz nowszych. Więcej informacji na ten temat można znaleźć na moim blogu, w artykule zatytułowanym „Indexes from Every Angle” (Spojrzenie na indeksowanie z każdej perspektywy). Podsumowując, zadania administracyjne związane z utrzymywaniem indeksu powinny:
|
Kompresja danych a niepowodzenie podczas odtwarzania bazy |
| Podczas eksperymentowania z wersją serwera SQL Serwer 2008 spotkałem się z bardzo niepokojącym zachowaniem. Wygląda na to, że jeśli włączę kompresję danych dla produkcyjnej bazy danych, wykonam jej kopię zapasową, a następnie spróbuję odtworzyć tę bazę na instancji serwera w edycji Standard, to proces odtwarzania z kopii zapasowej zakończy się niepowodzeniem! Czy jest to oczekiwane zachowanie? Jeśli tak, to czy ten efekt ogranicza się tylko do kompresji danych, czy może zostać wywołany także przez inne funkcje? I dlaczego proces odtwarzania z kopii zapasowej nie kończy się błędem od razu na samym początku, zamiast dopiero na końcu, po odczytaniu całej kopii zapasowej? |
Odp. Opisane przez Pana zachowanie jest celowe. W przypadku większości funkcji przeznaczonych „tylko dla przedsiębiorstw” możliwość ich używania ograniczona jest po prostu do edycji Enterprise Edition, Enterprise Evaluation Edition oraz Developer Edition. Istnieją jednak także takie funkcje, dla których występują również ograniczenia edycji produktu, na których można odtwarzać bazę danych, w której ta funkcja została włączona. W pańskim przypadku natrafił Pan na jedną z funkcji serwera SQL Server 2008 podlegającą temu ograniczeniu - na funkcję kompresji danych. W rzeczywistości takie zachowanie nie jest wcale czymś nowym, co pojawiło się dopiero w wersji SQL Server 2008. W wersji SQL Server 2005, jeśli baza danych zawierała jakiekolwiek podzielone na partycje tabele lub indeksy (jawnie korzystając z funkcjonalności partycjonowania), to kopię zapasową takiej bazy danych można było odtworzyć tylko przy użyciu jednej z wymienionych wcześniej edycji typu Enterprise. W wersji SQL Server 2005 wiązały się z tym jednak dwa problemy. Po pierwsze, trudno było stwierdzić, czy w bazie danych została użyta funkcjonalność partycjonowania, a po drugie proces odtwarzania kończył się błędem dopiero na samym końcu, po odczytaniu całej kopii zapasowej. Oznacza to, że użytkownik nie mógł stwierdzić, że proces odtwarzania z kopii zapasowej się nie powiedzie, dopóki nie wykonał całej (potencjalnie długotrwałej) operacji odtwarzania kopii zapasowej. Wynika to z faktu, że baza danych nie jest transakcyjnie spójna, dopóki nie zakończy się ta część procesu odtwarzania z kopii zapasowej, która odpowiada za przywrócenie bazy danych (ang. recovery) - a operacje wykonywane podczas procesu przywracania mogą dodawać lub usuwać partycje. Spotkanie się z takim zachowaniem może rzeczywiście być irytujące, zwłaszcza w sytuacji odtwarzania po awarii, gdy jedynym dostępnym serwerem jest akurat serwer z edycją Standard. W wersji SQL Server 2008 lista funkcjonalności przeznaczonych „tylko dla przedsiębiorstw” i powodujących opisane przez Pana zachowanie zwiększyła się do czterech. Są to: kompresja danych, przechwytywanie zmian danych, przezroczyste szyfrowanie danych oraz partycjonowanie. Oznacza to, że z opisywanym przez Pana problemem może zetknąć się większa liczba osób. Z tego powodu dodany został nowy dynamiczny widok zarządzający o nazwie sys.dm_db_persisted_sku_features, który pozwala administratorom baz danych na szybkie sprawdzenie, czy w bazie danych została włączona którakolwiek z wymienionych funkcjonalności. Np. w bazie danych, w której istnieje tabela z włączoną kompresją danych, zapytanie skierowane do tego dynamicznego widoku zarządzającego zwróci następujący rezultat: |
SELECT * FROM sys.dm_db_persisted_sku_features;
GO
feature_name feature_id
-------------- -----------
Compression 100
W wersji SQL Server 2008 nie rozwiązano jednak problemu polegającego na tym, że operacja przywracania z kopii zapasowej musi zostać prawie całkowicie zakończona, zanim możliwe będzie poinformowanie administratora, że danej bazy danych nie można odtworzyć. W rzeczywistości, biorąc pod uwagę sposób działania operacji przywracania z kopii zapasowej, jest mało prawdopodobne, aby problem ten został kiedyś rozwiązany - więcej informacji na ten temat można znaleźć w artykule zamieszczonym w dziale SQL Q&A, z października 2008 roku. Jeśli istnieje ewentualność, że baza danych będzie musiała zostać odtworzona na serwerze w edycji Standard (lub niższej), to administrator bazy danych musi albo zabronić korzystania z wymienionych funkcji, albo regularnie sprawdzać wskazany dynamiczny widok zarządzający, aby w krytycznej sytuacji uniknąć przykrej niespodzianki podczas próby otworzenia bazy danych z kopii zapasowej. |
Stan „Synchronizing" po implementacji funkcji synchronicznego dublowania bazy danych |
| W naszej firmie stosujemy plan wykonywanych w nocy zadań administracyjnych, który obejmuje również działania poprawiające wydajność indeksów. Słyszałem, że skonfigurowanie dla indeksów „współczynnika wypełnienia” pozwala całkowicie wyeliminować konieczność wykonywania zadań związanych z utrzymywaniem indeksów. Czy to prawda? Wydaje mi się, że niektóre z indeksów istniejących w naszej bazie danych nie ulegają fragmentacji, a inne tak. Czy powinniśmy skonfigurować w naszej bazie danych domyślną wartość współczynnika wypełniania, która będzie stosowana dla wszystkich indeksów, a jeśli tak, to jaka powinna być wartość tego współczynnika? |
Odp. Zanim odpowiem na to pytanie, zdefiniuję najpierw znaczenie stanów SYNCHRONIZED i SYNCHRONIZING dla lustrzanej bazy danych. Funkcjonalność dublowania baz danych polega, krótko mówiąc, na ciągłym przesyłaniu rekordów z fizycznego dziennika transakcji pomiędzy instancją serwera SQL Server, na której znajduje się główna baza danych, a instancją, na której znajduje się lustrzana baza danych. Jeśli w dzienniku transakcji głównej bazy danych nie ma żadnych rekordów oczekujących na wysłanie do lustrzanej bazy danych, to baza lustrzana znajduje się w stanie SYNCHRONIZED (czyli inaczej mówiąc obydwie bazy danych są ze sobą zsynchronizowane). Jeśli jednak w dzienniku transakcji znajdują się rekordy, które nie zostały jeszcze wysłane do lustrzanej bazy danych (lustrzana baza danych pozostaje „w tyle” w stosunku do bazy głównej), to lustrzana baza danych znajduje się w stanie SYNCHRONIZING. Sposobem na zainicjowanie dublowania bazy danych jest wzięcie pełnej kopii zapasowej bazy danych oraz przynajmniej jednej kopii zapasowej dziennika transakcji i odtworzenie tych kopii w systemie lustrzanym (z opcją WITH NO_RECOVERY ). Wówczas po włączeniu funkcjonalności dublowania lustrzana baza danych będzie początkowo znajdować się w stanie SYNCHRONIZING. Wynika to z faktu, że od czasu odtworzenia dziennika transakcji w systemie lustrzanym w systemie głównym mogły zostać zarejestrowane nowe transakcje. Jest to bardzo prawdopodobne w przypadku bazy danych znajdującej się w ciągłym użyciu. Trik pozwalający na zminimalizowanie ilości czasu, w którym lustrzana baza danych pozostawać będzie w stanie SYNCHRONIZING, polega na odtworzeniu jak najbardziej aktualnej wersji bazy danych przy użyciu kopii zapasowych dziennika transakcji. Jednak w przypadku bazy danych znajdującej się w ciągłym użyciu może to być bardzo trudne do zrealizowania i dlatego początkowo lustrzana baza danych przez jakiś czas znajduje się w stanie SYNCHRONIZING. Gdy lustrzana baza danych nadrobi już zaległości, jej stan zmieni się na SYNCHRONIZED. Od tej chwili może się zdarzać, że lustrzana baza danych będzie znowu pozostawać w tyle za bazą główną i wówczas jej stan będzie się zmieniał na SYNCHRONIZING. Jedną z możliwych przyczyn takiego stanu jest brak przez jakiś czas możliwości komunikowania się systemu lustrzanego z systemem głównym. Może do tego dojść również wówczas, gdy system główny generuje nowe zapisy w dzienniku transakcji szybciej, niż są one dostarczane do systemu lustrzanego. W każdym z tych przypadków, w zależności od sposobu skonfigurowania funkcjonalności dublowania, główna baza danych może kontynuować przetwarzanie transakcji i tworzyć kolejkę rekordów z dziennika transakcji (nazywaną kolejką SEND). Rekordy z tej kolejki muszą zostać przesłane do systemu lustrzanego, aby umożliwić mu nadrobienie zaległości w zakresie wykonywanych transakcji oraz jego zsynchronizowanie. Dopóki system lustrzany nie nadrobi tych zaległości, pozostawać będzie w stanie SYNCHRONIZING. Ciągłe przełączanie stanu lustrzanej bazy danych pomiędzy stanami SYNCHRONIZING i SYNCHRONIZED można obserwować również wtedy, gdy połączenie sieciowe pomiędzy systemem głównym i lustrzanym jest zawodne. Pełne omówienie tych stanów można znaleźć w artykule z serii „white paper”, zatytułowanym Database Mirroring in SQL Server 2005 (Dublowanie baz danych w wersji SQL Server 2005). |
Tworzenie raportów oraz sprawdzanie spójności w lustrzanej bazie danych |
| Zamierzamy przejść na wersję SQL Server 2008 jak tylko firma Microsoft opublikuje dodatek SP1, a jedną z oczekiwanych przez nas funkcjonalności jest funkcjonalność FILESTREAM, ponieważ znosi ona limit 2GB dla wartości kolumny. Zanim jednak zaczniemy projektować nową wersję schematu bazy danych z wykorzystaniem typu danych FILESTREAM, chcielibyśmy wiedzieć, czy istnieją jakieś wady lub utrudnienia związane ze stosowaniem tego typu danych, które mogłyby doprowadzić do powstania problemów w środowisku produkcyjnym? |
Odp. Pierwszym problem jest licencjonowanie. Licencja dla instancji serwera SQL Server, na której znajdować się będzie lustrzana baza danych, jest bezpłatna, jeśli instancja ta wykorzystywana będzie wyłącznie do tego celu. Z chwilą utworzenia obrazu migawkowego lustrzanej bazy danych lub wykonania jakiejkolwiek innej operacji przy użyciu tej instancji serwera SQL Server, konieczny jest zakup dodatkowej licencji. Co się zaś tyczy sprawdzania spójności danych, to przeprowadzanie tego procesu na lustrzanej bazie danych (z wykorzystaniem migawkowego obrazu bazy danych) nie daje żadnych gwarancji co do spójności głównej bazy danych. Pomiędzy główną a lustrzaną bazą danych dublowane są jedynie rekordy dziennika transakcji, a więc w przypadku uszkodzenia przez podsystem we/wy strony z głównej bazy danych uszkodzenie to nie zostanie odtworzone w lustrzanej bazie danych. Oznacza to, że proces sprawdzania spójności lustrzanej bazy danych nie wykryje tej uszkodzonej strony, istniejącej w głównej bazie danych. Dobrym zastosowaniem lustrzanej bazy danych jest natomiast raportowanie. Pierwszym problemem, z jakim można się wówczas zetknąć, jest pytanie o sposób odświeżania migawkowych obrazów bazy danych, używanych do generowania raportów. Migawkowych obrazów bazy danych nie można odświeżać, a więc konieczne będzie tworzenie nowego obrazu migawkowego, a aplikacja raportująca będzie musiała łączyć się z tym nowym obrazem. Wymaga to wyposażenia aplikacji raportującej w dodatkową logikę. Drugi problem związany jest z określeniem, jak powinna zachować się aplikacja raportująca w przypadku awaryjnego przełączenia na lustrzaną bazę danych - czy powinna pozostać połączona z nową bazą główną, czy może powinna przełączyć się na nową bazę lustrzaną? Szczegółowe omówienie tej kwestii wykraczałoby jednak poza ramy tego artykułu. W przypadku jakichkolwiek dodatkowych zastosowań lustrzanej bazy danych, główne obawy wiążą się z potencjalną możliwością wystąpienia w systemie lustrzanym problemów wydajnościowych. Tworzenie migawkowego obrazu bazy danych wiąże się z dodatkowym obciążeniem podsystemu we/wy, ponieważ pierwsza zmiana strony w lustrzanej bazie danych będzie musiała zostać odwzorowana również w obrazie migawkowym tej bazy. Ponadto wszelkie obciążenie migawkowego obrazu bazy danych zwiększa ilość operacji we/wy w lustrzanej bazie danych, ponieważ większość stron odczytywanych z obrazu migawkowego nie uległa jeszcze zmianie od czasu utworzenia tego obrazu i dlatego w rzeczywistości odczytywana jest z lustrzanej bazy danych. Te dodatkowe operacje we/wy wykonywane na lustrzanej bazie danych mogą spowolnić proces ponownego odtwarzania rekordów dziennika transakcji, co z kolei prowadzi do powstawania tzw. dziennika operacji (ang. backlog). Dziennik ten nazywany jest kolejką REDO i obejmuje tę część dziennika transakcji, która musi zostać ponownie zastosowana, zanim lustrzana baza danych będzie dostępna w trybie online po wykonaniu operacji przełączenia awaryjnego. Im więcej operacji we/wy dla lustrzanej bazy danych pochodzi z operacji wykonywanych na obrazie migawkowym, tym bardziej może powiększyć się rozmiar kolejki REDO i tym dłużej baza danych będzie niedostępna po wykonaniu operacji przełączania awaryjnego. Wspomniał Pan, że długość kolejki REDO zwykle jest zbliżona do zera, a więc w Pańskim przypadku nie stanowi to problemu, ale z pewnością jest to jedna z tych rzeczy, którą należy obserwować, aby skutkiem zwiększonych możliwości generowania raportów nie była kompromitacja dostępności bazy danych. |
Więcej informacji
Paul S. Randal jest dyrektorem naczelnym firmy SQLskills.com i posiadaczem tytułu SQL Server MVP. W latach 1999-2007 pracował w firmie Microsoft w zespole zajmującym się motorem magazynowania danych serwera SQL Server. Jest autorem programu DBCC CHECKDB/repair dla wersji SQL Server 2005 i podczas prac nad wersją SQL Server 2008 był odpowiedzialny za główny motor magazynowania danych (Core Storage Engine). Paul jest ekspertem w dziedzinie odtwarzania po awarii, wysokiej dostępności i utrzymywania bazy danych, a także regularnie występuje jako prezenter na różnych konferencjach odbywających się na całym świecie. Prowadzi również swój blog pod adresem SQLskills.com/blogs/paul i można skontaktować się z nim za pomocą serwisu Twitter, korzystając z adresu Twitter.com/PaulRandal.
.gif){kind=icon}
Do początku strony