Czy defragmentacja indeksów SQL w środowisku SAN ma jakieś zalety?

16

Nasz serwer SQL działa w sieci SAN. Zawiera dziesiątki baz danych OLTP, niektóre z kilkoma tabelami zawierającymi ponad 1 milion rekordów.

Co tydzień uruchamiamy skrypty konserwacji indeksu Oli Hallengren i za każdym razem działają one przez kilka godzin. W oparciu o próg fragmentacji skrypt reorganizuje lub ponownie indeksuje indeks. Zauważyliśmy, że podczas ponownego indeksowania pliki dziennika stają się ogromne, co prowadzi do nadmiernego zużycia przepustowości podczas przesyłania dziennika.

Potem pojawia się artykuł Brenta Ozara, w którym mówi on, aby przestał się martwić indeksami SQL :

Twoje dyski twarde są współużytkowane z innymi serwerami, które jednocześnie wysyłają żądania dysków, więc dyski zawsze będą przeskakiwać wszędzie, aby uzyskać dane. Defragmentacja indeksów jest po prostu bezsensownym zajęciem.

Googlowanie tego pytania prowadzi do różnych opinii, najczęściej popartych argumentami, które wydają się zbyt krótkie lub słabe. Naszym wstępnym planem jest dostosowanie progu fragmentacji w naszym skrypcie konserwacji, aby reorganizował się znacznie częściej niż reindeksował.

Jaki jest ostateczny werdykt? Czy warto defragmentować indeksy SQL w sieci SAN, biorąc pod uwagę obciążenia związane z wykonywaniem cotygodniowych zadań konserwacyjnych?

— dev_etter
źródło

10

Strategie defragmentacji pomagają poprawić szybkość skanowania do / z dysku .

Różnorodność opinii wynika z faktu, że idealna strategia defragmentacji środowiska powinna zależeć od wielu różnych czynników. W grze występuje również wiele potencjalnych warstw fragmentacji .

Mówienie, że twoje bazy danych są przechowywane w sieci SAN, nie wystarcza informacji. Na przykład:

Czy pliki bazy danych są przechowywane w oddzielnych fizycznych grupach RAID lub w tej samej grupie RAID? Jakie inne procesy są aktywne na tym samym urządzeniu? Czy twoje pliki kopii zapasowej też tam się kończą? Być może będziesz musiał poprosić administratora SAN o te informacje, ponieważ nie zawsze są one przejrzyste.
Jakie są wzorce dostępu do baz danych? OLTP ma ogólnie dostęp losowy, ale czasami aplikacja jest zadowolona ze skanowania tabeli i nie można zmienić jej zachowania (aplikacja ISV). Czy aplikacje są głównie do odczytu, głównie do zapisu, czy gdzieś pomiędzy?
Czy w okresie odzyskiwania / przełączania awaryjnego obowiązują umowy SLA dotyczące wydajności ?

Post Brenta zakłada, że istnieje jedna gigantyczna pula pamięci i wszystko ją dzieli. Oznacza to, że dyski fizyczne rzadko są bezczynne, a zatem większość dostępu jest losowa. Jeśli taka jest Twoja sytuacja, to ta rada ma zastosowanie i w większości się z nią zgadzam. Chociaż tego typu strategią jest znacznie łatwiej zarządzać, niekoniecznie (a) to, co masz w swoim środowisku lub (b) jakie jest najlepsze rozwiązanie dla twojego środowiska.

Jeśli utrzymanie indeksu jest uciążliwe, rozważ robienie go mniej agresywnie i / lub amortyzuj koszty w ciągu tygodnia (tj. Uruchom lekką konserwację raz dziennie, zamiast intensywnej konserwacji raz w tygodniu).

Możesz także włączyć SortInTempdbopcję potencjalnego ograniczenia rejestrowania danych w bazach danych użytkowników.

— Jon Seigel
źródło

Wow, dokładna odpowiedź. Prawdopodobnie zajmie mi trochę czasu, aby przeprowadzić wszystkie badania, ale nie wątpię, że poprowadzisz mnie właściwą drogą. Nasza obecna strategia polega na tym, aby konserwacja przebiegała mniej agresywnie, zarówno z punktu widzenia przebudowy, jak i reorga, myślę, że w tym pytaniu źle to zrozumiałem. Stamtąd przeprowadzę więcej badań nad resztą wymienionych przez ciebie czynników.

— dev_etter

1

@dev_etter: Wymieniłem tylko kilka czynników; jest o wiele więcej. Głównym punktem jest pierwsze zdanie. Jeśli będziesz o tym pamiętać, zastanawiając się przez swoje otoczenie, będzie to właściwie kierowało twoją decyzją. Wszystko z tego wynika. (Ponadto wszystko to zakłada, że nie dotyczy to dysków SSD.)

— Jon Seigel

FWIW, całkowicie przeoczyłem coś - rzeczywisty skrypt na etapie zadania (zamiast źródła) został skonfigurowany tak, aby adresować każdy indeks, który miał minimalny odsetek fragmentacji wynoszący 1. Zwiększyłem to do 15, a także podniosłem próg odbudowy z 30 do 35. Praca trwa teraz nieco ponad 3 godziny, a nie 8. Twoja sugestia, aby być mniej agresywnym, była poprawna. Moja wina polegała na tym, że myślałem, że zadanie zostało już wdrożone jako mniej agresywne. Takie podejście jest prawdopodobnie dla nas najlepsze, wciąż dotykaj i idź, ale już złagodziło trochę bólu.

— dev_etter

@JonSeigel Całkowicie zgadzam się z tą odpowiedzią. Podczas moich podróży widzę, jak większość DBA dzieli jedną pulę lub przynajmniej tablicę na tym samym poziomie RAID. Miałem DBA o 3 rano 24x7 tylko po to, aby defragmentować pojedyncze aplikacje grup ponad 100 baz danych TB ... i po co dokładnie? Na dyskach mieliśmy całkowicie losowe operacje we / wy, a opóźnienie wynosiło 15 ms. W tym momencie powinienem wskazać 15ms i powiedzieć programistom, żeby zostawili mnie w spokoju.

— ooutwire

2

Idealnie byłoby, gdybyś reorganizował / reindeksował TYLKO te indeksy, które wymagają uwagi, w przeciwnym razie marnujesz zasoby i potencjalnie powodujesz inne problemy.

Musisz ustalić poziom bazowy wydajności i za każdym razem, gdy wprowadzasz zmiany, porównaj zmianę wydajności z linią bazową, aby ustalić, czy warto ją wprowadzić.

— Jimbo
źródło

Nasza natychmiastowa strategia polega właśnie na tym - poprawimy ustawienia zmiennych minFragmentation i odbudujemy zmienne progowe w tym skrypcie: sqlfool.com/2011/06/index-defrag-script-v4-1

— dev_etter

0

Okej, pytanie dotyczy indeksów baz danych, które są konstrukcją pliku lub zestawu plików. Przeczytanie powyższych odpowiedzi doprowadziłoby osobę do przekonania, że mówimy o fragmentacji na poziomie dysku, a nie o indeksach w pliku. Te całkowicie odrębne tematy.

Podejściem krótkowzrocznym jest wydajność podczas pobierania danych, a baza danych OLTP ulegnie poprawie, jeśli indeksy zostaną zdefragmentowane lub odbudowane. Odpowiedź brzmi tak! Należy jednak pamiętać, że fragmentacja dysku jest również czynnikiem.

Najniższy całkowity „koszt”? Wykonaj konserwację bazy danych. Drugi najniższy koszt, odłącz bazę danych, przenieś ją gdzie indziej, ponownie sformatuj dyski i postępuj zgodnie z najlepszymi praktykami dotyczącymi wyrównania partycji dysku http://msdn.microsoft.com/en-us/library/dd758814.aspx . Last but not least, użyj zaawansowanego defragmentatora innej firmy, takiego jak Diskkeeper.

Należy pamiętać, że jest to zalecane TYLKO w przypadku pamięci typu NTFS (np. Windows OS) i nie stanowi to aprobaty dla żadnego produktu ani nie jestem powiązany z Condusiv Technologies lub jej spółkami zależnymi.

— IryDBA2791
źródło

2

Prawdopodobnie chcesz uniknąć kategorycznego powiedzenia „Odpowiedź brzmi TAK!” do problematycznych przestrzeni, które zostały obszernie omówione przez inne plakaty. Chociaż może być prawdą, że czasami odpowiedź brzmi „Tak”, jak pokazał Brent Ozar w swoim blogu, nie zawsze tak jest.

— Max Vernon