Zalecenia dotyczące zmniejszania rywalizacji o przydziały w bazie danych tempdb programu SQL Server

Dotyczy: SQL Server 2008 DeveloperSQL Server 2008 EnterpriseSQL Server 2008 Express

Symptomy


Na serwerze, na którym jest uruchomiony program Microsoft SQL Server, zauważysz, że blokada jest poważnie zablokowana, gdy wystąpi duże obciążenie serwera. Widoki zarządzania dynamicznego [sys. dm_exec_request lub sys. dm_os_waiting_tasks] wskazują, że te żądania lub zadania oczekują na zasoby tempdb . Ponadto Typ oczekiwania to PAGELATCH_UP, a zasób oczekiwania wskazuje na strony w bazie danych tempdb. Te strony mogą mieć format 2:1:1, 2:1:3 itd. (doskonałe utajnienie i strony SGAM w bazie danych tempdb).
Uwaga Jeśli strona jest równo podzielna przez 8088, jest to strona PFS. Na przykład Strona 2:3:905856 jest doskonałe utajnienie przekazywania w file_id = 3 w bazie danych tempdb.
Poniższe operacje wykorzystują w szerokim zakresie bazę danych tempdb :
  • Repetative operacji tworzenia i upuszczania tabel tymczasowych (lokalnych lub globalnych).
  • Zmienne tabeli używające bazy danych tempdb dla magazynu.
  • Tabele służbowe skojarzone z KURSORami.
  • Tabele służbowe skojarzone z klauzulą ORDER BY.
  • Tabele służbowe skojarzone z klauzulą GROUP BY.
  • Pliki służbowe skojarzone z PLANami mieszania.
Te działania mogą powodować problemy z rywalizacją.

Przyczyna


Jeśli baza danych tempdb jest intensywnie używana, program SQL Server może powodować rywalizację podczas próby przydzielenia stron. W zależności od stopnia rywalizacji mogą one powodować, że kwerendy i żądania dotyczące bazy danych tempdb są krótko nieodpowiadające.
Podczas tworzenia obiektu dwie (2) strony muszą zostać przydzielone z zakresu mieszanego i przypisane do nowego obiektu. Jedna strona jest dla mapy przydziału indeksu (IAM), a druga na pierwszej stronie obiektu. Program SQL Server śledzi różne zakresy za pomocą udostępnionej strony mapy globalnej alokacji (SGAM). Każda strona SGAM śledzi około 4 gigabajtów danych.Aby przydzielić stronę w mieszanym zakresie, program SQL Server musi przeskanować stronę wolne miejsce na stronie (PFS), aby określić, która strona mieszana jest bezpłatna. Na stronie PFS można śledzić wolne miejsce dostępne na każdej stronie, a każda Strona PFS śledzi około 8000 stron. Zachowana zostanie odpowiednia synchronizacja, aby wprowadzić zmiany w doskonałej PFS i SGAM stronach. Możesz także zawiesić inne Modyfikatory na krótki okres.Gdy program SQL Server wyszukuje stronę mieszaną, która ma zostać przydzielona, zawsze rozpoczyna skanowanie na tym samym pliku i na SGAM stronie. Powoduje to powstanie intensywnej rywalizacji na stronie programu SGAM po przeniesieniu kilku przydziałów stron mieszanych. Może to powodować problemy opisane w sekcji "Symptomy".Uwaga Działania dotyczące rozdzielenia 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 przydziałów, które są używane przez program SQL Server (SGAM, GAM, PFS, IAM), zobacz sekcję "materiały referencyjne".

Rozwiązanie


Aby poprawić współbieżność bazy danych tempdb, wypróbuj następujące metody:

  • Zwiększ liczbę plików danych w bazie danych tempdb w celu zmaksymalizowania przepustowości dysków i zmniejszenia zawartości w strukturach alokacji. Jako ogólna reguła, jeśli liczba procesorów logicznych jest mniejsza niż lub równa osiem (8), Użyj tej samej liczby plików danych co procesory logiczne. Jeśli liczba procesorów logicznych jest większa niż osiem (8), użyj ośmiu plików danych. Jeśli zawartość jest wciąż kontynuowana, Zwiększ liczbę plików danych, używając wielokrotności liczby cztery (4) do liczby procesorów logicznych do momentu zmniejszenia zawartości do dopuszczalnych poziomów. Możesz też wprowadzić zmiany w obciążeniu lub kodzie.
  • Warto zapoznać się z zaleceniami dotyczącymi najlepszych praktyk w poniższym temacie usługi TechNet:  
  • Jeśli wykonanie poprzednich kroków nie powoduje znacznego zmniejszenia rywalizacji o przydział, a rywalizacja jest na stronach SGAM, zaimplementuj flagę śledzenia — T1118. W obszarze tej flagi śledzenia program SQL Server przydziela pełne zakresy każdemu obiektowi bazy danych, eliminując w ten sposób rywalizację na SGAM stronach. Uwagi  
  • Zastosuj odpowiedni CU for SQL Server 2016 i 2017, aby skorzystać z ostatniej aktualizacji. Dokonano usprawnienia, które dodatkowo ogranicza zawartość w programie SQL Server 2016 i SQL Server 2017. Oprócz przydzielenia środków na wszystkich plikach danych tempdb , Poprawka ulepsza przydziały stron PFS, wykonując przydziały rounding na kilku stronach PFS w tym samym pliku danych. Aby uzyskać więcej informacji, zobacz następującą zawartość:  

Więcej informacji


Zwiększanie liczby plików danych tempdb o równej wielkości

Jeśli na przykład rozmiar pliku danych w formacie tempdb wynosi 8 GB, a rozmiar pliku dziennika to 2 GB, zalecane jest zwiększenie liczby plików danych do ośmiu (8) (każdy z 1 GB w celu utrzymania równej wielkości) i pozostawienia pliku dziennika w formacie. Korzystanie z różnych plików danych na oddzielnych dyskach zapewnia dodatkową wydajność. Nie jest to jednak wymagane. Pliki mogą się współistnieć w tym samym woluminie dysku. Optymalna liczba tempdb plików danych tempdb zależy od stopnia rywalizacji, co jest widoczne w bazie danych tempdb. Jako punkt wyjścia możesz skonfigurować bazę danych tempdbco najmniej równa liczbie procesorów logicznych przypisanych dla programu SQL Server. W przypadku systemów z większą liczbą punktów początkowych może być osiem (8). Jeśli rywalizacja nie zostanie zmniejszona, może być konieczne zwiększenie liczby plików danych.Zalecamy używanie równego rozmiaru plików danych. W programie SQL Server 2000 z dodatkiem Service Pack 4 (SP4) wprowadzono poprawkę, która wykorzystuje algorytm okrężny dla przydziałów stron mieszanych. W związku z tym, że plik początkowy jest inny dla kolejnych mieszanych przydziałów stron (jeśli istnieje więcej niż jeden plik). Nowy algorytm przydziałów dla SGAM jest czystą rundą i nie zachowuje proporcjonalnego wypełnienia, aby zachować szybkość. Zalecamy tworzenie wszystkich tempdbplików danych tempdb w tym samym rozmiarze.  

Jak zwiększanie liczby plików danych tempdb powoduje zmniejszenie zawartości

Poniższa lista wyjaśnia, jak zwiększanie liczby tempdb plików danych tempdb o równej wielkości powoduje zmniejszenie zawartości:
  • Jeśli masz plik danych dla bazy danych tempdb, masz tylko po jednej stronie gam, a jedna strona SGAM dla każdego 4 GB miejsca.
  • Zwiększenie liczby plików danych o takich samych rozmiarach dla bazy danych tempdb w celu efektywnego utworzenia jednej lub większej liczby stron gam i SGAM dla każdego pliku danych.
  • Algorytm przydziałów dla GAM dzieli w jednym zakresie (osiem sąsiadujących stron) od liczby plików w sposób okrężny, zachowując proporcjonalne wypełnienie. Dlatego jeśli masz 10-jednakowy rozmiar plików, pierwszy przydział to od plik1, druga z file2, trzecia od file3 itd.
  • Rywalizacja o zasoby strony PFS jest zmniejszona, ponieważ osiem stron w danym momencie jest oznaczane jako pełne, ponieważ GAM przydziela strony.
 

Jak implementowanie flagi śledzenia — T1118 zmniejsza rywalizację

Na poniższej liście wyjaśniono, jak przy użyciu flagi Trace -T1118 jest zmniejszana zawartość:
  • -T1118 jest ustawieniem obejmującym cały serwer.
  • W parametrach uruchamiania programu SQL Server należy uwzględnić flagę śledzenia -T1118 , dzięki czemu flaga śledzenia nadal obowiązuje nawet po ODTWORZENIU programu SQL Server.
  • -T1118 usuwa prawie wszystkie pojedyncze przydziały stron na serwerze.
  • Wyłączając większość przydziałów jednej strony, możesz zmniejszyć ich rywalizację na stronie SGAM.
  • Jeśli opcja -T1118 jest włączona, prawie wszystkie nowe przydziały są tworzone na podstawie strony gam (na przykład 2:1:2), która przypisuje osiem (1) stron (1 zakres) jednocześnie do obiektu, w przeciwieństwie do jednej strony z zakresu pierwszych ośmiu (8) stron obiektu bez flagi śledź.
  • Na stronach IAM nadal są używane przydziały pojedynczej strony na stronie SGAM, nawet jeśli jest włączona opcja -T1118 . Jednak w połączeniu z poprawkami 8.00.0702 i zwiększonymi plikami danych tempdb efektem netto jest zmniejszenie zawartości na stronie SGAM. Aby uzyskać informacje dotyczące miejsca, zobacz następną sekcję.
Sytuację

Wadą korzystania z funkcji -T1118 jest to, że rozmiar bazy danych może być większy, 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 miejsca do magazynowania.

Jeśli te warunki są spełnione, możesz przydzielić 64 KB (8 stron * 8 KB = 64 KB) dla obiektu, który wymaga tylko 8 KB miejsca, co powoduje marnowanie 56 KB magazynu. Jeśli jednak w danym okresie istnienia nowego obiektu jest używana ponad 64 KB (8 stron), oznacza to, że nie ma żadnych niedogodności dotyczących flagi śledzenia. Dlatego w przypadku najgorszego przypadku program SQL Server może przydzielać siedem (7) dodatkowych stron podczas pierwszej alokacji tylko dla nowych obiektów, które nigdy nie rosną poza jedną stronę (1).

Informacje


Aby uzyskać więcej informacji na temat produktów lub narzędzi, które automatycznie wyszukują ten warunek w wystąpieniu programu SQL Server i wersji produktu SQL Server, zapoznaj się z poniższą tabelą.

Oprogramowanie reguły Tytuł reguły Opis reguły Wersje produktu, dla których jest oceniana reguła
Klasyfikator centrum systemowego Konfiguracja bazy danych programu SQL Server: liczba plików danych tempdb może powodować blokowanie Doradca programu System Center sprawdza liczbę plików danych skonfigurowanych dla bazy danych tempdb. Jeśli jest tylko jeden, a serwer, na którym jest uruchomiony program SQL Server, korzysta z więcej niż jednego procesora, ten alert zostanie wygenerowany. Przejrzyj informacje zawarte w tym artykule i Dodaj więcej plików danych do bazy danych tempdb. Program SQL Server 2008 SQL Server 2008 R2 Program SQL Server 2012