Monitorowanie wydajności przy użyciu magazynu zapytańMonitor performance by using the Query Store

Dotyczy: SQL Server 2016 (13.x) i nowsze wersje Wystąpienie zarządzane Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics (tylko dedykowana pula SQL)

Funkcja Magazyn zapytań zapewnia wgląd w wybór planu zapytań i wydajność dla programów SQL Server, Azure SQL Database, Azure SQL Managed Instance i Azure Synapse Analytics. Magazyn zapytań upraszcza rozwiązywanie problemów z wydajnością, ułatwiając szybkie znajdowanie różnic w wydajności spowodowanych zmianami planu zapytań. Magazyn zapytań automatycznie przechwytuje historię zapytań, planów i statystyk środowiska uruchomieniowego i zachowuje je do przeglądu. Oddziela dane według okien czasu, dzięki czemu można zobaczyć wzorce użycia bazy danych i zrozumieć, kiedy na serwerze nastąpiły zmiany planu zapytań. Magazyn zapytań można skonfigurować za pomocą opcji ZMIEŃ ZESTAW BAZ DANYCH.

• 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.For information about operating the Query Store in Azure SQL Database, see Operating the Query Store in Azure SQL Database.
• Aby uzyskać informacje na temat odnajdowania informacji umożliwiających podejmowanie działań i dostrajania wydajności za pomocą magazynu zapytań, zobacz Dostrajanie wydajności za pomocą magazynu zapytań.
• Aby uzyskać informacje na temat kształtowania planów zapytań bez zmieniania kodu aplikacji, zobacz Wskazówki dotyczące magazynu zapytań.

• Magazyn zapytań jest domyślnie włączony dla nowych baz danych Azure SQL Database i Azure SQL Managed Instances.
• Magazyn zapytań nie jest domyślnie włączony dla programu SQL Server 2016 (13.x), SQL Server 2017 (14.x), SQL Server 2019 (15.x). Jest on domyślnie włączony w trybie dla nowych baz danych, począwszy od SQL Server 2022 (16.x). Aby umożliwić funkcje lepszego śledzenia historii wydajności, rozwiązywania problemów związanych z planem zapytań i włączania nowych możliwości w programie SQL Server 2022 (16.x), zalecamy włączenie magazynu zapytań we wszystkich bazach danych.READ_WRITE
• Magazyn zapytań nie jest domyślnie włączony dla nowych baz danych usługi Azure Synapse Analytics.Query Store is not enabled by default for new Azure Synapse Analytics databases.

1. W Eksploratorze obiektów kliknij prawym przyciskiem myszy bazę danych, a następnie wybierz polecenie Właściwości.

   Uwaga

   Wymaga co najmniej wersji 16 programu Management Studio.

2. W oknie dialogowym Właściwości bazy danych wybierz stronę Magazyn kwerend.

3. W polu Operation Mode (Requested) (Tryb pracy (żądany) wybierz opcję Odczyt zapisu.

Użyj instrukcji, aby włączyć magazyn zapytań dla danej bazy danych. Na przykład:ALTER DATABASE

ALTER DATABASE <database_name>
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

W usłudze Azure Synapse Analytics włącz magazyn zapytań bez dodatkowych opcji, na przykład:In Azure Synapse Analytics, enable the Query Store without additional options, for example:

ALTER DATABASE <database_name>
SET QUERY_STORE = ON;

Aby uzyskać więcej opcji składni związanych z magazynem zapytań, zobacz ALTER DATABASE SET Options (Transact-SQL).

Plany wykonywania dla dowolnego konkretnego zapytania w programie SQL Server zazwyczaj ewoluują w czasie z wielu różnych powodów, takich jak zmiany statystyk, zmiany schematu, tworzenie/usuwanie indeksów itp. W pamięci podręcznej procedur (w której są przechowywane plany kwerend buforowanych) jest przechowywany tylko najnowszy plan wykonania. Plany są również eksmitowane z pamięci podręcznej planu z powodu obciążenia pamięcią. W rezultacie regresje wydajności zapytań spowodowane zmianami planu wykonania mogą być nietrywialne i czasochłonne do rozwiązania.

Ponieważ magazyn zapytań zachowuje wiele planów wykonywania na zapytanie, może wymuszać zasady nakazujące Procesorowi zapytań użycie określonego planu wykonywania dla zapytania. Jest to określane jako wymuszanie planu. Wymuszanie planu w magazynie zapytań jest dostarczane przy użyciu mechanizmu podobnego do wskazówki kwerendy USE PLAN, ale nie wymaga żadnych zmian w aplikacjach użytkownika. Wymuszanie planu może rozwiązać regresję wydajności zapytania spowodowaną zmianą planu w bardzo krótkim czasie.

Statystyki oczekiwania są kolejnym źródłem informacji, które pomagają rozwiązywać problemy z wydajnością w programie Aparat baz danych. Przez długi czas statystyki oczekiwania były dostępne tylko na poziomie instancji, co utrudniało cofnięcie oczekiwania na konkretne zapytanie. Począwszy od programu SQL Server 2017 (14.x) i usługi Azure SQL Database, magazyn zapytań zawiera wymiar, który śledzi statystyki oczekiwania. Poniższy przykład umożliwia magazynowi zapytań zbieranie statystyk oczekiwania.

ALTER DATABASE <database_name>
SET QUERY_STORE = ON ( WAIT_STATS_CAPTURE_MODE = ON );

Typowe scenariusze korzystania z funkcji magazynu zapytań to:Common scenarios for using the Query Store feature are:

• Szybko znajdowanie i naprawianie regresji wydajności planu przez wymuszenie poprzedniego planu zapytania. Napraw zapytania, których wydajność ostatnio spadła z powodu zmian planu wykonania.
• Określ, ile razy zapytanie zostało wykonane w danym przedziale czasu, pomagając administratorowi baz danych w rozwiązywaniu problemów z zasobami wydajności.
• Zidentyfikuj n najczęściej wyszukiwanych zapytań (według czasu wykonania, zużycia pamięci itp.) w ciągu ostatnich x godzin.
• Przeprowadź inspekcję historii planów zapytań dla danego zapytania.
• Analizowanie wzorców użycia zasobów (procesora, operacji we/wy i pamięci) dla określonej bazy danych.
• Zidentyfikuj n pierwszych zapytań, które oczekują na zasoby.
• Opis natury oczekiwania dla konkretnego zapytania lub planu.

Magazyn kwerend zawiera trzy magazyny:The Query Store contains three stores:

• Magazyn planu służący do utrwalania informacji o planie wykonania.
• Magazyn statystyk środowiska wykonawczego służący do utrwalania informacji o statystykach wykonania.
• Magazyn statystyk oczekiwania dla utrwalonych informacji o statystykach oczekiwania.

Liczba unikatowych planów, które mogą być przechowywane dla zapytania w magazynie planów, jest ograniczona przez opcję konfiguracji max_plans_per_query. Aby zwiększyć wydajność, informacje są zapisywane w sklepach asynchronicznie. Aby zminimalizować wykorzystanie miejsca, statystyki wykonywania środowiska wykonawczego w magazynie statystyk środowiska wykonawczego są agregowane w stałym przedziale czasu. Informacje w tych magazynach są widoczne przez wysłanie zapytania do widoków katalogu magazynu kwerend.

Poniższe zapytanie zwraca informacje o zapytaniach i planach w magazynie kwerend.

SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*
FROM sys.query_store_plan AS Pl
INNER JOIN sys.query_store_query AS Qry
    ON Pl.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
    ON Qry.query_text_id = Txt.query_text_id;

Dotyczy: SQL Server (począwszy od SQL Server 2022 (16.x))

Funkcja Magazyn zapytań dla replik pomocniczych udostępnia te same funkcje magazynu zapytań dla obciążeń replik pomocniczych, które są dostępne dla replik podstawowych. Gdy jest włączona usługa Magazyn zapytań dla replik pomocniczych, repliki wysyłają informacje o wykonywaniu zapytań, które normalnie byłyby przechowywane w magazynie zapytań, z powrotem do repliki podstawowej. Następnie replika podstawowa utrwala dane na dysku we własnym magazynie zapytań. Zasadniczo istnieje jeden magazyn zapytań współużytkowany między podstawową i wszystkimi replikami pomocniczymi. Magazyn zapytań istnieje w replice podstawowej i przechowuje dane dla wszystkich replik razem.

Aby uzyskać pełne informacje na temat magazynu zapytań dla replik pomocniczych, zobacz Magazyn zapytań dla zawsze włączonych replik pomocniczych grup dostępności.

Po włączeniu magazynu zapytań odśwież część bazy danych w okienku Eksplorator obiektów, aby dodać sekcję Magazyn zapytań.

Wybierz pozycję Regresed Queries (Zapytania regresywne), aby otworzyć okienko Regresed Queries (Zapytania regresywne) w programie SQL Server Management Studio. W okienku Zapytania regresyjne są wyświetlane zapytania i plany w magazynie zapytań. Użyj pól rozwijanych u góry, aby filtrować zapytania na podstawie różnych kryteriów: Czas trwania (ms) (domyślnie), Czas procesora (ms), Odczyty logiczne (KB), Zapisy logiczne (KB), Odczyty fizyczne (KB), Czas CLR (ms), DOP, Zużycie pamięci (KB), Liczba wierszy, Używana pamięć dziennika (KB), Czasowa pamięć bazy danych (KB) i Czas oczekiwania (ms).

Wybierz plan, aby wyświetlić graficzny plan zapytań. Dostępne są przyciski umożliwiające wyświetlanie zapytania źródłowego, wymuszanie i wymuszanie planu zapytania, przełączanie między formatami siatki i wykresu, porównywanie wybranych planów (jeśli wybrano więcej niż jeden) oraz odświeżanie wyświetlania.

Aby wymusić plan, wybierz zapytanie i plan, a następnie wybierz pozycję Wymuś plan. Można wymusić tylko plany, które zostały zapisane przez funkcję planu zapytania i są nadal zachowywane w pamięci podręcznej planu zapytań.

Począwszy od programu SQL Server 2017 (14.x) i usługi Azure SQL Database, statystyki oczekiwania na zapytanie w czasie są dostępne w magazynie zapytań.

W magazynie zapytań typy oczekiwania są łączone w kategorie oczekiwania. Mapowanie kategorii oczekiwania na typy oczekiwania jest dostępne w języku sys.query_store_wait_stats (języka Transact-SQL).

Wybierz pozycję Statystyka oczekiwania na kwerendę, aby otworzyć okienko Statystyka oczekiwania na zapytanie w programie SQL Server Management Studio w wersji 18 lub nowszej. W okienku Statystyka oczekiwania na zapytanie jest wyświetlany wykres słupkowy zawierający najważniejsze kategorie oczekiwania w magazynie zapytań. Użyj listy rozwijanej u góry, aby wybrać zbiorcze kryteria czasu oczekiwania: avg, max, min, std dev i total (domyślnie).

Select a wait category by selecting on the bar and a detail view on the selected wait category displays. This new bar chart contains the queries that contributed to that wait category.

Use the drop-down box at the top to filter queries based on various wait time criteria for the selected wait category: avg, max, min, std dev, and total (default). Select a plan to see the graphical query plan. Buttons are available to view the source query, force, and unforce a query plan, and refresh the display.

Wait categories are combining different wait types into buckets similar by nature. Different wait categories require a different follow-up analysis to resolve the issue, but wait types from the same category lead to very similar troubleshooting experiences, and providing the affected query on top of waits would be the missing piece to complete most such investigations successfully.

Here are some examples how you can get more insights into your workload before and after introducing wait categories in Query Store:

For the available options to configure Query Store parameters, see ALTER DATABASE SET options (Transact-SQL).

Query the view to determine the current options of the Query Store. For more information about the values, see sys.database_query_store_options.sys.database_query_store_options

For examples about setting configuration options using Transact-SQL statements, see Option Management.

View and manage Query Store through Management Studio or by using the following views and procedures.

Functions help operations with the Query Store.

sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)

Catalog views present information about the Query Store.

sys.database_query_store_options (Transact-SQL)

sys.query_context_settings (Transact-SQL)

sys.query_store_plan (Transact-SQL)

sys.query_store_query (Transact-SQL)

sys.query_store_query_text (Transact-SQL)

sys.query_store_runtime_stats (Transact-SQL)

sys.query_store_wait_stats (Transact-SQL)

sys.query_store_runtime_stats_interval (Transact-SQL)

sys.database_query_store_internal_state (Transact-SQL)

Stored procedures configure the Query Store.

sp_query_store_flush_db (Transact-SQL)

sp_query_store_reset_exec_stats (Transact-SQL)

sp_query_store_force_plan (Transact-SQL)

sp_query_store_unforce_plan (Transact-SQL)

sp_query_store_remove_plan (Transact-SQL)

sp_query_store_remove_query (Transact-SQL)

sp_query_store_clear_message_queues (Transact-SQL)

sp_query_store_consistency_check (Transact-SQL)¹

¹ In extreme scenarios Query Store can enter an ERROR state because of internal errors. Starting with SQL Server 2017 (14.x), if this happens, Query Store can be recovered by executing the stored procedure in the affected database. See sys.database_query_store_options for more details described in the column description.sp_query_store_consistency_checkactual_state_desc

Best practices and recommendations for maintenance and management of the Query Store have been expanded in this article: Best practices for managing the Query Store.

For more information about diving into performance tuning with Query Store, see Tune performance with the Query Store.

Other performance topics:

• Query Store Usage Scenarios

• Query Store Stored Procedures (Transact-SQL)
• Query Store Catalog Views (Transact-SQL)
• sys.database_query_store_options (Transact-SQL)
• Live Query Statistics
• Activity Monitor
• How Query Store Collects Data
• Monitor and Tune for Performance
• Performance Monitoring and Tuning Tools
• Using the Query Store with In-Memory OLTP

• Best Practices with the Query Store
• Best practices for managing the Query Store
• Tune performance with the Query Store
• Query Store hints
• Query Store Usage Scenarios
• Open Activity Monitor (SQL Server Management Studio)