Reorganizowanie i odbudowa indeksów
Aparat baz danych programu SQL Server Automatycznie zachowuje indeksy po każdej insert, update lub operacji usuwania są podstawowych danych.Nad czas modyfikacje mogą powodować informacje indeksu mogą być przechowywane w bazie danych (pofragmentowanych).Fragmentacja występuje indeksów mają stron, w których logicznych porządkowania, na podstawie wartości klucz nie pasuje fizycznej kolejności wewnątrz pliku danych.Indeksy w dużym stopniu fragmentacji może obniżyć wydajność kwerendy i aplikacji odpowiada zbyt wolno.Aby uzyskać więcej informacji, zobacz witryna sieci Web Microsoft.
Fragmentacja indeksu można zaradzić poprzez restrukturyzację indeks lub odbudowanie indeksu.Zbudowany na schemat partycji indeksy podzielonym na partycje można użyć albo tych metod na zakończenie indeksu lub na jednej partycji indeksu.
Wykrywanie fragmentacji
Pierwszym krokiem przy podejmowaniu decyzji metoda defragmentacji jest analizowanie indeks w celu określenia stopnia fragmentacji.Za pomocą funkcja systemu sys.dm_db_index_physical_stats, możesz wykrywać fragmentacja określonego indeksu, wszystkie indeksy tabela lub indeksowany widok, wszystkie indeksy w bazie danych lub wszystkie indeksy w przypadku wszystkich baz danych.Dla indeksów podzielonym na partycje sys.dm_db_index_physical_stats zawiera także informacje fragmentacji dla każdej partycji.
zestaw wyników Zwrócone przez sys.dm_db_index_physical_stats funkcja zawiera następujące kolumny.
Kolumna |
Opis |
|---|---|
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 jeden fragment do indeksu. |
Po stopnia rozdrobnienia jest znany, skorzystaj z poniższej tabela, aby określić najlepszą metoda poprawiania fragmentacji.
avg_fragmentation_in_percent wartości |
Korygującychinstrukcja |
|---|---|
>5% and < = 30% |
REORGANIZACJA INDEKSU ALTER |
> 30% |
PRZEBUDUJ INDEKS Z ALTER (= ONLINE NA) * |
* Przebudowywanie indeksu mogą być wykonywane online lub offline.Reorganizowanie indeks jest zawsze wykonywana online.Do osiągnięcia podobnych opcji reorganizacja dostępności, należy przebudować indeksy w trybie online.
Wartości te przewidują Wstępna orientacyjna określania punktu, jaką należy przełączanie między ALTER REORGANIZOWAĆ INDEKSU i zmieniać ODBUDOWANIE INDEKSU.Jednak rzeczywiste wartości mogą się różnić od przypadek.Jest ważne, eksperymentowania ustalić próg najlepszych dla środowiska.
Bardzo niski poziom rozdrobnienia (mniej niż 5 procent) nie powinny być kierowane przez tych poleceń, ponieważ korzyści z niewielką ilość fragmentacji usuwanie jest prawie zawsze znaczne zrównoważone przez koszt reorganizacji lub odbudowanie indeksu.
Ostrzeżenie
Ogólnie rzecz biorąc fragmentacji na małych indeksy często nie jest kontrolowane.Strony małych indeksy są przechowywane na mieszanych zakresów.Mieszane zakresy są współużytkowane przez maksymalnie osiem obiektów tak fragmentacji małych indeksu nie mogą być ograniczone po reorganizacji lub odbudowanie indeksu.Aby uzyskać więcej informacji na temat mieszanych zakresy Zobacz Opis stron i zakresów.
Przykład
W następujących przykładowych kwerendach sys.dm_db_index_physical_stats funkcja dynamicznego zarządzania zwraca średnią fragmentacji dla wszystkich indeksów na Production.Product tabela.Korzystając z poprzedniej tabela, zaleca się rozdzielczości do reorganizowania PK_Product_ProductID i przebudować inny indeksy.
USE AdventureWorks2008R2;
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)
Reorganizowanie indeksu
Do reorganizowania jednego lub kilku indeksów, należy użyć instrukcja ALTER INDEKSU z klauzula REORGANIZACJA.Ta instrukcja zastępuje instrukcji DBCC INDEXDEFRAG.Do reorganizowania pojedynczą partycję indeks partycjonowany, należy użyć klauzula PARTYCJI indeksu ALTER.
Reorganizowanie defragmentuje indeks poziom liścia poziom indeksy klastrowane i nieklastrowany na tabele i widoki fizycznie kolejności poziom liścia-poziom stron, aby dostosować porządek logiczny (od lewej do prawej) poziom liścia węzłów.Posiadające strony w kolejności zwiększa wydajność skanowanie indeksu.Indeks jest zmieniono ich organizację w ramach istniejących stron przydzielone są przydzielane nie nowe strony.Jeżeli indeks obejmuje więcej niż jeden plik, pliki są zreorganizowanych na czas.Strony nie są migrowane między plikami.
Również reorganizacji kompaktuje stron indeksowych.Żadnych pustych stron utworzonych przez to kompaktowanie są usuwane, zapewniając dodatkowe dostępne miejsce na dysku.Kompaktowanie opiera się na wartość współczynnik wypełnienia w sys.indexes wykazu widoku.
Proces reorganizacja używa minimalnych zasobów systemowych.Także reorganizacja jest wykonywana automatycznie online.Proces nie przechowuje blokad blokowania długoterminowych; Dlatego go nie będzie blok uruchamianie kwerendy lub aktualizacji.
Reorganizować indeksu, gdy indeks nie jest intensywnie pofragmentowany.Zobacz w poprzedniej tabela wytycznych fragmentacji.Jeżeli indeks jest silnie pofragmentowane, będzie jednak osiągnąć lepsze wyniki przez odbudowanie indeksu.
Kompaktowanie typ danych obiektu duże
Oprócz reorganizowanie jednego lub kilku indeksów, typy danych obiektu dużych (obiektów LOB), zawarte w indeks klastrowany lub tabela źródłowa skompaktować domyślnie, gdy zmieniono ich organizację indeksu.The data types image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml are large object data types.Kompaktowanie danych mogą spowodować lepsze wykorzystanie miejsca na dysku:
Reorganizowanie określony indeks klastrowany zostanie compact wszystkie kolumny LOB, które są zawarte w poziom liścia poziom (rzędy danych) indeks klastrowany.
Reorganizowanie indeks nieklastrowany zostanie skompaktować wszystkie kolumny LOB, które są kolumnami (dołączone) w indeksie.
Gdy wszystkie określone zmieniono ich organizację wszystkie indeksy skojarzone z określoną tabela lub widok i wszystkie kolumny LOB skojarzonych z indeksu klastrowanego tabela źródłowa, lub nieklastrowany indeks z uwzględnionych kolumn są skompaktować.
Klauzula LOB_COMPACTION jest ignorowany, jeśli nie ma kolumn LOB.
Przebudowywanie indeksu
Przebudowywanie indeksu spadnie indeksu i tworzy nowy.W ten sposób usuwane fragmentacji, miejsca na dysku jest regeneracji poprzez kompaktowanie stron przy użyciu ustawienia współczynnik wypełnienia określony lub istniejące i wiersze indeksu są zmianę kolejności stron ciągłych (przydzielanie nowych stron, w razie potrzeby).Wydajność dysku można zwiększyć przez zmniejszenie liczby odczytów strona wymagane do uzyskania żądanych danych.
Następujące metody służą do indeksów klastrowanych i nieklastrowany:
ZMIENIĆ indeks z klauzula REKONSTRUKCJI.Ta instrukcja zastępuje DBCC DBREINDEX instrukcji.
Tworzenie INDEKSU z klauzula DROP_EXISTING.
Każda metoda pełni tę samą funkcja, ale istnieją zalety i wady, należy rozważyć, jak pokazano w poniższej tabela.
Funkcja |
ALTER PRZEBUDUJ INDEKS |
TWORZENIE INDEKSU Z DROP_EXISTING |
|---|---|---|
Definicja indeksu można zmieniać, dodając lub usuwając kolumna klucz, zmiana kolejności kolumn lub zmienianie kolumna kolejność sortowania. * |
Nie |
Tak ** |
Opcje indeksu zestaw lub zmodyfikowany. |
Tak |
Tak |
Więcej niż jednym indeksie można zrekonstruować w pojedynczej transakcji. |
Tak |
Nie |
Większość typów indeksu można zrekonstruować online bez blokowania kwerendy lub aktualizacji. |
Tak |
Tak |
Zmodyfikowanie można indeksu podzielonym na partycje. |
Nie |
Tak |
Indeks można przenieść do innej grupa plików. |
Nie |
Tak |
Wymagane jest dodatkowe tymczasowego miejsca na dysku. |
Tak |
Tak |
Przebudowywanie indeks klastrowany Przebudowuje indeksy skojarzone nieklastrowany. |
Nie Jeśli nie wszystkie określonego słowa kluczowego. |
Nie O ile zmieniona definicja indeksu. |
Wymuszanie ograniczeń klucza podstawowego i unikatowe indeksy można zrekonstruować bez upuszczanie i odtwarzania ograniczenia. |
Tak |
Tak |
Można zrekonstruować indeksu jednej partycji. |
Tak |
Nie |
* Indeks nieklastrowany można konwertować na typ indeks klastrowany określając CLUSTERED w definicji indeksu.Tej operacji należy wykonać z opcją ONLINE zestaw do OFF.Do zbudowania klastra konwersja nie jest obsługiwane niezależnie od ustawienia ONLINE.
** Jeżeli indeks jest utworzony przy użyciu tej samej nazwy, kolumn i kolejność sortowania, można pominąć operacji sortowania.Operację rekonstrukcji sprawdza sortowania wierszy podczas budowania indeksu.
Można również odbudować indeks pierwszego upuszczanie indeksu z instrukcją DROP INDEKSU i odtwarzania go z osobnej instrukcja CREATE INDEX.Wykonywania tych operacji jako oddzielne sprawozdania ma kilka wad, a firma Microsoft nie zaleca tego.
Wyłączanie zbudowania indeksów, aby zaoszczędzić miejsce na dysku podczas odbudować operacji
Po wyłączeniu indeks nieklastrowany indeks wierszy danych są usuwane, ale definicja indeksu pozostaje metadane.Indeks jest włączone, gdy jest odbudowywany.Gdy indeks nieklastrowany nie jest wyłączona, operacja Odbuduj wymaga tymczasowego miejsca na dysku do przechowywania zarówno starego i nowego indeksu.Jednakże wyłączając i odbudowy indeks nieklastrowany w oddzielnej transakcji, miejsca na dysku przez wyłączenie indeksu mogą być ponownie użyte przez kolejne rekonstrukcji lub innych operacji.Nie dodatkowe miejsce jest wymagane z wyjątkiem tymczasowego miejsca na dysku do sortowania, który jest zwykle 20 procent rozmiaru indeksu.Jeśli indeks nieklastrowany znajduje się na klucz podstawowy, wszystkie aktywne, odwołujący się ograniczenia klucza OBCEGO zostanie automatycznie wyłączona.Ograniczenia te muszą być włączone ręcznie po indeks zostanie przebudowany.Aby uzyskać więcej informacji, zobacz Wyłączanie indeksów i Zasady dotyczące włączania indeksy i ograniczenia.
Odbudowa indeksów duże
Odbudowa indeksów o więcej niż 128 zakresów dwa etapy: logiczną i fizyczną.W fazie logiczne istniejących jednostek alokacji używane przez indeks są oznaczane do dezalokacji, wiersze danych są kopiowane i sortowane i przenoszone do nowych jednostek alokacji utworzone w magazynie skompilowane indeksu.W fazie fizycznej jednostek alokacji oznaczone wcześniej dla dezalokacji są fizycznie usunięte w skrócie transakcji, które pojawiają się w tle i nie wymagają wiele blokad.Aby uzyskać więcej informacji, zobacz Upuszczanie i odbudowując dużych obiektów.
Ustawianie opcji indeksu
Nie można określić opcje indeksu, gdy reorganizowanie indeksu.Jednakże następujące opcje indeksu zestaw podczas odbudować indeks za pomocą ALTER ODBUDOWAĆ indeks lub utworzyć indeks Z DROP_EXISTING:
PAD_INDEX |
DROP_EXISTING (tylko utworzyć indeks) |
FILLFACTOR |
ONLINE |
SORT_IN_TEMPDB |
ALLOW_ROW_LOCKS |
IGNORE_DUP_KEY |
ALLOW_PAGE_LOCKS |
STATISTICS_NORECOMPUTE |
MAXDOP |
Ostrzeżenie
Jeśli operacja sortowania nie jest wymagana lub sortowania mogą być wykonywane w pamięci, opcja SORT_IN_TEMPDB jest ignorowana.
Dodatkowo klauzula zestaw w instrukcja ALTER INDEKSU pozwala ustawić następujące opcje indeksu bez odbudowanie indeksu:
ALLOW_PAGE_LOCKS |
IGNORE_DUP_KEY |
ALLOW_ROW_LOCKS |
STATISTICS_NORECOMPUTE |
Aby uzyskać więcej informacji, zobacz Ustawianie opcji indeksu.
Aby odbudować lub reorganizować indeksu
Aby odbudować indeks przez usunięcie i ponowne tworzenie indeksu w jednym kroku
Przykłady
A.Przebudowywanie indeksu
Poniższy przykład odtwarza indeks.
USE AdventureWorks2008R2;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO
B.Odbudowywanie wszystkie indeksy tabela i określając opcje
W poniższym przykładzie Określa słowo kluczowe ALL.Przebudowuje indeksy wszystkich skojarzonych z tabela.Podano trzy opcje.
USE AdventureWorks2008R2;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);
GO
C.Reorganizowanie indeks o kompaktowanie obiektów typu LOB
Poniższy przykład Reorganizuje jeden indeks klastrowany.Ponieważ indeks zawiera typ dane LOB w poziom liścia poziomu, instrukcja również Kompaktuje wszystkie strony zawierające dane dużego obiektu.Uwaga, że nie trzeba określić Z (LOB_Compaction) opcji, ponieważ wartość domyślna jest włączone.
USE AdventureWorks2008R2;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO
Zobacz także