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

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:

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:

Kategoria:Optymalizacja SQLSQL Server