Kiedyś zapytanie działało dobrze, a teraz działa gorzej? Co się zmieniło, że spadek wydajności jest zauważalny?
Nie ma na to jednej odpowiedzi, bo do kroków analizy należałoby dodać także sprawdzenie dostępnej ilości RAM, szybkości dostępu do dysków itp. O ile jednak administratorzy z tymi testami zazwyczaj problemów nie mają, o tyle sprawdzanie wydajności z dokładnością do pojedynczego zapytania zwykle było pewnym problemem. Rozwiązaniem, które ma w tym pomóc jest Query Store dostępny w SQL Server od wersji 2016.
Dzięki zbieranym statystykom na temat zapytań, administrator może porównać plany zapytań jakie były wykorzystywane pewien czas temu z tymi, jakie są wykorzystywane teraz, a nawet określić, który plan zapytania ma być stosowany. I to wszystko odbywa się bez ingerencji w plan zapytania!
Oto jak skonfigurować Query Store
Query Store może być włączony na poziomie bazy danych:
ALTER DATABASE MyDb SET QUERY_STORE = ON
Korzystając z polecenia alter można zmieniać konfigurację Query Store:
- ON/OFF włączy lub wyłączy ten mechanizm
- CLEAR wyczyści zawartość
- OPERATION_MODE definiuje tryb pracy Query Store. Możliwe wartości to:
- READ_WRITE – domyślna wartość, dane będą zapisywane i odczytywane
- READ_ONLY – dane będą tylko odczytywane. Query Store automatycznie przechodzi w ten tryb, gdy całe miejsce dostępne w magazynie danych zostanie wyczerpane
- CLEANUP_POLICY określa politykę przechowywania danych w Query Store. Wskazana po tej opcji liczba określa ilość dni, za jakie dane powinny być przechowywane w magazynie. Dane starsze niż ta liczba, będą usuwane.
- DATA_FLUSH_INTERVAL_SECONDS oznacza co ile sekund dane zbierane przez Query Store są zapisywane na stałe w magazynie. Trzeba zdawać sobie sprawę z tego, że częste zapisy mogą negatywnie wpływać na wydajność bazy oraz, że OPERATION_MODE powinien być READ_WRITE
- MAX_STORAGE_SIZE_MB określa maksymalny rozmiar danych zapisywanych w Query Store. Jeśli rozmiar magazynu osiągnie wskazaną tu wartość, to OPERATION_MODE zmieni się na READ_ONLY. Widać więc, że jeśli chcesz utrzymać tryb pracy READ_WRITE, to sposobem powstrzymującym przechodzenie do trybu READ_ONLY jest zwiększenie tej wartości.
- INTERVAL_LENGTH_MINUTES to interwał w jakim są zbierane i agregowane statystyki
- SIZE_BASED_CLEANUP_MODE może być ustawiony na AUTO lub OFF. Zmiany tego parametru również wpływają na powstrzymanie Query Store do przechodzenia w automatyczny tryb READ_ONLY. Gdy magazyn wypełni się w 90%, to najtańsze w wykonaniu i najstarsze plany zapytań zostaną usunięte z magazynu, robiąc wolne miejsce dla nowych planów. Czyszczone jest ok. 10% wielkości magazynu. Po wszystkim powinno więc pozostać ok 80% wolnego miejsca
- QUERY_CAPTURE_MODE przyjmuje wartości ALL, NONE, AUTO lub CUSTOM.
- ALL – zbiera statystyki dla wszystkich zapytań
- NONE – zbiera statystyki tylko dla tych zapytań, które już wcześniej znalazły się w Query Store
- AUTO – SQL sam decyduje jakie zapytania zbierać. Robi to w oparciu o koszt pojedynczego zapytania oraz częstotliwość uruchamiania zapytań
- CUSTOM – oddaje kontrolę w ręce administratora, który musi zdefiniować odpowiednie policy korzystając z QUERY_CAPTURE_POLICY
- QUERY_CAPTURE_POLICY – określa jakie zapytania należy analizować
- STALE_CAPTURE_POLICY_THRESHOLD – określa czas oceny (evaluation period) używany przez pozostałe parametry do stwierdzenia, czy dane zapytanie należy zapisać w Query Store czy nie
- EXECUTION_COUNT – jeśli w STALE_CAPTURE_POLICY_THRESHOLD dane zapytanie będzie wykonane co najmniej EXECUTION_COUNT razy, to to zapytanie zostanie zapisane w Query Store
- TOTAL_COMPILE_CPU_TIME_MS – jeśli czas kompilacji (CPU compilation time) przekroczy tą wartość w STALE_CAPTURE_POLICY_THRESHOLD to zapytanie zostanie zapisane
- TOTAL_EXECUTION_CPU_TIME_MS – jeśli czas wykonania (CPU execution time) przekroczy tą wartość w STALE_CAPTURE_POLICY_THRESHOLD to zapytanie zostanie zapisane
- MAX_PLANS_PER_QUERY – domyślnie 200. Oznacza ile planów maksymalnie będzie przechowywanych dla jednego zapytania
- WAIT_STATS_CAPTURE_MODE defiuniuje czy należy też zbierać informacje o wait stats. Te dane mogą być przydatne ale zajmują miejsce w magazynie
ALTER DATABASE MyDb SET QUERY_STORE = ON ( OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 15 ), DATA_FLUSH_INTERVAL_SECONDS = 600, MAX_STORAGE_SIZE_MB = 2048, SIZE_BASED_CLEANUP_MODE = AUTO, WAIT_STATS_CAPTURE_MODE = ON ) ;
