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
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.
See Also