Sugestie dla Query Store

Dotyczy: SQL Server 2022 (16.x) Azure SQL DatabaseAzure SQL Managed InstanceSQL Database w Microsoft Fabric

W tym artykule opisano sposób stosowania wskazówek dotyczących zapytań przy użyciu magazynu zapytań. Wskazówki dotyczące magazynu zapytań zapewniają łatwą w użyciu metodę kształtowania planów zapytań bez zmieniania kodu aplikacji.

Podpowiedzi Query Store są dostępne w Azure SQL Database i Azure SQL Managed Instance. Podpowiedzi Query Store są również funkcją wprowadzoną do programu SQL Server w wersji 2022 (16.x).

• 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 odnajdywania użytecznych informacji i dostrajania wydajności przy użyciu Query Store, zobacz Dostrajanie wydajności przy użyciu Query Store.
• 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.

Ostrożność

Ponieważ optymalizator zapytań programu SQL Server zazwyczaj wybiera najlepszy plan wykonania zapytania, zalecamy używanie wskazówek tylko w ostateczności dla doświadczonych deweloperów i administratorów baz danych. Aby uzyskać więcej informacji, zobacz Wskazówki dotyczące zapytań.

Obejrzyj ten film, aby dowiedzieć się więcej o wskazówkach dotyczących Query Store.

Przegląd

W idealnym przypadku optymalizator zapytań wybiera optymalny plan wykonywania zapytania.

Jeśli nie wybrano optymalnego planu, deweloper lub administrator bazy danych może chcieć ręcznie zoptymalizować pod kątem określonych warunków. Wskazówki dotyczące zapytań są określane za pośrednictwem klauzuli OPTION i mogą służyć do wpływania na zachowanie wykonywania zapytań. Chociaż wskazówki dotyczące zapytań pomagają zapewnić zlokalizowane rozwiązania różnych problemów związanych z wydajnością, wymagają ponownego zapisywania oryginalnego tekstu zapytania. Administratorzy bazy danych i deweloperzy mogą nie zawsze być w stanie wprowadzać zmiany bezpośrednio w kodzie Transact-SQL w celu wstrzyknięcia wskazówki dotyczącej zapytania. Transact-SQL może być zakodowane w aplikacji lub generowane automatycznie przez aplikację. Wcześniej deweloper mógł polegać na przewodnikach dotyczących planów, które mogą być skomplikowane w użyciu.

Aby dowiedzieć się, które podpowiedzi dotyczące zapytań można zastosować, zobacz Obsługiwane podpowiedzi dotyczące zapytań.

Kiedy używać podpowiedzi Query Store

Jak sugeruje nazwa, ta funkcja rozszerza się i zależy od magazynu zapytań. Magazyn zapytań umożliwia przechwytywanie zapytań, planów wykonywania i skojarzonych statystyk środowiska uruchomieniowego. Magazyn zapytań znacznie upraszcza ogólne dostrajanie wydajności środowiska klienta. Program SQL Server 2016 (13.x) po raz pierwszy wprowadził magazyn zapytań, a teraz jest domyślnie włączony w programie SQL Server 2022 (16.x), usłudze Azure SQL Managed Instance i usłudze Azure SQL Database.

Najpierw zapytanie jest wykonywane, a potem przechwytywane przez Query Store. Następnie administrator bazy danych tworzy Query Store hint dla zapytania. Następnie zapytanie jest wykonywane przy użyciu wskazówki dotyczącej magazynu zapytań.

Przykłady, w których wskazówki dotyczące magazynu zapytań mogą pomóc w rozwiązywaniu problemów z wydajnością na poziomie zapytania:

• Ponownie skompiluj zapytanie przy każdym wykonaniu.
• Ogranicz rozmiar przydziału pamięci dla operacji wstawiania zbiorczego.
• Ogranicz maksymalny stopień równoległości podczas aktualizowania statystyk.
• Użyj sprzężenia haszującego zamiast sprzężenia zagnieżdżonych pętli.
• Użyj poziomu zgodności 110 dla określonego zapytania, zachowując jednocześnie wszystkie inne elementy w bazie danych na poziomie zgodności 150.
• Wyłącz optymalizację celu wiersza dla zapytania SELECT TOP.

Aby użyć opcji Query Store:

1. Zidentyfikuj magazyn zapytań query_id zapytania, które chcesz zmodyfikować. Można to zrobić na różne sposoby:
   • Zapytania dotyczące widoków katalogu Query Store.
   • Korzystanie z wbudowanych raportów magazynu zapytań programu SQL Server Management Studio.
   • Korzystanie ze szczegółowych informacji o wydajności zapytań w portalu Azure dla usługi Azure SQL Database.
2. Wykonaj sys.sp_query_store_set_hints za pomocą query_id oraz ciągu wskazówek, które chcesz zastosować do zapytania. Ten ciąg może zawierać co najmniej jedną wskazówkę zapytania. Aby uzyskać pełne informacje, zobacz sys.sp_query_store_set_hints.

Po utworzeniu wskazówki dla magazynu zapytań są utrwalane i przetrwają ponowne uruchomienia i przejścia w tryb failover. Wskazówki dotyczące magazynu zapytań zastępują trwale zakodowane wskazówki na poziomie instrukcji i istniejące wskazówki przewodnika po planie.

Jeśli wskazówka dotycząca zapytania jest sprzeczna z tym, co jest możliwe w przypadku optymalizacji zapytań, wykonywanie zapytań nie jest blokowane i nie jest stosowana wskazówka. W przypadkach, w których wskazówka spowoduje niepowodzenie zapytania, wskazówka jest ignorowana, a najnowsze szczegóły błędu można wyświetlić w sys.query_store_query_hints.

Procedury składowane systemu wskazówek Query Store

Aby utworzyć lub zaktualizować wskazówki, użyj sys.sp_query_store_set_hints. Wskazówki są określane w prawidłowym formacie ciągu N'OPTION (...)'.

• Jeśli tworzysz wskazówkę dla magazynu zapytań, a dla określonego query_id nie istnieje jeszcze żadna wskazówka, zostanie utworzona nowa wskazówka magazynu zapytań.
• Podczas tworzenia lub aktualizowania podpowiedzi magazynu zapytań, jeśli istnieje już podpowiedź magazynu zapytań dla określonego elementu query_id, ostatnia podana wartość zastępuje wcześniej określone wartości dla skojarzonego zapytania.
• Jeśli element query_id nie istnieje, zostanie zgłoszony błąd.

Uwaga

Aby uzyskać pełną listę obsługiwanych wskazówek, zobacz sys.sp_query_store_set_hints.

Aby usunąć wskazówki skojarzone z elementem query_id, użyj sys.sp_query_store_clear_hints.

Atrybuty XML planu wykonywania

Po zastosowaniu wskazówek następujący zestaw wyników jest wyświetlany w StmtSimple elemecie planu wykonywania w formacie XML:

Atrybut	Opis
QueryStoreStatementHintText	Aktualne wskazówki magazynu zapytań zastosowane do zapytania
QueryStoreStatementHintId	Unikatowy identyfikator wskazówki zapytania
QueryStoreStatementHintSource	Źródło podpowiedzi dla magazynu zapytań (np. "Użytkownik")

Uwaga

Te elementy XML są dostępne za pośrednictwem danych wyjściowych poleceń Transact-SQL SET STATISTICS XML i SET SHOWPLAN XML.

Sugestie dla magazynu zapytań i interoperacyjność funkcji

• Wskazówki dotyczące magazynu zapytań zastępują inne zakodowane wskazówki dotyczące poziomu instrukcji i przewodniki dotyczące planu.
• Z wyjątkiem wskazówki ABORT_QUERY_EXECUTION, zapytania oznaczone wskazówkami magazynu zapytań zawsze są wykonywane. Przeciwdziałające podpowiedzi dotyczące Query Store są pomijane, co inaczej spowodowałoby błąd.
• Jeśli wskazówki dotyczące magazynu zapytań są sprzeczne, program SQL Server nie blokuje wykonywania zapytań, a wskazówka magazynu zapytań nie jest stosowana.
• Prosta parametryzacja — wskazówki dotyczące magazynu zapytań nie są obsługiwane w przypadku instrukcji, które kwalifikują się do prostej parametryzacji.
• Wymuszona parametryzacja — wskazówka RECOMPILE nie jest zgodna z wymuszonym ustawieniem parametryzacji na poziomie bazy danych. Jeśli baza danych ma wymuszony zestaw parametryzacji, a RECOMPILE wskazówka jest częścią ciągu wskazówek ustawionych w magazynie zapytań dla zapytania, program SQL Server ignoruje RECOMPILE wskazówkę i zastosuje wszelkie inne wskazówki, jeśli zostaną zastosowane.
  • Ponadto program SQL Server wyświetla ostrzeżenie (kod błędu 12461) z informacją, że RECOMPILE wskazówka została zignorowana.
  • Aby uzyskać więcej informacji na temat zagadnień dotyczących przypadków użycia wymuszonych parametryzacji, zobacz Wytyczne dotyczące używania wymuszonej parametryzacji.
• Ręcznie utworzone podpowiedzi w magazynie zapytań są wyłączone z czyszczenia. Wskazówka i zapytanie nie zostaną wyczyszczone z Query Store przez automatyczne zatrzymywanie zasad przechwytywania.
  • Zapytania mogą zostać ręcznie usunięte przez użytkowników, co spowoduje również usunięcie skojarzonej podpowiedzi Store zapytań.
  • Wskazówki dotyczące magazynu zapytań generowane automatycznie przez opinię CE podlegają oczyszczeniu przez automatyczne przechowywanie zasad przechwytywania.
  • Sprzężenie zwrotne DOP i sprzężenie zwrotne przydziału pamięci kształtują zachowanie zapytań bez użycia podpowiedzi Magazynu Zapytań. Gdy zapytania są czyszczone przez automatyczne czyszczenie zgodnie z polityką przechwytywania, opinie dotyczące DOP i dane dotyczące opinii o zapotrzebowaniu pamięci są również czyszczone.
  • Możesz ręcznie utworzyć tę samą wskazówkę magazynu zapytań, którą zaimplementowało dostosowanie od CE, a następnie zapytanie ze wskazówką nie będzie już podlegać czyszczeniu przez automatyczne utrzymywanie polityki przechwytywania.

Wskazówki magazynu zapytań i grupy dostępności

Wskazówki magazynu zapytań nie mają wpływu na repliki pomocnicze, chyba że magazyn zapytań dla replik pomocniczych jest włączony. Aby uzyskać więcej informacji, zobacz Magazyn zapytań dla replik pomocniczych.

• Przed wersją SQL Server 2022 (16.x) można zastosować podpowiedzi magazynu zapytań na podstawowej replice grupy dostępności.
• Począwszy od programu SQL Server 2022 (16.x), gdy magazyn zapytań dla replik pomocniczych jest włączony, wskazówki dotyczące magazynu zapytań są również obsługujące repliki pomocnicze w grupach dostępności.
• Możesz dodać wskazówkę Query Store do określonej repliki lub zestawu replik, kiedy masz włączoną funkcję Query Store dla replik pomocniczych. W sys.sp_query_store_set_query_hints jest on ustawiany przez @query_hint_scope parametr wprowadzony w programie SQL Server 2022 (16.x).
• Znajdź dostępne zestawy replik, wykonując zapytanie sys.query_store_replicas.
• Znajdź plany wymuszone na replikach pomocniczych w sys.query_store_plan_forcing_locations.

Najlepsze wskazówki dotyczące Query Store

• Ukończ konserwację indeksów i statystyk przed oceną zapytań pod kątem potencjalnych nowych wskazówek magazynu zapytań.
• Przetestuj bazę danych aplikacji na najnowszym poziomie zgodności przed użyciem wskazówek magazynu zapytań.
  • Na przykład optymalizacja Parametrycznego Planu Wrażliwego (PSP) została wprowadzona w systemie SQL Server 2022 (16.x) (poziom zgodności 160), który używa wielu aktywnych planów na zapytanie, aby rozwiązać nierównomierne rozkłady danych. Jeśli środowisko nie może korzystać z najnowszego poziomu zgodności, wskazówki dotyczące magazynu zapytań przy użyciu wskazówki RECOMPILE mogą być używane na dowolnym poziomie zgodności pomocniczej.
• Wskazówki dotyczące magazynu zapytań zastępują zachowanie planu zapytań programu SQL Server. Zaleca się używanie wskazówek magazynu zapytań tylko wtedy, gdy jest to konieczne, aby rozwiązać problemy związane z wydajnością.
• Zaleca się ponowne przeanalizowanie podpowiedzi Query Store, podpowiedzi na poziomie instrukcji, przewodników planu i wymuszonych planów Query Store w przypadku zmiany rozkładów danych oraz podczas projektów migracji bazy danych. Zmiany w dystrybucji danych mogą spowodować, że wskazówki Query Store generują nieoptymalne plany wykonywania.

Przykłady

Odp. Demonstracja wskazówek dotyczących Query Store

Poniższy przewodnik po wskazówkach dotyczących sklepu zapytań w usłudze Azure SQL Database używa zaimportowanej bazy danych przy użyciu pliku BACPAC (bacpac). Dowiedz się, jak zaimportować nową bazę danych na serwer usługi Azure SQL Database, zobacz Szybki start: importowanie pliku BACPAC do bazy danych.

-- ************************************************************************ --
-- Query Store hints demo

-- Demo uses "PropertyMLS" database which can be imported from BACPAC here:
-- https://github.com/microsoft/sql-server-samples/tree/master/samples/features/query-store

-- Email QSHintsFeedback@microsoft.com for questions\feedback
-- ************************************************************************ --

/*
    Demo prep, connect to the PropertyMLS database
*/

ALTER DATABASE [PropertyMLS] SET QUERY_STORE CLEAR;
ALTER DATABASE CURRENT SET QUERY_STORE = ON;
ALTER DATABASE CURRENT SET QUERY_STORE  (QUERY_CAPTURE_MODE = ALL);
GO

-- Should be READ_WRITE
SELECT actual_state_desc
FROM sys.database_query_store_options;
GO

/*
    You can verify Query Store Hints in sys.query_store_query_hints.
    Checking if any already exist (should be none).
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

/*
     The PropertySearchByAgent stored procedure has a parameter
     used to filter AgentId.  Looking at the statistics for AgentId,
     you will see that there is a big skew for AgentId 101.
*/
SELECT	hist.range_high_key AS [AgentId],
        hist.equal_rows
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_histogram(s.[object_id], s.stats_id) AS hist
WHERE s.[name] = N'NCI_Property_AgentId';


-- Show actual query execution plan to see plan compiled.
-- Agent with many properties will have a scan with parallelism.
EXEC [dbo].[PropertySearchByAgent] 101;

-- Agents with few properties still re-use this plan (assuming no recent plan eviction).
EXEC [dbo].[PropertySearchByAgent] 4;


/*
    Now let's find the query_id associated with this query.
*/
SELECT query_sql_text, q.query_id
FROM sys.query_store_query_text qt
INNER JOIN sys.query_store_query q ON
    qt.query_text_id = q.query_text_id
WHERE query_sql_text like N'%ORDER BY ListingPrice DESC%' and query_sql_text not like N'%query_store%';
GO

/*
     We can set the hint associated with the query_id returned in the previous result set, as below.
     Note, we can designate one or more query hints
*/
EXEC sp_query_store_set_hints @query_id=5, @value = N'OPTION(RECOMPILE)';
GO

/*
    You can verify Query Store Hints in sys.query_store_query_hints
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

-- Execute both at the same time and show actual query execution plan.
-- You should see two different plans, one for AgentId 101 and one for AgentId 4.
EXEC [dbo].[PropertySearchByAgent] 101;
EXEC [dbo].[PropertySearchByAgent] 4;
GO

SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

/*
    We can remove the hint using sp_query_store_clear_query_hints
*/
EXEC sp_query_store_clear_hints @query_id = 10;
GO

/*
    That Query Store Hint is now removed
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

-- Execute both at the same time and show actual query execution plan.
-- You should see one plan again.
EXEC [dbo].[PropertySearchByAgent] 101;
EXEC [dbo].[PropertySearchByAgent] 4;
GO

B. Identyfikacja zapytania w magazynie zapytań

Poniższe przykładowe zapytania sys.query_store_query_text i sys.query_store_query zwracają query_id dla wykonanego fragmentu tekstu zapytania.

W tym pokazie zapytanie, które próbujemy dostroić, znajduje się w przykładowej SalesLT bazie danych.

SELECT * FROM SalesLT.Address as A 
INNER JOIN SalesLT.CustomerAddress as CA
on A.AddressID = CA.AddressID
WHERE PostalCode = '98052' ORDER BY A.ModifiedDate DESC;

Magazyn zapytań nie odzwierciedla natychmiast danych zapytania w widokach systemowych.

Zidentyfikuj zapytanie w widokach wykazu systemu magazynu zapytań:

SELECT q.query_id, qt.query_sql_text
FROM sys.query_store_query_text qt 
INNER JOIN sys.query_store_query q ON 
    qt.query_text_id = q.query_text_id 
WHERE query_sql_text like N'%PostalCode =%'  
  AND query_sql_text not like N'%query_store%';
GO

W poniższych przykładach poprzedni przykład zapytania w SalesLT bazie danych został zidentyfikowany jako query_id 39.

Po zidentyfikowaniu zastosuj wskazówkę, aby wymusić maksymalny rozmiar przydziału pamięci w procentach skonfigurowanego limitu pamięci do :query_id

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(MAX_GRANT_PERCENT=10)';

Można również zastosować wskazówki dotyczące zapytań przy użyciu następującej składni, na przykład opcję wymuszania starszego narzędzia do szacowania kardynalności:

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';

Można zastosować wiele wskazówek dla zapytań, używając listy oddzielanej przecinkami.

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(RECOMPILE, MAXDOP 1, USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110''))';

Przejrzyj wskazówkę dla Store zapytań dotyczącą query_id 39:

SELECT query_hint_id, query_id, query_hint_text, last_query_hint_failure_reason, last_query_hint_failure_reason_desc, query_hint_failure_count, source, source_desc 
FROM sys.query_store_query_hints 
WHERE query_id = 39;

Na koniec usuń wskazówkę z query_id 39, używając sp_query_store_clear_hints.

EXEC sys.sp_query_store_clear_hints @query_id = 39;

Powiązana zawartość

• sys.query_store_query_hints (Transact-SQL)
• sys.sp_query_store_set_hints (Transact-SQL)
• sys.sp_query_store_clear_hints (Transact-SQL)
• zapisz plan wykonywania w formacie XML
• Wyświetlanie i zapisywanie planów wykonywania
• wskazówki (Transact-SQL) - zapytania
• Najlepsze rozwiązania dotyczące monitorowania obciążeń za pomocą magazynu zapytań
• Najlepsze praktyki dotyczące wskazówek dla Magazynu Zapytań
• Monitorowanie wydajności za pomocą magazynu zapytań
• Konfigurowanie maksymalnego stopnia równoległości (MAXDOP) w usłudze Azure SQL Database