SQL Server 2016 – Query Store część 2

W ostatnim artykule omówiliśmy sobie wstępne informacje na temat mechanizmu Query Store. Mając te podstawowe informacje możemy przejść do obiektów powiązanych z opisywanym mechanizmem, scenariuszami ich użycia oraz bardziej zaawansowanymi funkcjonalnościami – zapraszam do lektury!

Na początku wypiszemy sobie widoki systemowe związane z Query Store po to, aby utrwalić ogólną informację na ich temat – oczywiście nie będziemy ich opisywać kolumna po kolumnie gdyż te takowy opis bardzo łatwo znaleźć w dokumentacji.

sys.database_query_store_options – widok systemowy pozwalający sprawdzić obecne ustawienia mechanizmu takie jak m.in tryb działania, tryb czyszczenia czy chociażby interwał zrzutów na dysk twardych -w skrócie jest to odpowiednik tego co widzimy na zakładce Query Store we właściwościach bazy danych.

sys.query_store_query – widok systemowy pozwalający wyświetlić zapytania znajdujące się w mechanizmie Query store oraz statystyki wywołania z nimi związane.

sys.query_store_plan – widok systemowy pozwalający wyświetlić plany zapytania oraz ich właściwości związane z zapytaniami przechowywanymi w Query store.

sys.query_store_query_text– widok systemowy pozwalający wyświetlić tekst konkretnego zapytania przechowywanego w mechanizmie i jego tzw. sql_handle (czyli wartość hash naszego zapytania/batcha).

sys.query_store_runtime_stats – widok systemowy pozwalający wyświetlić pełne statystyki wykonania zapytania.

sys.query_context_settings – widok systemowy zwracający ustawienia kontekstowe z użyciem którego wykonane zostało zapytanie.

Poniżej przedstawiony został diagram powyższych widoków systemowych i pól po których możemy je połączyć.

Query Store - dmv diagramJPG

Korzystanie z obiektów systemowych związanych z Query Store jest bardzo proste i tak jak w przypadku innych tego typu obiektów wymaga jedynie znajomości diagramu relacji. Poniżej przedstawię kilka przydatnych zapytań zbudowanych na podstawie powyższych widoków.

15 najbardziej wymagających zapytań pod kontem średniego czasu wykonania. Dodatkowo wyświetlane są pozostałe statystyki związane z wykonaniem oraz informacja czy zapytanie korzysta z wymuszonego przez Query Store planu zapytania.

15 ostatnio wykonywanych zapytań na bazie danych wraz z takimi informacjami jak to czy użyto wymuszonego planu, średni czas kompilacji planu, czy plan był równoległy oraz czy plan był trywialny.

15 zapytań, które zwróciły największą liczbę wierszy wraz ze średnią ilością odczytów logicznych i fizycznych

Oczywiście oprócz widoków wraz z mechanizmem Query Store dostarczone zostały następujące procedury składowane:

sp_query_store_flush_db -bezparametrowa procedura, która zrzuca dane Query Store z pamięci na dysk.

sp_query_store_force_plan @query_id, @plan_id – procedura powodująca wymuszenie wykorzystania określonego planu wykonania dla danego zapytania. Identyfikatory zapytania i planu można uzyskać korzystając z wyżej wymienionych widoków systemowych lub graficznej nakładki na Query Store.

sp_query_store_unforce_plan @query_id, @plan_id – procedura usuwająca wymuszenie określonego planu dla określonego zapytania.

sp_query_store_remove_plan @plan_id – procedura usuwająca z plan store mechanizmu Query Store określony plan zapytania.

sp_query_store_remove_query @query_id – procedura usuwająca z Query store określone zapytanie oraz wszelkie powiązane z nim plany zapytań.

sp_query_store_reset_exec_stats @plan_id – procedura powodująca reset statystyk wykonania powiązanych z określonym planem zapytania.

Jak zapewne się domyślacie w przypadku użycia graficznej wersji Query Store pod spodem wykonywane są powyższe procedury. Warto jednak używać kodu samego w sobie aby przyswoić i lepiej zrozumieć cały mechanizm. Jeśli chodzi o przykład użycia powyższych procedur to na MSDN znalazłem świetny przykład, który usuwa z mechanizmu wszystkie zapytania, które zostały wykonane tylko raz i te, które są starsze niż 30 dni

Warto opracować sobie swoją własną procedurę czyszczącą Query Store ze względu na fakt, iż po pewnym czasie nasz mechanizm zostanie zasypany ogromną ilością śmieci pod postacią zapytań ad-hoc, zapytań do samego query store i nie znaczących pod kątem tuningu szybko wykonujących się zapytań.

Oprócz obiektów systemowych Query Store przynosi nam dodatkowe liczniki monitora wydajności. Jak możecie zauważyć na poniższym zrzucie ekranowym liczniki te są dosyć łatwe do rozszyfrowania i służą głównie do monitorowania narzutu jakie generuje QS.

Query Store - perfmon counters

Oprócz tego mamy do dyspozycji ogromną ilość obiektów Extended Events oraz nowe rodzaje waits’ów. Tych pierwszych nie będę opisywał ze względu na ich mnogość natomiast jeśli chodzi o waits’y to możemy je odróżnić poprzez przedrostek QDS występujący w nazwie – tak więc poniższe zapytanie zwróci nam podstawowe informacje o waits’ach związanych z Query Store:

W przypadku gdy zobaczymy, iż waitsy związane z QS zajmują istotną ilość czasu warto zainteresować się m.in interwałami związanymi z zrzucaniem danych na dysk oraz ogólnie tzw. “memory pressure” czyli szczytowym wykorzystaniem pamięci, które jak już wspominałem w porzednim poście takie zrzuty wymusza. Mam nadzieję, że powyższe informacje okazały się dla Was użytecznei zaprzyjaźnicie się “na dłużej” z mechanizmem Query Store.

Follow me

Adrian Chodkowski

SQL geek, Data enthusiast, Consultant & Developer
Adrian Chodkowski
Follow me

Leave a Comment

Your email address will not be published. Required fields are marked *