Jak uzyskać dokładną wydajność zapytania?

Usiłuję poprawić wydajność procedury składowanej. Kiedy uruchamiam SP, kończy się on niemal natychmiast, jakby coś było buforowane. Powiedziano mi, żebym używał następujących dwóch wierszy SQL przed wykonaniem SP w SSMS:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Kiedy uruchamiam SP z dwoma liniami kodu powyżej SP, kończy się w około 8 sekund. Jednak czy to naprawdę daje mi rzeczywisty czas wykonania (tak, jak gdybym uruchomił to z aplikacji)? Skąd mam wiedzieć?

Te polecenia wykonują dwie rzeczy:

• Wyczyść pamięć podręczną stron, która przechowuje strony danych, które zostały już pobrane z dysku (zwykle największym czynnikiem w czasie zapytania jest dostęp do dysku)
• Wyczyść pamięć podręczną planu zapytań, co oznacza, że ​​serwer musi utworzyć nowy plan zapytań. Zwykle nie jest to istotne, z wyjątkiem bardzo dużych transakcji.

Zasadniczo otrzymujesz czas, który jest równoważny scenariuszowi „najgorszego przypadku” - po prostu ponownie uruchomiłeś serwer i nic nie jest w pamięci. Kolejne przebiegi nie muszą ponosić kosztów pobierania danych z dysku, ponieważ strony te są już załadowane do pamięci.

Jest to podobne do rzeczywistej sytuacji - pierwszy użytkownik, który uruchomi określone zapytanie, prawdopodobnie będzie musiał czekać dłużej niż przy kolejnych uruchomieniach, zakładając, że sprawdzasz te same dane.

Dobrą metodą, którą lubię używać, jest uruchamianie wiele razy i przyjmowanie średniej. Jest to szczególnie pomocne w środowisku współdzielonym, ponieważ nie masz pełnej kontroli nad zasobami współdzielonymi, takimi jak tempdb.

Możesz także użyć tych poleceń, aby uzyskać więcej informacji o tym, co faktycznie dzieje się za kulisami:

SET STATISTICS IO ON
SET STATISTICS TIME ON

Dostarczą one szczegółowych informacji o odczytach stron z dysku (na obiekt), logicznych odczytach stron, czasie spędzonym na tworzeniu planu i czasie wykonywania zapytania.