Sdílet prostřednictvím

Pokyny k Query Store

Platí pro: SQL Server 2022 (16.x) a novější verze Azure SQL DatabaseAzure SQL Managed InstanceSQL databáze v Microsoft Fabric

Tento článek popisuje, jak použít nápovědy k dotazům pomocí úložiště dotazů. Rady úložiště dotazů poskytují snadno použitelnou metodu pro tvarování plánů dotazů beze změny kódu aplikace.

Caution

Vzhledem k tomu, že optimalizátor dotazů SQL Serveru obvykle vybírá nejlepší plán provádění dotazu, doporučujeme jako poslední možnost použít pouze rady pro zkušené vývojáře a správce databází. Další informace najdete v tématu Nápovědy k dotazům.

Podívejte se na toto video pro přehled návodů k Úložišti dotazů.

Overview

Optimalizátor dotazů v ideálním případě vybere optimální plán provádění dotazu.

Pokud není vybraný optimální plán, může vývojář nebo správce databáze (DBA) chtít ručně optimalizovat konkrétní podmínky. Nápovědy dotazu se zadají prostřednictvím klauzule OPTION a dají se použít k ovlivnění chování při provádění dotazu. Nápovědy k dotazům sice pomáhají poskytovat lokalizovaná řešení různých problémů souvisejících s výkonem, ale vyžadují přepsání původního textu dotazu. Správci databází a vývojáři nemusí vždy provádět změny přímo v kódu Transact-SQL a přidat nápovědu k dotazu. Transact-SQL může být pevně zakódovaná do aplikace nebo automaticky vygenerovaná aplikací. Dříve se vývojář mohl spoléhat na průvodci plánováním, což může být složité.

Hinty pro úložiště dotazů tento problém řeší vložením hintu dotazu do dotazu, aniž by bylo nutné upravovat text dotazu Transact-SQL přímo. Informace o tom, které dotazové nápovědy lze použít, naleznete v tématu Podporované dotazové nápovědy.

Kdy použít pokyny k úložišti dotazů

Jak název napovídá, tato funkce rozšiřuje svoji funkčnost a závisí na úložišti dotazů. Úložiště dotazů umožňuje zachytávání dotazů, plánů spouštění a přidružených statistik modulu runtime. Úložiště dotazů výrazně zjednodušuje celkové možnosti ladění výkonu pro zákazníky. SQL Server 2016 (13.x) poprvé zavedl úložiště dotazů a teď je ve výchozím nastavení povolený v SQL Serveru 2022 (16.x), Spravované instanci Azure SQL, Azure SQL Database a databázi SQL v Microsoft Fabric.

Pracovní postup tipů pro úložiště dotazů.

Příklady, kdy vám mohou pomoci nápovědy Úložiště dotazů při řešení problémů s výkonem dotazů:

  • Překompilujte dotaz při každém spuštění.
  • Omezte velikost přidělení paměti pro hromadnou operaci vložení.
  • Omezte maximální stupeň paralelismu při aktualizaci statistik.
  • Místo spojení Nested Loops použijte hash spojení.
  • Pro konkrétní dotaz použijte úroveň kompatibility 110 a přitom ponechte všechno ostatní v databázi na úrovni kompatibility 150.
  • Zakázat optimalizaci cílového počtu řádků pro dotaz SELECT TOP.

Jak používat tipy pro úložiště dotazů:

  1. Identifikujte úložiště dotazů query_id příkazu dotazu, který chcete upravit. Můžete to udělat různými způsoby:

    • Dotazování na zobrazení katalogu Query Store (Transact-SQL).
    • Použití vestavěných sestav Úložiště dotazů v aplikaci SQL Server Management Studio.
    • Použití nástroje Azure Portal Query Performance Insight pro Azure SQL Database

  2. Spusťte sys.sp_query_store_set_hints s query_id a návrhovým řetězcem dotazu, který chcete pro dotaz použít. Tento řetězec může obsahovat jeden nebo více tipů pro dotazy. Pro úplné informace se podívejte na sys.sp_query_store_set_hints.

Po vytvoření zůstanou pokyny Úložiště dotazů zachovány a přežijí restartování a převzetí služeb při selhání. Nápovědy k úložišti dotazů přepisují pevně zakódované rady na úrovni příkazů a existující rady průvodce plánem.

Pokud nápověda dotazu odporuje tomu, co je možné pro optimalizaci dotazů, spuštění dotazu není zablokované a nápověda se nepoužije. V případech, kdy by nápověda způsobila selhání dotazu, je tip ignorován a nejnovější podrobnosti o selhání je možné zobrazit v sys.query_store_query_hints.

Před použitím tipů pro Query Store

Než začnete používat tipy pro úložiště dotazů, vezměte v úvahu následující.

  • Před vyhodnocením dotazů pro potenciální nové hinty Úložiště dotazů dokončete údržbu statistik a údržbu indexu, pokud je to nutné. Údržba statistik a údržba indexu nižšího stupně může vyřešit problém, který vyžaduje nápovědu k dotazu v opačném případě.
  • Než použijete nápovědu k úložišti dotazů, otestujte svou aplikační databázi na nejnovější úrovni kompatibility , abyste zjistili, jestli se tím vyřeší problém, který vyžaduje nápovědu k dotazu.
    • Například optimalizace plánu citlivého na parametry (PSP) byla zavedena v SQL Serveru 2022 (16.x) pod úrovní kompatibility 160. Používá více aktivních plánů na dotaz k řešení neuniformních distribucí dat. Pokud vaše prostředí nemůže používat nejnovější úroveň kompatibility, lze nápovědy úložiště dotazů s použitím příkazu RECOMPILE využít na jakékoli úrovni kompatibility, která je podporována.
  • Parametry úložiště dotazů přepíší chování výchozího plánu dotazů na databázovém serveru. Nápovědu úložiště dotazů byste měli použít jenom v případě, že je potřeba řešit problémy související s výkonem.
  • Měli byste znovu vyhodnotit rady úložiště dotazů, rady na úrovni příkazů, příručky plánů a vynucené plány úložiště dotazů kdykoli, když dojde ke změnám objemu a distribuce dat a během migrace databází. Změny objemu dat a jejich distribuce můžou způsobit, že v Query Store se vygenerují suboptimální plány vykonávání.

Systémové uložené procedury v úložišti dotazů

Chcete-li vytvořit nebo aktualizovat nápovědy, použijte sys.sp_query_store_set_hints. Rady jsou zadány v platném řetězcovém formátu N'OPTION (...)'.

  • Pokud vytváříte nápovědu k úložišti dotazů a neexistuje žádná nápověda k úložišti dotazů pro konkrétní query_id, vytvoří se nová nápověda k úložišti dotazů.
  • Pokud při vytváření nebo aktualizaci nápovědy úložiště dotazů již pro konkrétní query_idexistuje nápověda úložiště dotazů, poslední zadaná hodnota přepíše hodnoty dříve zadané pro přidružený dotaz.
  • Pokud query_id neexistuje, vyvolá se chyba.

Úplný seznam nápověd podporovaných jako nápověda úložiště dotazů najdete v sys.sp_query_store_set_hints.

Pro odebrání nápověd spojených s query_idpoužijte sys.sp_query_store_clear_hints.

Tip

Možná budete muset nastavit nebo vymazat nápovědy pro všechny query_id hodnoty odpovídající hash dotazu.

dbo.sp_query_store_modify_hints_by_query_hash je ukázková uložená procedura, která volá systémovou uloženou proceduru sys.sp_query_store_set_hints nebo sys.sp_query_store_clear_hints v rámci smyčky, aby toho dosáhla.

Atributy XML plánu provádění

Při použití nápovědy se v elementu StmtSimple ve formátu XML zobrazí následující sada výsledků:

Attribute Description
QueryStoreStatementHintText Aktuální nápovědy úložiště dotazů aplikované na dotaz
QueryStoreStatementHintId Jedinečný identifikátor nápovědy dotazu
QueryStoreStatementHintSource Zdroj tipu pro Query Store (například User)

Note

Tyto elementy XML jsou k dispozici ve výstupu příkazů Transact-SQL SET STATISTICS XML a SET SHOWPLAN_XML.

Nápovědy k úložišti dotazů a interoperabilita funkcí

  • Pokyny úložiště dotazů přebijí ostatní pevně zakódované pokyny úrovně příkazů a průvodce plánu.
  • S výjimkou ABORT_QUERY_EXECUTION nápovědy se dotazy s hinty Query Store vždy spustí. Protichůdné hinty úložiště dotazů, které by jinak způsobily chybu, jsou ignorovány.
  • Pokud se hinty úložiště dotazů vzájemně vylučují, databázový stroj neblokuje provádění dotazů a hint úložiště dotazů se nepoužije.
  • Pokyny úložiště dotazů nejsou podporované pro příkazy, které mají nárok na jednoduchou parametrizaci.
  • Nápověda RECOMPILE není kompatibilní s vynucenou parametrizací nastavenou na úrovni databáze. Pokud má databáze nastavenou vynucenou parametrizaci a RECOMPILE tip je součástí nápovědy úložiště dotazů pro dotaz, databázový stroj tuto nápovědu RECOMPILE ignoruje a použije všechny další rady, pokud jsou zadané.
    • Databázový stroj vydává upozornění (kód chyby 12461), které hlásí, že RECOMPILE hint byl ignorován.
    • Další informace o aspektech použití vynucené parametrizace naleznete v tématu Pokyny pro použití vynucené parametrizace.
  • Ručně vytvořené hinty pro úložiště dotazů jsou vyjmuté z procesu čištění úložiště dotazů. Nápověda a dotaz nejsou čištěny zásadami automatického uchovávání dat.
    • Uživatelé můžou dotazy odebrat ručně. Tím se odebere také přidružený tip úložiště dotazů.
    • Nápovědy k úložišti dotazů automaticky generované zpětné vazby CE se můžou vyčistit automatickým uchováváním zásad zachycení.
    • zpětná vazba DOP a zpětná vazba udělování paměti formují chování dotazu bez použití hintů z úložiště dotazů. Když se dotazy vyčistí pomocí zásad automatického zachytávání informací o uchovávání informací, vyčistí se také zpětná vazba DOP a data o udělení paměti.
    • Pokud vytvoříte stejnou nápovědu pro úložiště dotazů, jakou zpětná vazba CE manuálně implementovala, dotaz s touto nápovědou již nebude podléhat čištění podle zásady automatického zachytávání uchovávání.

Query Store nápovědy a sekundární repliky

Nápovědy úložiště dotazů nemají žádný vliv na sekundární repliky, pokud není povolené úložiště dotazů pro sekundární repliky. Další informace najdete v tématu Úložiště dotazů pro čitelné sekundární soubory.

  • V SYSTÉMU SQL Server 2022 (16.x) a starších verzích je možné na primární repliku použít pouze rady úložiště dotazů.
  • Pokud je v SQL Serveru 2025 (17.x) a novějších verzích povolené úložiště dotazů pro sekundární repliky, dají se na sekundární repliky ve skupinách dostupnosti použít rady úložiště dotazů. Kompletní podporu platformy najdete v tématu Úložiště dotazů pro čtení sekundárních souborů.

Kde je úložiště dotazů podporováno na sekundárních replikách:

Examples

A. Ukázka použití nápověd pro Query Store

Následující návod na použití nápověd úložiště dotazů v rámci služby Azure SQL Database využívá databázi importovanou prostřednictvím souboru BACPAC (.bacpac). Informace o importu nové databáze na server Azure SQL Database najdete v tématu Rychlý start: Import souboru bacpac do databáze ve službě Azure SQL Database nebo Azure SQL Managed Instance.

-- ************************************************************************ --
-- 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. Identifikace dotazu v úložišti dotazů

Následující příklad dotazu sys.query_store_query_text a sys.query_store_query je použitý k vrácení query_id pro fragment textu vykonaného dotazu.

V této ukázce je dotaz, který se pokoušíme vyladit, v ukázkové databázi SalesLT:

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;

Úložiště dotazů okamžitě neodráží data dotazů do systémových zobrazení.

Identifikujte dotaz v zobrazeních systémového katalogu úložiště dotazů:

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

V následujících ukázkách byl předchozí příklad dotazu v databázi SalesLT identifikován jako query_id 39.

Jakmile je identifikováno, použijte nápovědu k vynucení maximální velikosti přidělení paměti, které představuje procento nakonfigurovaného limitu paměti pro query_id.

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

Můžete také použít nápovědy pro dotazy s následující syntaxí, například možnost vynutit starší verzi nástroje pro posouzení kardinality:

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

S čárkami odděleným seznamem můžete použít několik tipů pro dotazy:

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

Zkontrolujte doporučení Query Store pro 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;

Nakonec odeberte nápovědu z query_id 39 pomocí sp_query_store_clear_hints.

EXEC sys.sp_query_store_clear_hints @query_id = 39;

Související obsah

  • Last updated on