Udostępnij za pośrednictwem


Reorganizing and Rebuilding Indexes

The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data.Za pośrednictwem czas modyfikacje mogą spowodować informacje w indeksu, który ma być rozproszone w bazie danych (pofragmentowanych).Fragmentacja występuje wówczas, gdy indeksy strony, w którym logiczną kolejność, na podstawie wartości klucz nie pasuje fizycznej kolejności wewnątrz pliku danych.Indeksy w dużym stopniu pofragmentowane może zmniejszyć wydajność kwerendy i spowodować, że aplikacja odpowiada powoli.Aby uzyskać więcej informacji zobacz temat to Witryny firmy Microsoft w sieci Web.

Fragmentacja indeksu można zaradzić reorganizacji indeks lub przebudowywanie indeksu.Zbudowany na schemat partycji indeksów podzielonym na partycje można użyć zarówno z tych metod, na zakończenie indeksu lub na jednej partycji indeksu.

Wykrywanie fragmentacji

Pierwszym krokiem przy podejmowaniu decyzji metoda defragmentacji jest analizowanie indeks tak, aby określić stopień fragmentacji.Za pomocą funkcja systemu sys.dm_db_index_physical_stats, można wykryć fragmentacji określonego indeksu, wszystkie indeksy na tabela lub widok indeksowany, wszystkie indeksy w bazie danych lub wszystkie indeksy w wszystkich baz danych.Dla indeksów podzielonym na partycje, sys.dm_db_index_physical_stats dla każdej partycji informacje fragmentacji.

Zestaw wyników zwróconych przez sys.dm_db_index_physical_stats funkcja zawiera następujące kolumny.

Kolumna

Description

avg_fragmentation_in_percent

Procent fragmentacji logiczne (poza kolejnością stron w indeksie).

fragment_count

Liczba fragmentów (typu liść fizycznie kolejnych stron), w indeksie.

avg_fragment_size_in_pages

Średnia liczba stron w jednym fragmencie do indeksu.

Po stopień fragmentacji jest znany, skorzystaj z poniższej tabela, aby określić najlepszą metoda poprawiania fragmentacji.

avg_fragmentation_in_percent value

Instrukcja storna

> 5 % i < = 30 %

REORGANIZACJA INDEKSU ZMIANY

> 30%

ZMIENIA ODBUDOWYWANIA INDEKSU Z (ONLINE = Z) *

* Przebudowywanie indeksu mogą być wykonywane online lub offline.Reorganizacja indeks jest zawsze wykonywana online.Uzyskanie dostępności podobna do opcji reorganizacja, należy przebudować indeksów w trybie online.

Te wartości przewidywać surowca wytyczne dotyczące określania punktu, w którym powinien przełączania się między ALTER REORGANIZOWAĆ indeks i ALTER PRZEBUDOWAĆ indeks.Jednak rzeczywistych wartości, które mogą się różnić od przypadek.Jest rzeczą ważną, wypróbuj różne do określenia progu najlepszy dla danego środowiska.

Bardzo niski stopień fragmentacji (mniej niż 5 procent) nie powinny być kierowane przez jedną z tych poleceń, ponieważ korzyści w zakresie usuwania takich niewielkiej ilości fragmentacji jest prawie zawsze znaczne zneutralizowane przez koszt reorganizacji lub przebudowywanie indeksu.

Uwaga

Fragmentacji na małe indeksów często nie jest ogólnie rzecz biorąc, którymi można sterować.Strony małych indeksy są przechowywane na mieszanych zakresów.Mieszanych zakresów są współużytkowane przez maksymalnie osiem obiektów, dzięki czemu fragmentacja małych indeksu nie mogą być ograniczone po reorganizacji lub przebudowywanie indeksu.Aby uzyskać więcej informacji na temat zakresy mieszanych zobacz Opis stron i zakresów.

Przykład

W następujących przykładowych kwerendach sys.dm_db_index_physical_stats dynamiczne zarządzanie funkcja zwraca średni fragmentacji dla wszystkich indeksów na Production.Product Tabela. Korzystając z poprzedniej tabela, jest zalecane rozdzielczości do reorganizowania PK_Product_ProductID i innych indeksów.

USE AdventureWorks;
GO
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Production.Product'),
     NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO

Instrukcja może zwrócić podobny do następującego zestaw wyników.

index_id    name                        avg_fragmentation_in_percent
----------- --------------------------- ----------------------------
1           PK_Product_ProductID        15.076923076923077
2           AK_Product_ProductNumber    50.0
3           AK_Product_Name             66.666666666666657
4           AK_Product_rowguid          50.0

(4 row(s) affected)

Zmiana indeksu

Aby zreorganizować jednego lub kilku indeksów, należy użyć instrukcja ALTER INDEX klauzula REORGANIZACJA.Ta instrukcja zastępuje INDEXDEFRAG DBCC instrukcji.Aby zreorganizować jednej partycji indeks partycjonowany, należy użyć klauzula PARTITION ALTER indeks.

Zmiana indeksu defragmentuje poziom liścia indeksów klastrowanych i nieklastrowany na tabele i widoki przez fizycznie Zmienianie kolejności stron poziom liścia, aby dostosować porządek logiczny (od lewej do prawej) węzłów liściowych.O stron w celu zwiększa wydajność skanowanie indeksu.Indeks jest zreorganizowana przydzielonych do niego istniejących stron w obrębie; są przydzielane nie nowe strony.Jeżeli indeks obejmuje więcej niż jeden plik, pliki są zreorganizowanych pojedynczo.Strony nie są migrowane między plikami.

Również zmiana kompaktuje strony indeksu.Żadnych pustych stron utworzonych przez to kompaktowanie są usuwane, zapewniając dodatkowe dostępnego miejsca na dysku.Kompaktowanie opiera się na wartość współczynnik wypełnienia w sys.Indexes Służy do wyświetlania katalogu.

Proces reorganizacja używa minimalnych zasobów systemowych.Ponadto reorganizacji jest wykonywana automatycznie online.Proces nie przechowuje długoterminowej blokady blokowania, dlatego nie zablokuje to uruchamianie kwerendy lub aktualizacji.

Reorganizować indeksu, gdy indeks nie jest bardzo pofragmentowany.Można znaleźć w poprzedniej tabela wskazówki fragmentacji.Jednak gdy indeks jest bardzo pofragmentowany, będzie osiągnąć lepsze wyniki przy przebudowywanie indeksu.

Duże kompaktowanie typ danych obiektu

Oprócz reorganizacji jednego lub kilku indeksów, typy danych dużego obiektu (LOBs), które są zawarte w indeks klastrowany lub tabela źródłowa skompaktować są domyślnie, gdy indeks jest zreorganizowana.Typy danych image, text, ntext, varchar(max), nvarchar(max), varbinary(max), a xml typu danych są dużego obiektu. Kompaktowanie te dane mogą być przyczyną lepszego wykorzystania miejsca na dysku:

  • Reorganizacja określony indeks klastrowany zostanie compact wszystkie kolumny LOB, które znajdują się na poziomie poziom liścia (wiersze danych) indeksu klastrowanego.

  • Reorganizacja indeks nieklastrowany będzie Kompaktuj wszystkie LOB kolumny nonkey (włączone) w indeksie.

  • Po określeniu wszystkich wszystkie indeksy skojarzone z określonej tabela lub widoku są zreorganizowana, wszystkie kolumny LOB związanych z indeksem klastrowanym, podstawowej tabela, lub indeks nieklastrowany z kolumnami dołączone są skompaktować.

  • Klauzula LOB_COMPACTION jest ignorowana, jeśli LOB kolumn, które są niedostępne.

Przebudowywanie indeksu

Przebudowywanie indeksu spadnie indeksu i tworzy nową.W ten sposób fragmentacji zostanie usunięty, miejsca na dysku jest odzyskane w procesie kompaktowanie za pomocą ustawienia współczynnik wypełnienia określony lub istniejących stron i wierszy indeksu są zmianę kolejności w sąsiadujących ze sobą stron (w razie potrzeby przydzielania nowych stron).Może to zwiększyć wydajność dysku przez zmniejszenie liczby odczytów strona wymagane do uzyskania żądanych danych.

Celu indeksów klastrowanych i nieklastrowany można użyć następujących metod:

  • ALTER indeks z klauzula ODBUDOWYWANIA.Ta instrukcja zastępuje DBCC DBREINDEX instrukcji.

  • CREATE INDEX z klauzula DROP_EXISTING.

Każda metoda pełni tę samą funkcja, ale są zalety i wady, które należy rozważyć, jak pokazano w poniższej tabela.

Funkcja

ODBUDOWYWANIE INDEKSU ZMIANY

TWORZENIE INDEKSU Z DROP_EXISTING

Definicja indeksu mogą być zmieniane przez dodanie lub usunięcie kolumna klucz, zmienianie kolejności kolumn lub zmiana order.* sortowania kolumna

Nie

Tak**

Opcje indeks może być zestaw lub zmodyfikowane.

Tak

Tak

Więcej niż jednym indeksie może być ponownie w ramach pojedynczej transakcji.

Tak

Nie

Większość typów indeksu można ponownie online bez blokowania, uruchamianie kwerendy lub aktualizacji.

Tak

Tak

Może być repartitioned indeks partycjonowany.

Nie

Tak

Indeks można przenosić na inną grupa plików.

Nie

Tak

Wymagane jest dodatkowe tymczasowego miejsca na dysku.

Tak

Tak

Przebudowywanie indeks klastrowany odtwarza skojarzone ponownego zbudowania indeksów nie klastrowanych.

Nie

Jeśli słowo kluczowe ALL jest określony.

Nie

Chyba że zmienione definicji indeksu.

Indeksy wymuszanie ograniczenia klucz podstawowy i unikatowy może być przebudowany bez usuwania i odtwarzania ograniczenia.

Tak

Tak

Może być przebudowany indeksu jednej partycji.

Tak

Nie

* Jest indeks nieklastrowany można przekonwertować na typ indeks klastrowany określając CLUSTERED w definicji indeksu.Tę operację należy wykonać przy użyciu opcji ONLINE, ustawić na OFF.Konwersja z klastrów do nieklastrowany nie są obsługiwane niezależnie od ustawienia ONLINE.

** Jeżeli indeks jest ponownie tworzony przy użyciu tej samej nazwie, kolumn i kolejność sortowania, można pominąć operacji sortowania.Operacja odbudowywania sprawdza sortowania wierszy podczas budowania indeksu.

Przez pierwsze upuszczanie indeksu z instrukcja DROP INDEX i odtwarzania go za pomocą instrukcja CREATE INDEX oddzielnych, ponownie utwórz indeks.Wykonywanie tych operacji jako odrębne sprawozdania ma kilka wad, a firma Microsoft nie zaleca tego.

Wyłączanie nieklastrowany indeksy, aby oszczędzanie miejsca na dysku podczas odbudować operacji

Po wyłączeniu indeks nieklastrowany indeks wierszy danych są usuwane, ale definicji indeksu pozostaje metadane.Indeks jest włączone, gdy jest ponownie zbudowana.Gdy indeks nieklastrowany nie jest wyłączona, operacja odbudowywania wymaga za mało tymczasowego miejsca na dysku do przechowywania zarówno stare i nowe indeksu.Jednak wyłączając i przebudowa nieklastrowany indeks w oddzielnych transakcji, miejsca na dysku przez wyłączenie indeksu może zostać użyty ponownie przez następne rekonstrukcji lub innej operacji.Dodatkowy obszar, nie jest wymagane z wyjątkiem tymczasowego miejsca na dysku do sortowania, która jest zwykle 20 procent rozmiaru indeksu.W przypadku nieklastrowany indeks klucz podstawowy, ograniczenia klucz obcy aktywne, odwołujący się automatycznie zostaną wyłączone.Ograniczenia te muszą być włączone ręcznie po indeksu jest ponownie zbudowana.Aby uzyskać więcej informacji zobacz Disabling Indexes i Guidelines for Enabling Indexes and Constraints.

Odbudowa indeksów duże

Indeksy z więcej niż 128 zakresy są przebudowywane w dwóch oddzielnych faz: logicznych i fizycznych. W fazie logiczne istniejących jednostek alokacji używane przez indeks są oznaczane do dezalokacji, wierszy danych są kopiowane i sortowane, a następnie przeniesiony do nowej jednostki alokacji, utworzone w magazynie odbudowany indeksu.W fazie fizycznej jednostek alokacji, wcześniej zaznaczone do dezalokacji są fizycznie usunięty w skrócie transakcji, które pojawiają się w tle i nie wymagają wiele blokad.Aby uzyskać więcej informacji zobaczDropping and Rebuilding Large Objects.

Ustawianie opcji indeksu

Nie można określić opcje indeksu, gdy zmiana indeksu.Jednak następujące opcje indeksu zestaw podczas odbudować indeks za pomocą instrukcji ALTER ODBUDOWAĆ indeks lub CREATE INDEX WITH DROP_EXISTING:

PAD_INDEX

DROP_EXISTING (tylko CREATE INDEX)

FILLFACTOR

TRYB ONLINE

SORT_IN_TEMPDB

ALLOW_ROW_LOCKS

IGNORE_DUP_KEY

ALLOW_PAGE_LOCKS

STATISTICS_NORECOMPUTE

MAXDOP

Uwaga

Jeśli operacja sortowania nie jest wymagane lub sortowania mogą być wykonywane w pamięci, opcja SORT_IN_TEMPDB jest ignorowana.

Ponadto w instrukcja ALTER INDEX klauzula zestaw umożliwia ustawić następujące opcje indeksu bez przebudowywanie indeksu:

ALLOW_PAGE_LOCKS

IGNORE_DUP_KEY

ALLOW_ROW_LOCKS

STATISTICS_NORECOMPUTE

Aby uzyskać więcej informacji zobaczSetting Index Options.

Aby odbudować lub do reorganizacji indeksu

ALTER INDEX (języka Transact-SQL)

Aby odbudować indeks przez usunięcie i ponowne tworzenie indeksu w jednym kroku

CREATE INDEX (języka Transact-SQL)

Przykłady

A.Odbudowywanie indeksu

W poniższym przykładzie odtwarza pojedynczy indeks.

B.Odbudowywanie wszystkich indeksów w tabela i określając opcje

W poniższym przykładzie Określa słowo kluczowe ALL. To buduje ponownie wszystkie indeksy skojarzone z tabelą.Podano trzy opcje.

C.Reorganizacja indeks o kompaktowanie obiektów typu LOB

W poniższym przykładzie Reorganizuje jeden indeks klastrowany.Ponieważ indeks zawiera typ dane LOB na poziomie poziom liścia, instrukcja również Kompaktuje wszystkie strony, które zawierają dane dużego obiektu.Uwaga, że nie trzeba określać WITH (LOB_Compaction) opcji, ponieważ wartość domyślna jest włączone.