Hints voor Query Store

Van toepassing op: SQL Server 2022 (16.x) en latere versies Van Azure SQL DatabaseAzure SQL Managed InstanceSQL Database in Microsoft Fabric Preview

In dit artikel wordt beschreven hoe u queryhints toepast met behulp van de Query Store. Query Store-hints bieden een gebruiksvriendelijke methode voor het vormgeven van queryplannen zonder toepassingscode te wijzigen.

• Zie Prestaties bewaken met behulp van de Query Store voor meer informatie over het configureren en beheren van Query Store.
• Voor informatie over het ontdekken van bruikbare gegevens en het verbeteren van de prestaties, zie Prestaties afstemmen met de Query Store.
• Zie het gebruik van de Query Store in Azure SQL Databasevoor meer informatie over het gebruik van de Query Store.

Bekijk deze video voor een overzicht van Query Store-hints:

Overview

In het ideale voorbeeld selecteert de Query Optimizer een optimaal uitvoeringsplan voor een query.

Als er geen optimaal plan is geselecteerd, wil een ontwikkelaar of databasebeheerder (DBA) mogelijk handmatig optimaliseren voor specifieke voorwaarden. Queryhints worden opgegeven via de OPTION-component en kunnen worden gebruikt om het gedrag van de queryuitvoering te beïnvloeden. Hoewel queryhints helpen gelokaliseerde oplossingen te bieden voor verschillende prestatieproblemen, moeten ze de oorspronkelijke querytekst opnieuw schrijven. Databasebeheerders en ontwikkelaars kunnen mogelijk niet altijd rechtstreeks wijzigingen aanbrengen in Transact-SQL code om een queryhint toe te voegen. De Transact-SQL kan in code worden vastgelegd in een toepassing of automatisch worden gegenereerd door de toepassing. Voorheen moest een ontwikkelaar mogelijk afhankelijk zijn van planhandleidingen, wat complex kan zijn in het gebruik.

Query Store-hints lossen dit probleem op door een queryhint aan een query toe te voegen zonder de Transact-SQL-tekst van de query direct te wijzigen. Zie Ondersteunde queryhintsvoor informatie over welke queryhints kunnen worden toegepast.

Wanneer gebruikt u Query Store-hints

Zoals de naam al aangeeft, wordt deze functie uitgebreid en is afhankelijk van de Query Store-. Query Store maakt het vastleggen van query's, uitvoeringsplannen en bijbehorende runtimestatistieken mogelijk. Query Store vereenvoudigt het algehele proces van prestatieoptimalisatie voor de klantervaring aanzienlijk. SQL Server 2016 (13.x) heeft Query Store voor het eerst geïntroduceerd en nu is deze standaard ingeschakeld in SQL Server 2022 (16.x), Azure SQL Managed Instance, Azure SQL Database en SQL Database in Microsoft Fabric Preview.

Voorbeelden waarbij Query Store-hints kunnen helpen bij prestatieproblemen op queryniveau:

• Een query opnieuw compileren voor elke uitvoering.
• Limiteer de geheugenverleening voor een bulk-insertoperatie.
• Beperk de maximale mate van parallelle uitvoering bij het bijwerken van statistieken.
• Gebruik een Hash-join in plaats van een Geneste Lussen-join.
• Gebruik compatibiliteitsniveau 110 voor een specifieke query terwijl u alle andere items in de database op compatibiliteitsniveau 150 bewaart.
• Schakel rijdoeloptimalisatie bij een SELECT TOP query uit.

Hints gebruiken voor Query Store:

1. Identificeer de Query Store query_id van de queryverklaring die u wilt wijzigen. U kunt dit op verschillende manieren doen:

   • Query's uitvoeren op de Query Store-catalogusweergaven (Transact-SQL).
   • Ingebouwde Query Store-rapporten van SQL Server Management Studio gebruiken.
   • Query Performance Insight gebruiken in Azure Portal voor Azure SQL Database.

2. Voer sys.sp_query_store_set_hints uit met de queryhint-tekenreeks query_id die u op de query wilt toepassen. Deze tekenreeks kan een of meer query hints bevatten. Zie sys.sp_query_store_set_hintsvoor volledige informatie.

Na het maken worden Query Store-hints behouden en overleven ze herstarten en failovers. Query Store-hints overschrijven hints op basis van code op instructieniveau en bestaande hints voor planrichtlijnen.

Als een queryhint in strijd is met wat er mogelijk is voor queryoptimalisatie, wordt de uitvoering van query's niet geblokkeerd en wordt de hint niet toegepast. In de gevallen waarin een hint ertoe zou leiden dat een query mislukt, wordt de hint genegeerd en kunnen de meest recente foutdetails worden weergegeven in sys.query_store_query_hints.

Voordat u Query Store-hints gebruikt

Houd rekening met het volgende voordat u Query Store-hints gaat gebruiken.

• Voltooi onderhoud van statistieken en indexonderhoud (indien nodig) voordat u query's evalueert op mogelijke nieuwe Query Store-hints. Onderhoud van statistieken en indexonderhoud kan het probleem oplossen waarvoor anders een queryhint is vereist.
• Voordat u Query Store-hints gebruikt, test u de toepassingsdatabase op het meest recente compatibiliteitsniveau om te zien of dat het probleem oplost waarvoor een queryhint is vereist.
  • Zo is de parametergevoelige planoptimalisatie (PSP) geïntroduceerd in SQL Server 2022 (16.x) onder compatibiliteitsniveau 160. Er worden meerdere actieve abonnementen per query gebruikt om niet-uniforme gegevensdistributies aan te pakken. Als uw omgeving het meest recente compatibiliteitsniveau niet kan gebruiken, kunnen Query Store-hints met behulp van de RECOMPILE hint worden gebruikt op elk ondersteunend compatibiliteitsniveau.
• Query Store-hints overschrijven het standaardgedrag van het Query Engine-queryplan. U moet alleen Query Store-hints gebruiken wanneer het nodig is om prestatieproblemen op te lossen.
• ** U moet aanwijzingen voor de Query Store, aanwijzingen op instructieniveau, planhandleidingen en afgedwongen Query Store-plannen opnieuw evalueren wanneer gegevensvolume en distributie veranderen, en tijdens databasemigratieprojecten. Wijzigingen in het gegevensvolume en de distributie kunnen ertoe leiden dat Query Store-hints suboptimale uitvoeringsplannen genereren.

Systeem opgeslagen procedures voor Query Store-hints

Gebruik sys.sp_query_store_set_hintsom hints te maken of bij te werken. Hints worden opgegeven in een geldige tekenreeksindeling N'OPTION (...)'.

• Wanneer u een Query Store-hint maakt en er geen Query Store-hint bestaat voor een specifieke query_id, wordt er een nieuwe Query Store-hint gemaakt.
• Wanneer u een Query Store-hint maakt of bijwerkt, als er al een Query Store-hint bestaat voor een specifieke query_id, overschrijft de laatste opgegeven waarde eerder opgegeven waarden voor de bijbehorende query.
• Als er geen query_id bestaat, wordt er een fout gegenereerd.

Zie sys.sp_query_store_set_hints voor een volledige lijst met hints die worden ondersteund als een Query Store-hint.

Als u hints wilt verwijderen die zijn gekoppeld aan een query_id, gebruikt u sys.sp_query_store_clear_hints.

XML-kenmerken van uitvoeringsplan

Wanneer hints worden toegepast, wordt de volgende resultatenset weergegeven in het element van het StmtSimpleuitvoeringsplan in XML-indeling:

Hints en functie-interoperabiliteit van Query Store

• Query Store-hints overschrijven andere hard-gecodeerde instructieniveau-hints en plangidsen.
• Met uitzondering van de ABORT_QUERY_EXECUTION hint worden query's met Query Store-hints altijd uitgevoerd. Tegengestelde Query Store-hints worden genegeerd die anders een fout zouden veroorzaken.
• Als Query Store-hints in strijd zijn, blokkeert de database-engine de uitvoering van query's niet en wordt de Query Store-hint niet toegepast.
• Query Store-hints worden niet ondersteund voor instructies die in aanmerking komen voor eenvoudige parameterisatie.
• De RECOMPILE hint is niet compatibel met geforceerde parameters die zijn ingesteld op databaseniveau. Als een database geforceerde parameterset heeft ingesteld en de RECOMPILE hint deel uitmaakt van de Hints van Query Store voor een query, negeert de database-engine de RECOMPILE hint en past deze andere hints toe als deze zijn opgegeven.
  • De database-engine geeft een waarschuwing (foutcode 12461) aan dat de RECOMPILE hint is genegeerd.
  • Zie Richtlijnen voor het gebruik van geforceerde parameterisatie voor meer informatie over overwegingen bij het gebruik van geforceerde parameterisatie.
• Handmatig gemaakte Query Store-hints zijn uitgesloten van het opschonen van Query Store. De hint en de query worden niet opgeschoond door het automatische bewaarbeleid.
  • Query's kunnen handmatig worden verwijderd door gebruikers. Hiermee wordt ook de bijbehorende Query Store-hint verwijderd.
  • Query Store-hints die automatisch worden gegenereerd door de CE-feedback worden opgeschoond door de automatische retentie van het opnamebeleid.
  • DOP-feedback en geheugen toewijzingsfeedback beïnvloeden het gedrag van query's zonder Query Store-hints te gebruiken. Wanneer query's worden opgeschoond door het automatische bewaarbeleid, worden ook DOP-feedback en feedbackgegevens voor geheugentoekenningen opgeschoond.
  • Als u dezelfde Query Store-hint maakt die CE-feedback handmatig heeft geïmplementeerd, is de query met de hint niet langer onderhevig aan opschoning door het beleid voor automatische retentieopname.

Query Store hints en secundaire replicas

Query Store-hints hebben geen effect op secundaire replica's, tenzij Query Store voor secundaire replica's is ingeschakeld. Zie Query Store voor leesbare secundaire bestanden voor meer informatie.

• In SQL Server 2022 (16.x) en eerdere versies kunnen alleen Query Store-hints worden toegepast op de primaire replica.
• In SQL Server 2022 (16.x) en latere versies, wanneer Query Store voor secundaire replica's is ingeschakeld, kunnen Query Store-hints worden toegepast op secundaire replica's in beschikbaarheidsgroepen.
• U kunt een Query Store-hint toevoegen die alleen van kracht is op een specifieke replicagroep wanneer Query Store is ingeschakeld voor secundaire replica's. Gebruik hiervoor de @replica_group_id parameter bij het aanroepen van sys.sp_query_store_set_query_hints. Omgekeerd kunt u een Query Store-hint uit een specifieke replicagroep verwijderen met behulp van sys.sp_query_store_clear_query_hints.
• Zoek de beschikbare replicagroepen door een query uit te voeren op sys.query_store_replicas.
• Plannen zoeken die op secundaire replica's worden afgedwongen met sys.query_store_plan_forcing_locations.

Examples

A. Demo van hints voor Query Store

In het volgende overzicht van Query Store-hints in Azure SQL Database wordt een geïmporteerde database gebruikt via een BACPAC-bestand (.bacpac). Leer hoe u een nieuwe database kunt importeren op een Azure SQL Database server, zie Quickstart: Een bacpac-bestand importeren in een database in Azure SQL Database of 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. Een query identificeren in Query Store

In het volgende voorbeeld worden de query's van sys.query_store_query_text en sys.query_store_query gebruikt om de query_id voor een uitgevoerd tekstfragment van een query te retourneren.

In deze demo bevindt de query die we proberen af te stemmen zich in de SalesLT voorbeelddatabase:

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;

Query Store geeft niet onmiddellijk querygegevens weer in de systeemweergaven.

Identificeer de query in de systeemcatalogusweergaven van Query Store:

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

In de volgende voorbeelden is het vorige queryvoorbeeld in de SalesLT-database geïdentificeerd als query_id 39.

Pas na identificatie de hint toe om een maximale geheugentoekenningsgrootte af te dwingen in procent van de geconfigureerde geheugenlimiet op de query_id:

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

U kunt ook queryhints toepassen met de volgende syntaxis, bijvoorbeeld de optie om de verouderde kardinaliteitsschatter af te dwingen:

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

U kunt meerdere queryhints toepassen met een door komma's gescheiden lijst:

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

Controleer de Query Store-hint voor 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;

Verwijder ten slotte de hint uit query_id 39 met behulp van sp_query_store_clear_hints.

EXEC sys.sp_query_store_clear_hints @query_id = 39;

Related content

• sys.query_store_query_hints (Transact-SQL)
• sys.sp_query_store_set_hints (Transact-SQL)
• sys.sp_query_store_clear_hints (Transact-SQL)
• Een uitvoeringsplan opslaan in XML-indeling
• uitvoeringsplannen weergeven en opslaan
• queryhints (Transact-SQL)
• Aanbevolen procedures voor het bewaken van workloads met Query Store-
• aanbevolen procedures voor Query Store
• Prestaties bewaken met behulp van de Query Store-
• de maximale mate van parallelle uitvoering (MAXDOP) configureren in Azure SQL Database