Free Press

Celem tworzenia indeksów SQL na tabelach bazy danych jest zwiększenie wydajności zapytań, które odczytują dane z tabeli bazowej poprzez przyspieszenie procesów pobierania danych. Jednak te tabele transakcyjne nie są statyczne i zmieniają się bardzo często w czasie. Te zmiany, które są wykonywane na tabeli bazy danych obejmują dodawanie nowych rekordów, modyfikowanie lub usuwanie istniejących rekordów. Ponieważ zmiany te powinny być replikowane do powiązanych indeksów tabel, indeks tabeli z czasem ulegnie fragmentacji.

Przegląd fragmentacji indeksów serwera MySQL

Istnieją dwa główne typy fragmentacji indeksów SQL; Fragmentacja wewnętrzna i fragmentacja zewnętrzna. Fragmentacja wewnętrzna jest spowodowana przez wstawienie nowego rekordu lub aktualizację istniejącego rekordu z wartościami, które nie mieszczą się w obecnej wolnej przestrzeni na stronie danych i skutkują podzieleniem strony na dwie strony, aby zmieścić nową wartość. W tym przypadku silnik SQL Server przeniesie około 50% danych z aktualnej strony do nowej strony danych, która zostanie wygenerowana w wyniku operacji podziału, aby zachować równowagę w drzewie indeksów SQL. Fragmentacja wewnętrzna może być również spowodowana przez losowe operacje usuwania danych, które powodują powstanie pustego miejsca na stronach danych. Ten typ operacji pozostawia niezapełnione strony danych i skutkuje większym rozmiarem indeksu SQL Server oraz wolniejszą wydajnością ze względu na konieczność odczytu większej ilości stron danych w celu pobrania żądanych danych.

Fragmentacja zewnętrzna jest spowodowana, gdy strony danych indeksu SQL Server są rozproszone na fizycznym pliku bazy danych, ze względu na to, że logiczna kolejność indeksu SQL Server nie odpowiada fizycznej kolejności na dysku bazowym. Fragmentacja zewnętrzna może być spowodowana przez losowe operacje usuwania, które pozostawiają część stron zakresu pustą, podczas gdy sam zakres jest zarezerwowany. Ten typ fragmentacji skutkuje pogorszeniem wydajności, ponieważ zajmuje więcej czasu i zużywa więcej zasobów na przeskakiwanie pomiędzy rozproszonymi stronami danych w celu pobrania żądanych danych z dysku bazowego.

Powodem, dla którego tak bardzo zależy nam na fragmentacji indeksu SQL jest to, że indeks, który został utworzony w celu zwiększenia wydajności systemu, może pogorszyć wydajność zapytań po tym, jak jest bardzo pofragmentowany. Wyobraź sobie różnicę w czasie i liczbie odczytów dysku między odczytem określonej liczby wierszy ze zdrowego indeksu SQL, w którym dane są przechowywane w ciągłych stronach danych w pliku bazy danych, a odczytem tej samej liczby wierszy z wysoce pofragmentowanego indeksu SQL Server, w którym dane są rozproszone w nieciągłych stronach danych w pliku bazy danych.

Jeśli okaże się, że indeks SQL tabeli jest pofragmentowany, należy naprawić problem fragmentacji, wykonując SQL Server Index Reorganization lub Index Rebuild, w oparciu o procent fragmentacji indeksu SQL. Na przykład indeks SQL Server o poziomie fragmentacji poniżej 30% może zostać zreorganizowany, natomiast indeks SQL o poziomie fragmentacji powyżej 30% powinien zostać przebudowany.

• Uwaga: Sprawdź Konserwacja indeksów SQL Server aby uzyskać więcej informacji.

Można proaktywnie przezwyciężyć problemy związane z fragmentacją indeksu SQL Server i podziałem stron, odpowiednio projektując indeks i ustawiając opcje tworzenia indeksu Fill Factor i pad_index z wartościami propervalues.

• Uwaga: Sprawdź SQL Server Index Operations, aby uzyskać więcej informacji.

Raport fragmentacji indeksu serwera SQL

Nie jest uważane za najlepszą praktykę samo tworzenie indeksu SQL w tabeli bazy danych w celu skorzystania z ulepszenia operacji pobierania danych i pozostawienie tego indeksu SQL Server na zawsze bez ciągłego monitorowania jego fragmentacji i utrzymania go.

SQL Server dostarcza nam kilka metod, które mogą być użyte do sprawdzenia procentowej fragmentacji indeksu SQL.

SQL Server Index Properties

Pierwszą metodą jest strona Fragmentacja w oknie SQL Server Index Properties. Kliknij prawym przyciskiem myszy na nazwę indeksu, który chcesz sprawdzić i wybierz polecenie Właściwości. Z poziomu strony Właściwości indeksu SQL Server przejdź do strony Fragmentacja, a zobaczysz procent fragmentacji indeksu oraz stopień zapełnienia stron indeksu SQL Server w sekcji Fragmentacja, jak pokazano poniżej:

W kilku krokach, możesz łatwo sprawdzić procent fragmentacji indeksu SQL Server wybranego indeksu z poziomu okna właściwości indeksu. Ale co jeśli chcesz mieć przegląd fragmentacji procentowej dla wszystkich indeksów tabel lub wszystkich indeksów tabel bazy danych? W takim przypadku musisz sprawdzić je jeden po drugim. W rezultacie, jeden tydzień pracy nie wystarczy na jeden raport o bazie danych!

sys.dm_db_index_physical_stats

Inną metodą, która może być użyta do sprawdzenia procentu fragmentacji dla indeksów bazy danych, jest zapytanie o dynamiczną funkcję zarządzania sys.dm_db_index_physical_stats, która została wprowadzona po raz pierwszy w SQL Server 2005, jako zamiennik dla przestarzałego polecenia DBCC SHOWCONTIG. Dostarcza ona informacji o rozmiarze i procencie fragmentacji indeksów bazy danych.

Aby uzyskać znaczące informacje o procencie fragmentacji wszystkich indeksów pod określoną bazą danych z sys.dm_db_index_physical_stats DMF, należy dołączyć do niej DMV sys.indexes DMV, tak jak w poniższym skrypcie T-SQL:

SELECT OBJECT_NAME(Index_Info.OBJECT_ID) AS TableName ,Index_Info.name AS IndexName ,Index_Stat.index_type_desc AS IndexType ,Index_Stat.avg_fragmentation_in_percent IndexFragmPercentFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) Index_StatINNER JOIN sys.indexes Index_Info ON Index_Info.object_id = Index_Stat.object_id AND Index_Info.index_id = Index_Stat.index_idORDER BY IndexFragmPercent DESC

Bazując na swoich umiejętnościach tworzenia kodu SQL, możesz wykonać filtrację, która jest potrzebna do pobrania fragmentacji indeksów SQL w wymaganym zakresie, bez opcji uzyskania podsumowania dla całości baz danych instancji, biorąc pod uwagę, że wyświetlane dane są od ostatniego restartu usługi SQL Server. Wynik w naszym przypadku będzie wyglądał następująco:

SQL Server Index Physical Statistics Standard Report

SQL Server udostępnia nam kilka standardowych raportów, które zawierają przydatne informacje statystyczne na temat wybranej bazy danych. Raport Index Physical Statistics, jest jednym ze standardowych raportów SQL Server, który zwraca informacje statystyczne na temat partycji indeksu SQL Server, procentu fragmentacji oraz liczby stron na każdej partycji indeksu SQL na poziomie bazy danych, od ostatniego restartu usługi SQL Server, wraz z rekomendacją jak naprawić problem fragmentacji indeksu, jeśli istnieje, na podstawie procentu fragmentacji indeksu SQL Server.

Aby wyświetlić raport Statystyki fizyczne indeksów SQL Server, kliknij prawym przyciskiem myszy na swoją bazę danych, wybierz Raporty -> Raporty standardowe, a następnie wybierz raport Statystyki fizyczne indeksów, jak pokazano poniżej:

A wygenerowany raport na poziomie bazy danych, który nie może być filtrowany i wymaga ręcznego rozszerzenia wyników indeksów każdej tabeli, będzie wyglądał jak pokazano poniżej:

ApexSQL Defrag

ApexSQL Defrag jest zewnętrznym narzędziem do defragmentacji indeksów SQL, które może być łatwo wykorzystane do sprawdzenia fragmentacji indeksów SQL Server oraz informacji o ich użyciu, a także do wykonania lub zaplanowania odpowiedniej naprawy problemu fragmentacji indeksów.

Z jednej centralnej lokalizacji, można analizować i przeglądać fragmentację i informacje o użyciu na różnych poziomach, w tym fragmentacji indeksu SQL i informacji o użyciu na poziomie indeksu SQL Server, na poziomie tabeli, na poziomie bazy danych i wreszcie na poziomie całej instancji SQL Server. ApexSQL Defrag umożliwia również konfigurację współczynnika wypełnienia dla indeksów tabel, tworzenie alertów dla wszystkich zadań defragmentacji oraz polityk, a także wysyłanie powiadomień e-mail o udanych lub nieudanych zadaniach.

Wyświetlane informacje zapewniają dobry wgląd w rozmiar indeksu SQL oraz procent fragmentacji, na które nie wpłynie żaden restart usługi SQL Server lub ponowne uruchomienie serwera. Dzieje się tak, ponieważ informacje te będą przechowywane w centralnym repozytorium dla narzędzia ApexSQL Defrag.

Instalacja ApexSQL Defrag

ApexSQL Defrag można łatwo pobrać z Centrum Pobierania ApexSQL i zainstalować na swoim serwerze, postępując zgodnie z prostym kreatorem instalacji, jak pokazano poniżej:

Na początku zostaniesz poproszony o określenie konta usługi, które będzie używane jako kontekst wykonawczy dla agenta ApexSQL Defrag i przekazuje polecenia do instancji serwera SQL, jak poniżej:

Po określeniu konta usługi ApexSQL Defrag, zostaniesz poproszony o określenie gdzie zainstalować narzędzie ApexSQL Defrag oraz czy utworzyć ikonę skrótu do narzędzia na pulpicie, jak pokazano poniżej:

Sprawdzając czy podana ścieżka zawiera minimum 127MB wolnego miejsca, ApexSQL Defrag zostanie pomyślnie zainstalowany na Twoim serwerze. Zostanie wyświetlone następujące powiadomienie, umożliwiające bezpośrednie uruchomienie ApexSQL Defrag:

Jeśli uruchamiasz ApexSQL Defrag po raz pierwszy, zostaniesz poproszony o potwierdzenie utworzenia centralnej bazy danych repozytorium, w którym będą przechowywane dane historyczne i konfiguracyjne, w następujący sposób:

Dodaj nowy serwer

Po utworzeniu bazy danych repozytorium, ApexSQL Defrag zostanie uruchomiony bez instancji SQL. Aby sprawdzić procentową fragmentację indeksu SQL na konkretnej instancji serwera SQL, musisz dodać tę instancję do narzędzia. W tym celu należy kliknąć na przycisk Dodaj, w zakładce Strona główna, jak pokazano poniżej:

W oknie Połącz z serwerem SQL zostaniesz poproszony o podanie nazwy instancji serwera SQL, która będzie używana oraz poświadczeń wymaganych do połączenia z tym serwerem, jak poniżej:

Analiza indeksów serwera SQL

Po dodaniu serwera można wykonać nowe sprawdzenie wszystkich informacji o fragmentacji indeksów SQL z zakładki Fragmentacja poniżej:

ApexSQL Defrag daje możliwość analizy wszystkich indeksów na tej instancji w oparciu o trzy tryby:

• DETAILED: w którym podczas procesu skanowania zostaną odczytane wszystkie strony danych i indeksu SQL
• SAMPLED: w którym zostanie odczytany tylko 1% stron, biorąc pod uwagę, że indeks SQL Server zawiera ponad 10 000 stron
• LIMITED: w którym odczytane zostaną tylko strony znajdujące się na poziomie nadrzędnym drzewa b

Aby przeanalizować informacje o indeksie SQL Server z podłączonej instancji SQL Server, klikamy na przycisk Analyze, w zakładce Fragmentation i wybieramy wykonanie analizy Fast lub Deep. Wykonamy Głębokie analizowanie, aby uzyskać pełne informacje, jak pokazano poniżej:

Monitoruj pasek postępu w dolnej części narzędzia, aby sprawdzić postęp analizy, jak poniżej:

A kompleksowy raport, który zawiera pełne informacje o wszystkich indeksach SQL utworzonych w każdej bazie danych tej instancji SQL Server, zostanie wyświetlony, jak pokazano poniżej:

Z możliwością nurkowania pomiędzy różnymi poziomami; instancją SQL Server, bazą danych, tabelą lub indeksem, za pomocą jednego kliknięcia w tym samym centralnym oknie, oraz filtrowania pobranych wyników na podstawie określonego poziomu fragmentacji lub progu, jak pokazano poniżej:

Dodatkowo możesz przeciągnąć dowolną kolumnę z kolumn raportu, aby pogrupować cały raport na podstawie wartości tej kolumny, tak jak w poniższym przykładzie, w którym pogrupowaliśmy dane raportu na podstawie nazwy tabeli:

Raporty defragmentacji ApexSQL

Defragmentacja ApexSQL udostępnia nam szereg raportów, które przedstawiają informacje statystyczne na temat indeksów bazy danych. Raporty te można skonfigurować i sprawdzić w zakładce Raporty poniżej:

Pierwszym raportem jest Raport całkowity, który przedstawia w formie graficznej fragmentację indeksów SQL oraz wykorzystanie miejsca na dysku na wybranym poziomie. Na przykład, jeśli klikniemy na nazwę instancji, raport narysuje wykres procentowy fragmentacji i wykorzystania przestrzeni dyskowej na poziomie instancji SQL Server, jak poniżej:

Jeśli klikniemy na dowolną bazę danych z okna Server Explorer, raport automatycznie zmieni widok, aby pokazać wykres na poziomie bazy danych, jak pokazano poniżej:

Drugim raportem, który również można przeglądać na różnych poziomach, jest raport Statistics. Raport ten przedstawia informacje statystyczne o całkowitym rozmiarze indeksów, fragmentacji oraz dystrybucji typów indeksów na wybranym poziomie, jak pokazano poniżej:

Trzecim raportem jest raport Top 10, który wyświetla 10 najlepszych indeksów klastrowych i nieklastrowych o największym rozmiarze i największej procentowej fragmentacji indeksu SQL, na określonym poziomie. Na przykład, poniższy raport pokazuje 10 najdroższych indeksów, w oparciu o rozmiar i fragmentację, na poziomie instancji serwera SQL:

Przechodząc do konkretnej bazy danych, raport automatycznie pokaże 10 najdroższych indeksów, w oparciu o rozmiar i fragmentację, na poziomie wybranej bazy danych, jak pokazano poniżej:

Schodząc głęboko do konkretnej tabeli w danej bazie danych, raport pokaże 10 najdroższych indeksów, w oparciu o rozmiar i fragmentację, w następujący sposób:

ApexSQL Defrag Eksport raportu

ApexSQL Defrag umożliwia eksport raportów fragmentacji do różnych formatów plików, w tym PDF, IMG, HTML, CS oraz XML, z możliwością dostosowania ustawień raportu przed jego wygenerowaniem. W zakładce Raporty kliknij przycisk Opcje:

W oknie Edytuj opcje eksportu możesz skonfigurować różne opcje, począwszy od konwencji nazewnictwa generowanego pliku, ścieżki, gdzie plik zostanie zapisany, orientacji strony i wreszcie zawartości każdego raportu, jak pokazano poniżej:

Przykładowo, aby wyeksportować wygenerowany raport Razem na określonym poziomie, należy kliknąć przycisk Eksportuj, w zakładce Raporty i wybrać typ pliku, do którego ten raport Razem zostanie wyeksportowany. W ciągu kilku sekund zostanie wygenerowany raport z zapytaniem, czy chcesz otworzyć wygenerowany raport. Kliknij przycisk Tak, aby otworzyć bezpośrednio ten raport, jak pokazano poniżej:

Ponadto można wyeksportować raport Top 10 na określonym poziomie, klikając przycisk Eksport, w zakładce Raporty, i określić typ pliku eksportu, a raport zostanie wygenerowany szybko, z pytaniem, czy chcesz wyświetlić bezpośrednio wygenerowany raport. Kliknij Tak aby otworzyć wygenerowany plik, jak pokazano poniżej:

Z poprzednich przykładów jasno wynika, że możemy z łatwością używać narzędzia ApexSQL Defrag do przeglądania i analizowania informacji o procentowej fragmentacji indeksu SQL oraz nurkowania na różnych poziomach począwszy od poziomu instancji SQL Server aż do poziomu indeksu SQL Server i wyświetlania ich w różnych formatach eksportu, bez wpływu na wyświetlane informacje w przypadku restartu serwera lub usługi, ponieważ będą one przechowywane w bazie danych repozytorium.