SQL Server 2016 – Query Store część 1

Query Store  jest jedną z nowych funkcjonalności SQL Server 2016, które zaintrygowały mnie najbardziej. Mechanizm ten pozwala m.in na detekcję ogólnych problemów wydajnościowych, parameter sniffingu i przede wszystkim pozwala na trwałe przechowywanie danych na temat zapytań w naszej bazie danych.  Każdy z nas napotkał na problem różnych planów zapytań dla tego samego zapytania – do tej pory wykrycie tego typu przypadków nie należało do najprostszych. Zawsze stawaliśmy pomiędzy młotem a kowadłem: z jednej strony mechanizm cache’owania planów zapytań pozwalający na oszczędności w czasie potrzebne na optymalizację zapytań, z drugiej zaś strony powodowało to, iż plan znajdujący się w cache nie był optymalny dla wszystkich możliwych wartości parametrów tego samego zapytania – szczególnie jeśli mamy do czynienia ze zbiorem o skośnym rozkładzie częstości. Query Store jest niezwykle przydatnym narzędziem w tego typu sytuacjach zarówno pod kątem ich monitorowania jak i rozwiązywania – zapraszam do lektury!

Pierwszą informacją jaką warto zapamiętać jest fakt, iż Query Store jest funkcjonalnością dostępną już w wersji Express SQL Server 2016! Jest to świetna wiadomość gdyż bardzo wiele instancji u klientów z którymi pracuje nie posiada SQL Server Enterprise, a dobrze wiemy jako bardzo mniejsze wersje są ograniczone pod kątem funkcjonalności w stosunku do wersji Enterprise – także Microsoftowi należą się podziękowania – gdyż wreszcie uboższe wersje dostaje coś naprawdę wartościowego.

Przechodząc już nieco do technicznej strony opisywanego mechanizmu poniżej przedstawiłem uproszczony schemat i architekturę jego działania.

Jak można zauważyć po fazie optymalizacji dane z nowopowstałego planu zapytania przesyłane są do Query Store, następnie po fazie wykonania przesyłane są dane związane z statystykami wykonania. Dane te znajdują się w pamięci operacyjnej, następnie są one asynchronicznie zrzucane na dysk w określonych interwałach czasowych. Interwały te mogą oczywiście być przez nas modyfikowane – domyślnie zrzuty na dysk odbywają się co 15 minut. Możecie powiedzieć, że wszystkie informacje były już dostępne wcześniej – jest jednak między standardowymi DMV a Query Store zasadnicza różnica. Dane w opisywanej technologii są trwale przechowywane, gdzie DMV są automatycznie czyszczone w momencie restartu serwera. Sam cache planów nie jest stworzony do przechowywania historii – znajdziemy tam jedynie ostatnie plany  dla naszych zapytań. Oczywiście jak każda dodatkowa funkcjonalność dodana do fazy optymalizacji i wykonania zapytania tak również QueryStore wiąże się z dodatkowym narzutem wydajnościowym wynoszącym do 5%- jednak w bardzo wielu przypadkach jest to bardzo niewiele w porównaniu z zyskami jakie otrzymujemy.

Na ten moment zostawmy architekturę i sprawdźmy Query Store w działaniu. Do testów posłuży nam hurtownia WideWorldImportersDW dostępna do ściągnięcia na CodePlex. Pierwszym krokiem jaki musimy wykonać polega na utworzeniu testowej procedury , która będzie mogła mieć więcej niż jeden plan zapytania.

CREATE PROC dbo.usp_GetQuantityBySalesPerson @SalesPersonKey int
AS
 SELECT 
	[Salesperson Key],
	SUM([Quantity]) AS [Quantity],
	count(*) AS Cnt
FROM 
	[Fact].[Order]
WHERE 
	[Salesperson Key]=@SalesPersonKey
 GROUP BY
	[Salesperson Key]

Jak widzicie do parametryzacji wybrałem atrybut SalesPersonKey, który charakteryzuje się skośnym rozkładem częstości tak jak zostało to przedstawione na poniższym zestawieniu

Domyślnie Query Store jest wyłączony – włączmy go poniższą komendą:

ALTER DATABASE [WideWorldImportersDW] SET QUERY_STORE=ON;

Jeżeli mechanizm został przez was włączony wcześniej to należy wyczyścić zebrane przez niego dane aby uzyskać porównywalny z niniejszym przykładem efekt:

ALTER DATABASE [WideWorldImportersDW] SET QUERY_STORE CLEAR;

Następnie wykonajmy wcześniej przygotowaną procedurę z różnymi parametrami.

EXEC dbo.usp_GetQuantityBySalesPerson @SalesPersonKey=19 

EXEC dbo.usp_GetQuantityBySalesPerson @SalesPersonKey=162 WITH RECOMPILE
GO 10

Do drugiego wykonania procedury dodałem WITH RECOMPILE aby nastąpiła pełna optymalizacja bez korzystania z cache. Po wykonaniu procedury możemy przejść do analizy gotowych raportów mechanizmu Query Store. Znajdziemy je poprzez eksplorator obiektów dostępny z poziomu Management Studio.

Otwórzmy zestawienie Regressed Queries, gdzie przedstawione zostaną podstawowe informacje na temat zapytań wykonywanych na bazie. Odszyfrujmy sobie tutaj pojęcie “regressed query” – jest to nic innego jak zapytanie, które na przestrzeni czasu otrzymała “gorszy” plan niż miała poprzednio – wspomniany raport ma za zadanie przedstawiać właśnie takie zapytania.

Górna część okna przedstawia zapytania zebrane przez Query Store oraz podstawowe informacje na ich temat. Okno to możemy sortować po statystyce, która w danym momencie nas interesuje tj.:

• Czas trwania
• Czas procesora
• Logiczne odczyty
• Logiczne zapisy
• Fizyczne zapisy
• Konsumpcja pamięci

Do każdej z tych statystyk możemy wybrać funkcję agregacji:

• sumę
• średnią
• maksimum
• minimum
• odchylenie standardowe

Czyli możemy analizować zapytania po np. średniej konsumpcji pamięci lub też po maksymalnej liczbie logicznych odczytów. Zamiast ustawiać osie wykresu możemy również wybrać przycisk Configure – pojawi się okno konfiguracyjne pozwalające ustawić powyższe opcje oraz interwał czasu z jakiego mają być wyświetlane dane. Ponadto w tym miejscu możemy ustawić czy wyświetlane mają być n najbardziej obciążającyh zapytań czy też wszystkie zapytania.

W drugiej części ekranu mamy do dyspozycji wykres bąbelkowy obrazujący różne plany wykonania dla aktualnie wybranego zapytania na przestrzeni czasu i określonej funkcji agregacji. Okno to jest bardzo przydatne gdyż pozwala nam w szybki sposób dostrzec, iż to samo zapytanie mogło na przestrzeni czasu zostać wykonane różnymi planami zapytania.

Jak można zauważyć nasze zapytanie zostało wykonane z dwoma różnymi planami zapytań opatrzonymi identyfikatorami 1 oraz 2. Można wywnioskować, iż plan o id 2 wykonał się zdecydowanie szybciej dla tego zapytania. Aby porównać oba zapytania możemy zaznaczyć wybrane plany poprzez przytrzymanie klawisza SHIFT i wybrać opcję “Compare Plans” – pozwoli to nam na wyświetlenie obu planów i ich analizę w bardziej przystępnej formie.

Jeżeli zaznaczymy jeden z operatorów i wyświetlimy jego okno właściwości (korzystając z menu kontekstowego lub klikając F4) to dodatkowo SSMS graficznie wskaże nam różnice pomiędzy poszczególnymi właściwościami operatora:

W momencie gdy wiemy, który plan powinien być brany pod uwagę dla każdego wykonania zapytania możemy wrócić do panelu Regressed Queries następnie zaznaczyć wybrany przez nas plan i wybrać opcję Force:

Od tej pory plan ten będzie wymuszony bez względu na wartość parametru. Oczywistym jest fakt, iż należy przeanalizować zapytanie gdyż wymuszenie może wiązać się z pogorszeniem działania zapytania w późniejszym czasie. Warto w tym miejscu odnotować, iż okna w Query Store pozwalają na przeglądanie danych w sposób graficzny oraz tabelaryczny – ten pierwszy oferuje tzw. tooltipy po najechaniu na element graficzny dodatkowe informacje zostaną wyświetlone. Informacje przedstawione w formie tekstowej przedstawiają wszystkie zebrane informacje na temat zapytania.

Z Query Store związana jest duża ilość właściwości – przejrzyjmy je po krótce i mówmy. Aby je wyświetlić należy wejść we właściwości bazy danych i wybrać zakładkę Query Store – powinien wyświetlić się nam obraz podobny do poniższego:

Operation Mode – OFF oznacza, iż mechanizm jest wyłączony, READ_WRITE oznacza, iż można odczytywać zebrane informacje i zbierane są kolejne, a READ_ONLY pozwala jedynie na odczyt tego co udało się zebrać – nowe dane nie są gromadzone.

Data Flush Interval (minutes) – ilość minut po których dane Query Store mają być zrzucane na dysk. W przypadku gdy SQL Server jest mocno obciążony pod kątem użycia pamięci operacyjnej dane mogą być zrzucone na dysk wcześniej.

Statistics Collection Interval – ilość minut po których Query Store zbiera dane.

Max Size (MB) – ile miejsca Query Store ma używać do zapisywania danych. W momencie zapełnienia tej wielkości mechanizm automatycznie przełączany jest z trybu READ_WRITE na READ_ONLY.

Query Store Capture Mode – oznacza tryb zbierania danych, NONE – dane nie będą zbierane,All -wszystkie zapytania będą zapisywane,  Auto – zapytania będą zbierane na podstawie użycia zasobów.

Size Based Cleanup Mode – działania czyszczące podjęte po osiągnięciu maksymalnego rozmiaru: Off – żadne działania nie zostaną podjęte, Auto- czyszczenie zostanie zainicjowane automatycznie.

Stale Query Threshold (Days) – właściwość ustawiająca liczbę dni po których dane będą z Query Store będą automatycznie usuwane.

W dolnej części okna można zauważyć dwa wykresy kołowe przedstawiające rozmiar Query Store w stosunku do bazy danych oraz ilość wolnego i zajętego miejsca w mechanizmie. Przycisk Purge Query Data jak sama nazwa wskazuje czyści dane i jest odpowiednikiem przedstawionego wcześniej zapytania SET QUERY_STORE=CLEAR.

Po dłuższej pracy z Query Store zobaczycie, iż potrafi on bardzo szybko się zaśmiecić zarówno przypadkowymi zapytaniami adhoc jak i zapytaniami do samego Query Store. Oczywiście mamy pewne możliwości sterowania tym co trafia do mechanizmu oraz co powinno być z niego usunięte. Warto również odnotować, iż wbudowane zestawienia są jedynie gotową propozycją korzystania z mechanizmu. Możliwe jest budowanie własnych rozwiązań opartych o Query Store – możemy to zrobić dzięki całemu zestawowi obiektów dynamicznych oraz procedur ale o nich powiemy sobie w drugiej części niniejszego artykułu, który pojawi się już niebawem.