Automatisch afstemmen

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

Automatisch afstemmen is een databasefunctie die inzicht biedt in mogelijke problemen met queryprestaties, het aanbevelen van oplossingen en het automatisch oplossen van geïdentificeerde problemen.

Automatisch afstemmen, geïntroduceerd in SQL Server 2017 (14.x), meldt u wanneer een mogelijk prestatieprobleem wordt gedetecteerd en kunt u corrigerende acties toepassen of de database-engine automatisch prestatieproblemen laten oplossen. Automatisch afstemmen van SQL Server identificeert en lost prestatieproblemen op die worden veroorzaakt door regressies van het queryuitvoeringsplan. Automatisch afstemmen in Azure SQL Database en SQL-database in Microsoft Fabric maakt ook noodzakelijke indexen en verwijdert ongebruikte indexen. Zie Uitvoeringsplannen voor meer informatie over queryuitvoeringsplannen.

De SQL Server Database Engine bewaakt de query's die worden uitgevoerd op de database en verbetert automatisch de prestaties van de workload. De database-engine heeft een ingebouwd intelligentiemechanisme dat automatisch de prestaties van uw query's kan afstemmen en verbeteren door de database dynamisch aan te passen aan uw workload. Er zijn twee functies voor automatisch afstemmen beschikbaar:

  • Automatische plancorrectie identificeert problematische uitvoeringsplannen voor query's, zoals een parametergevoeligheid of parametersniffing-problemen en lost prestatieproblemen met betrekking tot queryuitvoeringsplan op door het laatst bekende goede plan af te dwingen voordat de regressie is opgetreden. Van toepassing op: SQL Server (te beginnen met SQL Server 2017 (14.x)), Azure SQL Database en SQL-database in Microsoft Fabric en Azure SQL Managed Instance

  • Automatisch indexbeheer identificeert indexen die moeten worden toegevoegd aan uw database en indexen die moeten worden verwijderd. Van toepassing op: Azure SQL Database en SQL-database in Microsoft Fabric

Note

In dit artikel zijn functies en gedragingen van Azure SQL Database ook van toepassing op SQL Database in Microsoft Fabric.

Waarom automatisch afstemmen?

Drie van de belangrijkste taken in het klassieke databasebeheer zijn het controleren van de workload, het identificeren van kritieke Transact-SQL query's en het identificeren van indexen die moeten worden toegevoegd om de prestaties te verbeteren, of indexen die zelden worden gebruikt en kunnen worden verwijderd om de prestaties te verbeteren. De SQL Server Database Engine biedt gedetailleerd inzicht in de query's en indexen die u moet bewaken. Het voortdurend bewaken van een database is echter een moeilijke en tijdrovende taak, vooral bij het omgaan met veel databases. Het beheren van een groot aantal databases is mogelijk onmogelijk om efficiënt te doen. In plaats van uw database handmatig te controleren en af te stemmen, kunt u overwegen om enkele van de bewakings- en afstemmingsacties te delegeren aan de database-engine met behulp van de functie voor automatisch afstemmen.

Hoe werkt automatisch afstemmen?

Automatisch afstemmen is een continu bewakings- en analyseproces dat voortdurend leert over de kenmerken van uw workload en potentiële problemen en verbeteringen identificeert.

Automatisch afstemmen.

Met dit proces kan de database dynamisch worden aangepast aan uw workload door te bepalen welke indexen en plannen de prestaties van uw workloads kunnen verbeteren en welke indexen van invloed zijn op uw workloads. Op basis van deze bevindingen past automatisch afstemmen toe op afstemmingsacties die de prestaties van uw workload verbeteren. Daarnaast bewaakt automatisch afstemmen continu de prestaties van de database na het implementeren van wijzigingen om ervoor te zorgen dat de prestaties van uw workload worden verbeterd. Elke actie die de prestaties niet heeft verbeterd, wordt automatisch teruggezet. Dit verificatieproces is een belangrijke functie die ervoor zorgt dat wijzigingen die door automatisch afstemmen worden aangebracht, de algehele prestaties van uw workload niet verminderen.

Automatische correctie van plannen

Automatische correctie van plannen is een automatische afstemmingsfunctie waarmee keuze-regressie van het uitvoeringsplan wordt geïdentificeerd en het probleem automatisch wordt opgelost door het laatst bekende goede plan af te dwingen. Zie de architectuurhandleiding voor queryverwerking voor meer informatie over queryuitvoeringsplannen en queryoptimalisatie.

Important

Automatische correctie van plannen is afhankelijk van het inschakelen van Query Store in de database voor het bijhouden van werkbelastingen.

Wat is prestatie-terugval bij de keuze van het uitvoeringsplan?

De SQL Server Database Engine kan verschillende uitvoeringsplannen gebruiken om de Transact-SQL-query's uit te voeren. Queryplannen hangen af van statistieken, indexen en andere factoren. Het optimale plan dat moet worden gebruikt om een Transact-SQL query uit te voeren, kan na verloop van tijd veranderen, afhankelijk van wijzigingen in deze factoren. In sommige gevallen is het nieuwe plan mogelijk niet beter dan het vorige plan en kan het nieuwe plan een prestatieregressie veroorzaken, zoals een parametergevoeligheid of een probleem met betrekking tot parametersniffing .

Queryuitvoeringsplankeuzeregressie.

Wanneer u merkt dat er een plankeuzeregressie is opgetreden, moet u een eerder goed plan vinden en afdwingen dat het moet worden gebruikt in plaats van de huidige. U kunt dit doen met behulp van de sp_query_store_force_plan procedure. De database-engine in SQL Server 2017 (14.x) bevat informatie over teruggedraaide plannen en aanbevolen corrigerende acties. Bovendien kunt u met database-engine dit proces volledig automatiseren en database-engine elk probleem laten oplossen dat verband houdt met de wijziging van het plan.

Important

Automatische correctie van plannen moet worden gebruikt in het kader van een upgrade van het databasecompatibiliteitsniveau, nadat een basislijn is vastgelegd, om de risico's van de upgrade van workloads automatisch te beperken. Zie Prestatiestabiliteit behouden tijdens de upgrade naar nieuwere SQL Server voor meer informatie over deze use case.

Automatische correctie van de keuze van plannen

De database-engine kan automatisch overschakelen naar het laatst bekende goede plan wanneer er een regressie in de plan keuze wordt gedetecteerd.

Correctie van de keuze van het queryuitvoeringsplan.

De database-engine detecteert automatisch eventuele regressie van plannen, inclusief het plan dat moet worden gebruikt in plaats van het verkeerde plan. Het resulterende uitvoeringsplan dat wordt gedwongen door automatische correctie van plannen, is hetzelfde of vergelijkbaar met het laatst bekende goede plan. Omdat het resulterende plan mogelijk niet identiek is aan het laatste goede plan, kunnen de prestaties van het geforceerde plan variëren. In zeldzame gevallen kan het prestatieverschil aanzienlijk en negatief zijn; In dit geval stopt automatische correctie van plannen automatisch met het afdwingen van het vervangende plan.

Wanneer de database-engine het laatst bekende goede plan toepast voordat de regressie heeft plaatsgevonden, wordt automatisch de prestaties van het geforceerde plan bewaakt. Als het geforceerde plan niet beter is dan het geregresseerde plan, wordt het nieuwe plan niet geforceerd en compileert de Database Engine een nieuw plan. Als de database-engine controleert of het geforceerde plan beter is dan het teruggedraaide plan, blijft het geforceerde plan behouden. Deze wordt bewaard totdat een hercompilatie plaatsvindt (bijvoorbeeld bij de volgende update van statistieken of schemawijziging). Zie Beperkingen van het afdwingen van plannen voor meer informatie over het afdwingen van plannen en typen plannen die geforceerd kunnen worden.

Note

Als het SQL Server-exemplaar opnieuw wordt opgestart voordat een plan voor het afdwingen van actie wordt geverifieerd, wordt dat plan automatisch niet afgedwongen. Anders blijft de planafdwinging behouden wanneer SQL Server opnieuw wordt opgestart.

Automatische correctie van de keuze van het abonnement inschakelen

U kunt automatische afstemming per database inschakelen en opgeven dat het laatste goede plan geforceerd moet worden wanneer een regressie in de veranderingen van plannen wordt gedetecteerd. Automatisch afstemmen is ingeschakeld met behulp van de volgende opdracht:

ALTER DATABASE <yourDatabase>
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );

Zodra u deze optie inschakelt, dwingt de database-engine automatisch een aanbeveling af waarbij de geschatte CPU-toename hoger is dan 10 seconden, of het aantal fouten in het nieuwe plan hoger is dan het aantal fouten in het aanbevolen plan en controleert u of het geforceerde plan beter is dan het huidige.

Zie Automatisch afstemmen in Azure SQL Database en Azure SQL Managed Instanceinschakelen met behulp van Azure Portal om automatisch afstemmen in te schakelen in Azure SQL Database.

Alternatief - handmatige correctie van plankeuze

Zonder automatische tuning moeten gebruikers het systeem periodiek controleren en zoeken naar de query's die zijn geregresseerd. Als een plan is teruggedraaid, moet de gebruiker een eerder goed plan vinden en dit afdwingen in plaats van het huidige plan met behulp van de sp_query_store_force_plan procedure. De aanbevolen procedure is om het laatst bekende goede plan af te dwingen, omdat oudere plannen mogelijk ongeldig zijn vanwege wijzigingen in de statistiek of index. De gebruiker die het laatst bekende goede plan dwingt, moet de prestaties van de query bewaken die wordt uitgevoerd met behulp van het geforceerde plan en controleren of het geforceerde plan werkt zoals verwacht. Afhankelijk van de resultaten van bewaking en analyse moet het plan worden gedwongen of moet de gebruiker een andere manier vinden om de query te optimaliseren, zoals het herschrijven van de query. Handmatig geforceerde plannen mogen niet voor altijd worden afgedwongen, omdat de database-engine optimale plannen moet kunnen toepassen. De gebruiker of DBA moet het plan uiteindelijk ongedaan maken met behulp van sp_query_store_unforce_plan een procedure en de Database Engine het optimale plan laten vinden.

Tip

U kunt ook de Query Store-weergave voor query's met geforceerde plannen gebruiken om plannen op te zoeken en de forcering op te heffen.

SQL Server biedt alle benodigde weergaven en procedures die nodig zijn om de prestaties te bewaken en problemen op te lossen in Query Store.

In SQL Server 2016 (13.x) kunt u regressies in de keuze van plannen vinden met behulp van systeemweergaven van Query Store. Vanaf SQL Server 2017 (14.x) detecteert en toont de database-engine mogelijke regressies voor de keuze van plannen en de aanbevolen acties die in de sys.dm_db_tuning_recommendations (Transact-SQL) DMV moeten worden toegepast. De DMV toont informatie over het probleem, het belang van het probleem en details zoals de geïdentificeerde query, de id van het teruggedraaide plan, de id van het plan dat is gebruikt als basislijn voor vergelijking en de Transact-SQL-instructie die kan worden uitgevoerd om het probleem op te lossen.

type description datetime score details ...
FORCE_LAST_GOOD_PLAN DE CPU-tijd is gewijzigd van 4 ms in 14 ms 3/17/2017 83 queryId recommendedPlanId regressedPlanId T-SQL
FORCE_LAST_GOOD_PLAN DE CPU-tijd is gewijzigd van 37 ms in 84 ms 3/16/2017 26 queryId recommendedPlanId regressedPlanId T-SQL

Sommige kolommen uit deze weergave worden beschreven in de volgende lijst:

  • Type van de aanbevolen actie FORCE_LAST_GOOD_PLAN.
  • Beschrijving die informatie bevat waarom de database-engine denkt dat deze planwijziging een mogelijke regressie van prestaties is.
  • Datum/tijd waarop de potentiële regressie wordt gedetecteerd.
  • Score van deze aanbeveling.
  • Details over de problemen, zoals de id van het gedetecteerde plan, de id van het teruggedraaide plan, de id van het plan dat moet worden gedwongen om het probleem op te lossen, Transact-SQL script dat kan worden toegepast om het probleem op te lossen, enzovoort. Details worden opgeslagen in JSON-indeling.

Gebruik de volgende query om een script te verkrijgen waarmee het probleem wordt opgelost en aanvullende informatie over de geschatte winst:

SELECT reason, score,
      script = JSON_VALUE(details, '$.implementationDetails.script'),
      planForceDetails.*,
      estimated_gain = (regressedPlanExecutionCount + recommendedPlanExecutionCount)
                  * (regressedPlanCpuTimeAverage - recommendedPlanCpuTimeAverage)/1000000,
      error_prone = IIF(regressedPlanErrorCount > recommendedPlanErrorCount, 'YES','NO')
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON (Details, '$.planForceDetails')
    WITH (  [query_id] int '$.queryId',
            regressedPlanId int '$.regressedPlanId',
            recommendedPlanId int '$.recommendedPlanId',
            regressedPlanErrorCount int,
            recommendedPlanErrorCount int,
            regressedPlanExecutionCount int,
            regressedPlanCpuTimeAverage float,
            recommendedPlanExecutionCount int,
            recommendedPlanCpuTimeAverage float
          ) AS planForceDetails;

Hier is het resultatenoverzicht.

reason score script query_id huidige plan-ID aanbevolen plan_id geschatte_winst foutgevoelig
DE CPU-tijd is gewijzigd van 3 ms in 46 ms 36 EXEC sp_query_store_force_plan 12, 17; 12 28 17 11:59 0

De kolom estimated_gain geeft het geschatte aantal seconden aan dat wordt opgeslagen als het aanbevolen plan wordt gebruikt voor het uitvoeren van query's in plaats van het huidige plan. Het aanbevolen plan moet worden afgedwongen in plaats van het huidige plan als de prestatieverbetering groter is dan 10 seconden. Als er meer fouten zijn (bijvoorbeeld time-outs of afgebroken uitvoeringen) in het huidige plan dan in het aanbevolen plan, wordt de kolom error_prone ingesteld op de waarde YES. Een foutgevoelig plan is een andere reden waarom het aanbevolen plan moet worden gedwongen in plaats van het huidige.

Hoewel de database-engine alle informatie biedt die nodig is om regressies van plannen te identificeren, kan continue bewaking en het oplossen van prestatieproblemen een tijdrovend proces worden. Automatisch afstemmen maakt dit proces veel eenvoudiger.

Note

Gegevens in de sys.dm_db_tuning_recommendations DMV blijven niet behouden na het opnieuw opstarten van de database-engine. Gebruik de kolom sqlserver_start_time in sys.dm_os_sys_info om de laatste opstarttijd van de database-engine te vinden.

Automatisch indexbeheer

In Azure SQL Database is indexbeheer eenvoudig omdat Azure SQL Database meer te weten komt over uw workload en ervoor zorgt dat uw gegevens altijd optimaal worden geïndexeerd. Het juiste indexontwerp is essentieel voor optimale prestaties van uw werkbelasting en automatisch indexbeheer kan u helpen uw indexen te optimaliseren. Automatisch indexbeheer kan prestatieproblemen in onjuist geïndexeerde databases oplossen of indexen in het bestaande databaseschema onderhouden en verbeteren. Automatisch afstemmen in Azure SQL Database voert de volgende acties uit:

  • Identificeert indexen die de prestaties van uw Transact-SQL query's kunnen verbeteren die gegevens uit de tabellen lezen.
  • Identificeert redundante indexen of indexen die niet in langere tijd zijn gebruikt die kunnen worden verwijderd. Het verwijderen van onnodige indexen verbetert de prestaties van query's die gegevens in tabellen bijwerken.

Waarom hebt u indexbeheer nodig?

Indexen versnellen enkele van uw query's die gegevens uit de tabellen lezen, maar ze kunnen de query's vertragen die gegevens bijwerken. U moet zorgvuldig analyseren wanneer u een index maakt en welke kolommen u in de index moet opnemen. Sommige indexen zijn mogelijk na enige tijd niet nodig. Daarom moet u deze indexen periodiek identificeren en verwijderen die geen voordelen opleveren. Als u de ongebruikte indexen negeert, worden de prestaties van de query's die gegevens bijwerken verlaagd zonder enig voordeel te hebben van de query's die gegevens lezen. Ongebruikte indexen hebben ook invloed op de algehele prestaties van het systeem, omdat extra updates onnodige logboekregistratie vereisen.

Het vinden van de optimale set indexen die de prestaties verbeteren van de query's die gegevens uit uw tabellen lezen en minimale invloed hebben op updates, vereisen mogelijk continue en complexe analyses.

Azure SQL Database maakt gebruik van ingebouwde intelligentie en geavanceerde regels die uw query's analyseren, indexen identificeren die optimaal zijn voor uw huidige workloads en de indexen identificeren die mogelijk moeten worden verwijderd. Azure SQL Database zorgt ervoor dat u een minimaal benodigde set indexen hebt waarmee de query's die gegevens lezen, worden geoptimaliseerd, met een minimale impact op de andere query's.

Automatisch indexbeheer

Naast detectie kan Azure SQL Database automatisch geïdentificeerde aanbevelingen toepassen. Als u merkt dat de ingebouwde regels de prestaties van uw database verbeteren, kunt u azure SQL Database uw indexen automatisch laten beheren.

Wanneer Azure SQL Database een aanbeveling CREATE INDEX of DROP INDEX toepast, worden automatisch de prestaties gecontroleerd van de query's die worden beïnvloed door de index. Nieuwe index wordt alleen bewaard als de prestaties van de betrokken query's worden verbeterd. Verwijderde index wordt automatisch opnieuw gemaakt als er een aantal query's zijn die langzamer worden uitgevoerd vanwege het ontbreken van de index.

Overwegingen voor automatisch indexbeheer

Acties die nodig zijn om de benodigde indexen in Azure SQL Database te maken, kunnen resources verbruiken en tijdelijk invloed hebben op de prestaties van de workload. Om de impact van het maken van een index op de prestaties van de werkbelasting te minimaliseren, vindt Azure SQL Database een geschikt tijdvenster voor een indexbeheerbewerking. De afstemmingsactie wordt uitgesteld als de database resources nodig heeft om uw workload uit te voeren en opnieuw wordt opgestart wanneer de database voldoende ongebruikte resources heeft die kunnen worden gebruikt voor de onderhoudstaak. Een belangrijke functie in automatisch indexbeheer is een verificatie van de acties. Wanneer Azure SQL Database een index maakt of verwijdert, analyseert een bewakingsproces de prestaties van uw workload om te controleren of de actie de algehele prestaties heeft verbeterd. Als het geen aanzienlijke verbetering heeft gebracht- wordt de actie onmiddellijk teruggedraaid. Op deze manier zorgt Azure SQL Database ervoor dat automatische afstemmingsacties geen negatieve invloed hebben op de prestaties van uw workload. Indexen die door automatisch afstemmen worden gemaakt, zijn transparant voor de onderhoudsbewerking in het onderliggende schema. Schemawijzigingen, zoals kolommen verwijderen of hernoemen, worden niet geblokkeerd door de aanwezigheid van automatisch gemaakte indexen. Indexen die automatisch door Azure SQL Database worden gemaakt, worden onmiddellijk verwijderd wanneer de gerelateerde tabel of kolommen worden verwijderd.

Alternatief - handmatig indexbeheer

Zonder automatisch indexbeheer moet een gebruiker of DBA handmatig een query uitvoeren op de sys.dm_db_missing_index_details (Transact-SQL) of het rapport Prestatiedashboard in Management Studio gebruiken om indexen te vinden die de prestaties kunnen verbeteren, indexen kunnen maken met behulp van de details in deze weergave en handmatig de prestaties van de query kunnen bewaken. Om de indexen te vinden die moeten worden verwijderd, moeten gebruikers de operationele gebruiksstatistieken van de indexen controleren om zelden gebruikte indexen te vinden.

Azure SQL Database vereenvoudigt dit proces. Azure SQL Database analyseert uw workload, identificeert de query's die sneller kunnen worden uitgevoerd met een nieuwe index en identificeert ongebruikte of gedupliceerde indexen. Meer informatie over het identificeren van indexen die moeten worden gewijzigd bij Aanbevelingen voor index zoeken in Azure Portal.

Volgende stappen

  • Last updated on