Kiedy używać sort_in_tempdb podczas odbudowywania indeksów?

Zastanawiamy się, czy użyć opcji SORT_IN_TEMPDB dla naszych tabel DW. Rozumiem, że przy tej opcji jest więcej zapisów, chociaż są one bardziej sekwencyjne. Mamy SAN (który czasami był bardzo wolny), więc w naszym przypadku chcemy ograniczyć liczbę zapisów tak bardzo, jak to możliwe. Wierzę, że tempdb znajduje się na osobnej jednostce LUN (zestaw dysków).

Mamy dużo miejsca na dysku w naszym pliku danych i pliku tempdb. Czy w takim przypadku skorzystalibyśmy z SORT_IN_TEMPDB?

Jedną rzeczą, która mnie uderzyła, był ten komentarz do tej odpowiedzi

Podczas przebudowywania indeksu potrzeba dwa razy więcej miejsca niż indeks + 20% do sortowania. Ogólnie rzecz biorąc, aby odbudować każdy indeks w bazie danych, potrzebujesz tylko 120% swojego największego indeksu w bazie danych. Jeśli używasz SORT_IN_TEMPDB, wygrywasz tylko 20%, nadal potrzebujesz dodatkowych 100% w pliku danych. Co więcej, użycie sortowania w tempdb drastycznie zwiększa obciążenie IO, ponieważ zamiast jednokrotnego zapisu indeksu do pliku danych, teraz zapisujesz go raz w tempdb, a następnie zapisujesz w pliku danych. To nie zawsze jest idealne.

Zdecydowanie nie chcemy zwiększać obciążenia IO dzięki naszej wolnej / prawdopodobnie źle skonfigurowanej sieci SAN.

Jaki byłby najlepszy sposób na przetestowanie tego? Po prostu odbudowując tabelę z opcją i bez opcji i rejestrując czasy?

Edycja : Mamy 8 plików tempdb, każde 15 GB. Mamy ustawione flagi TF 1117/1118 i IFI jest włączony. Obecnie wykonujemy mieszankę przebudowy z opcją sort_in_tempdb i bez niej.

Dzięki!

SQL Server 2012 Enterprise

SORT_IN_TEMPDBoznacza, że ​​serwer SQL użyje tempdbdo przydzielenia przestrzeni tymczasowej, a nie do przydzielenia miejsca w bazie danych użytkowników, której indeks jest odbudowywany. Oznacza to, że będziesz potrzebować mniej wolnego miejsca w bazie danych użytkowników podczas operacji odbudowywania indeksu i więcej wolnego miejsca w tempdb.

Daje to większą przewagę, gdy tempdb znajduje się na innym zestawie dysków (LUN) niż baza danych użytkowników.

Z opcji SORT_IN_TEMPDB - BOL :

Jeśli opcja SORT_IN_TEMPDB jest ustawiona na WŁĄCZ, a tempdb znajduje się na oddzielnym zestawie dysków od docelowej grupa plików, w pierwszej fazie odczyty stron danych mają miejsce na innym dysku niż zapisy do obszaru roboczego sortowania w tempdb. Oznacza to, że odczyty kluczy danych na dysku są generalnie kontynuowane bardziej szeregowo na dysku, a zapisy na dysku tempdb są również ogólnie szeregowe, podobnie jak zapisy w celu zbudowania końcowego indeksu. Nawet jeśli inni użytkownicy korzystają z bazy danych i uzyskują dostęp do oddzielnych adresów dysków, ogólny wzorzec odczytów i zapisów jest bardziej wydajny, gdy podano SORT_IN_TEMPDB, niż gdy nie jest.

Upewnij się, że przeczytałeś wymagania dotyczące miejsca na dysku, gdy SORT_IN_TEMPDB jest włączony .

wolny / prawdopodobnie źle skonfigurowany SAN

Znasz punkt bólu. Dlaczego nie pracujesz z administratorem SAN, aby to naprawić? Błędnie skonfigurowana i / lub wolna sieć SAN spowoduje różnego rodzaju problemy, takie jak spowolnienie .

Kilka ważnych punktów, na które należy zwrócić uwagę:

• Użyj MAXDOP = 1 podczas przebudowy indeksu online - w przeciwnym razie spowoduje to zwiększenie fragmentacji.
• Inteligentnie przeprowadź / przebuduj swoje indeksy na podstawie poziomu fragmentacji, a także fragmentów strony.
• Przeczytaj doświadczenie SQLFool w tworzeniu indeksu 60 GB
• Upewnij się, że poprawnie skonfigurowałeś tempdb - TF 1117 (ZWIĘKSZ WSZYSTKIE PLIKI W FILEGROUP RÓWNIEŻ) i 1118 (TYLKO PEŁNE ZAKRESY) i masz więcej plików danych tempdb .

Jaki byłby najlepszy sposób na przetestowanie tego?

Tak, musisz go przetestować, analizując stan oczekiwania podczas odbudowywania indeksu zi bez niego SORT_IN_TEMPDB. Zmierz również czas działania, a robiąc to w PROD, upewnij się, że robisz to w oknie konserwacji lub mniejszej aktywności serwera. Sprawdź także swoje dane do odczytu / zapisu i opóźnienie dziennika .

Nie jestem pewien, czy masz Natychmiastową inicjalizację pliku , ale przyniesie to korzyści podczas przywracania, podczas automatycznego wzrostu plików danych i podczas tworzenia nowej bazy danych (wystarczy wspomnieć o kompletności).