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ć.

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.

 	
SELECT TOP 15
	qt.query_sql_text,
	rs.avg_duration,
	rs.max_duration,
	rs.min_duration,
	rs.avg_physical_io_reads,
	rs.avg_logical_io_reads,
	qp.is_forced_plan
FROM 
	sys.query_store_plan qp
INNER JOIN 
	sys.query_store_query q
ON 
	qp.query_id = q.query_id
INNER JOIN 
	sys.query_store_query_text qt
ON 
	q.query_text_id = qt.query_text_id
INNER JOIN 
	sys.query_store_runtime_stats rs
ON 
	qp.plan_id = rs.plan_id
ORDER BY 
	rs.avg_duration DESC

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.

SELECT TOP 15 
	q.query_id,
	qt.query_sql_text, 
    rs.last_execution_time,
	p.is_forced_plan,
	p.avg_compile_duration,
	p.is_parallel_plan,
	p.is_trivial_plan
FROM 
	sys.query_store_query_text AS qt   
JOIN 
	sys.query_store_query AS q   
ON 
	qt.query_text_id = q.query_text_id   
JOIN 
	sys.query_store_plan AS p   
ON 
	q.query_id = p.query_id   
JOIN 
	sys.query_store_runtime_stats AS rs   
ON 
	p.plan_id = rs.plan_id  
ORDER BY 
	rs.last_execution_time DESC;

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

SELECT TOP 15  
	q.query_id,
	qt.query_sql_text,      
        rsi.start_time, 
	rsi.end_time,
	rs.avg_physical_io_reads,
	rs.avg_logical_io_reads, 
	rs.avg_rowcount 
FROM 
	sys.query_store_query_text AS qt   
JOIN 
	sys.query_store_query AS q   
ON 
	qt.query_text_id = q.query_text_id   
JOIN 
	sys.query_store_plan AS p   
ON 
	q.query_id = p.query_id   
JOIN 
	sys.query_store_runtime_stats AS rs   
ON 
	p.plan_id = rs.plan_id   
JOIN 
	sys.query_store_runtime_stats_interval AS rsi   
ON 
	rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id    
ORDER BY 
	rs.avg_rowcount DESC;

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

DECLARE @id int  
DECLARE adhoc_queries_cursor CURSOR   
FOR   
SELECT q.query_id  
FROM sys.query_store_query_text AS qt  
JOIN sys.query_store_query AS q   
    ON q.query_text_id = qt.query_text_id  
JOIN sys.query_store_plan AS p   
    ON p.query_id = q.query_id  
JOIN sys.query_store_runtime_stats AS rs   
    ON rs.plan_id = p.plan_id  
GROUP BY q.query_id  
HAVING SUM(rs.count_executions) < 2   
AND MAX(rs.last_execution_time) < DATEADD (day, 30, GETUTCDATE())  
ORDER BY q.query_id ;  
  
OPEN adhoc_queries_cursor ;  
FETCH NEXT FROM adhoc_queries_cursor INTO @id;  
WHILE @@fetch_status = 0  
    BEGIN   
        PRINT @id  
        EXEC sp_query_store_remove_query @id  
        FETCH NEXT FROM adhoc_queries_cursor INTO @id  
    END   
CLOSE adhoc_queries_cursor ;  
DEALLOCATE adhoc_queries_cursor;

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.

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:

SELECT 
	wait_type,
	waiting_tasks_count,
	wait_time_ms,
	max_wait_time_ms,
	signal_wait_time_ms 
FROM 
	sys.dm_os_wait_stats
WHERE 
	wait_type like 'qds%'

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.