Query Store-gebruiksscenario's

van toepassing op: SQL Server 2016 (13.x) en latere versies Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (alleen toegewezen SQL-pool)SQL-database in Microsoft Fabric

Query Store kan worden gebruikt in een breed scala aan scenario's wanneer het van cruciaal belang is om het bijhouden en garanderen van voorspelbare prestaties van workloads. Hier volgen enkele voorbeelden die u kunt overwegen:

• Query's lokaliseren en oplossen met regressies van plankeuze

• Identificeer en optimaliseer de query's die de meeste resources verbruiken.

• A/B testen

• Prestatiestabiliteit behouden tijdens de upgrade naar nieuwere SQL Server

• De ad hoc workloads identificeren en verbeteren

• Zie Prestaties bewaken met behulp van de Query Store-voor meer informatie over het configureren en beheren met de Query Store.

• Zie Prestaties afstemmen met behulp van de Query Store-voor informatie over het detecteren van bruikbare informatie en het afstemmen van prestaties met de Query Store.

• Zie Query Store in Azure SQL Database beherenvoor meer informatie over het beheren van de Query Store in Azure SQL Database.

Query's lokaliseren en oplossen met regressies van plankeuze

Tijdens de normale uitvoering van query's kan Query Optimizer besluiten om een ander plan te kiezen, omdat belangrijke invoer is veranderd: gegevenskardinaliteit is gewijzigd, indexen zijn gemaakt, gewijzigd of verwijderd, statistieken zijn bijgewerkt, enzovoort. Meestal is het nieuwe plan beter of ongeveer hetzelfde als het eerder gebruikte plan. Er zijn echter gevallen waarin een nieuw plan aanzienlijk slechter is- deze situatie wordt aangeduid als regressie van wijziging van plankeuze. Voorafgaand aan Query Store was het een probleem dat moeilijk te identificeren en op te lossen was omdat SQL Server geen ingebouwd gegevensarchief biedt, zodat gebruikers kunnen kijken naar uitvoeringsplannen die in de loop van de tijd zijn gebruikt.

Met Query Store kunt u snel het volgende doen:

• Identificeer alle query's waarvan de uitvoeringsstatistieken zijn verslechterd, in de periode van interesse (afgelopen uur, dag, week, enzovoort). Gebruik teruggedraaide query's in SQL Server Management Studio om uw analyse te versnellen.

• Onder de geregresseerde query's is het gemakkelijk om die query's te vinden die meerdere plannen hadden en die zijn verslechterd vanwege de verkeerde plankeuze. Gebruik het deelvenster Samenvatting van plannen in geregresseerde query's om alle plannen voor een geregresseerde query en de bijbehorende queryprestaties in de loop van de tijd te visualiseren.

• Dwing het vorige plan uit de geschiedenis af, indien het beter bleek. Gebruik knop Plan forceren in teruggedraaide query's om het geselecteerde plan voor de query af te dwingen.

Raadpleeg Query Store voor gedetailleerde beschrijving van het scenario: een gegevensrecorder voor vluchtgegevens voor uw database blog.

Identificeer en optimaliseer de belangrijkste query's die veel resources verbruiken.

Hoewel uw workload duizenden query's kan genereren, wordt meestal slechts een handvol query's daadwerkelijk gebruikt voor het grootste deel van de systeemresources en hebt u daarom uw aandacht nodig. Onder de belangrijkste resource-intensieve query's vindt u meestal query's die ofwel zijn verslechterd, of die kunnen worden verbeterd met extra optimalisatie.

De eenvoudigste manier om te verkennen is door Meest resources verbruikende query's te openen in Management Studio. De gebruikersinterface is onderverdeeld in drie deelvensters: een histogram dat de meestgebruikte query's (links) vertegenwoordigt, een planoverzicht voor geselecteerde query (rechts) en een visueel queryplan voor het geselecteerde plan (onder). Selecteer Configureer om te bepalen hoeveel query's u wilt analyseren en hoeveel tijdsinterval u wilt gebruiken. Daarnaast kunt u kiezen tussen verschillende dimensies voor resourceverbruik (duur, CPU, geheugen, IO, aantal uitvoeringen) en de basislijn (Gemiddelde, Min, Max, Totaal, Standaarddeviatie).

Bekijk het overzicht van het plan aan de rechterkant om de uitvoeringsgeschiedenis te analyseren en meer te weten te komen over de verschillende plannen en hun runtimestatistieken. Gebruik het onderste deelvenster om de verschillende plannen te onderzoeken of om ze visueel te vergelijken, naast elkaar weergegeven (gebruik de knop Vergelijken).

Wanneer u een query met suboptimale prestaties identificeert, is uw actie afhankelijk van de aard van het probleem:

1. Als de query is uitgevoerd met meerdere plannen en het laatste plan aanzienlijk slechter is dan het vorige plan, kunt u het mechanisme voor het afdwingen van het plan gebruiken om ervoor te zorgen dat SQL Server het optimale plan gebruikt voor toekomstige uitvoeringen

2. Controleer of de optimizer ontbrekende indexen in het XML-plan voorstelt. Zo ja, maakt u de ontbrekende index en gebruikt u de Query Store om de queryprestaties te evalueren na het maken van de index

3. Zorg ervoor dat de statistieken up-toup-to-date zijn voor de onderliggende tabellen die door de query worden gebruikt.

4. Zorg ervoor dat indexen die door de query worden gebruikt, zijn gedefragmenteerd.

5. Overweeg dure queries te herschrijven. Profiteer bijvoorbeeld van queryparameterisatie en verminder het gebruik van dynamische SQL. Implementeer optimale logica bij het lezen van de gegevens (pas gegevensfiltering toe aan de databasezijde, niet aan de toepassingszijde).

A/B testen

Gebruik Query Store om de prestaties van workloads te vergelijken voor en nadat de toepassing is gewijzigd.

De volgende lijst bevat verschillende voorbeelden waarin u Query Store kunt gebruiken om de impact van de omgeving of toepassing te beoordelen op de prestaties van de werkbelasting:

• Nieuwe toepassingsversie implementeren.

• Nieuwe hardware toevoegen aan de server.

• Ontbrekende indexen maken voor tabellen waarnaar wordt verwezen door dure query's.

• Filterbeleid toepassen voor beveiliging op rijniveau. Zie Beveiliging op rijniveau optimaliseren met Query Storevoor meer informatie.

• Tijdelijke systeemversiebeheer toevoegen aan tabellen die regelmatig worden gewijzigd door uw OLTP-toepassingen.

In een van deze scenario's wordt de volgende werkstroom toegepast:

1. Voer uw workload uit met de Query Store voordat de geplande wijziging de prestatiebasislijn genereert.

2. Toepassingswijziging toepassen op het gecontroleerde moment in de tijd.

3. Ga door met het uitvoeren van de workload lang genoeg om een prestatiebeeld van het systeem na de wijziging te genereren.

4. Vergelijk de resultaten van #1 en #3.

   1. Open Algemene databaseverbruik om de impact op de hele database te bepalen.

   2. Open meest gebruikte query's (of voer uw eigen analyse uit met behulp van Transact-SQL) om de impact van de wijziging in de belangrijkste query's te analyseren.

5. Bepaal of u de wijziging wilt behouden of terugdraait in het geval dat nieuwe prestaties onacceptabel zijn.

In de volgende afbeelding ziet u een Query Store-analyse (stap 4) bij het maken van een ontbrekende index. Open meest verbruikte query's/samenvattingsvenster plannen om deze weergave te verkrijgen voor de query die moet worden beïnvloed door het maken van de index:

Daarnaast kunt u plannen vergelijken voor en na het maken van de index door ze naast elkaar weer te geven. ("Vergelijk de plannen voor de geselecteerde query in een afzonderlijk venster"; deze werkbalkoptie is gemarkeerd met een rood vierkantje op de werkbalk.)

Plan vóór het maken van de index (plan_id = 1 hierboven) heeft een ontbrekende indexhint en u kunt controleren of geclusterde indexscan de duurste operator in de query (rode rechthoek) was.

Plan na het aanmaken van de ontbrekende index (plan_id = 15, hieronder) heeft nu een Index Seek (Nonclustered), wat de totale kosten van de query verlaagt en de prestaties verbetert (groene rechthoek).

Op basis van analyse zou u de index waarschijnlijk behouden als de queryprestaties zijn verbeterd.

Prestatiestabiliteit behouden tijdens de upgrade naar nieuwere SQL Server

Vóór SQL Server 2014 (12.x) werden gebruikers blootgesteld aan het risico op prestatieregressie tijdens de upgrade naar de nieuwste platformversie. De reden hiervoor was dat de nieuwste versie van Query Optimizer onmiddellijk actief werd zodra nieuwe bits zijn geïnstalleerd.

Vanaf SQL Server 2014 (12.x) zijn alle wijzigingen in Query Optimizer gekoppeld aan het meest recente databasecompatibiliteitsniveau, zodat plannen niet direct op het punt van upgrade worden gewijzigd, maar wanneer een gebruiker de COMPATIBILITY_LEVEL wijzigt in de meest recente versie. Deze mogelijkheid, in combinatie met Query Store, biedt u een groot niveau van controle over de queryprestaties in het upgradeproces. Aanbevolen upgradewerkstroom wordt weergegeven in de volgende afbeelding:

1. Voer een upgrade uit van SQL Server zonder het compatibiliteitsniveau van de database te wijzigen. De meest recente wijzigingen in Query Optimizer worden niet weergegeven, maar biedt nog steeds nieuwere SQL Server-functies, waaronder Query Store.

2. Schakel Query Store in. Zie Query Store aangepast houden aan uw workloadvoor meer informatie.

3. Sta Query Store toe om query's en plannen vast te leggen en stelt een prestatiebasislijn vast met het compatibiliteitsniveau van de bron/vorige database. Blijf bij deze stap lang genoeg om alle plannen vast te leggen en een stabiele basislijn te krijgen. Dit kan de duur zijn van een gebruikelijke bedrijfscyclus voor een productieworkload.

4. Ga naar het nieuwste databasecompatibiliteitsniveau: zorg ervoor dat uw workload wordt blootgesteld aan de nieuwste Query Optimizer om mogelijk nieuwe plannen te maken.

5. Query Store gebruiken voor analyse- en regressiecorrecties: meestal moeten de nieuwe verbeteringen in Query Optimizer betere plannen opleveren. Query Store biedt echter een eenvoudige manier om regressies van plankeuzes te identificeren en op te lossen met behulp van een mechanisme voor het afdwingen van plannen. Vanaf SQL Server 2017 (14.x) wordt deze stap automatisch wanneer u de functie Automatische correctie van plannen gebruikt.

   een. Voor gevallen waarin er regressies zijn, dwingt u het eerder bekende goede plan in de Query Store af.

   b. Als er queryplannen zijn die niet geforceerd kunnen worden of als de prestaties nog steeds onvoldoende zijn, moet u overwegen het compatibiliteitsniveau van de database op de vorige instelling terug te zetten of vervolgens Microsoft Klantondersteuning te betrekken.

Tip

Gebruik de taak "SQL Server Management Studio Database bijwerken" om het compatibiliteitsniveau van de -database te upgraden. Zie Databases upgraden met behulp van de queryafstemmingsassistent voor meer informatie.

Ad-hoctaken identificeren en verbeteren

Sommige workloads hebben geen dominante query's die u kunt afstemmen om de algehele prestaties van toepassingen te verbeteren. Deze workloads worden doorgaans gekenmerkt door een relatief groot aantal verschillende query's die elk een deel van systeembronnen verbruiken. Aangezien deze query's uniek zijn, worden ze zeer zelden uitgevoerd (meestal slechts één keer, vandaar de naam ad hoc), waardoor hun uitvoeringstijd niet kritisch is. Aangezien de toepassing echter altijd nieuwe nettoquery's genereert, wordt een aanzienlijk deel van de systeembronnen besteed aan het compileren van query's, wat niet optimaal is. Dit is geen ideale situatie voor Query Store, gezien het grote aantal query's en plannen de ruimte overspoelt die u hebt gereserveerd. Dit betekent dat Query Store waarschijnlijk zeer snel in de modus Alleen-lezen terechtkomt. Als u op grootte gebaseerd opschoningsbeleid hebt geactiveerd (ten zeerste aanbevolen om de Query Store altijd actief te houden), zal een achtergrondproces de Query Store-structuren meestal opschonen, wat een aanzienlijke aanslag op systeembronnen kan vormen.

De Top bronverbruikende query's weergave geeft u een eerste indicatie van het ad-hoc karakter van uw werklast.

Gebruik uitvoeringsaantal metrische gegevens om te analyseren of uw belangrijkste query's ad hoc zijn (hiervoor moet u Query Store uitvoeren met QUERY_CAPTURE_MODE = ALL). In het bovenstaande diagram ziet u dat 90% van uw hoogste bronverbruikende query's slechts eenmaal worden uitgevoerd.

U kunt ook Transact-SQL script uitvoeren om het totale aantal queryteksten, query's en plannen in het systeem op te halen en te bepalen hoe verschillend ze zijn door de query_hash en query_plan_hashte vergelijken:

--Do cardinality analysis when suspect on ad hoc workloads
SELECT COUNT(*) AS CountQueryTextRows FROM sys.query_store_query_text;  
SELECT COUNT(*) AS CountQueryRows FROM sys.query_store_query;  
SELECT COUNT(DISTINCT query_hash) AS CountDifferentQueryRows FROM  sys.query_store_query;  
SELECT COUNT(*) AS CountPlanRows FROM sys.query_store_plan;  
SELECT COUNT(DISTINCT query_plan_hash) AS  CountDifferentPlanRows FROM  sys.query_store_plan;  

Dit is een potentieel resultaat dat u kunt tegenkomen bij een werklast met ad-hocquery's:

Het queryresultaat laat zien dat ondanks het grote aantal query's en plannen in de Query Store hun query_hash en query_plan_hash eigenlijk niet van elkaar verschillen. Een verhouding tussen unieke queryteksten en unieke query-hashes, die veel groter is dan 1, is een indicatie dat de workload een goede kandidaat is voor parameterisering, omdat het enige verschil tussen de query's letterlijke constante (parameter) is die is opgegeven als onderdeel van de querytekst.

Deze situatie treedt meestal op als uw toepassing query's genereert (in plaats van opgeslagen procedures of geparameteriseerde query's aan te roepen) of als deze afhankelijk is van frameworks voor object-relationele toewijzing die standaard query's genereren.

Als u controle hebt over de toepassingscode, kunt u overwegen om de gegevenstoegangslaag te herschrijven om opgeslagen procedures of geparameteriseerde query's te gebruiken. Deze situatie kan echter ook aanzienlijk worden verbeterd zonder toepassingswijzigingen door queryparameterisatie af te dwingen voor de hele database (alle query's) of voor de afzonderlijke querysjablonen met dezelfde query_hash.

Voor een benadering met afzonderlijke querysjablonen is het maken van een planhandleiding vereist:

--Apply plan guide for the selected query template 
DECLARE @stmt nvarchar(max);  
DECLARE @params nvarchar(max);  
EXEC sp_get_query_template   
    N'<your query text goes here>',  
    @stmt OUTPUT,   
    @params OUTPUT;  
  
EXEC sp_create_plan_guide   
    N'TemplateGuide1',   
    @stmt,   
    N'TEMPLATE',   
    NULL,   
    @params,   
    N'OPTION (PARAMETERIZATION FORCED)';  

Oplossing met planhandleidingen is nauwkeuriger, maar vereist meer werk.

Als al uw query's (of de meerderheid ervan) kandidaten zijn voor automatische parameterisatie, kunt u overwegen om PARAMETERIZATION = FORCED te configureren voor de hele database. Zie Richtlijnen voor het gebruik van geforceerde parametersvoor meer informatie.

--Apply forced parameterization for entire database  
ALTER DATABASE <database name> SET PARAMETERIZATION FORCED;  

Nadat u een van deze stappen hebt toegepast, geeft Top Resource Consumerende Query's u een ander beeld van uw workload.

In sommige gevallen kan uw toepassing veel verschillende query's genereren die geen goede kandidaten zijn voor automatische parameterisatie. In dat geval ziet u een groot aantal query's in het systeem, maar de verhouding tussen unieke query's en unieke query_hash is waarschijnlijk dicht bij 1.

In dat geval kunt u de optie Optimaliseren voor ad-hocworkloads server inschakelen om te voorkomen dat cachegeheugen wordt verspild aan query's die waarschijnlijk niet opnieuw worden uitgevoerd. Als u wilt voorkomen dat deze query's in de Query Store worden vastgelegd, stelt u QUERY_CAPTURE_MODE in op AUTO.

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO 
  
ALTER DATABASE [QueryStoreTest] SET QUERY_STORE CLEAR;  
ALTER DATABASE [QueryStoreTest] SET QUERY_STORE = ON   
    (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO);  

Volgende stappen

• Prestaties bewaken met de Query Store gebruiken
• Best Practice met de Query Store
• Databases upgraden met behulp van de Query Tuning Assistant