Scenariusze użycia repozytorium zapytań

Dotyczy: SQL Server 2016 (13.x) i nowsze wersje Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (tylko dedykowana pula SQL)bazy danych SQL w usłudze Microsoft Fabric

Repozytorium zapytań może być używane w wielu scenariuszach, kiedy śledzenie i zapewnienie przewidywalnej wydajności obciążenia są krytyczne. Oto kilka przykładów, które można wziąć pod uwagę:

• Punktowanie i naprawianie zapytań przy użyciu regresji wyboru planu

• Identyfikowanie i dostrajanie zapytań zużywających najwięcej zasobów

• Testowanie A/B

• Zachowaj stabilność wydajności podczas uaktualniania do nowszego programu SQL Server

• Identyfikowanie i ulepszanie obciążeń ad hoc

• Aby uzyskać więcej informacji na temat konfigurowania magazynu zapytań i administrowania nimi, zobacz Monitorowanie wydajności przy użyciu magazynu zapytań.

• Aby uzyskać informacje na temat odkrywania informacji do podjęcia działań i poprawy wydajności za pomocą magazynu zapytań, zobacz Dostrajanie wydajności przy użyciu magazynu zapytań.

• Aby uzyskać informacje na temat obsługi magazynu zapytań w usłudze Azure SQL Database, zobacz Obsługa magazynu zapytań w usłudze Azure SQL Database.

Identyfikowanie i naprawianie zapytań z regresjami dotyczącymi wyboru planu

Podczas regularnego wykonywania zapytań optymalizator zapytań może zdecydować się na wybranie innego planu, ponieważ ważne dane wejściowe stały się inne: kardynalność danych uległa zmianie, utworzono indeksy, zmieniono lub usunięto, zaktualizowano statystyki itp. Zazwyczaj nowy plan jest lepszy lub mniej więcej taki sam, jak wcześniej używany plan. Istnieją jednak przypadki, w których nowy plan jest znacznie gorszy — ta sytuacja jest określana jako regresja zmiany wyboru planu. Przed magazynem zapytań trudno było zidentyfikować i rozwiązać problem, ponieważ program SQL Server nie dostarczył wbudowanego magazynu danych, aby użytkownicy mogli przyjrzeć się planom wykonywania, które były używane w czasie.

Magazyn zapytań umożliwia szybkie wykonywanie następujących czynności:

• Zidentyfikuj wszystkie zapytania, których wskaźniki wydajności zostały pogorszone, w okresie zainteresowania (ostatnia godzina, dzień, tydzień itp.). Użyj zapytań cofniętych w programie SQL Server Management Studio, aby przyspieszyć analizę.

• Wśród zapytań, które uległy pogorszeniu, łatwo jest znaleźć te zapytania, które miały wiele planów i które uległy pogorszeniu z powodu złego wyboru planu. Użyj okienka podsumowania planu w zapytaniach o regresji, aby zwizualizować wszystkie plany dla zapytań o regresję i wydajność zapytań w czasie.

• Wymuś poprzedni plan z historii, jeśli okaże się on lepszy. Użyj przycisku Force Plan w Regressed Query, aby wymusić wybór planu dla zapytania.

Aby uzyskać szczegółowy opis scenariusza, zapoznaj się z artykułem Query Store: rejestrator danych lotu dla twojej bazy danych blog.

Identyfikowanie i dostrajanie zapytań zużywających najwięcej zasobów

Mimo że obciążenie może generować tysiące zapytań, zazwyczaj tylko kilka z nich rzeczywiście używa większości zasobów systemowych i w związku z tym wymaga uwagi. Wśród najważniejszych zapytań zużywających zasoby zazwyczaj znajdziesz zapytania, które uległy pogorszeniu lub które można ulepszyć za pomocą dodatkowego dostrajania.

Najprostszym sposobem rozpoczęcia eksploracji jest otwarcie zapytań najbardziej obciążających zasoby w programie Management Studio. Interfejs użytkownika jest oddzielony trzema okienkami: histogram reprezentujący zapytania zużywające najwięcej zasobów (po lewej), podsumowanie planu dla wybranego zapytania (po prawej) i wizualny plan zapytania dla wybranego planu (u dołu). Wybierz pozycję Konfiguruj, aby kontrolować liczbę zapytań, które chcesz analizować, oraz interwał czasu zainteresowania. Ponadto można wybrać różne wymiary zużycia zasobów (czas trwania, procesor CPU, pamięć, operacje we/wy, liczba wykonań) i punkt odniesienia (Średnia, Minimalna, Maksymalna, Łączna, Odchylenie standardowe).

Zapoznaj się z podsumowaniem planu po prawej stronie, aby przeanalizować historię wykonania i dowiedzieć się więcej o różnych planach oraz ich statystykach czasu wykonywania. Użyj dolnego okienka, aby sprawdzić różne plany lub porównać je wizualnie, renderowane obok siebie (użyj przycisku Porównaj).

Po zidentyfikowaniu zapytania z nieoptymalną wydajnością akcja zależy od charakteru problemu:

1. Jeśli zapytanie zostało wykonane z wieloma planami, a ostatni plan jest znacznie gorszy niż poprzedni plan, możesz użyć mechanizmu wymuszania planu, aby upewnić się, że program SQL Server będzie używać optymalnego planu do przyszłych wykonań

2. Sprawdź, czy optymalizator sugeruje brak indeksów w planie XML. Jeśli tak, utwórz brakujący indeks i użyj magazynu zapytań, aby ocenić wydajność zapytań po utworzeniu indeksu

3. Upewnij się, że statystyki bazowych tabel używanych przez zapytanie są aktualne up-to-date.

4. Upewnij się, że indeksy używane przez zapytanie są defragmentowane.

5. Rozważ ponowne zapisywanie kosztownych zapytań. Na przykład skorzystaj z zalet parametryzacji zapytań i zmniejsz użycie dynamicznego języka SQL. Zaimplementuj optymalną logikę podczas odczytywania danych (zastosuj filtrowanie danych po stronie bazy danych, a nie po stronie aplikacji).

Testowanie A/B

Użyj Query Store, aby porównać wydajność obciążenia przed i po zmianach w aplikacji.

Poniższa lista zawiera kilka przykładów, w których można użyć magazynu zapytań do oceny wpływu zmiany środowiska lub aplikacji na wydajność obciążenia:

• Wdrażanie nowej wersji aplikacji.

• Dodawanie nowego sprzętu do serwera.

• Tworzenie brakujących indeksów w tabelach, do których odnoszą się kosztowne zapytania.

• Stosowanie zasad filtrowania dla zabezpieczeń na poziomie wiersza. Aby uzyskać więcej informacji, zobacz Optymalizowanie zabezpieczeń na poziomie wiersza przy użyciu magazynu zapytań.

• Dodawanie wersjonowania czasowego systemu do tabel, które są często modyfikowane przez aplikacje OLTP.

W każdym z tych scenariuszy stosuje się następujący przepływ pracy:

1. Uruchom swoje operacyjne obciążenia z Query Store przed planowaną zmianą, aby stworzyć bazowy pomiar wydajności.

2. Zastosuj zmianę aplikacji w kontrolowanym momencie w czasie.

3. Kontynuuj uruchamianie obciążenia wystarczająco długo, aby wygenerować obraz wydajności systemu po zmianie

4. Porównaj wyniki z #1 i #3.

   1. Otwórz Ogólne spożycie bazy danych, aby określić wpływ na całą bazę danych.

   2. Otwórz zapytania zużywające najwięcej zasobów (lub uruchom własną analizę przy użyciu Transact-SQL), aby przeanalizować wpływ zmiany na najważniejsze zapytania.

5. Zdecyduj, czy zmiana ma być zachowana, czy wycofana w przypadku, gdy nowa wydajność jest niedopuszczalna.

Poniższa ilustracja pokazuje analizę Query Store (krok 4) w przypadku utworzenia brakującego indeksu. Otwórz okienko podsumowania zapytań zużywających najwięcej zasobów/Planu, aby uzyskać ten widok dla zapytania, które powinno mieć wpływ na tworzenie indeksu:

Ponadto można porównać plany przed utworzeniem indeksu i po nim, renderując je obok siebie. ("Opcja paska narzędzi 'Porównaj plany wybranego zapytania w osobnym oknie', która jest oznaczona czerwonym kwadratem na pasku narzędzi.")

Plan przed utworzeniem indeksu (plan_id = 1, powyżej) brakuje wskazówki dotyczącej indeksu, a możesz sprawdzić, czy „skanowanie indeksu klastrowanego” było najdroższym operatorem w zapytaniu, oznaczony czerwonym prostokątem.

Po utworzeniu brakującego indeksu (plan_id = 15, znajduje się poniżej) plan teraz zawiera wyszukiwanie indeksu (nieklastrowane), co zmniejsza całkowity koszt zapytania i poprawia wydajność zapytania (zielony prostokąt).

Na podstawie analizy prawdopodobnie zachowasz indeks w miarę poprawy wydajności zapytań.

Zachowaj stabilność wydajności podczas uaktualniania do nowszego programu SQL Server

Przed programem SQL Server 2014 (12.x) użytkownicy byli narażeni na ryzyko regresji wydajności podczas uaktualniania do najnowszej wersji platformy. Powodem było to, że najnowsza wersja optymalizatora zapytań stała się aktywna natychmiast po zainstalowaniu nowych bitów.

Począwszy od programu SQL Server 2014 (12.x), wszystkie zmiany optymalizatora zapytań są powiązane z najnowszymi poziomu zgodności bazy danych, więc plany nie są zmieniane bezpośrednio w momencie uaktualniania, ale raczej wtedy, gdy użytkownik zmieni COMPATIBILITY_LEVEL na najnowszy. Ta funkcja, w połączeniu z magazynem zapytań, zapewnia doskonałą kontrolę nad wydajnością zapytań w procesie uaktualniania. Zalecany przepływ pracy uaktualniania przedstawiono na poniższej ilustracji:

1. Uaktualnij program SQL Server bez zmiany poziomu zgodności bazy danych. Nie uwidacznia najnowszych zmian optymalizatora zapytań, ale nadal udostępnia nowsze funkcje programu SQL Server, w tym magazyn zapytań.

2. Włącz magazyn zapytań. Aby uzyskać więcej informacji, zobacz Dostosuj magazyn zapytań do obciążenia.

3. Pozwala magazynowi zapytań przechwytywać zapytania i plany oraz ustawia punkt odniesienia wydajności na podstawie źródłowego lub poprzedniego poziomu zgodności bazy danych. Spędź na tym kroku wystarczająco dużo czasu, aby uchwycić wszystkie plany i uzyskać stabilną podstawę odniesienia. Może to być czas trwania zwykłego cyklu biznesowego dla obciążenia produkcyjnego.

4. Przejdź do najnowszego poziomu zgodności bazy danych: uzyskaj obciążenie uwidocznione najnowszemu optymalizatorowi zapytań, aby potencjalnie utworzyć nowe plany.

5. Użyj Query Store do analizy i napraw regresyjnych: zazwyczaj nowe ulepszenia Optymalizatora Zapytań powinny prowadzić do lepszych planów. Magazyn zapytań zapewnia jednak łatwy sposób identyfikowania regresji wyboru planu i naprawiania ich przy użyciu mechanizmu wymuszania planu. Począwszy od programu SQL Server 2017 (14.x), w przypadku korzystania z funkcji automatycznej korekty planu ten krok staje się automatyczny.

   a. W przypadku występowania regresji, wymuś wcześniej znany dobry plan w Query Store.

   b. Jeśli istnieją plany zapytań, których nie można wymusić, lub jeśli wydajność jest nadal niewystarczająca, rozważ przywrócenie poziomu zgodności bazy danych do poprzedniego ustawienia, a następnie skontaktowanie się z działem pomocy technicznej firmy Microsoft.

Napiwek

Użyj programu SQL Server Management Studio uaktualnij bazę danych, aby uaktualnić poziom zgodności bazy danych bazy danych. Aby uzyskać szczegółowe informacje, zobacz Uaktualnianie baz danych przy użyciu asystenta dostrajania zapytań.

Identyfikowanie i ulepszanie obciążeń ad hoc

Niektóre obciążenia nie mają dominujących zapytań, które można dostosować, aby poprawić ogólną wydajność aplikacji. Te obciążenia są zwykle scharakteryzowane stosunkowo dużą liczbą różnych zapytań, z których każda zużywa część zasobów systemowych. Ponieważ są unikatowe, te zapytania są wykonywane bardzo rzadko (zwykle tylko raz, dlatego nazywamy je ad hoc), więc ich zużycie zasobów czasowych nie jest krytyczne. Z drugiej strony, biorąc pod uwagę, że aplikacja generuje nowe zapytania netto przez cały czas, znaczna część zasobów systemowych jest poświęcana na kompilację zapytań, co nie jest optymalne. Nie jest to idealna sytuacja w przypadku Magazynu Zapytań, ponieważ duża liczba zapytań i planów powoduje zapełnianie zarezerwowanego miejsca, co oznacza, że Magazyn Zapytań prawdopodobnie bardzo szybko trafi do trybu tylko do odczytu. Jeśli aktywowałeś zasady oczyszczania opartego na rozmiarze (zdecydowanie zalecane, aby magazyn zapytań był ciągle uruchomiony), proces w tle będzie czyścił struktury magazynu zapytań, i zużywający znaczące zasoby systemowe.

Widok zapytań zużywających najwięcej zasobów daje pierwsze pojęcie o ad hoc charakterze obciążenia:

Użyj metryki liczba wykonań, aby przeanalizować, czy najważniejsze zapytania są ad hoc (wymaga to uruchomienia magazynu zapytań przy użyciu QUERY_CAPTURE_MODE = ALL). Na powyższym diagramie widać, że 90% spośród Twoich najwięcej zużywających zasoby zapytań jest wykonywanych tylko raz.

Alternatywnie możesz uruchomić skrypt Transact-SQL, aby uzyskać całkowitą liczbę tekstów zapytań, zapytań i planów w systemie i określić, jak różnią się one, porównując query_hash i query_plan_hash:

--Do cardinality analysis when suspect on ad hoc workloads
SELECT COUNT(*) AS CountQueryTextRows FROM sys.query_store_query_text;  
SELECT COUNT(*) AS CountQueryRows FROM sys.query_store_query;  
SELECT COUNT(DISTINCT query_hash) AS CountDifferentQueryRows FROM  sys.query_store_query;  
SELECT COUNT(*) AS CountPlanRows FROM sys.query_store_plan;  
SELECT COUNT(DISTINCT query_plan_hash) AS  CountDifferentPlanRows FROM  sys.query_store_plan;  

Jest to jeden z potencjalnych wyników, które można uzyskać w przypadku obciążenia za pomocą zapytań ad hoc:

Wynik zapytania pokazuje, że pomimo dużej liczby zapytań i planów w magazynie zapytań ich query_hash i query_plan_hash faktycznie nie różnią się. Współczynnik między unikatowymi tekstami zapytań a unikatowymi skrótami zapytań, który jest znacznie większy niż 1, wskazuje, że obciążenie jest dobrym kandydatem do parametryzacji, ponieważ jedyną różnicą między zapytaniami jest stała (parametr) podana jako część tekstu zapytania.

Zazwyczaj taka sytuacja występuje, jeśli aplikacja generuje zapytania (zamiast wywoływania procedur składowanych lub sparametryzowanych zapytań) lub jeśli opiera się na strukturach mapowania relacyjnego obiektów, które domyślnie generują zapytania.

Jeśli kontrolujesz kod aplikacji, możesz rozważyć ponowne zapisanie warstwy dostępu do danych w celu korzystania z procedur składowanych lub sparametryzowanych zapytań. Można jednak znacznie poprawić tę sytuację bez zmian w aplikacji, wymuszając parametryzację zapytań dla całej bazy danych (wszystkich zapytań) lub dla poszczególnych szablonów zapytań z tym samym query_hash.

Podejście z poszczególnymi szablonami zapytań wymaga utworzenia przewodnika po planie:

--Apply plan guide for the selected query template 
DECLARE @stmt nvarchar(max);  
DECLARE @params nvarchar(max);  
EXEC sp_get_query_template   
    N'<your query text goes here>',  
    @stmt OUTPUT,   
    @params OUTPUT;  
  
EXEC sp_create_plan_guide   
    N'TemplateGuide1',   
    @stmt,   
    N'TEMPLATE',   
    NULL,   
    @params,   
    N'OPTION (PARAMETERIZATION FORCED)';  

Rozwiązanie z przewodnikami planu jest bardziej precyzyjne, ale wymaga więcej pracy.

Jeśli wszystkie zapytania (lub większość z nich) są kandydatami do automatycznego parametryzacji, rozważ skonfigurowanie PARAMETERIZATION = FORCED dla całej bazy danych. Aby uzyskać więcej informacji, zobacz Wytyczne dotyczące używania wymuszonej parametryzacji.

--Apply forced parameterization for entire database  
ALTER DATABASE <database name> SET PARAMETERIZATION FORCED;  

Po zastosowaniu dowolnego z tych kroków zapytania zużywające najwięcej zasobów zostaną wyświetlone różne obrazy obciążenia.

W niektórych przypadkach aplikacja może wygenerować wiele różnych zapytań, które nie są dobrymi kandydatami do automatycznego parametryzacji. W takim przypadku w systemie jest widoczna duża liczba zapytań, ale stosunek między unikatowymi zapytaniami a unikatowymi query_hash prawdopodobnie zbliża się do 1.

W takim przypadku możesz włączyć opcję serwera Optymalizowanie pod kątem obciążeń ad hoc, aby zapobiec marnowaniu pamięci podręcznej na zapytania, które prawdopodobnie nie zostaną wykonane ponownie. Aby zapobiec przechwytywaniu tych zapytań w magazynie zapytań, ustaw QUERY_CAPTURE_MODE na wartość AUTO.

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO 
  
ALTER DATABASE [QueryStoreTest] SET QUERY_STORE CLEAR;  
ALTER DATABASE [QueryStoreTest] SET QUERY_STORE = ON   
    (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO);  

Następne kroki

• Monitorowanie Wydajności przy Użyciu Magazynu Zapytań
• Najlepsze praktyki w przypadku magazynu zapytań
• uaktualnianie baz danych przy użyciu asystenta dostrajania zapytań