Loading…

READY TO DEAL WITH YOUR DATA?

Get some tips on how to do that!
Start exploring

SQL Server – o tempdb raz jeszcze

Zapisków po konferencji SQLDay 2014 ciąg dalszy. I znów natchnieniem okazały się sesje niezastąpionego Boba Warda (a to niespodzianka…) :-)

Dlaczego o tym?

Przez ostatnią dekadę wiele osób pisało i dyskutowało o problemie dotyczącym alokacji w bazie systemowej tempdb. Dość dobre repozytorium wiedzy na temat tempdb zebrała jeszcze w 2009 roku Cindy Gross na swoim blogu.

Także i polscy specjaliści poruszali ten temat wielokrotnie, niech za przykład posłuży wpis Czarka Ołtuszyka (pozdrawiam!). Z jednej strony wiadomo już, że odpowiedź na pytanie, czy dzielić bazę tempdb na wiele plików danych, brzmi “to zależy” :-) Z drugiej strony na ogół skupiano się na udowadnianiu, że zalecenie dotyczące podziału tempdb jest mitem (w sensie – dzielenie bazy tempdb nie powinno być regułą)…

Dlatego pomyślałem, że dobrze byłoby też pokazać, jak wygląda problem dotyczący alokacji w tempdb (“tempdb allocation contention problem”) oraz że dodanie plików do bazy tempdb jest w przypadku wystąpienia tego problemu dobrym rozwiązaniem.

Doświadczenie

Testy wykonałem na instancji SQL Server 2014 RTM zainstalowanej na laptopie o parametrach: 8 GB RAM, dysk SSD, Intel Core i7 (4-core). Instancja miała ustawiony limit max server memory na 4 GB. Doświadczenie powtórzyłem kilkukrotnie dla weryfikacji wyników.

Żeby uzyskać ekfekt wystąpienia problemu z alokacją w tempdb, utworzyłem bazę danych, a w niej tabelę i procedurę składowaną, która tworzyła tabele tymczasowe w tempdb.

USE master;
GO
 
IF DB_ID('DemoDB') IS NOT NULL BEGIN
  ALTER DATABASE DemoDB
  SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  DROP DATABASE DemoDB;
END;
GO
 
CREATE DATABASE DemoDB;
GO
 
USE DemoDB;
GO
 
CREATE TABLE dbo.T (
  c int NOT NULL PRIMARY KEY, 
  n char(7000) not null
);
GO
 
INSERT INTO dbo.T (c, n) 
SELECT 
  number, 
  newid()
FROM master.dbo.spt_values 
WHERE type = 'P' AND number BETWEEN 1 AND 1000;
GO
 
CREATE PROC dbo.P
  @i int
AS
SELECT * INTO #x FROM dbo.T WHERE c < @i;
GO

Przygotowałem skrypt, który pozwolił obserwować zadania oczekujące na alokacje w tempdb (zmodyfikowany skrypt z sesji Boba Warda):

SELECT 
  er.session_id, 
  er.status, 
  er.wait_type, 
  er.wait_time, 
  er.wait_resource, 
  er.last_wait_type, 
  er.blocking_session_id, 
  wt.resource_address
FROM sys.dm_exec_requests AS er
JOIN sys.dm_exec_sessions AS es
ON es.session_id = er.session_id
INNER JOIN sys.dm_os_waiting_tasks AS wt
ON wt.session_id = er.session_id
WHERE er.wait_type LIKE '%PAGE%'
AND er.wait_resource LIKE '2:1:%'
AND es.is_user_process = 1;
GO

Do symulacji wielu jednoczesnych połączeń użyłem aplikacji ostress, która jest częścią darmowego pakietu narzędzi RML Utilities. Uruchomiłem symulację 100 sesji wykonujących uruchomienie procedury dbo.P po 50 razy:

image

 

Efekt wykonania zapytania monitorującego przy jednym pliku danych (o rozmiarze 100 MB) w bazie tempdb:

image

 

Zapytanie uruchamiałem wielokrotnie w czasie trwania doświadczenia i za każdym razem obserwowałem do 99 zadań oczekujących. Obserwacje danych w kolumnach:

  • wait type – PAGELATCH_UP lub PAGELATCH_EX,
  • wait_resource – zawsze 2:1:X (notacja db_id:file_id:page_id), gdzie X mógł ulec zmianie, jeśli było na tyle dużo alokacji, by potrzebna była kolejna strona PFS.

Czas wykonania całości wsadu (100 sesji x 50 wykonań) w moim doświadczeniu wynosił ok. 7 sekund.

Idąc za najlepszą praktyką stworzyłem trzy dodatkowe pliki danych w bazie tempdb, a następnie wykonałem restart usługi SQL Server.

USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE ( 
  NAME = N'tempdev1', 
  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\tempdev1.ndf', 
  SIZE = 102400KB , 
  FILEGROWTH = 10240KB 
);
GO
ALTER DATABASE [tempdb] ADD FILE ( 
  NAME = N'tempdev2', 
  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\tempdev2.ndf', 
  SIZE = 102400KB, 
  FILEGROWTH = 10240KB 
);
GO
ALTER DATABASE [tempdb] ADD FILE ( 
  NAME = N'tempdev3', 
  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\tempdev3.ndf', 
  SIZE = 102400KB, 
  FILEGROWTH = 10240KB 
);
GO

Przed uruchomieniem testu po raz kolejny stworzyłem na instancji sesję Extended Events (XE), dzięki której zebrałem statystyki dotyczące liczby alokacji per plik bazy tempdb. Poniższa definicja sesji nie jest wzorcowa (w warunkach produkcyjnych warto dodać chociażby atrybuty ograniczające rozmiary plików, możliwość utraty zdarzeń itd.).

DBCC TRACEON(1106, -1);
DBCC TRACESTATUS(-1);
GO
 
CREATE EVENT SESSION XE_track_tempdb_usage ON SERVER 
ADD EVENT sqlserver.allocation_ring_buffer_recorded(
    ACTION(sqlserver.sql_text)
    WHERE (package0.equal_uint64(event,10))) 
ADD TARGET package0.event_file(
  SET filename=N'C:\Temp\track_tempdb_usage.xel'
);
GO
 
ALTER EVENT SESSION XE_track_tempdb_usage 
ON SERVER STATE = START;
GO

Ważna uwaga: flagi 1106, która włącza rejestrację zdarzeń alokacji w bazie tempdb, nie należy włączać na produkcji. Tu posłużyła wyłącznie do celów edukacyjnych.

Ponownie uruchomiłem symulację obciążenia i tym razem czas wykonania wynosił poniżej 6 sekund. A więc szybciej Co więcej, obserwacje oczekujących zadań pokazywały, że tylko przez ułamki sekund zadania oczekiwały na alokacje, które dodatkowo zachodziły na stronach PFS w czterech plikach (a były takie uruchomienia zapytania monitorującego, które nie wykazywały żadnych oczekujących zadań). Odpowiednie pogrupowanie zapisanych przez sesję XE zdarzeń pokazało, że alokacje rozłożyły się równomiernie po plikach bazy tempdb:

image

 

 

 

Poniżej wyniki doświadczenia dla 1, 2 i 4 plików bazy tempdb (testowałem także 8 plików, ale zysk był już w zasadzie niemierzalny) i różnych ilości sesji i wykonań procedury. Wysokość słupka jest determinowana przez uśredniony czas z 5 powótrzeń doświadczenia wyrażony w sekundach.

image

 

Czy różnice są duże? Nie, ale jednak są (im więcej sesji i powtórzeń, tym większa różnica) i to na korzyść konfiguracji z wieloma plikami (a doświadczenie było prowadzone przecież z wykorzystaniem tempdb rezydującej na szybkim dysku SSD). Czyli jeśli już mamy problem z alokacją w tempdb, podział tej bazy na wiele plików wydaje się być dobrym pomysłem (wówczas można się już stosować do artykułu KB 2154845).

Trace flagi – dobrze je znać

W kuluarach konferencji SQLDay kilka razy padło w dyskusjach pytanie, czemu flagi 1117 i 1118 nie są domyślnie włączone w SQL Server. Pytanie pozostawiam bez odpowiedzi, ale znaczenie obu flag dla efektywnego działania bazy tempdb na pewno trzeba znać.

Włączenie na instancji flagi 1117 powoduje, że pliki baz danych znajdujące się w pojedynczej grupie plików ulegają automatycznemu powiększeniu w tym samym momencie. Czyli, jeśli jeden z plików danej grupy musi powiększyć automatycznie swój rozmiar, wszystkie pozostałe pliki znajdujące się w tej samej grupie automatycznie się rozrastają. A ponieważ w bazie tempdb wszystkie pliki danych znajdują się zawsze w jednej grupie plików, wystarczy ustawić taki sam rozmiar początkowy i taki sam rozmiar automatycznego rozrostu dla wszystkich plików i dalej SQL Server sam będzie utrzymywał pliki tempdb w dokładnie takim samym rozmiarze, a co za tym idzie rozkład alokacji będzie równomierny po wszystkich plikach. Zresztą, równomierny rozkład danych w plikach jest korzystny nie tylko z punktu widzenia bazy tempdb, więc często flaga 1117 może mieć pozytywny wpływ także na funkcjonowanie baz tworzonych przez użytkowników.

Z kolei ustawienie flagi 1118 powoduje, że alokacje w tempdb nie przekraczające 64 kB (8 stron) są alokowane jako jednolite zakresy (ang. uniform extent). Sprawdza się to na przykład w sytuacjach, gdy na instancji powstaje wiele małych tabel tymczasowych (w przypadku opisanym powyżej włączenie tej flagi nie miało mierzalnego wpływu na wydajność). Wielcy świata SQL-owego, w tym Paul S. Randal, zalecają ustawianie tej flagi w zasadzie na wszystkich instancjach :-)

Podsumowanie

Problem alokacji w bazie tempdb jest realny i może się pojawić, zwłaszcza w systemach transakcyjnych, na których wielu użytkowników wykonuje jednocześnie dużo szybkich operacji z wykorzystaniem obiektów tymczasowych. Stworzenie wielu plików w bazie tempdb nie eliminuje tego problemu, ale może zminimalizować jego skutki. Czy należy od razu tworzyć wiele plików dla bazy tempdb? Oczywiście, niekoniecznie. Można śledzić aktywność tej bazy, a gdy okaże się, że mamy problem wynikający z oczekiwania na alokację w tempdb, wtedy zaaplikujemy strategię podziału na wiele plików. Niemniej, trzeba pamiętać, że dodanie nowych plików do bazy tempdb i doprowadzenie do sytuacji, gdy są one równomiernie wykorzystywane, będzie wymagało restartu usługi SQL Server, a nie zawsze możemy sobie na to pozwolić w produkcyjnych systemach :-)

Wydaje się, że dużo ważniejsze jest zadbanie o to, by pliki bazy tempdb, niezależnie od ich ilości, znalazły się na szybkim nośniku (umieszczenie ich na klasycznych dyskach spiętych w RAID czy na dysku SSD – to może znacznie pomóc). Pod rozwagę wypada wziąć ustawienie flag 1117 i 1118.

Zapraszam do dyskusji na ten wiecznie żywy temat. A może macie swoje własne sprawdzone najlepsze praktyki? Chętnie poczytam :-)

5 thoughts on “SQL Server – o tempdb raz jeszcze

  1. To ja dołożę parę słów :)
    Będę się powoływał na wspomnianego przez Ciebie Boba Warda, on zrobił fajną sesję – 4h (Inside TempDB level 500), gdzie pokazuje i zalece właśnie właczenie flagi T1118 i podaje konkretny algorytm, że dla core<=8 robimy tyle ile mamy corów, powyżej zostawiamy 8 i obserwujemy PAGELATCH contention, ewentualnie zwiększamy ilość plików w poracjach o 4 pliki, nie przekraczając ilości corów. Warunek konieczny, oczywiście równa wielkość rozmiaru pliku.

    Koncepcja Boba Warda na tyle spodobała się wspomnianemu Paulowi Randalowi, że chciało mu się poprawić swój wpis z 2010 roku informując o tym co Bob podał na PASS w 2011 roku :)

    "But there’s now even better guidance, and what I also recommend. At PASS in 2011, my good friend Bob Ward, who’s the top guy in SQL Product Support, espoused a new formula: if you have less than 8 cores, use #files = #cores. If you have more than 8 cores, use 8 files and if you’re seeing in-memory contention, add 4 more files at a time."
    http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/

    Pozdrawiam ;-)

    1. Łukasz,

      Dzięki za komentarz. W moim wpisie właśnie ten post Randala jest podlinkowany (pod “Paul S. Randal”) :-) I pewnie jest tak, jak napisałeś, że Paul wiele rzeczy napisał pod wpływem Boba ;-) Ważne, że wiadomo, co robić, a informacje pochodzą z Redmond. Ja potrzebę tworzenia wielu plików tempdb obserwuję u klientów zwłaszcza przy intensywnych systemach ERP (ostatnio głównie Dynamics AX).

      PS. Sesję Boba o tempdb oczywiście widziałem (jak to zwykle w przypadku Boba, wymiata). A algorytm dotyczący liczy plików tempdb jest skutecznie propagowany od lat, więc uznałem, że nie będę o nim pisał :-)

      Pozdrawiam,
      Paweł

  2. Paweł,
    Dałem tego linka bo ty dałeś do “Misconceptions around TF 1118”, a później Randal napisał jeszcze “A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core” i ja ten dałem jako uzupełnienie :-)

    Algorytm faktycznie od tego PASSA w 2011 jest znany, ale wiesz dobrze, że w zamkniętym gronie :) U klientów cały czas obowiązują zasady ze starych dobrych praktyk :-)

  3. Łukasz, racja. Faktycznie wygląda na to, że Randal przy okazji wspomnianego przez Ciebie posta uzupełnił wpis o 1118 o algorytm Boba. Co do klientów, to spora ich część jest jednak już świadoma problemu i często na starcie dzielą tempdb na wiele plików. Nie wspomnę, że coraz częściej oglądam kombinację 1117 + 1118 włączoną na serwerach. Tak między nami, zdaje się, że Bob też jest zwolennikiem tych flag i orędownikiem ich włączenia by default w instancjach SQL Server ;-)

    Pozdrawiam,
    Paweł

  4. Pingback: dotnetomaniak.pl

Leave a Reply