Rywalizacja DDL o TempDB

Mam SQL Server 2005 Standard x64, który od kilku miesięcy ma problemy z rywalizacją o DDL TempDB. Serwer napotyka rywalizację o zasób oczekiwania 2: 1: 103 (typ oczekiwania to PAGELATCH_EX).

Problem wydaje się występować sporadycznie, gdy serwer jest porządnie obciążony. Monitorowałem wskaźnik „Tabele temperatur do zniszczenia” i może on wzrosnąć do ponad 5000 w czasie, gdy mamy problemy z PAGELATCH_EX w stosunku 2: 1: 103. Z tego, co przeczytałem, ten licznik powinien wynosić w większości przypadków 0, ale nasz wydaje się pozostawać gdziekolwiek w przedziale 300-1100 przez większość czasu. Licznik przechodzi do zera tylko wtedy, gdy w systemie jest bardzo mało użytkowników.

Jak mogę zawęzić przyczyny DDL na tempdb bez konieczności szukania igły w stosie siana?

Widziałem ten problem, a poprawka, która została ostatecznie wydana w celu naprawy, była w rzeczywistości bezpośrednim wynikiem mojej sprawy z Microsoft CSS. Brak publicznego artykułu bazy wiedzy dla poprawki. Upewnij się, że zastosowałeś dodatek Service Pack 4 i najnowszą aktualizację zbiorczą do programu SQL Server (w chwili pisania tego tekstu jest to aktualizacja zbiorcza nr 3 (9.00.5259) ).

Do czasu wydania poprawki sugestia Microsoftu polegała na zaprzestaniu tworzenia # tabel tempa (podobnie jak KB # 916086 ). Ponieważ oznaczałoby to znaczne ponowne zapisanie dziesiątek procedur raportowania, w moim przypadku obejście (niezależnie od flag śledzenia lub układu plików tymczasowych) polegało na ponownym uruchamianiu naszego klastra co drugi weekend. Fuj

Aby wyśledzić użycie tempdb, istnieje kilka skryptów, które mogą pomóc, np. Zobacz sp_whoIsActive Adama Machanica , w szczególności:

• sp_whoisactive: Analiza rywalizacji Tempdb
• Jak rozpoznać, które zapytanie wypełnia dziennik transakcji tempdb?

A także ten skrypt (i te w komentarzach) z @SQLSoldier:

• Podział rywalizacji TempDB

Upewnij się, że wszystkie kursory używają LOCAL STATIC READ_ONLY FORWARD_ONLY(zobacz to i to ), i zobacz, czy są jakieś znane drogie zapytania, które szeroko wykorzystują tabele #temp / zmienne @table, CTE, lub mogą zawierać niepotrzebne sortowania lub prowadzić do łączenia ... wszystko to może przyczynić się do problemu (wątpię, czy znajdziesz jedną złotą przyczynę). Najłatwiejszym rozwiązaniem zamiatania jako punktu początkowego „huk za grosze” będzie użycie właściwych i niedrogich opcji kursora zamiast domyślnych.

W międzyczasie chciałbym (a) zainstalować CU # 3 i (b) wywołać PSS. Powiedz im, że szukasz bardzo konkretnej poprawki, która została już potwierdzona jako błąd i udostępniona innym użytkownikom jako prywatna poprawka: „VSTS # 109112 - Odroczone upuszczenie tabeli temp nie jest skalowane dla niektórych obciążeń”. Być może będziesz musiał najpierw uiścić opłatę za sprawę, ale ponieważ jest to błąd, opłata powinna zostać zwrócona.

Prawdopodobnie potrzebujesz flagi śledzenia 1118

Najpierw zobacz mity Paula Randala o tempdb , a także jego artykuł o TF 1118

TF opisano tutaj w KB 328551

Nie mam z tym bezpośredniego doświadczenia, ale brzmi to tak, jak przeczytałem

Zakładam, że już podzieliłeś swoje pliki danych TempDB, aby spróbować złagodzić rywalizację (oczywiście najpierw przez produkcję). Jeśli jesteś odważniejszy, weź pod uwagę flagę śledzenia, do której autorytatywnie odnosi się Paul Randal: http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230) -tempdb-powinien-zawsze-mieć-jeden-plik-danych-na-procesor-rdzeń.aspx

Jeśli chodzi o przyczyny bólu, musisz wykonać pewne czynności dochodzeniowe:

• czy to właśnie się zaczęło? co się zmieniło?
• czy serwer jest pod presją pamięci, więc sortowanie musi być wykonywane w TempDB?
• czy są uruchomione jakieś procesy DBA, takie jak CheckDB, czy ponowne indeksowanie online?
• czy stosowane są bardziej egzotyczne poziomy izolacji, czy pośrednik usług? spójrz na sys.databases

Na dole tego dokumentu Microsoft TempDB znajduje się ładne zapytanie, aby spróbować dowiedzieć się, co korzysta z tempdb: http://technet.microsoft.com/en-gb/library/cc966545.aspx

Jeśli nadal chcesz to wyśledzić, ostatnio miałem podobny dziwny problem z wydajnością z synchronicznymi spadkami tabel. Jeśli masz dużą liczbę baz danych (> 100 lub więcej) w instancji SQL z uruchomionym SQL 2005 i masz wiele instrukcji tworzenia i usuwania tabeli tymczasowej, możesz uzyskać powolne upuszczanie tabeli tymczasowej. Sprawdzanie liczby wierszy zwróconych z sys.dm_db_index_usage_stats może od razu wykluczyć to jako winowajcę.

Artykuł KB opisuje problem. http://support.microsoft.com/kb/2003031

Wydajność zapytania spada, gdy sys.dm_db_index_usage_stats ma dużą liczbę wierszy

Rozważ następujący scenariusz:

W Microsoft SQL Server 2005 często wykonuje się operacje DDL, które polegają na usuwaniu i odtwarzaniu wielu tabel (szczególnie tabel tymczasowych w bazie danych tempdb). Masz dużą liczbę wpisów (100 000 lub więcej) w widoku dynamicznego zarządzania sys.dm_db_index_usage_stats (DMV).

Z mojej zaakceptowanej odpowiedzi na to pytanie. Jest tam również więcej szczegółów. Powolne spadki tabeli w sql 2005