Najlepsze rozwiązania dotyczące monitorowania obciążeń za pomocą magazynu zapytań

Artykuł
03/05/2025

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

W tym artykule opisano najlepsze praktyki dotyczące używania Query Store w SQL Server z twoim obciążeniem.

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 użytecznych informacji i optymalizowania wydajności za pomocą Magazynu Zapytania, zobacz Dostrajanie wydajności przy użyciu Magazynu Zapytania.
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.
W usłudze Azure Synapse Analytics magazyn zapytań nie jest domyślnie włączony dla dedykowanych pul SQL, ale można go włączyć. Dalsze opcje konfiguracji magazynu zapytań nie są obsługiwane. Aby uzyskać więcej informacji, zobacz Magazyn zapytań historycznych i analiza w usłudze Azure Synapse Analytics.

Korzystanie z najnowszego programu SQL Server Management Studio

Program SQL Server Management Studio ma zestaw interfejsów użytkownika przeznaczonych do konfigurowania magazynu zapytań i używania zebranych danych dotyczących obciążenia. Pobierz najnowszą wersję programu SQL Server Management Studio.

Aby uzyskać krótki opis sposobu używania Magazynu Zapytań w scenariuszach rozwiązywania problemów, zobacz Query Store Azure blogs.

Korzystanie z szczegółowych informacji o wydajności zapytań w usłudze Azure SQL Database

Jeśli uruchomisz magazyn zapytań w usłudze Azure SQL Database, możesz użyć szczegółowych informacji o wydajności zapytań do analizowania zużycia zasobów w czasie. Chociaż możesz użyć programu Management Studio i Azure Data Studio, aby uzyskać szczegółowe informacje o zużyciu zasobów dla wszystkich swoich zapytań, takich jak procesor CPU, pamięć i operacje we/wy, funkcja Query Performance Insight zapewnia szybki i wydajny sposób określenia ich wpływu na ogólne zużycie jednostek DTU Twojej bazy danych. Aby uzyskać więcej informacji, zobacz Szczegółowe informacje o wydajności zapytań usługi Azure SQL Database.

Aby monitorować wydajność w bazie danych Fabric SQL, użyj Pulpitu nawigacyjnego Wydajności .

Używanie magazynu zapytań z bazami danych elastycznej puli

Magazyn zapytań można używać we wszystkich bazach danych bez obaw, nawet w gęsto zapakowanych elastycznych pulach usługi Azure SQL Database. Wszystkie poprzednie problemy związane z nadmiernym użyciem zasobów, które mogły wystąpić, gdy magazyn zapytań został włączony dla dużej liczby baz danych w pulach elastycznych, zostały rozwiązane.

Rozpocznij od rozwiązywania problemów z wydajnością zapytań

Przepływ pracy rozwiązywania problemów z magazynem zapytań jest prosty, jak pokazano na poniższym diagramie:

Diagram rozwiązywania problemów z magazynem zapytań: Włącz magazyn zapytań, Pozwól magazynowi zapytań zbierać dane, zlokalizuj i napraw problematyczne zapytania.

Włącz magazyn zapytań przy użyciu programu Management Studio zgodnie z opisem w poprzedniej sekcji lub wykonaj następującą instrukcję Transact-SQL:

ALTER DATABASE [DatabaseOne] SET QUERY_STORE = ON;

Zanim Magazyn zapytań zbierze zestaw danych, który dokładnie reprezentuje twoje obciążenie, mija trochę czasu. Zwykle jeden dzień wystarczy nawet w przypadku bardzo złożonych obciążeń. Możesz jednak zacząć eksplorować dane i identyfikować zapytania, które wymagają uwagi natychmiast po włączeniu tej funkcji. Przejdź do podfolderu Magazynu zapytań w węźle bazy danych w Eksploratorze obiektów programu Management Studio, aby otworzyć widoki rozwiązywania problemów dla określonych scenariuszy.

Widoki magazynu zapytań programu Management Studio działają z zestawem metryk wykonywania, z których każda jest wyrażona jako dowolna z następujących funkcji statystyk:

Wersja programu SQL Server	Metryka wykonawcza	Funkcja statystyczna
SQL Server 2016 (13.x)	Czas procesora CPU, Czas trwania, Liczba wykonań, Odczyty logiczne, Zapisy logiczne, Zużycie pamięci, Odczyty fizyczne, Czas CLR, Stopień równoległości (DOP) i Liczba wierszy	Średnia, Maksimum, Minimum, Odchylenie standardowe, Suma
SQL Server 2017 (14.x)	Czas procesora CPU, Czas trwania, Liczba wykonań, Odczyty logiczne, Zapisy logiczne, Zużycie pamięci, Odczyty fizyczne, Czas CLR, Stopień równoległości, Liczba wierszy, Pamięć dziennika, Pamięć tempDB i Czas oczekiwania	Średnia, Maksimum, Minimum, Odchylenie standardowe, Suma

Na poniższej ilustracji przedstawiono sposób lokalizowania widoków Query Store:

zrzut ekranu z programu SSMS przedstawiający lokalizację widoków magazynu zapytań.

W poniższej tabeli wyjaśniono, kiedy należy używać każdego z widoków magazynu zapytań:

Widok programu SQL Server Management Studio	Scenariusz
Zregresowane zapytania	Wyszukaj zapytania, dla których metryki wykonywania ostatnio się pogorszyły (na przykład zmieniły się na gorsze). Użyj tego widoku, aby skorelować zaobserwowane problemy z wydajnością w aplikacji z rzeczywistymi zapytaniami, które muszą zostać naprawione lub ulepszone.
Ogólna konsumpcja zasobów	Przeanalizuj łączne użycie zasobów dla bazy danych dla dowolnej z metryk wykonywania. Ten widok służy do identyfikowania wzorców zasobów (codziennych i nocnych obciążeń) oraz optymalizacji ogólnego zużycia bazy danych.
zapytania zużywające najwięcej zasobów	Wybierz interesującą metrykę wykonania i zidentyfikuj zapytania, które miały najbardziej ekstremalne wartości dla podanego przedziału czasu. Użyj tego widoku, aby skupić uwagę na najbardziej odpowiednich zapytaniach, które mają największy wpływ na użycie zasobów bazy danych.
zapytania z wymuszonymi planami	Wyświetla listy wcześniej wymuszonych planów przy użyciu Query Store. Użyj tego widoku, aby szybko uzyskać dostęp do wszystkich aktualnie wymuszonych planów.
Zapytania z z dużą zmiennością	Analizowanie zapytań z dużą zmiennością w wykonaniu w odniesieniu do dowolnego z dostępnych wymiarów, takich jak czas trwania, czas CPU, operacje wejścia/wyjścia i użycie pamięci, w żądanym przedziale czasowym. Ten widok służy do identyfikowania zapytań o bardzo zróżnicowanej wydajności, które mogą wpływać na doświadczenie użytkownika w Twoich aplikacjach.
Statystyki oczekiwania na zapytania	Przeanalizuj kategorie oczekiwania, które są najbardziej aktywne w bazie danych i które zapytania przyczyniają się najbardziej do wybranej kategorii oczekiwania. Ten widok umożliwia analizowanie statystyk oczekiwania i identyfikowanie zapytań, które mogą mieć wpływ na środowisko użytkownika w aplikacjach. Dotyczy: Począwszy od programu SQL Server Management Studio w wersji 18.0 i SQL Server 2017 (14.x).
śledzone zapytania	Śledzenie wykonywania najważniejszych zapytań w czasie rzeczywistym. Zazwyczaj używasz tego oglądu, gdy masz zapytania z narzuconymi planami i chcesz upewnić się, że wydajność zapytań jest stabilna.

Napiwek

Aby uzyskać szczegółowy opis sposobu używania programu Management Studio do identyfikowania najważniejszych zapytań zużywających zasoby i naprawiania tych, które uległy pogorszeniu ze względu na zmianę wybranego planu, zobacz Query Store Azure Blogs.

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

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 go wymusić. Program SQL Server próbuje wymusić plan w optymalizatorze. Jeśli implementacja planu zakończy się niepowodzeniem, zostanie wyzwolony element XEvent, a optymalizator zostanie poinstruowany, aby dokonać optymalizacji w standardowy sposób.

zrzut ekranu z programu SSMS przedstawiający przycisk wymuszania planu w Magazynie Zapytań.

Notatka

Poprzednia grafika może zawierać różne kształty dla określonych planów zapytań, z następującymi znaczeniami dla każdego możliwego stanu:

Kształt	Znaczenie
Koło	Zapytanie zostało wykonane, co oznacza, że jego regularne wykonanie zakończyło się pomyślnie.
Kwadrat	Anulowano, co oznacza, że wykonanie przerwano z inicjatywy klienta.
Trójkąt	Niepowodzenie, co oznacza, że wyjątek przerwał wykonywanie.

Ponadto rozmiar kształtu odzwierciedla liczbę wykonań zapytania w określonym interwale czasu. Rozmiar zwiększa się wraz z większą liczbą wykonanych operacji.

Możesz stwierdzić, że w zapytaniu brakuje indeksu dla optymalnego działania. Te informacje są udostępniane w ramach planu wykonywania zapytania. Utwórz brakujący indeks i sprawdź wydajność zapytań przy użyciu magazynu zapytań.

Zrzut ekranu z programu SSMS przedstawiający plan Magazyn Zapytania z wyróżnionym powiadomieniem o braku indeksu.

Jeśli uruchamiasz obciążenie w usłudze SQL Database, zarejestruj się w usłudze SQL Database Index Advisor, aby automatycznie otrzymywać zalecenia dotyczące indeksów.

W niektórych przypadkach można wymusić ponowną kompilację statystyk, jeśli zobaczysz, że różnica między szacowaną a rzeczywistą liczbą wierszy w planie wykonywania jest znacząca.
Ponowne zapisywanie problematycznych zapytań, na przykład w celu skorzystania z parametryzacji zapytań lub zaimplementowania bardziej optymalnej logiki.

Napiwek

W usłudze Azure SQL Database rozważ użycie funkcji wskazówek magazynu zapytań w celu wymuszenia podpowiedzi dla zapytań bez zmiany kodu. Aby uzyskać więcej informacji i przykładów, przejrzyj wskazówki dotyczące magazynu zapytań .

Sprawdź, czy magazyn zapytań zbiera dane zapytania w sposób ciągły

Magazyn zapytań może dyskretnie zmieniać tryb działania. Regularnie monitoruj stan magazynu zapytań, aby upewnić się, że magazyn zapytań działa i podejmować działania, aby uniknąć awarii z powodu możliwych do uniknięcia przyczyn. Wykonaj następujące zapytanie, aby określić tryb operacji i wyświetlić najbardziej odpowiednie parametry:

USE [QueryStoreDB];
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

Różnica między actual_state_desc a desired_state_desc wskazuje, że zmiana trybu operacji nastąpiła automatycznie. Najczęstszą zmianą jest to, że Magazyn Zapytań po cichu przełącza się w tryb tylko do odczytu. W niezwykle rzadkich okolicznościach, Query Store może znajdować się w stanie ERROR z powodu błędów wewnętrznych.

Gdy rzeczywisty stan jest tylko do odczytu, użyj kolumny readonly_reason, aby określić główną przyczynę. Zazwyczaj można zauważyć, że magazyn zapytań został przeniesiony do trybu tylko do odczytu, ponieważ przekroczono limit przydziału rozmiaru. W takim przypadku readonly_reason jest ustawiona na wartość 65536. Z innych powodów zobacz sys.database_query_store_options (Transact-SQL).

Rozważ następujące kroki, aby przełączyć magazyn zapytań do trybu odczytu i zapisu i aktywować zbieranie danych:

Zwiększ maksymalny rozmiar przechowywania za pomocą opcji MAX_STORAGE_SIZE_MBALTER DATABASE.
Wyczyść dane magazynu zapytań przy użyciu następującej instrukcji:
```
ALTER DATABASE [QueryStoreDB] SET QUERY_STORE CLEAR;
```

Możesz zastosować jedną lub obie z tych kroków, wykonując następującą instrukcję, która jawnie zmienia tryb operacji z powrotem na odczyt-zapis:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

Wykonaj następujące kroki, aby być proaktywne:

Możesz zapobiec dyskretnym zmianom trybu działania, stosując najlepsze rozwiązania. Upewnij się, że rozmiar magazynu zapytań jest zawsze niższy od maksymalnej dozwolonej wartości, aby znacznie zmniejszyć prawdopodobieństwo przejścia do trybu tylko do odczytu. Aktywuj zasady oparte na rozmiarach zgodnie z opisem w sekcji Konfigurowanie magazynu zapytań, aby magazyn zapytań automatycznie czyścił dane, gdy rozmiar zbliża się do limitu.
Aby upewnić się, że najnowsze dane są zachowywane, skonfiguruj zasady oparte na czasie, aby regularnie usuwać nieaktualne informacje.
Na koniec rozważ ustawienie trybu przechwytywania magazynu zapytań na Auto, ponieważ filtruje zapytania, które są zwykle mniej istotne dla obciążenia.

Stan BŁĘDU

Aby odzyskać magazyn zapytań, spróbuj jawnie ustawić tryb odczytu i zapisu i ponownie sprawdzić rzeczywisty stan.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

Jeśli problem będzie się powtarzać, oznacza to, że uszkodzenie danych magazynu zapytań jest utrwalane na dysku.

Począwszy od programu SQL Server 2017 (14.x), magazyn zapytań można odzyskać, wykonując procedurę składowaną sys.sp_query_store_consistency_check w bazie danych, której dotyczy problem. Magazyn zapytań musi być wyłączony przed podjęciem próby wykonania operacji odzyskiwania. Oto przykładowe zapytanie do użycia lub zmodyfikowania w celu przeprowadzenia sprawdzania spójności i odzyskiwania QDS:

IF EXISTS (SELECT * FROM sys.database_query_store_options WHERE actual_state=3) 
BEGIN
  BEGIN TRY
    ALTER DATABASE [QDS] SET QUERY_STORE = OFF
    Exec [QDS].dbo.sp_query_store_consistency_check
    ALTER DATABASE [QDS] SET QUERY_STORE = ON
    ALTER DATABASE [QDS] SET QUERY_STORE (OPERATION_MODE = READ_WRITE)
  END TRY
 
  BEGIN CATCH 
    SELECT  
      ERROR_NUMBER() AS ErrorNumber  
      ,ERROR_SEVERITY() AS ErrorSeverity  
      ,ERROR_STATE() AS ErrorState  
      ,ERROR_PROCEDURE() AS ErrorProcedure  
      ,ERROR_LINE() AS ErrorLine  
      ,ERROR_MESSAGE() AS ErrorMessage; 
  END CATCH;   
END

W przypadku programu SQL Server 2016 (13.x) należy wyczyścić dane z magazynu zapytań, jak pokazano poniżej.

Jeśli odzyskiwanie nie powiodło się, możesz spróbować wyczyścić magazyn zapytań przed ustawieniem trybu odczytu i zapisu.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE CLEAR;
GO

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

Unikaj używania zapytań niesparametryzowanych

Używanie niesparametryzowanych zapytań, gdy nie jest to konieczne, nie jest najlepszym rozwiązaniem. Przykładem jest analiza ad hoc. Nie można ponownie użyć buforowanych planów, co zmusza optymalizator zapytań do kompilowania zapytań dla unikatowego tekstu każdego zapytania. Aby uzyskać więcej informacji, zobacz Wytyczne dotyczące użycia wymuszonej parametryzacji.

Ponadto, Query Store może szybko przekroczyć limit rozmiaru z powodu potencjalnie dużej liczby różnych tekstów zapytań, a co za tym idzie, także dużej liczby planów wykonania o zbliżonym charakterze. W związku z tym wydajność obciążenia jest nieoptymalna, a magazyn zapytań może przełączyć się do trybu tylko do odczytu lub stale usuwać dane, aby starać się nadążyć za zapytaniami przychodzącymi.

Rozważ następujące opcje:

Parametryzowanie zapytań tam, gdzie ma to zastosowanie. Na przykład zawijanie zapytań wewnątrz procedury składowanej lub sp_executesql. Aby uzyskać więcej informacji, zobacz parametry i ponowne użycie planu wykonywania.
Użyj opcji zoptymalizowania dla obciążeń ad hoc, jeśli obciążenie zawiera wiele jednorazowych partii ad hoc z różnymi planami zapytań.
- Porównaj liczbę unikatowych wartości query_hash z całkowitą liczbą wpisów w sys.query_store_query. Jeśli stosunek jest zbliżony do 1, obciążenie ad hoc generuje różne zapytania.
Zastosuj wymuszoną parametryzację dla bazy danych lub podzbioru zapytań, jeśli liczba różnych planów zapytań nie jest duża.
- Użyj przewodnika planu , aby wymusić parametryzację tylko dla wybranego zapytania.
- Skonfiguruj wymuszoną parametryzację przy użyciu polecenia opcji parametryzacji bazy danych , jeśli w przypadku obciążenia istnieje niewielka liczba różnych planów zapytań. Przykładem jest, gdy stosunek liczby unikatowych query_hash do całkowitej liczby wpisów w sys.query_store_query jest znacznie mniejszy niż 1.
Ustaw QUERY_CAPTURE_MODE na AUTO, aby automatycznie filtrować zapytania ad hoc z małym użyciem zasobów.

Napiwek

W przypadku korzystania z rozwiązania Object-Relational Mapping (ORM), takiego jak Entity Framework (EF), zapytania aplikacji, takie jak ręczne drzewa zapytań LINQ lub niektóre nieprzetworzone zapytania SQL, mogą nie być sparametryzowane, co ma wpływ na ponowne użycie planu i możliwość śledzenia zapytań w magazynie zapytań. Aby uzyskać więcej informacji, zobacz buforowanie i parametryzację zapytań w EF oraz zapytania Raw SQL.

Znajdź niesparametryzowane zapytania w Sklepie z zapytaniami

Liczbę planów przechowywanych w magazynie zapytań można znaleźć przy użyciu poniższego zapytania, używając widoków DMV magazynu zapytań, w programie SQL Server, usłudze Azure SQL Managed Instance lub usłudze Azure SQL Database:

SELECT count(Pl.plan_id) AS plan_count, Qry.query_hash, Txt.query_text_id, Txt.query_sql_text
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
GROUP BY Qry.query_hash, Txt.query_text_id, Txt.query_sql_text
ORDER BY plan_count desc;

Poniższy przykład tworzy sesję Extended Events do przechwytywania zdarzenia query_store_db_diagnostics, co może być przydatne podczas diagnozowania zużycia zasobów zapytań. W programie SQL Server ta rozszerzona sesja zdarzeń domyślnie tworzy plik zdarzenia w folderze dziennika programu SQL Server. Na przykład w domyślnej instalacji programu SQL Server 2019 (15.x) w systemie Windows plik zdarzenia (plik xel) powinien zostać utworzony w folderze C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log. W przypadku usługi Azure SQL Managed Instance określ zamiast tego lokalizację usługi Azure Blob Storage. Aby uzyskać więcej informacji, zobacz XEvent event_file dla usługi Azure SQL Managed Instance. Zdarzenie "qds.query_store_db_diagnostics" nie jest dostępne dla usługi Azure SQL Database.

CREATE EVENT SESSION [QueryStore_Troubleshoot] ON SERVER 
ADD EVENT qds.query_store_db_diagnostics(
      ACTION(sqlos.system_thread_id,sqlos.task_address,sqlos.task_time,sqlserver.database_id,sqlserver.database_name))
ADD TARGET package0.event_file(SET filename=N'QueryStore',max_file_size=(100))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);

Dzięki tym danym można znaleźć liczbę planów w magazynie zapytań, a także wiele innych statystyk. Wyszukaj kolumny plan_count, query_count, max_stmt_hash_map_size_kbi max_size_mb w danych zdarzenia, aby zrozumieć wielkość używanej pamięci oraz liczbę planów śledzonych przez Query Store. Jeśli liczba planów jest wyższa niż zwykle, może to wskazywać na wzrost zapytań niesparametryzowanych. Użyj poniższego zapytania DMVs Query Store, aby przejrzeć sparametryzowane zapytania i zapytania niesparametryzowane w Query Store.

W przypadku zapytań sparametryzowanych:

SELECT qsq.query_id, qsqt.query_sql_text
FROM sys.query_store_query AS qsq 
INNER JOIN sys.query_store_query_text AS qsqt
ON qsq.query_text_id= qsqt.query_text_id 
WHERE qsq.query_parameterization_type<>0 or qsqt.query_sql_text like '%@%';

W przypadku zapytań niesparametryzowanych:

SELECT qsq.query_id, qsqt.query_sql_text
FROM sys.query_store_query AS qsq 
INNER JOIN sys.query_store_query_text AS qsqt
ON qsq.query_text_id= qsqt.query_text_id 
WHERE query_parameterization_type=0;

Unikaj wzorca DROP i CREATE dla obiektów zawierających

Magazyn zapytań kojarzy wpis zapytania z obiektem, takim jak procedura składowana, funkcja czy wyzwalacz. Podczas ponownego tworzenia obiektu zawierającego zostanie wygenerowany nowy wpis zapytania dla tego samego tekstu zapytania. Uniemożliwia to śledzenie statystyk wydajności dla tego zapytania w czasie i używanie mechanizmu wymuszania planu. Aby uniknąć tej sytuacji, użyj procesu ALTER <object>, aby zmienić definicję obiektu zawierającego zawsze, gdy jest to możliwe.

Regularne sprawdzanie stanu wymuszonych planów

Wymuszanie planu jest wygodnym mechanizmem poprawy wydajności zapytań krytycznych i uczynienia ich bardziej przewidywalnymi. Podobnie jak w przypadku wskazówek i przewodników dotyczących planów, wymuszenie planu nie jest gwarancją, że będzie on używany w przyszłych wykonaniach. Zazwyczaj, gdy schemat bazy danych zmienia się w taki sposób, że obiekty referencyjne planu wykonywania są modyfikowane lub usuwane, wymuszanie planu zaczyna zawodzić. W takim przypadku program SQL Server powraca do ponownej kompilacji zapytań, podczas gdy rzeczywista przyczyna niepowodzenia wymuszania jest uwidoczana w sys.query_store_plan. Następujące zapytanie zwraca informacje o planach wymuszonych:

USE [QueryStoreDB];
GO

SELECT p.plan_id, p.query_id, q.object_id as containing_object_id,
    force_failure_count, last_force_failure_reason_desc
FROM sys.query_store_plan AS p
JOIN sys.query_store_query AS q on p.query_id = q.query_id
WHERE is_forced_plan = 1;

Aby uzyskać pełną listę powodów, zobacz sys.query_store_plan. Możesz również użyć query_store_plan_forcing_failed XEvent do śledzenia i rozwiązywania problemów dotyczących niepowodzeń wymuszania planu.

Napiwek

W usłudze Azure SQL Database rozważ użycie funkcji wskazówek magazynu zapytań do wymuszania wskazówek na zapytaniach bez konieczności wprowadzania zmian w kodzie. Aby uzyskać więcej informacji i przykładów, zobacz podpowiedzi Magazynu Zapytań .

Unikaj zmieniania nazw baz danych dla zapytań z wymuszonymi planami

Plany wykonywania odwołują się do obiektów przy użyciu nazw trzyelementowych, takich jak database.schema.object.

Jeśli zmienisz nazwę bazy danych, wymuszenie planu zakończy się niepowodzeniem, co powoduje ponowne skompilowanie we wszystkich kolejnych wykonaniach zapytań.

Używanie magazynu zapytań na serwerach o znaczeniu krytycznym

Globalne flagi śledzenia 7745 i 7752 mogą służyć do poprawy dostępności baz danych przy użyciu magazynu zapytań. Aby uzyskać więcej informacji, zapoznaj się z flagami śledzenia.

Flaga śledzenia 7745 zapobiega domyślnemu zachowaniu, w którym magazyn zapytań zapisuje dane na dysku przed zamknięciem programu SQL Server. Oznacza to, że dane magazynu zapytań, które zostały zebrane, ale nie zostały jeszcze utrwalone na dysku, zostaną utracone do przedziału czasu zdefiniowanego za pomocą DATA_FLUSH_INTERVAL_SECONDS.
Flaga śledzenia 7752 umożliwia asynchroniczne ładowanie magazynu zapytań. Dzięki temu baza danych stanie się dostępna online, a zapytania będą wykonywane, zanim magazyn zapytań zostanie w pełni odzyskany. Domyślnym zachowaniem jest wykonywanie synchronicznego obciążenia magazynu zapytań. Domyślne zachowanie uniemożliwia wykonywanie zapytań przed odzyskaniem magazynu zapytań, ale także uniemożliwia pomijanie zapytań w kolekcji danych.

Notatka

Począwszy od SQL Server 2019 (15.x), to zachowanie jest kontrolowane przez silnik, a flaga śledzenia 7752 nie ma żadnego wpływu.

Ważny

Jeśli używasz Magazynu Zapytania do uzyskiwania informacji o obciążeniu just-in-time w programie SQL Server 2016 (13.x), zaplanuj jak najszybsze zainstalowanie ulepszeń skalowalności wydajności w programie SQL Server 2016 (13.x) SP2 CU2 (KB 4340759). Bez tych ulepszeń, przy dużym obciążeniu bazy danych, może wystąpić konflikt spinlock, a wydajność serwera może się pogorszyć. W szczególności możesz zobaczyć duże rywalizacje na QUERY_STORE_ASYNC_PERSIST spinlock lub SPL_QUERY_STORE_STATS_COOKIE_CACHE spinlock. Po zastosowaniu tej poprawy Query Store nie spowoduje już blokady spinlock.

Ważny

Jeśli używasz Query Store do uzyskiwania wglądów w obciążenie just-in-time w SQL Server (od SQL Server 2016 (13.x) do SQL Server 2017 (14.x)), zaplanuj zainstalowanie poprawki skalowalności wydajnościowej w SQL Server 2016 (13.x) SP2 CU15, SQL Server 2017 (14.x) CU23 i SQL Server 2019 (15.x) CU9 w trybie pilnym. Bez tej poprawy, gdy baza danych jest w dużych obciążeniach ad hoc, magazyn zapytań może korzystać z dużej ilości pamięci, a wydajność serwera może być niska. Po zastosowaniu tej poprawy magazyn zapytań nakłada wewnętrzne limity na ilość pamięci, której mogą używać różne jego komponenty, i może automatycznie zmienić tryb działania na tylko do odczytu, dopóki nie zostanie zwrócona wystarczająca ilość pamięci do aparatu bazy danych. Limity pamięci wewnętrznej magazynu zapytań nie są udokumentowane, ponieważ mogą ulec zmianie.

Użyj magazynu zapytań w usłudze Azure SQL Database do aktywnej replikacji geograficznej

Magazyn zapytań w pomocniczej aktywnej repliki geograficznej usługi Azure SQL Database będzie kopią działania tylko do odczytu w repliki podstawowej.

Unikaj niedopasowanych warstw w usłudze Azure SQL Database podczas korzystania z replikacji geograficznej. Pomocnicza baza danych powinna być w tym samym rozmiarze obliczeniowym podstawowej bazy danych i w tej samej warstwie usługi podstawowej bazy danych. Wyszukaj typ oczekiwania HADR_THROTTLE_LOG_RATE_MISMATCHED_SLO w sys.dm_db_wait_stats, który wskazuje ograniczanie szybkości przepływu dziennika transakcji w replice głównej z powodu opóźnienia wtórnego.

Aby uzyskać więcej informacji na temat szacowania i konfigurowania rozmiaru pomocniczej bazy danych Azure SQL Database aktywnej replikacji geograficznej, zobacz Konfigurowanie pomocniczej bazy danych.

Utrzymuj dostosowanie Magazynu Zapytań do swojego obciążenia

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

opcje ALTER DATABASE SET (Transact-SQL)
widoki katalogowe Query Store (Transact-SQL)
procedury składowane magazynu zapytań (Transact-SQL)
używanie magazynu zapytań z In-Memory OLTP
przewodnik po architekturze przetwarzania zapytań
wskazówki dotyczące magazynu zapytań
Monitorowanie wydajności przy użyciu magazynu zapytań
Optymalizacja wydajności przy użyciu Sklepu Zapytań
Historyczne przechowywanie zapytań i analiza w usłudze Azure Synapse Analytics

Udostępnij za pośrednictwem

Najlepsze rozwiązania dotyczące monitorowania obciążeń za pomocą magazynu zapytań

Korzystanie z najnowszego programu SQL Server Management Studio

Korzystanie z szczegółowych informacji o wydajności zapytań w usłudze Azure SQL Database

Używanie magazynu zapytań z bazami danych elastycznej puli

Rozpocznij od rozwiązywania problemów z wydajnością zapytań

Sprawdź, czy magazyn zapytań zbiera dane zapytania w sposób ciągły

Stan BŁĘDU

Unikaj używania zapytań niesparametryzowanych

Znajdź niesparametryzowane zapytania w Sklepie z zapytaniami

Unikaj wzorca DROP i CREATE dla obiektów zawierających

Regularne sprawdzanie stanu wymuszonych planów

Unikaj zmieniania nazw baz danych dla zapytań z wymuszonymi planami

Używanie magazynu zapytań na serwerach o znaczeniu krytycznym

Użyj magazynu zapytań w usłudze Azure SQL Database do aktywnej replikacji geograficznej

Utrzymuj dostosowanie Magazynu Zapytań do swojego obciążenia

Opinia

Dodatkowe zasoby

Udostępnij za pośrednictwem

Najlepsze rozwiązania dotyczące monitorowania obciążeń za pomocą magazynu zapytań

Korzystanie z najnowszego programu SQL Server Management Studio

Korzystanie z szczegółowych informacji o wydajności zapytań w usłudze Azure SQL Database

Używanie magazynu zapytań z bazami danych elastycznej puli

Rozpocznij od rozwiązywania problemów z wydajnością zapytań

Sprawdź, czy magazyn zapytań zbiera dane zapytania w sposób ciągły

Stan BŁĘDU

Unikaj używania zapytań niesparametryzowanych

Znajdź niesparametryzowane zapytania w Sklepie z zapytaniami

Unikaj wzorca DROP i CREATE dla obiektów zawierających

Regularne sprawdzanie stanu wymuszonych planów

Unikaj zmieniania nazw baz danych dla zapytań z wymuszonymi planami

Używanie magazynu zapytań na serwerach o znaczeniu krytycznym

Użyj magazynu zapytań w usłudze Azure SQL Database do aktywnej replikacji geograficznej

Utrzymuj dostosowanie Magazynu Zapytań do swojego obciążenia

Powiązana zawartość

Opinia

Dodatkowe zasoby