Udostępnij za pośrednictwem

Monitorowanie wydajności przy użyciu magazynu zapytań

  • Artykuł

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

Funkcja Magazynu zapytań zapewnia szczegółowe informacje na temat wyboru planu zapytań i wydajności dla programu SQL Server, usługi Azure SQL Database, bazy danych SQL Fabric, usługi Azure SQL Managed Instance i usługi Azure Synapse Analytics. Magazyn zapytań upraszcza rozwiązywanie problemów z wydajnością, pomagając szybko znaleźć różnice w wydajności spowodowane zmianami planu zapytań. Magazyn zapytań automatycznie przechwytuje historię zapytań, planów i statystyk czasu wykonania oraz zachowuje je do przejrzenia. Oddziela dane według okien czasowych, dzięki czemu można zobaczyć wzorce użycia bazy danych i zrozumieć, kiedy na serwerze wystąpiły zmiany planu zapytań.

Magazyn zapytań można skonfigurować przy użyciu opcji ALTER DATABASE SET.

Ważny

Jeśli używasz Query Store do uzyskiwania informacji o obciążeniu just in time w programie SQL Server 2016 (13.x), zaplanuj jak najszybsze zainstalowanie poprawek skalowalności wydajności w KB 4340759.

Włącz magazyn zapytań

  • Magazyn zapytań jest domyślnie włączony dla nowych baz danych usługi Azure SQL Database i usługi Azure SQL Managed Instance.
  • 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 ona domyślnie włączona w trybie READ_WRITE dla nowych baz danych, począwszy od programu SQL Server 2022 (16.x). Aby umożliwić funkcjom lepsze śledzenie historii wydajności, rozwiązywanie problemów związanych z planem zapytań i włączanie nowych funkcji w programie SQL Server 2022 (16.x), zalecamy włączenie magazynu zapytań we wszystkich bazach danych.
  • Magazyn zapytań nie jest domyślnie włączony dla nowych baz danych usługi Azure Synapse Analytics.

Korzystanie ze strony Magazyn zapytań w programie SQL Server Management Studio

  1. W Eksploratorze obiektów kliknij prawym przyciskiem myszy bazę danych, a następnie wybierz pozycję 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ę Magazynu zapytań.

  3. Na polu Tryb operacyjny (żądany) wybierz pozycję Odczyt zapis.

Użyj instrukcji Transact-SQL

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

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

Opcje konfigurowania magazynu zapytań w bazie danych SQL fabric z ALTER DATABASE są obecnie ograniczone.

W usłudze Azure Synapse Analytics włącz magazyn zapytań bez dodatkowych opcji, na przykład:

ALTER DATABASE <database_name>
SET QUERY_STORE = ON;

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

Notatka

Nie można włączyć magazynu zapytań dla baz danych master lub tempdb.

Ważny

Aby uzyskać informacje na temat włączania magazynu zapytań i utrzymywania go dostosowanego do obciążenia, zobacz Najlepsze rozwiązanie z magazynem zapytań.

Informacje w ramach magazynu zapytań

Plany wykonywania dla dowolnego określonego zapytania w programie SQL Server zwykle ewoluują wraz z upływem czasu z różnych powodów, takich jak zmiany statystyk, zmiany schematu, tworzenie/usuwanie indeksów itp. Pamięć podręczna procedury (gdzie przechowywane są buforowane plany zapytań) przechowuje tylko najnowszy plan wykonania. Plany są również eksmitowane z pamięci podręcznej z powodu presji pamięci. W związku z tym pogorszenie wydajności zapytań spowodowane zmianami planu wykonywania mogą być trudne i czasochłonne do rozwiązania.

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

Notatka

Store zapytań zbiera plany dla instrukcji DML, takich jak SELECT, INSERT, UPDATE, DELETE, MERGEi BULK INSERT.

Magazyn Zapytania z założenia nie gromadzi planów dla instrukcji DDL, takich jak CREATE INDEXitp. Magazyn Zapytania rejestruje skumulowane zużycie zasobów poprzez gromadzenie planów dla podstawowych instrukcji DML. Na przykład magazyn zapytań może wyświetlać instrukcje SELECT i INSERT wykonywane wewnętrznie w celu wypełnienia nowego indeksu.

Magazyn zapytań domyślnie nie zbiera danych dla natywnie skompilowanych procedur składowanych. Użyj sys.sp_xtp_control_query_exec_stats, aby włączyć zbieranie danych dla natywnie skompilowanych procedur składowanych.

Statystyki oczekiwania są kolejnym źródłem informacji, które pomagają rozwiązywać problemy z wydajnością silnika bazy danych. Od dłuższego czasu statystyki oczekiwań były dostępne tylko na poziomie instancji, co utrudniało śledzenie oczekiwań do konkretnego zapytania. 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:

  • Szybko znajdź i napraw regresję wydajności planu, wymuszając poprzedni plan zapytania. Napraw zapytania, które ostatnio ulegały pogorszeniu wydajności z powodu zmian planu wykonania.
  • Określ, ile razy zapytanie zostało wykonane w danym przedziale czasu, pomagając administratorowi zasobów w rozwiązywaniu problemów z zasobami wydajności.
  • Zidentyfikuj n zapytań (według czasu wykonywania, użycia pamięci itp.) w ciągu ostatnich x godzin.
  • Przeprowadź inspekcję historii planów zapytań dla danego zapytania.
  • Przeanalizuj wzorce użycia zasobów (procesora CPU, we/wy i pamięci) dla określonej bazy danych.
  • Zidentyfikuj najważniejsze n zapytań oczekujących na zasoby.
  • Zrozum charakter oczekiwania dla określonego zapytania lub planu.

Magazyn zapytań zawiera trzy sekcje:

  • magazyn planu na potrzeby utrwalania informacji o planie wykonywania.
  • statystyki środowiska uruchomieniowego przechowują na potrzeby utrwalania informacji dotyczących statystyk wykonywania.
  • magazyn statystyk oczekiwania do utrwalania 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 do pamięci asynchronicznie. Aby zminimalizować użycie miejsca, statystyki wykonywania środowiska uruchomieniowego w magazynie statystyk środowiska uruchomieniowego są agregowane w stałym przedziale czasu. Informacje w tych magazynach są widoczne przez zapytania do widoków katalogu Query Store.

Poniższe zapytanie zwraca informacje o zapytaniach, ich planach, czasie kompilacji i statystykach czasu wykonania z Query Store.

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

Query Store dla replik wtórnych

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

Funkcja Magazynu Zapytań dla replik pomocniczych umożliwia korzystanie z tej samej funkcjonalności Magazynu Zapytań w obciążeniach replik pomocniczych, która jest dostępna dla replik podstawowych. Gdy magazyn zapytań dla replik pomocniczych jest włączony, repliki wysyłają informacje o wykonywaniu zapytań, które normalnie będą przechowywane w magazynie zapytań z powrotem do repliki podstawowej. Następnie replika podstawowa utrwala dane na dysku we własnym magazynie zapytań. W istocie istnieje jeden magazyn zapytań współużytkowany między replikami podstawowymi i wszystkimi replikami pomocniczymi. Magazyn zapytań istnieje na replice podstawowej i przechowuje dane dla wszystkich replik razem.

Aby uzyskać pełne informacje na temat Query Store dla replik pomocniczych, zobacz Query Store dla replik pomocniczych Always On grupy dostępności.

Użyj funkcji zapytań regresywnych

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

zrzut ekranu przedstawiający drzewo raportowania magazynu zapytań w Eksploratorze obiektów programu SSMS.

Notatka

W przypadku usługi Azure Synapse Analytics, widoki magazynu zapytań są dostępne w obszarze System Views w części bazy danych okienek Eksploratora obiektów.

Wybierz Zapytania o regresji, aby otworzyć okienko Zapytania o regresji w programie SQL Server Management Studio. Panel Zapytania regresywne zawiera zapytania i plany w Magazynie zapytań. Użyj pól listy rozwijanej u góry, aby filtrować zapytania na podstawie różnych kryteriów: czas trwania (ms) (domyślnie), czas CPU (ms), odczyty logiczne (KB), zapisy logiczne (KB), odczyty fizyczne (KB), czas CLR (ms), DOP, zużycie pamięci (KB), liczba wierszy, wykorzystanie pamięci dziennika (KB), wykorzystanie pamięci TempDB (KB) i czas oczekiwania (ms).

Wybierz plan, aby wyświetlić graficzny plan zapytania. Przyciski są dostępne do wyświetlania zapytania źródłowego, wymuszania i wymuszenia planu zapytania, przełączania między formatami siatki i wykresu, porównywania wybranych planów (jeśli wybrano więcej niż jeden) i odświeżenia ekranu.

zrzut ekranu przedstawiający raport zapytań regresywnych w SQL Server w Eksploratorze obiektów programu SSMS.

Aby wymusić plan, wybierz zapytanie i plan, a następnie wybierz pozycję Force Plan. Można wymusić tylko plany, które zostały zapisane za pomocą funkcji planu zapytania i są nadal przechowywane w pamięci podręcznej zapytań.

Znajdowanie oczekujących 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 Query Store typy oczekiwania są łączone w kategorie oczekiwania . Mapowanie kategorii oczekiwania na typy oczekiwania jest dostępne w sys.query_store_wait_stats (Transact-SQL).

Wybierz pozycję Statystyki oczekiwania zapytań, aby otworzyć okienko statystyki oczekiwania zapytań w programie SQL Server Management Studio 18.0 lub nowszych wersjach. Okienko Statystyki oczekiwania zapytań prezentuje wykres słupkowy, który zawiera najważniejsze kategorie oczekiwania w magazynie zapytań. Użyj listy rozwijanej u góry, aby wybrać kryteria agregacji czasu oczekiwania: średnia, maksymalna, minimalna, std dev i łączna (wartość domyślna).

zrzut ekranu przedstawiający raport statystyki oczekiwania zapytań programu SQL Server w Eksploratorze obiektów programu SSMS.

Wybierz kategorię oczekiwania, klikając na pasku menu, a zostanie wyświetlony widok szczegółowy dla wybranej kategorii oczekiwania. Ten nowy wykres słupkowy zawiera zapytania, które przyczyniły się do tej kategorii oczekiwania.

zrzut ekranu przedstawiający widok szczegółów statystyk oczekiwania zapytań programu SQL Server w Eksploratorze obiektów programu SSMS.

Użyj pola listy rozwijanej u góry, aby filtrować zapytania na podstawie różnych kryteriów czasu oczekiwania dla wybranej kategorii oczekiwania: średnia, maksymalna, minimalna, std dev i łączna (wartość domyślna). Wybierz plan, aby wyświetlić graficzny plan zapytania. Przyciski są dostępne do wyświetlania zapytania źródłowego, wymuszenia i cofnięcia wymuszenia planu zapytania oraz odświeżenia wyświetlania.

Kategorie oczekiwania łączą różne typy oczekiwania w grupy o podobnym charakterze. Różne kategorie oczekiwania wymagają odrębnej analizy kontynuacyjnej, aby rozwiązać problem. Jednak typy oczekiwań z tej samej kategorii prowadzą do bardzo podobnych procesów rozwiązywania problemów, a dostarczenie dotkniętego zapytania na czoło oczekiwań byłoby brakującym elementem do pomyślnego ukończenia większości takich badań.

** Poniżej kilka przykładów, jak można uzyskać więcej szczegółowych informacji o obciążeniu przed i po wprowadzeniu kategorii oczekiwania w Query Store:

Poprzednie środowisko Nowe środowisko Akcja
Wysokie oczekiwania RESOURCE_SEMAPHORE na każdą bazę danych Wysokie oczekiwania pamięciowe w magazynie zapytań dla określonych zapytań Znajdź zapytania zużywające najwięcej pamięci w magazynie zapytań. Te zapytania prawdopodobnie opóźniają dalszy postęp zapytań, których dotyczy problem. Rozważ użycie wskazówki MAX_GRANT_PERCENT dla tych zapytań lub zapytań, których dotyczy problem.
Wysokie czasy oczekiwania LCK_M_X na bazie danych Oczekiwanie na wysoką blokadę w magazynie zapytań dla określonych zapytań Sprawdź teksty zapytań pod kątem zapytań, których dotyczy problem, i zidentyfikuj jednostki docelowe. Wyszukaj w magazynie zapytań inne zapytania modyfikujące tę samą jednostkę, które są wykonywane często i/lub mają wysoki czas trwania. Po zidentyfikowaniu tych zapytań rozważ zmianę logiki aplikacji w celu poprawy współbieżności lub użyj mniej restrykcyjnego poziomu izolacji.
Wysokie czasy oczekiwania PAGEIOLATCH_SH na bazę danych Wysokie czasy oczekiwania na operacje I/O w przechowalni zapytań dla określonych zapytań. Znajdź zapytania z dużą liczbą odczytów fizycznych w Sklepie Zapytań. Jeśli pasują one do zapytań z dużymi oczekiwaniami we/wy, rozważ wprowadzenie indeksu w jednostce bazowej, aby wykonać wyszukiwanie zamiast skanowania, a tym samym zminimalizować obciążenie operacjami we/wy zapytań.
Wysokie SOS_SCHEDULER_YIELD oczekiwania na bazę danych Wysokie czasy oczekiwania CPU w Query Store dla określonych zapytań Znajdź zapytania zużywające najwięcej CPU w bazie danych Query Store. Wśród nich zidentyfikuj zapytania, dla których trend wysokiego użycia procesora CPU jest skorelowany z dużymi oczekiwaniami procesora CPU na zapytania, których dotyczy problem. Skoncentruj się na optymalizacji tych zapytań — może istnieć regresja planu lub być może brakujący indeks.

Opcje konfiguracji

Aby uzyskać dostępne opcje konfigurowania parametrów magazynu zapytań, zobacz ALTER DATABASE SET options (Transact-SQL).

Wykonaj zapytanie względem widoku sys.database_query_store_options, aby określić bieżące opcje Query Store. Aby uzyskać więcej informacji na temat wartości, zobacz sys.database_query_store_options.

Przykłady dotyczące ustawiania opcji konfiguracji przy użyciu instrukcji Transact-SQL można znaleźć w temacie Option Management.

Notatka

W przypadku usługi Azure Synapse Analytics magazyn zapytań można włączyć tak jak na innych platformach, ale dodatkowe opcje konfiguracji nie są obsługiwane.

Wyświetlanie magazynu zapytań i zarządzanie nim za pomocą programu Management Studio lub przy użyciu następujących widoków i procedur.

Funkcje magazynu zapytań

Funkcje wspierają operacje z wykorzystaniem Query Store.

sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)

Widoki katalogu Query Store

Widoki katalogu zawierają informacje o magazynie zapytań.

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)

Procedury składowane Query Store

Procedury składowane konfigurują magazyn zapytań.

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)1

1 W skrajnych scenariuszach magazyn zapytań może wprowadzić stan BŁĘDU z powodu błędów wewnętrznych. Począwszy od programu SQL Server 2017 (14.x), jeśli tak się stanie, magazyn zapytań można odzyskać, wykonując procedurę składowaną sp_query_store_consistency_check w bazie danych, której dotyczy problem. Zobacz sys.database_query_store_options, aby uzyskać więcej szczegółów opisanych w opisie kolumny actual_state_desc.

Zarządzanie magazynem zapytań

W tym artykule rozszerzono najlepsze rozwiązania i zalecenia dotyczące konserwacji magazynu zapytań i zarządzania nim: Najlepsze rozwiązania dotyczące zarządzania magazynem zapytań.

Inspekcja wydajności i rozwiązywanie problemów

Aby uzyskać więcej informacji na temat optymalizacji wydajności za pomocą magazynu zapytań, zobacz Optymalizacja wydajności za pomocą magazynu zapytań.

Inne tematy dotyczące wydajności:

Powiązana zawartość