Databases upgraden met behulp van de queryafstemmingsassistent

van toepassing op: SQL Server 2016 (13.x) en latere versies Azure SQL Database Azure Synapse Analytics Analytics Platform System (PDW)

Wanneer u migreert van een oudere versie van SQL Server naar SQL Server 2014 (12.x) of hoger, en het compatibiliteitsniveau van de database bijwerken naar de nieuwste beschikbare versie, kan een workload worden blootgesteld aan het risico van prestatieregressie. Dit is ook mogelijk in mindere mate wanneer u een upgrade uitvoert tussen SQL Server 2014 (12.x) en een nieuwere versie.

Vanaf SQL Server 2014 (12.x) en bij elke nieuwe versie worden alle wijzigingen van de query-optimizer gekoppeld aan het nieuwste compatibiliteitsniveau van de database. Dit betekent dat uitvoeringsplannen niet direct op het moment van de upgrade veranderen, maar pas wanneer een gebruiker de COMPATIBILITY_LEVEL-databaseoptie wijzigt naar het nieuwste beschikbare compatibiliteitsniveau. Zie Kardinaliteitsschattervoor meer informatie over wijzigingen in queryoptimalisatie die zijn geïntroduceerd in SQL Server 2014 (12.x). Zie Compatibiliteitsniveaus en Database Engine-upgradesvoor meer informatie over compatibiliteitsniveaus en hoe deze van invloed kunnen zijn op upgrades.

Deze beperkingsmogelijkheid die wordt geboden door het compatibiliteitsniveau van de database, in combinatie met Query Store, biedt u een groot niveau van controle over de queryprestaties in het upgradeproces als de upgrade de aanbevolen werkstroom volgt die hieronder wordt weergegeven. Zie De databasecompatibiliteitsmodus wijzigen en de Query Store-gebruiken voor meer informatie over de aanbevolen werkstroom voor het upgraden van het compatibiliteitsniveau.

Deze controle over upgrades is verder verbeterd met SQL Server 2017 (14.x), waarbij automatische afstemming werd geïntroduceerd en de laatste stap in de bovenstaande aanbevolen werkstroom kan worden geautomatiseerd.

Vanaf SQL Server Management Studio v18 leidt de nieuwe QTA-functie (Query Tuning Assistant) gebruikers door de aanbevolen werkstroom om de stabiliteit van de prestaties te behouden tijdens upgrades naar nieuwere SQL Server-versies, zoals beschreven in de sectie Prestatiestabiliteit behouden tijdens de upgrade naar nieuwere SQL Server- van Query Store-gebruiksscenario's. QTA draait echter niet terug naar een eerder bekend goed plan, zoals te zien is in de laatste stap van de aanbevolen werkstroom. In plaats daarvan zal QTA alle regressies volgen die zijn gevonden in de Query Store weergave van Geregresseerde Query's, en door mogelijke permutaties van toepasselijke optimizer-modelvariaties itereren, zodat er een nieuw en beter plan kan worden geproduceerd.

Belangrijk

QTA genereert geen gebruikersworkload. Als u QTA uitvoert in een omgeving die niet door uw toepassingen wordt gebruikt, moet u ervoor zorgen dat u op een andere wijze een representatieve testworkload kunt uitvoeren op de beoogde SQL Server Database Engine.

De werkstroom Query Tuning Assistant

Het uitgangspunt van QTA is dat een database vanuit een eerdere versie van SQL Server wordt verplaatst (via CREATE DATABASE ... VOOR BIJKOPPELEN of RESTORE) naar een nieuwere versie van de SQL Server Database Engine, en het compatibiliteitsniveau van de database vóór de upgrade niet direct wordt veranderd. QTA begeleidt u bij de volgende stappen:

1. Configureer Query Store volgens de aanbevolen instellingen voor de duur van de werkbelasting (in dagen) die door de gebruiker zijn ingesteld. Denk na over de duur van de workload die overeenkomt met uw gebruikelijke bedrijfscyclus.
2. Verzoek om de vereiste workload te starten, zodat Query Store een basislijn van gegevens over de workload kan verzamelen (indien deze nog niet beschikbaar is).
3. Voer een upgrade uit naar het compatibiliteitsniveau van de doeldatabase dat door de gebruiker is gekozen.
4. Vraag of er een tweede pass van workloadgegevens wordt verzameld voor vergelijkings- en regressiedetectie.
5. Itereer door eventuele regressies die zijn gevonden op basis van de Query Store geregresseerde queries weergave; experimenteer door het verzamelen van runtimestatistieken over mogelijke permutaties van relevante optimizer-modelvariaties en meet het resultaat.
6. Rapporteer over de gemeten verbeteringen en laat deze wijzigingen eventueel behouden met behulp van planhandleidingen.

Zie Database koppelen en loskoppelenvoor meer informatie over het koppelen van een database.

Zie hieronder hoe QTA alleen de laatste stappen van de aanbevolen werkstroom wijzigt voor het upgraden van het compatibiliteitsniveau met behulp van Query Store die hierboven wordt weergegeven. In plaats van de optie te hebben om te kiezen tussen het momenteel inefficiënte uitvoeringsplan en het laatst bekende goede uitvoeringsplan, biedt QTA afstemmingsopties die specifiek zijn voor de geselecteerde teruggedraaide query's, om een nieuwe verbeterde status te creëren met afgestemde uitvoeringsplannen.

QTA Interne zoekruimte afstemmen

QTA is alleen gericht op SELECT query's die kunnen worden uitgevoerd vanuit Query Store. Geparameteriseerde query's komen in aanmerking als de gecompileerde parameter bekend is. Query's die afhankelijk zijn van runtimeconstructies, zoals tijdelijke tabellen of tabelvariabelen, komen op dit moment niet in aanmerking.

QTA is gericht op bekende mogelijke patronen van queryregressies vanwege wijzigingen in Kardinaliteitsschatter (CE) versies. Als u bijvoorbeeld een database bijwerkt van SQL Server 2012 (11.x) en databasecompatibiliteitsniveau 110 naar SQL Server 2017 (14.x) en databasecompatibiliteitsniveau 140, kunnen sommige query's terugkeren omdat ze specifiek zijn ontworpen om te werken met de CE-versie die bestond in SQL Server 2012 (11.x) (CE 70). Dit betekent niet dat het terugdraaien van CE 140 naar CE 70 de enige optie is. Als alleen een specifieke wijziging in de nieuwere versie de regressie introduceert, is het mogelijk om die query te hinten om alleen het relevante deel van de vorige CE-versie te gebruiken dat beter werkte voor de specifieke query, terwijl nog steeds alle andere verbeteringen van nieuwere CE-versies worden gebruikt. Bovendien, sta ook andere query's in de workload toe die niet zijn teruggevallen, om te profiteren van nieuwere CE-verbeteringen.

De CE-patronen die door QTA worden doorzocht, zijn het volgende:

• Onafhankelijkheid versus correlatie-: als onafhankelijkheid betere schattingen biedt voor de specifieke query, zorgt de queryhint USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES') ervoor dat SQL Server een uitvoeringsplan genereert door minimale selectiviteit te gebruiken bij het schatten van AND predicaten voor filters om rekening te houden met correlatie. Zie HINT-queryhints gebruiken en versies van de CE-voor meer informatie.
• Simple Containment vs. Base Containment: Als een andere join-insluiting betere schattingen biedt voor de specifieke query, zorgt de queryhint USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS') ervoor dat SQL Server een uitvoeringsplan genereert met behulp van de aanname Simple Containment in plaats van de standaardaanname van base containment. Zie voor meer informatie USE HINT-queryhints en Versies van de CE.
• Multi-statement tabelwaarde functie (MSTVF) vaste kardinaliteit schatting van 100 rijen versus 1 rij: Als de standaard vaste schatting voor TVF's van 100 rijen niet resulteert in een efficiënter plan dan het gebruik van de vaste schatting voor TVF's van 1 rij (overeenkomend met de standaardwaarde onder het CE-model voor queryoptimalisatie van SQL Server 2008 R2 (10.50.x) en eerdere versies), dan wordt de query-aanwijzing QUERYTRACEON 9488 gebruikt om een uitvoeringsplan te genereren. Zie Door de gebruiker gedefinieerde functies (Database Engine) makenvoor meer informatie over MSTVF's.

Notitie

Als laatste redmiddel, als de nauw gedefinieerde hints niet genoeg goede resultaten opleveren voor de in aanmerking komende querypatronen, wordt ook het volledig gebruik van CE 70 overwogen door de queryhint USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION') te gebruiken om een uitvoeringsplan te genereren.

Belangrijk

Elke hint dwingt bepaalde gedragingen af die in toekomstige SQL Server-updates kunnen worden aangepakt. We raden u aan om alleen hints toe te passen wanneer er geen andere optie bestaat en plan om hinted code opnieuw te bekijken bij elke nieuwe upgrade. Door gedrag af te dwingen, kunt u voorkomen dat uw workload profiteert van verbeteringen die zijn geïntroduceerd in nieuwere versies van SQL Server.

Activeer Query Tuning Assistant voor database-upgrades

QTA is een sessiefunctie waarmee de sessiestatus wordt opgeslagen in het msqta schema van de gebruikersdatabase waarin een sessie voor het eerst wordt gemaakt. Er kunnen in de loop van de tijd meerdere afstemmingssessies worden gemaakt op één database, maar er kan slechts één actieve sessie bestaan voor elke bepaalde database.

Een upgradesessie voor een database maken

1. Open de Objectverkenner in SQL Server Management Studio en maak verbinding met Database Engine.

2. Voor de database die is bedoeld om het compatibiliteitsniveau van de database bij te werken, klikt u met de rechtermuisknop op de naam van de database, selecteert u Taken, selecteert u Database-upgradeen selecteert u Nieuwe Database-upgradesessie.

3. In het venster van de QTA-wizard zijn twee stappen vereist om een sessie te configureren:

   1. Configureer Query Store in het venster Setup om het equivalent van één volledige bedrijfsperiode van workloadgegevens vast te leggen voor analyse en afstemming.

      • Voer de verwachte duur van de werkbelasting in dagen in (minimaal 1 dag). Dit wordt gebruikt om aanbevolen Query Store-instellingen voor te stellen om voorlopig toe te staan dat de volledige basislijn wordt verzameld. Het vastleggen van een goede basislijn is belangrijk om ervoor te zorgen dat teruggedraaide query's die worden gevonden nadat het compatibiliteitsniveau van de database is gewijzigd, kunnen worden geanalyseerd.
      • Stel het beoogde compatibiliteitsniveau voor de doeldatabase in waarop de gebruikersdatabase zich moet bevinden nadat de QTA-werkstroom is voltooid. Als u klaar bent, selecteert u Volgende.

      

   2. In het venster Instellingen worden in twee kolommen de Huidige status van de Query Store in de doeldatabase weergegeven, evenals de Aanbevolen instellingen.

      • De aanbevolen instellingen zijn standaard geselecteerd, maar als u het keuzerondje boven de huidige kolom selecteert, worden de huidige instellingen geaccepteerd en kunt u ook de huidige Query Store-configuratie verfijnen.
      • De voorgestelde drempelwaarde voor verlopen query's instelling is twee keer het aantal verwachte workloadduur in dagen. Dit komt doordat Query Store informatie moet bevatten over de basisworkload en de workload na de upgrade van de database. Als u klaar bent, selecteert u Volgende.

      

      Belangrijk

      De voorgestelde maximale grootte is een willekeurige waarde die geschikt kan zijn voor een korte workload. Houd er echter rekening mee dat het mogelijk onvoldoende kan zijn om informatie over de basislijn- en workloads na de database-upgrade te bewaren voor zeer intensieve workloads, vooral wanneer veel verschillende plannen kunnen worden gegenereerd. Als u verwacht dat dit het geval is, voert u een hogere waarde in die geschikt is.

4. Het venster Afstemmen sluit de sessieconfiguratie en geeft instructies over de volgende stappen om de sessie te openen en door te gaan. Als u klaar bent, selecteert u voltooien.

   

De werkstroom voor het upgraden van de database uitvoeren

1. Voor de database die is bedoeld om het databasecompatibiliteitsniveau te upgraden, klikt u met de rechtermuisknop op de databasenaam, selecteert u Taken, selecteert u Database-upgradeen selecteert u Sessies bewaken.

2. De pagina sessiebeheer bevat huidige en eerdere sessies voor de database binnen het bereik. Selecteer de gewenste sessie en selecteer Details.

   Notitie

   Als de huidige sessie niet aanwezig is, selecteert u de knop Vernieuwen.

   De lijst bevat de volgende informatie:

   • Sessie-ID
   • sessienaam: door het systeem gegenereerde naam die bestaat uit de databasenaam, de datum en het tijdstip waarop de sessie is gemaakt.
   • Status: Status van de sessie (actief of gesloten).
   • Beschrijving: door het systeem gegenereerd, samengesteld uit het door de gebruiker geselecteerde compatibiliteitsniveau van de doeldatabase en het aantal dagen voor de werkbelasting van de bedrijfscyclus.
   • Tijd gestart: Datum en tijd waarop de sessie is gemaakt.

   

   Notitie

   Sessie verwijderen verwijdert alle gegevens die zijn opgeslagen voor de geselecteerde sessie. Het verwijderen van een gesloten sessie echter geen eerder geïmplementeerde planhandleidingen verwijderen. Wanneer u een sessie verwijdert die planhandleidingen heeft geïmplementeerd, kunt u QTA niet gebruiken om terugdraaien. Zoek in plaats daarvan naar planhandleidingen met behulp van de sys.plan_guides systeemtabel en verwijder handmatig met behulp van sp_control_plan_guide.

3. Het toegangspunt voor een nieuwe sessie is stap gegevensverzameling.

   Notitie

   De knop Sessies keert terug naar de pagina sessiebeheer, waardoor de actieve sessie as-isverlaten wordt.

   Deze stap heeft drie substappen:

   1. Baseline gegevensverzameling vraagt de gebruiker om de representatieve workloadcyclus uit te voeren, zodat Query Store een basislijn kan vastleggen. Zodra deze workload is voltooid, controleert u de Gereed met de werkbelasting en selecteert u Volgende.

      Notitie

      Het QTA-venster kan worden gesloten terwijl de workload wordt uitgevoerd. Terugkeren naar de sessie die op een later tijdstip actief blijft, wordt hervat vanuit dezelfde stap waar deze was gebleven.

      

   2. Database upgraden wordt gevraagd om toestemming om het databasecompatibiliteitsniveau te upgraden naar het gewenste doel. Als u naar de volgende substap wilt gaan, selecteert u Ja.

      

      Op de volgende pagina wordt bevestigd dat het compatibiliteitsniveau van de database is bijgewerkt.

      

   3. waargenomen gegevensverzameling vraagt de gebruiker om de representatieve workloadcyclus opnieuw uit te voeren, zodat Query Store een vergelijkende basislijn kan verzamelen die wordt gebruikt om te zoeken naar optimalisatiekansen. Wanneer de workload wordt uitgevoerd, gebruikt u de knop Vernieuwen om de lijst met teruggedraaide query's bij te werken, indien aanwezig. Wijzig de Query's om waarde weer te geven om het aantal weergegeven query's te beperken. De volgorde van de lijst wordt beïnvloed door de Metric (Duration of CpuTime) en de Aggregation (Gemiddelde is standaard). Selecteer ook hoeveel query's omweer te geven. Zodra de workload is voltooid, controleert u de Gereed met workload en selecteert u Volgende.

      

      De lijst bevat de volgende informatie:

      • Vraag-ID
      • zoekopdrachttekst: Transact-SQL verklaring die kan worden uitgevouwen door de knop ... te selecteren.
      • Wordtuitgevoerd: geeft het aantal uitvoeringen van die query weer voor de hele workloadverzameling.
      • nl-NL: Basislijnmetriek: de geselecteerde metriek (Duur of CpuTime) in ms voor het verzamelen van basislijngegevens vóór de upgrade van de databasecompatibiliteit.
      • waargenomen metriek: de geselecteerde metriek (Duur of CpuTime) in ms voor de gegevensverzameling na de databasecompatibiliteitsupgrade.
      • % Wijzigen: Percentagewijziging voor de geselecteerde metrische waarde tussen de status van vóór en na de upgradestatus van de databasecompatibiliteit. Een negatief getal vertegenwoordigt de hoeveelheid gemeten regressie voor de query.
      • kan niet worden: True of False, afhankelijk van of de query in aanmerking komt voor experimenten.

4. Analyse bekijken stelt u in staat om te selecteren welke query's u wilt gebruiken om te experimenteren en optimalisatiemogelijkheden te ontdekken. De query's om de waarde weer te geven, vormen het bereik van in aanmerking komende query's om mee te experimenteren. Zodra de gewenste query's zijn gecontroleerd, selecteert u Volgende om te experimenteren.

   Notitie

   Query's met Tunable = False kunnen niet worden geselecteerd voor experimenten.

   Belangrijk

   Een prompt adviseert dat zodra QTA de experimentenfase bereikt, het niet mogelijk is om terug te keren naar de Analysepagina.
   Als u niet alle in aanmerking komende query's selecteert voordat u naar de experimentatiefase gaat, moet u op een later tijdstip een nieuwe sessie maken en de werkstroom herhalen. Hiervoor moet het compatibiliteitsniveau van de database opnieuw worden ingesteld op de vorige waarde.

   

5. Resultaten weergeven kunt u selecteren welke query's de voorgestelde optimalisatie kunnen implementeren als planhandleiding.

   De lijst bevat de volgende informatie:

   • query-id
   • Query Text: Transact-SQL verklaring die uitgebreid kan worden door op de knop ... te klikken.
   • status: geeft de huidige experimentenstatus voor de query weer.
   • Basislijnmetriek: de geselecteerde metrische waarde (Duur of CpuTime) in ms voor de query, zoals uitgevoerd in Stap 2 Deelstap 3, die de teruggestelde query vertegenwoordigt na de upgrade van de databasecompatibiliteit.
   • waargenomen metriek: de geselecteerde metriek (Duur of CpuTime) in ms voor de query na experimenten, voor een voldoende voorgestelde optimalisatie.
   • % Wijzigen: Percentagewijziging voor de geselecteerde metrische waarde tussen de status vóór en na experimenten, die de hoeveelheid gemeten verbetering voor de query aangeeft met de voorgestelde optimalisatie.
   • queryoptie: maak een koppeling naar de voorgestelde hint waarmee de metrische gegevens over de uitvoering van query's worden verbeterd.
   • Kanimplementeren: True of False, afhankelijk van of de voorgestelde queryoptimalisatie kan worden geïmplementeerd als planhandleiding.

   

6. verificatie toont de implementatiestatus van eerder geselecteerde query's voor deze sessie. De lijst op deze pagina verschilt van de vorige pagina door de kolom Can Deploy te wijzigen in Can Rollback. Deze kolom kan True of False zijn, afhankelijk van de vraag of de geïmplementeerde queryoptimalisatie kan worden teruggedraaid en of de bijbehorende plangids kan worden verwijderd.

   

   Als er op een later tijdstip een voorgestelde optimalisatie moet worden teruggedraaid, selecteert u de relevante query en kiest u Terugdraaien. Deze handleiding voor het queryplan wordt verwijderd en de lijst is bijgewerkt om de teruggedraaide query te verwijderen. In de onderstaande afbeelding ziet u dat query 8 is verwijderd.

   

   Notitie

   Als u een gesloten sessie verwijdert, u geen eerder geïmplementeerde planhandleidingen verwijderen. Als u een sessie verwijdert die plangidsen had geïmplementeerd, kunt u QTA niet gebruiken om dit ongedaan te maken. Zoek in plaats daarvan naar planhandleidingen met behulp van de sys.plan_guides systeemtabel en verwijder handmatig met behulp van sp_control_plan_guide.

Machtigingen

Vereist lidmaatschap van db_owner rol.

Zie ook

• compatibiliteitsniveaus en upgrades van database-engines
• hulpprogramma's voor prestatiemonitoring en -afstemming
• Prestaties bewaken met behulp van de Query Store
• de databasecompatibiliteitsmodus wijzigen en de Query Store- gebruiken
• traceringsvlagmen
• HINT-queryhints gebruiken
• kardinaliteitsschatter
• Automatisch afstemmen
• de SQL Server Query Tuning Assistant gebruiken