Zalecenia dotyczące zmniejszenia rywalizacji o alokację w bazie danych SQL Server tempdb

  • Artykuł

Ten artykuł pomaga rozwiązać problem polegający na tym, że występuje poważne blokowanie, gdy na serwerze występuje duże obciążenie.

Oryginalna wersja produktu: SQL Server
Oryginalny numer KB: 2154845

Symptomy

Na serwerze z uruchomioną usługą Microsoft SQL Server można zauważyć poważne blokowanie, gdy na serwerze występuje duże obciążenie. Dynamiczne widoki zarządzania [sys.dm_exec_request lub sys.dm_os_waiting_tasks] wskazują, że te żądania lub zadania oczekują na zasoby bazy danych tempdb . Ponadto typ oczekiwania to PAGELATCH_UP, a zasób oczekiwania wskazuje strony w bazie danych tempdb. Te strony mogą mieć format 2:1:1, 2:1:3 itd. (strony PFS i SGAM w bazie danych tempdb).

Uwaga

Jeśli strona jest równomiernie podzielna przez 8088, jest to strona PFS. Na przykład strona 2:3:905856 jest usługą PFS w file_id=3 w bazie danych tempdb.

Następujące operacje intensywnie używają bazy danych tempdb :

  • Powtarzające się operacje tworzenia i upuszczania tabel tymczasowych (lokalnych lub globalnych).
  • Zmienne tabeli, które używają bazy danych tempdb do przechowywania.
  • Tabele robocze skojarzone z kursorami.
  • Tabele robocze skojarzone z klauzulą ORDER BY.
  • Tabele robocze skojarzone z klauzulą GROUP BY.
  • Pliki robocze skojarzone z planami hash.

Te działania mogą powodować problemy z rywalizacją.

Przyczyna

Gdy baza danych tempdb jest intensywnie używana, SQL Server mogą wystąpić rywalizacje podczas próby przydzielenia stron. W zależności od stopnia rywalizacji może to spowodować, że zapytania i żądania obejmujące bazę danych tempdb na krótko przestają odpowiadać.

Podczas tworzenia obiektu dwie (2) strony muszą być przydzielone z zakresu mieszanego i przypisane do nowego obiektu. Jedna strona dotyczy mapy alokacji indeksu (IAM), a druga dla pierwszej strony obiektu. SQL Server śledzi zakresy mieszane przy użyciu strony Udostępniona mapa alokacji globalnej (SGAM). Każda strona SGAM śledzi około 4 gigabajtów danych.

Aby przydzielić stronę z zakresu mieszanego, SQL Server musi przeskanować stronę Wolnego miejsca (PFS), aby określić, która strona mieszana jest bezpłatna do przydzielenia. Strona PFS śledzi wolne miejsce dostępne na każdej stronie, a każda strona PFS śledzi około 8000 stron. Utrzymywana jest odpowiednia synchronizacja w celu wprowadzania zmian na stronach PFS i SGAM; i które mogą zatrzymać inne modyfikatory na krótki czas.

Gdy SQL Server wyszukuje mieszaną stronę do przydzielenia, zawsze uruchamia skanowanie na tym samym pliku i stronie SGAM. Powoduje to intensywne rywalizacje na stronie SGAM, gdy trwa kilka alokacji stron mieszanych. Może to spowodować problemy udokumentowane w sekcji Objawy .

Uwaga

Działania cofania alokacji muszą również modyfikować strony. Może to przyczynić się do zwiększenia rywalizacji.

Aby dowiedzieć się więcej na temat różnych mechanizmów alokacji używanych przez SQL Server (SGAM, GAM, PFS, IAM), zobacz sekcję Odwołania.

Rozwiązanie

Zwiększ liczbę plików danych bazy danych tempdb, które mają równe rozmiary

Jeśli na przykład rozmiar pojedynczego pliku danych bazy danych tempdb wynosi 8 GB, a rozmiar pliku dziennika wynosi 2 GB, zaleca się zwiększenie liczby plików danych do ośmiu (8) (każdy z 1 GB w celu zachowania równego rozmiaru) i pozostawienie pliku dziennika w następującym stanie. Posiadanie różnych plików danych na oddzielnych dyskach zapewniłoby dodatkową korzyść w zakresie wydajności. Nie jest to jednak wymagane. Pliki mogą współistnieć na tym samym woluminie dysku.

Optymalna liczba plików danych bazy danych tempdb zależy od stopnia rywalizacji w bazie danych tempdb. Jako punkt początkowy można skonfigurować, aby baza danych tempdb była co najmniej równa liczbie procesorów logicznych przypisanych do SQL Server. W przypadku systemów wyższej klasy numer początkowy może wynosić osiem (8). Jeśli rywalizacja nie zostanie zmniejszona, może być konieczne zwiększenie liczby plików danych.

Zalecamy użycie równego rozmiaru plików danych. SQL Server 2000 z dodatkiem Service Pack 4 (SP4) wprowadzono poprawkę, która używa algorytmu działania okrężnego do mieszanych alokacji stron. W związku z tym ulepszeniem plik początkowy jest inny dla każdej kolejnej alokacji strony mieszanej (jeśli istnieje więcej niż jeden plik). Nowy algorytm alokacji dla SGAM jest czystym działaniem okrężnym i nie uwzględnia proporcjonalnego wypełnienia w celu utrzymania szybkości. Zalecamy utworzenie wszystkich plików danych bazy danych tempdb o tym samym rozmiarze.

Jak zwiększenie liczby plików danych bazy danych tempdb zmniejsza rywalizację

Na poniższej liście wyjaśniono, jak zwiększenie liczby plików danych bazy danych tempdb , które mają równe rozmiary, zmniejsza rywalizację:

  • Jeśli masz jeden plik danych dla bazy danych tempdb, masz tylko jedną stronę GAM i jedną stronę SGAM dla każdego 4 GB miejsca.

  • Zwiększenie liczby plików danych o tych samych rozmiarach dla bazy danych tempdb skutecznie tworzy co najmniej jedną stronę GAM i SGAM dla każdego pliku danych.

  • Algorytm alokacji dla gamy przydziela jeden zakres na raz (osiem ciągłych stron) z liczby plików w sposób okrężny przy jednoczesnym poszanowaniu proporcjonalnego wypełnienia. W związku z tym, jeśli masz 10 plików o równych rozmiarach, pierwsza alokacja pochodzi z pliku File1, druga z pliku File2, trzecia z pliku File3 itd.

  • Rywalizacja o zasoby strony PFS jest ograniczona, ponieważ osiem stron jednocześnie jest oznaczonych jako PEŁNE, ponieważ funkcja GAM przydziela strony.

Jak implementowanie flagi śledzenia -T1118 zmniejsza rywalizację

Uwaga

Ta sekcja dotyczy tylko wersji SQL Server 2014 i starszych.

Na poniższej liście wyjaśniono, jak używanie flagi śledzenia -T1118 zmniejsza rywalizację:

  • -T1118 to ustawienie dla całego serwera.
  • Uwzględnij flagę śledzenia -T1118 w parametrach uruchamiania dla SQL Server, aby flaga śledzenia pozostała w mocy nawet po odtworzeniu SQL Server.
  • -T1118 usuwa prawie wszystkie alokacje jednostronicowe na serwerze.
  • Wyłączenie większości alokacji jednostronicowych zmniejsza rywalizację na stronie SGAM.
  • Jeśli -T1118 jest włączony, prawie wszystkie nowe alokacje są wykonywane ze strony GAM (na przykład 2:1:2), która przydziela osiem (8) stron (w jednym zakresie) naraz do obiektu, w przeciwieństwie do jednej strony z zakresu dla pierwszych ośmiu (8) stron obiektu, bez flagi śledzenia.
  • Strony IAM nadal używają alokacji jednostronicowych ze strony SGAM, nawet jeśli -T1118jest włączony. Jednak w połączeniu z poprawką 8.00.0702 i zwiększonymi plikami danych bazy danych tempdb efektem netto jest zmniejszenie rywalizacji na stronie SGAM. Aby zapoznać się z zagadnieniami dotyczącymi przestrzeni, zobacz następną sekcję.

Wady

Wadą korzystania z -T1118 jest to, że może wystąpić wzrost rozmiaru bazy danych, jeśli spełnione są następujące warunki:

  • Nowe obiekty są tworzone w bazie danych użytkownika.
  • Każdy z nowych obiektów zajmuje mniej niż 64 KB magazynu.

Jeśli te warunki są spełnione, możesz przydzielić 64 KB (osiem stron * 8 KB = 64 KB) dla obiektu, który wymaga tylko 8 KB miejsca, co powoduje marnowanie 56 KB miejsca. Jeśli jednak nowy obiekt używa więcej niż 64 KB (osiem stron) w okresie istnienia, nie ma żadnych wad flagi śledzenia. W związku z tym w najgorszym przypadku SQL Server może przydzielić siedem (7) dodatkowych stron podczas pierwszej alokacji tylko dla nowych obiektów, które nigdy nie przekraczają jednej (1) strony.

Informacje