Spis treści:

Kategoria:Optymalizacja SQLSQL Server


Jak poprawnie mierzyć wydajność zapytań SQL

Chyba każdy, kto kiedyś spotkał się z wolno działającymi zapytaniami/procedurami w SQL Server, zastanawiał się nad możliwością ich poprawy. Co należy brać pod uwagę, aby testy były właściwe? Jest kilka zasad, których warto przestrzegać.

Pamiętaj o wyczyszczeniu bufora/pamięci cache serwera

Każdy test powinien być wykonywany tak, żeby SQL Server nie mógł zastosować triku polegającego na zwróceniu gotowych wyników, pomijając przez to rzeczywiste odczyty z dysku twardego. Wykonanie dowolnego zapytania może sprawić, że SQL Server zapamięta jego wyniki, przez co drugie, takie samo lub podobne zapytanie, będzie działało znacznie szybciej. Część stron będzie bowiem obecna w pamięci operacyjnej. Drugie wykonanie procedury składowanej może spowodować, że SQL Server będzie już miał gotowy plan wykonania i skorzysta z niego zamiast wyliczać nowy. O ile testy na wcześniej skompilowanej procedurze składowanej mają duży sens i mogą być przydatne, o tyle pozostawienie bufora danych najczęściej jest błędem. Warto wiedzieć o konsekwencjach i mieć świadomość, że pominięcie instrukcji czyszczącej cache może znacznie zafałszować końcowe wyniki.

Do czyszczenia pamięci cache służy instrukcja DBCC DROPCLEANBUFFERS. Nie wymaga ona zatrzymania, ani restartu serwera. Używa się jej w sposób następujący:

DBCC DROPCLEANBUFFERS

Proste i przejrzyste. W wyniku działania powyższej instrukcji na ekranie pojawi się taki oto komunikat:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Istnieje drugi sposób wywołania tej instrukcji:

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS

W tym przypadku żaden komunikat nie zostanie wyświetlony.
Korzystanie z instrukcji DBCC DROPCLEANBUFFERS nie zawsze od razu pomoże. Jak sama nazwa wskazuje, instrukcja usuwa czyste (clean) bufory. Czym są bufory czyste, a czym są bufory brudne (dirty) można przeczytać tutaj: Dirty page, czyli brudne strony w SQL server. Cała instrukcja będzie więc najczęściej przyjmowała postać następującą:
CHECKPOINT
GO

DBCC DROPCLEANBUFFERS
GO

Druga z instrukcji, rzadziej stosowana, czyści plany wykonania.

DBCC FREEPROCCACHE

Istnieje też alternatywna wersja, która nie wyświetla komunikatu:

DBCC FREEPROCCACHE WITH NO_INFOMSGS

Instrukcja DBCC FREEPROCCACHE ma też parametry. Opis ich można znaleźć w dokumentacji i nie będą tutaj opisywane. Warto też wspomnieć o instrukcji DBCC FREESYSTEMCACHE, która czyści wszystkie pamięci cache, z których korzysta SQL Server. Na koniec jeszcze jedna informacja: instrukcji nie należy używać na działającym i obciążonym serwerze. Wyczyszczenie pamięci podręcznych SQL Server sprawi, że przez pewien czas będzie on działał wolniej, bo będzie musiał od nowa stworzyć sobie odpowiednie struktury w pamięciach podręcznych.

Powtórz pomiary kilka razy

Każdy profesjonalny pomiar należy wykonać kilka razy - nie tylko pomiary SQL Server. Zasada jest prosta - im więcej pomiarów, tym są one dokładniejsze. Zdarza się, że jeden pomiar może odbiegać od pozostałych, bo na przykład system operacyjny w trakcie naszych pomiarów uznał, że musi sobie przenieść strony pamięci wirtualnej, albo uznał, że w systemie jest jakiś inny proces, któremu należy przydzielić czas procesora, a nasz SQL Server musi poczekać. Jeżeli wśród kilku pomiarów jeden znacznie odbiega od reszty, można go pominąć. Jeżeli wyniki są mocno zróżnicowane, to najprawdopodobniej coś robimy źle. W takim przypadku należy się upewnić, że nasz system operacyjny i SQL Server nie są czymś mocno obciążone - takie testy będą mało wiarygodne. Należy oczywiście pamiętać o punkcie nr 1, czyli czyszczeniu bufora.

Pomiary można wykonywać korzystając z gotowych narzędzi, takich jak SQL Server Profiler. Mamy tam podane wszystkie podstawowe informacje: czas ogólny w milisekundach (Duration), czas w milisekundach, potrzebny procesowrowi na przetworzenie zapytania (CPU), liczba logicznych odczytów z dysku (Reads) oraz liczba zapisów na dysku (Writes). Możemy się także posłużyć wyliczeniami szacunkowego planu wykonania (Display Estimated Execution Plan). Należy od razu podkreślić, że te szacunki nie zawsze oddają rzeczywiste warunki, co zresztą podkreślone jest słowem "Estimated" (ang. szacunkowy).

Jeżeli powyższe techniki nam nie pasują, można wykonać pomiary na piechotę. Procedura może wyglądać następująco:

DECLARE @time datetime = GETDATE()

--Operacja

SELECT DATEDIFF(millisecond, @time, GETDATE())

Można oczywiście wykonać wiele pomiarów za jednym razem, korzystając z podobnej techniki. Należy pamiętać o przywróceniu stanu bazy i testowanie na identycznym zbiorze danych. Inaczej mówiąc, jeżeli operacja polega na usuwaniu rekordów, to należy przed drugą operacją je przywrócić. Jeżeli badane instrukcje wstawiają rekordy, to należy je przed ponownym testem usunąć (w przeciwnym razie warunki początkowe nie byłyby te same i mogłoby to wpłynąć na zmianę wyników pomiaru). Procedura pomiarowa może wyglądać następująco:

DECLARE @i int=0

DECLARE @time datetime

WHILE @i<10

BEGIN

SET @time=GETDATE()

DBCC DROPCLEANBUFFERS

--Operacja

SELECT DATEDIFF(millisecond, @time, GETDATE())

SET @i = @i+1

END

Kategoria:Optymalizacja SQLSQL Server

, 2013-12-20

Brak komentarzy - bądź pierwszy

Dodaj komentarz
Wyślij
Ostatnie komentarze
chcę dodać kolumnę, która będzie połączeniem dwóch innych istniejących już kolumn, jak powinien wyglądać scrypt?
Przydałyby się jeszcze 2 rzeczy do cz. 3 i byłoby superanckie.
1. Na starcie sortuje wg jakiejś kolumny i tam jest już strzałeczka. Widok takiej strzałeczki daje znać użytkownikowi, że taką tabele można sortować, a na razie pojawia się ona tylko po kliknięciu.
2. Uwzględnienie polskich znaków, bo np. przy sortowaniu Nazwisk i Imion jest to bardzo uciążliwe.
Ogólnie bardzo fajnie i prosto.
PS. Jest ten artykuł z jQuery już dostępny.
bardo ciekawe , można dzięki takim wpisom dostrzec wędkę..
Bardzo dziękuję za jasne tłumaczenie z dobrze dobranym przykładem!