Zalecenia, aby zmniejszyć rywalizację związaną z alokacją bazy danych tempdb w programie SQL Server
Ten artykuł pomaga rozwiązać problem polegający na tym, że w przypadku wystąpienia dużego obciążenia serwera występuje poważne blokowanie.
Oryginalna wersja produktu: SQL Server
Oryginalny numer KB: 2154845
Symptomy
Na serwerze z uruchomionym programem Microsoft SQL Server zauważysz poważne blokowanie, gdy serwer ma 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 16.
Jeśli strona jest równomiernie podzielna przez 8088, jest to strona PFS. Na przykład strona 2:3:905856 jest plikiem PFS w file_id=3 w bazie danych tempdb.
Następujące operacje intensywnie używają bazy danych tempdb :
- Powtarzalna operacja tworzenia i upuszczania tabel tymczasowych (lokalna lub globalna).
- Zmienne tabeli korzystające z bazy danych tempdb na potrzeby magazynu.
- Tabele robocze skojarzone z kursorami.
- Tabele robocze skojarzone z klauzulą ORDER BY.
- Tabele robocze skojarzone z klauzulą GROUP BY.
- Pliki robocze skojarzone z planami SKRÓTu.
Te działania mogą powodować problemy z rywalizacją.
Przyczyna
Gdy baza danych tempdb jest intensywnie używana, program SQL Server może napotkać rywalizację podczas próby przydzielenia stron. W zależności od stopnia rywalizacji może to spowodować, że zapytania i żądania, które obejmują bazę danych tempdb , mogą być krótko nie odpowiada.
Podczas tworzenia obiektu należy przydzielić dwie strony (2) z mieszanego zakresu i przypisać je do nowego obiektu. Jedna strona dotyczy mapy alokacji indeksu (IAM), a druga jest pierwszą stroną obiektu. Program SQL Server śledzi mieszane zakresy przy użyciu strony Udostępnionej globalnej mapy alokacji (SGAM). Każda strona SGAM śledzi około 4 gigabajtów danych.
Aby przydzielić stronę z zakresu mieszanego, program SQL Server musi przeskanować stronę Zwolnij miejsce na stronie (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. Właściwa synchronizacja jest utrzymywana w celu wprowadzania zmian na stronach PFS i SGAM; i to może zatrzymać innych modyfikatorów przez krótkie okresy.
Gdy program SQL Server wyszukuje mieszaną stronę do przydzielenia, zawsze uruchamia skanowanie na tej samej stronie pliku i SGAM. Powoduje to intensywną rywalizację na stronie SGAM, gdy trwa kilka alokacji mieszanych stron. Może to spowodować problemy opisane w sekcji Objawy .
Uwaga 16.
Działania co do anulowania alokacji muszą również modyfikować strony. Może to przyczynić się do zwiększenia rywalizacji.
Aby dowiedzieć się więcej o różnych mechanizmach alokacji używanych przez program SQL Server (SGAM, GAM, PFS, IAM), zobacz sekcję Odwołania .
Rozwiązanie
SQL Server 2016 i nowsze wersje:
Wykonaj przegląd
Optymalizacja wydajności bazy danych tempdb w programie SQL Server.
Zastosuj odpowiednią aktualizację CU dla programu SQL Server 2016 i 2017, aby skorzystać z następującej aktualizacji. Wprowadzono ulepszenia, które dodatkowo zmniejszają rywalizację w programach SQL Server 2016 i SQL Server 2017. Oprócz alokacji działania okrężnego we wszystkich plikach danych bazy danych tempdb poprawka poprawia alokację strony PFS przez wykonywanie alokacji działania okrężnego na kilku stronach PFS w tym samym pliku danych. Aby uzyskać więcej informacji, zobacz KB4099472 — algorytm działania okrężnego PFS w programie SQL Server 2014, 2016 i 2017.
Aby uzyskać więcej informacji na temat tych zaleceń i innych zmian, które zostały wprowadzone w przeglądzie programu SQL 2016
SQL Server 2014 i starsze wersje:
Aby poprawić współbieżność bazy danych tempdb, wypróbuj następujące metody:
Zwiększ liczbę plików danych w bazie danych tempdb, aby zmaksymalizować przepustowość dysku i zmniejszyć rywalizację w strukturach alokacji. Z reguły, jeśli liczba procesorów logicznych jest mniejsza lub równa ośmiu (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 rywalizacja będzie kontynuowana, zwiększ liczbę plików danych o wielokrotność czterech (4) do liczby procesorów logicznych, dopóki rywalizacja nie zostanie zmniejszona do akceptowalnych poziomów. Alternatywnie wprowadź zmiany w obciążeniu lub kodzie.
Rozważ wdrożenie zaleceń dotyczących najlepszych rozwiązań w temacie Praca z bazą danych tempdb w programie SQL Server 2005.
Jeśli poprzednie kroki nie zmniejszają znacznie rywalizacji o alokację, a rywalizacja znajduje się na stronach SGAM, zaimplementuj flagę śledzenia -T1118. W ramach tej flagi śledzenia program SQL Server przydziela pełne zakresy do każdego obiektu bazy danych, eliminując tym samym rywalizację na stronach SGAM.
Uwaga 16.
Ta flaga śledzenia wpływa na każdą bazę danych w wystąpieniu programu SQL Server. Aby uzyskać informacje o sposobie określania, czy rywalizacja alokacji znajduje się na stronach SGAM, zobacz rywalizację monitorowania spowodowaną operacjami DML.
W przypadku środowisk programu SQL Server 2014 upewnij się, że zastosujesz dodatek Service Pack 3, aby skorzystać z poprawki opisanej w poniższym artykule bazy wiedzy. Poprawa dodatkowo zmniejsza rywalizację w środowiskach programu SQL Server 2014. Oprócz alokacji działania okrężnego we wszystkich plikach danych bazy danych tempdb poprawka poprawia alokację strony PFS przez wykonywanie alokacji działania okrężnego na kilku stronach PFS w tym samym pliku danych.
Blog zespołu ds. aplikacji MSSQL Tiger: pliki i flagi śledzenia oraz aktualizacje w bazie danych tempdb programu SQL Server
Zwiększ liczbę plików danych bazy danych tempdb, które mają równe rozmiary
Na przykład, jeśli 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 pozostawić plik dziennika tak, jak jest. Posiadanie różnych plików danych na oddzielnych dyskach zapewni dodatkową korzyść z 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 widocznego w bazie danych tempdb. Jako punkt wyjścia można skonfigurować bazę danych tempdb co najmniej równą liczbie procesorów logicznych przypisanych dla programu SQL Server. W przypadku systemów wyższego poziomu liczba początkowa może być ósemka (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. Program SQL Server 2000 z dodatkiem Service Pack 4 (SP4) wprowadził 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 mieszanej alokacji strony (jeśli istnieje więcej niż jeden plik). Nowy algorytm alokacji dla SGAM jest czystym działaniem okrężnym i nie honoruje 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, które mają te same rozmiary dla bazy danych tempdb , skutecznie tworzy co najmniej jedną stronę GAM i SGAM dla każdego pliku danych.
Algorytm alokacji dla gam przydziela jeden zakres w danym momencie (osiem ciągłych stron) z liczby plików w sposób działania okrężnego przy jednoczesnym przestrzeganiu proporcjonalnego wypełnienia. W związku z tym, jeśli masz 10 plików o takim samym rozmiarze, pierwsza alokacja pochodzi z pliku File1, drugiego z pliku File2, trzeciego z pliku3 itd.
Rywalizacja o zasoby strony PFS jest ograniczona, ponieważ osiem stron naraz jest oznaczonych jako PEŁNE, ponieważ GAM przydziela strony.
Jak implementowanie flagi śledzenia -T1118 zmniejsza rywalizację
Uwaga 16.
Ta sekcja dotyczy tylko programu SQL Server 2014 i starszych wersji.
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 programu SQL Server, aby flaga śledzenia pozostała w mocy nawet po recyklingu programu SQL Server.
- -T1118 usuwa prawie wszystkie alokacje jednostronicowe na serwerze.
- Wyłączenie większości alokacji pojedynczej strony zmniejsza rywalizację na stronie SGAM.
- Jeśli opcja -T1118 jest włączona, prawie wszystkie nowe alokacje są wykonywane ze strony GAM (na przykład 2:1:2), która przydziela osiem stron (8) (jeden zakres) jednocześnie do obiektu, w przeciwieństwie do pojedynczej strony z zakresu dla pierwszych ośmiu (8) stron obiektu, bez flagi śledzenia.
- Strony IAM nadal używają alokacji pojedynczej strony ze strony SGAM, nawet jeśli opcja -T1118 jest włączona. Jednak w połączeniu z poprawką 8.00.0702 i zwiększonymi plikami danych bazy danych tempdb efekt netto jest zmniejszeniem rywalizacji na stronie SGAM. Aby uzyskać informacje o problemach związanych z miejscem, zobacz następną sekcję.
Wady
Wadą użycia -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 miejsca do magazynowania.
Jeśli te warunki są prawdziwe, możesz przydzielić 64 KB (osiem stron * 8 KB = 64 KB) dla obiektu, który wymaga tylko 8 KB miejsca, co oznacza marnowanie 56 KB miejsca. Jeśli jednak nowy obiekt używa więcej niż 64 KB (osiem stron) w okresie istnienia, nie ma wady flagi śledzenia. W związku z tym w najgorszym scenariuszu program SQL Server może przydzielić siedem (7) dodatkowych stron podczas pierwszej alokacji tylko dla nowych obiektów, które nigdy nie rosną poza jedną (1) stronę.