Autor: Brady Upton | aktualizacja: 2016-06-22 | Komentarze (5) | powiązane: więcej > fragmentacja i utrzymanie indeksu
Problem
wszyscy słyszeliśmy o fragmentacji bazy danych/indeksu (a jeśli nie, kontynuuj czytanie), ale co to jest? To jakiś problem? Skąd mam wiedzieć, czy znajduje się w mojej bazie danych?Jak to naprawić? Te pytania mogą być podpowiedzią samą w sobie, ale postaram się dać ci wyobrażenie o każdym z tych podpowiedzi.
rozwiązanie
bez wchodzenia w szczegóły, SQL Server przechowuje dane na stronach 8KB. Gdy wstawimy dane do tabeli, SQL Server przydzieli jedną stronę do przechowywania tych danych bez włożonych danych jest więcej niż 8KB, w których obejmowałoby wiele stron.Każda strona jest przypisana do jednej tabeli. Jeśli utworzymy 10 tabel, będziemy mieli 10 różnych stron.
gdy wstawisz dane do tabeli, dane najpierw trafią do pliku dziennika transakcji. Plik dziennika operacji jest sekwencyjnym rekordem, co oznacza, że podczas wstawiania, aktualizowania i usuwania rekordów dziennik rejestruje te transakcje Od początku do końca. Z drugiej strony Plik danych nie jest odpowiedni. Plik dziennika spłukuje dane do pliku danych tworząc strony w całym miejscu.
teraz, gdy mamy już pojęcie o tym, jak przechowywane są dane, Co to ma wspólnego zfragmentacją?
istnieją dwa rodzaje fragmentacji: fragmentacja wewnętrzna i fragmentacja zewnętrzna.
wewnętrzna fragmentacja SQL Server
wewnętrzna fragmentacja SQL Server jest spowodowana przez strony, które mają zbyt dużo wolnego miejsca.Załóżmy, że na początku dnia mamy tabelę z 40 stronami, które są 100% pełne, ale pod koniec dnia mamy tabelę z 50 stronami, które są tylko 80% pełne z powodu różnych instrukcji usuwania i wstawiania w ciągu dnia.Powoduje to problem, ponieważ teraz, gdy musimy przeczytać z tej tabeli, mamy toscan 50 stron zamiast 40, co powinno skutkować spadkiem wydajności.Zobaczmy szybki i brudny przykład.
Załóżmy, że mam następującą tabelę z kluczem podstawowym i nieklastrowanym indeksem na firstname i LastName:
w dalszej części tej porady omówię sposoby analizy fragmentacji, ale na razie możemy kliknąć prawym przyciskiem myszy indeks, kliknąć Właściwości i fragmentację, aby zobaczyć fragmentationand pełnię strony. Jest to zupełnie nowy indeks, więc ma fragmentację 0%.
WSTAWMY 1000 wierszy do tej tabeli:
INSERT INTO Person VALUES('Brady', 'Upton', '123 Main Street', 'TN', 55555)GO 1000
teraz sprawdźmy ponownie nasz indeks:
widać, że nasz indeks staje się 75% fragmentowany, a średni procent pełności stron (pełności strony) wzrasta do 80%. Ta tabela jest nadal tak mała, że 75% fragmentacji prawdopodobnie nie spowoduje żadnych problemów z wydajnością, ale wraz ze wzrostem wielkości tabeli i liczby stron może dojść do pogorszenia wydajności. Możesz również zobaczyć z powyższego Zdjęcia, że ta tabela przeszła od 0 stron do 4.
fragmentacja zewnętrzna SQL Server
fragmentacja zewnętrzna jest spowodowana przez strony, które są nie w porządku. Na początku dnia mamy doskonale uporządkowany stół. W ciągu dnia wydajemy setki instrukcji aktualizacji, prawdopodobnie pozostawiając trochę pustego miejsca na jednej stronie i próbując zmieścić miejsce na innych stronach. Oznacza to, że nasz magazyn musi przeskakiwać, aby uzyskać potrzebne dane, zamiast odczytywać je w jednym kierunku.
Analiza fragmentacji serwera SQL
czy fragmentacja jest problemem? Chyba tak. Jeśli możesz zapisać całą swoją bazę danych w pamięci lub jeśli twoja baza danych jest tylko do odczytu, nie martwiłbym się o to, ale większość z nas nie ma takiego luksusu.Pracowałem na tysiącach serwerów i analizowanie poziomów fragmentacji to jedna z pierwszych rzeczy, na które patrzę. W rzeczywistości, po prostu naprawiając fragmentację, widziałem nawet 200% poprawę wydajności zapytań.
mówiąc o analizie poziomów fragmentacji, możesz się zastanawiać, jak możemy to zrobić. Cóż, jest kilka sposobów…
DBCC SHOWCONTIG – ta funkcja jest stara i zostanie usunięta w przyszłych wersjach SQL Server, ale jeśli nadal używasz SQL Server 2000 lub poniżej, to pomoże. Zamiast pisać o tym, wskażę cię tutaj, albo możesz sprawdzić cynk Chada Boyda. Oba są dobrymi zasobami.
sys.dm_db_index_physical_stats – wprowadzony w SQL Server 2005, ten dynamiczny widok zarządzania (DMV) zwraca informacje o rozmiarze i fragmentacji dla danych i indeksów określonej tabeli lub widoku.
SELECT OBJECT_NAME(ips.OBJECT_ID) ,i.NAME ,ips.index_id ,index_type_desc ,avg_fragmentation_in_percent ,avg_page_space_used_in_percent ,page_countFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ipsINNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id)ORDER BY avg_fragmentation_in_percent DESC
jest to prawdopodobnie najczęściej stosowana metoda analizy fragmentacji. Na powyższym zrzucie ekranu widać, że mam Indeks o nazwie ix_rpcust_1 na tabeli theRPCust, która jest fragmentowana w 98.83%. Możesz zobaczyć więcej informacji o tym Dmvhere from Arshad Ali.
Third party software-Oprogramowanie innych firm pomoże zidentyfikować fragmenty, więc nie musisz. Greg Robidoux oferuje dobrą wskazówkę na temat SQL Defrag Managera Idera.
naprawianie fragmentacji serwera SQL
teraz, gdy znaleźliśmy fragmentację w bazie danych, jak to naprawić? Podobnie jak indeksy, istnieje wiele sposobów.
plany konserwacji SQL Server – plany konserwacji są dostarczane z SQLServer i są dobre dla niektórych zadań. Utrzymanie indeksu moim zdaniem nie jest jednym z nich. Możesz dodać zadanie rebuild index lub zreorganizować zadanie index do maintenanceplan, ale problem polega na tym, że nie możesz tak naprawdę zastosować żadnej logiki do planu. Bez logowania plan odbuduje / zreorganizuje każdy indeks. Niektóre indeksy mogą nie być fragmentowane, więc nie trzeba ich odbudowywać. Niektóre indeksy mogą mieć minimalne fragmentationso byłoby w porządku z reorganizacją. Nie ma sposobu, aby określić to w planowaniu konserwacji. InSQL Server 2016 istnieją nowe opcje zarządzania fragmentacją.
skrypt Niestandardowy – niestandardowe skrypty są prawdopodobnie sposobem, aby przejść, ponieważ można zastosować logikę, aby sprawdzić fragmentację indeksu, a następnie zdecydować, czy pominąć indeks, zreorganizować lub odbudować. Używam skryptów hallengrena i robią dokładnie to, czego potrzebuję. MSSQLTips.com ma również theseresources-SQL Server Fragmentation i Index Maintenance Tips.
Third party software-Oprogramowanie innych firm pomoże w defragmentacji indeksów, których nie musisz.
kolejne kroki
- ta wskazówka daje ci wyobrażenie o kilku komponentach zaangażowanych w indexfragmentację. MSSQLTips.com oferuje bardziej wyczerpujące wskazówki dotyczące każdego z tematów, o których tu wspomniałem.
- Sprawdź tutorial fragmentacji serwera SQL Boyd, który oferuje świetny wgląd w wiele tematów dotyczących fragmentacji
Ostatnia aktualizacja: 2016-06-22
About the author
Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.View all my tips
- More SQL Server DBA Tips…