Treści TempDB

Mamy aktywną bazę danych OLTP 40 GB na SQL Server 2014 SP1. Stwierdzono, że zapytania są powolne, ponieważ IO_Completion czeka, długość kolejki dysków wzrasta do 900, a SQL Server przestaje odpowiadać. Co próbowaliśmy:

1. Zrestartuj instancję, a po minucie zacznie zachowywać się w ten sam sposób.

2. Po drugim ponownym uruchomieniu zmieniliśmy początkowy rozmiar każdego pliku danych tempdb (utworzono 16 plików danych) i zaczyna on działać poprawnie.

Uwaga: Używamy zmiennych tabeli do pośrednich zestawów wyników. Te zestawy wyników są bardzo małe.

Stało się to dwa razy w miesiącu. Za każdym razem, gdy ręcznie dodam trochę miejsca do plików danych, zaczyna ono działać normalnie. Bardziej interesujące jest to, że ta sama konfiguracja (ten sam sprzęt, ta sama konfiguracja folderów i plików, to samo obciążenie) mamy na SQL Server 2008 R2 i SQL Server 2012 działa dobrze.

Prosimy o pomoc w znalezieniu stałego rozwiązania.

Początkowy rozmiar wszystkich plików danych jest taki sam 1000 MB, bieżący to 1500 MB każdy. Wszystkie są identyczne. Autogrowth to 100 MB na każdy. Wcześniej mieliśmy do czynienia z rywalizacją o strony PFS i GAM i wzrosła do 16 i problem został rozwiązany. Obie flagi śledzenia 1117 i 1118 są włączone. 24 rdzenie na 2 węzłach NUMA. Wszystkie pliki danych znajdują się na tym samym woluminie. Prosty dysk, brak SAN.

Instancja znajduje się na maszynie fizycznej. Zapytania ze zmiennymi tabel i zapytania z łączeniami mieszającymi najczęściej generują oczekiwania na zakończenie IO_Completion.

Szczegółowa odpowiedź wBob zmusiła nas do szukania bardziej szczegółowych informacji. Jak to przegapiliśmy wcześniej:

Autogrow pliku „templog” w bazie danych „tempdb” został anulowany przez użytkownika lub upłynął limit czasu po 7704 milisekundach. Użyj ZMIEŃ bazę danych, aby ustawić mniejszą wartość FILEGROWTH dla tego pliku lub jawnie ustawić nowy rozmiar pliku.

Znaleźliśmy to w dzienniku, gdy występuje kiedykolwiek tego rodzaju problem. Przenosimy TempDB do oddzielnego szybkiego dysku.

Myślę, że przesadziłeś z tempdb i istnieje niezgodność między procesorem serwera a konfiguracją dysku, ale zbierzmy trochę więcej informacji:

Wymagane pytania / dodatkowe informacje

• Potwierdź nazwę i typ procesora (w zasadzie próbuję ustalić, czy jest 2 x sześciordzeniowy z HT). Użyj informacji o systemie (np. Panel sterowania> System i zabezpieczenia> System w systemie Windows Server 2012 R2) i / lub narzędzia sysinternals CoreInfo, aby potwierdzić.
• Potwierdź serwer maxdop (np EXEC sp_configure 'max degree of parallelism'.). Jeśli procesory są sześciordzeniowe, serwer maxdop powinien mieć co najwyżej 6 (jak tutaj ) lub być prawdopodobnie niższy w systemie OLTP. Zwykle utrzymuję pliki tempdb zgodne z DOP serwera na maksymalnie 8, ale przejdziemy do tego.
• Potwierdź całkowitą pamięć serwera na polu i limit pamięci SQL Server (np EXEC sp_configure 'max server memory (MB)'.).
• Proszę potwierdzić, czy jakieś inne usługi działają na urządzeniu (np. SSIS, SSAS, SSRS, aplikacja, iTunes itp.)
• Potwierdź, że dla konta usługi SQL Server włączona jest natychmiastowa inicjalizacja pliku. (Sposoby przetestowania tutaj ).
• Dlaczego istnieje tak ogromna rozbieżność między procesorem (rozbudowana konfiguracja NUMA z 2 węzłami) a jednym dyskiem (komputer domowy)? Rozważ dodanie dysków, striping, SSD do tempdb (choć unikaj nadmiernej reakcji:) .
• Dodaj aktualny plan wykonania jednego z zapytań problemowych. Anonimowość za pomocą SQL Sentry Plan Explorer, jeśli chcesz.

• Hash łączy się ze zmiennymi tabel w systemie OLTP? To sugeruje brak indeksowania zmiennej tabeli, tabeli głównej lub obu. Czy deklarujesz takie zmienne tabelowe (bez indeksów)?

  DECLARE @t TABLE ( x INT )

• Nie oszczędzaj na definicji zmiennej tabeli, mimo że zawiera ona małe zestawy wyników. Zawsze najlepiej jest podać optymalizatorowi jak najwięcej informacji, więc bądź wyraźny, biorąc pod uwagę dopuszczalność, unikalność, niezależnie od tego, czy indeks jest klastrowany / nieklastrowany, np.

  DECLARE @t TABLE ( x INT PRIMARY KEY )
  DECLARE @u TABLE ( x INT PRIMARY KEY NONCLUSTERED, u INT NOT NULL UNIQUE CLUSTERED, z INT NOT NULL UNIQUE, a CHAR(1) NULL ) -- not sure why you would do this but you can
  DECLARE @v TABLE ( x INT NOT NULL, y INT NOT NULL, PRIMARY KEY ( x, y ) )   -- multi-column primary key

• Opublikowanie planu wykonania pomoże to zdiagnozować.

• Sprawdź kod zapobiegający buforowaniu zmiennych tabeli zgodnie z tutaj , tutaj . Myślę, że dynamiczne SQL i proc wykonywane przy pomocy RECOMPILE są jedynymi, które wpływają na zmienne tabeli.

  DECLARE @u TABLE ( x INT )
  
  INSERT @u
  EXEC('DECLARE @t TABLE ( x INT ); INSERT INTO @t VALUES ( 1 ); SELECT x FROM @t;' )
  
  SELECT *
  FROM @u

• Sprawdź dziennik serwera SQL (Eksplorator obiektów> Zarządzanie> Dzienniki serwera SQL) pod kątem komunikatów, np. Ostrzeżeń we / wy.

• Sprawdź Podgląd zdarzeń systemu Windows
• Od wersji SP1 wydano wiele kompilacji. Przejrzyj poprawki CU wprowadzone od SP1 . Możliwe, że w dodatku SP1 są błędy naprawione w kolejnych jednostkach CU, np. POPRAWKA: Operator sortowania przelewa się do tempdb w SQL Server 2012 lub SQL Server 2014, gdy szacowana liczba wierszy i ich rozmiar są poprawne https://support.microsoft.com/en- us / kb / 3088480
• Ustal, że to jest Twoja przyczyna przed zastosowaniem jakichkolwiek poprawek, chociaż ważniejsze jest, aby być na bieżąco z jednostkami CU z SQL Server 2014, ze względu na liczbę nowych funkcji (OLTP w pamięci, klastrowany magazyn kolumn).
• Wreszcie, potrzeba jednego pliku tempdb na rdzeń jest mitem i patrząc na konfigurację dysku, domyślam się, że tempdb jest nadmiernie rozdrobniony. Mam dokuczliwe wrażenie, że masz jedną głowicę dysku, tempdb ma jedną grupę plików, wiele plików.

Zapomnij jednak o tym, co naszym zdaniem wiemy; stwórz zestaw testowy, który odtwarza Twój problem i eksperymentuj ze zmniejszaniem liczby plików tymczasowych ... zacznij od 1, 2, 4, 6 itd. zbierz informacje, aby podjąć decyzję opartą na dowodach. Teraz jest to trudniejsze, ponieważ twój problem wydaje się sporadyczny i możesz nie być w stanie zepsuć się z konfiguracją tempdb, ale tak do tego podejdę.

Powodzenia. Poinformuj nas, jak sobie radzisz.