Delen via

Databases upgraden met behulp van de queryafstemmingsassistent

Van toepassing op: SQL Server 2016 (13.x) en latere versies

Bij het migreren van een oudere versie van SQL Server naar SQL Server 2014 (12.x) of latere versies en het upgraden van het databasecompatibiliteitsniveau naar het meest recente beschikbare, kan een workload worden blootgesteld aan het risico van regressie van prestaties. Dit is ook mogelijk in mindere mate wanneer u een upgrade uitvoert tussen SQL Server 2014 (12.x) en een nieuwere versie.

In SQL Server 2014 (12.x) en latere versies worden alle wijzigingen van de queryoptimalisatie beperkt tot het meest recente compatibiliteitsniveau van de database, zodat uitvoeringsplannen niet direct bij het upgradetijdstip worden gewijzigd, maar wanneer een gebruiker de COMPATIBILITY_LEVEL optie wijzigt naar het meest recente beschikbare compatibiliteitsniveau. Zie Kardinaliteitsschatting (SQL Server) voor 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 in het volgende diagram wordt weergegeven. Zie Het compatibiliteitsniveau van de database wijzigen en Query Store gebruiken voor meer informatie over de aanbevolen werkstroom voor het upgraden van het compatibiliteitsniveau.

Diagram van de werkstroom voor aanbevolen database-upgrades met Query Store.

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

Vanaf SQL Server Management Studio v18 begeleidt de functie Query Tuning Assistant (QTA) 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-gebruiksscenario's voor Query Store. 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 houdt QTA eventuele regressies bij die zijn gevonden in de Query Store Weergave voor Geregresseerde Query's, en doorloopt mogelijke permutaties van toepasselijke optimizer-modelvariaties zodat er een nieuw, 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.

Het proces van de Query Tuning-assistent

Bij het beginpunt van QTA wordt ervan uitgegaan dat een database van een eerdere versie van SQL Server wordt verplaatst (via Een database of RESTORE-instructies koppelen) aan een nieuwere versie van de SQL Server Database Engine en dat het compatibiliteitsniveau van de database vóór de upgrade niet onmiddellijk wordt gewijzigd. QTA begeleidt 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.

In het volgende diagram ziet u hoe QTA alleen de laatste stappen van de aanbevolen werkstroom wijzigt voor het upgraden van het compatibiliteitsniveau met behulp van Query Store. In plaats van 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.

Diagram van de aanbevolen werkstroom voor een database-upgrade met QTA.

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 richt zich op bekende mogelijke patronen van queryregressies als gevolg van wijzigingen in SQL Server-versies voor Cardinality Estimation. Wanneer 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 kunnen ook andere query's in de workload die niet zijn geregresseerd, profiteren van nieuwere CE-verbeteringen.

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

  • Onafhankelijkheid versus correlatie: Als onafhankelijkheidsveronderstellingen betere schattingen voor de specifieke query bieden, zorgt de queryhint USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES') ervoor dat SQL Server een uitvoeringsplan genereert met behulp van minimale selectiviteit bij het schatten van AND predicaten voor filters om rekening te houden met correlatie. Zie USE HINT query-hints en versies van de CE voor meer informatie.

  • Eenvoudige insluiting versus basis insluiting: 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 basisbesluiting. Zie USE HINT query-hints en versies van de CE voor meer informatie.

  • 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.

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 kunnen worden aangepakt in toekomstige SQL Server-updates. 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 in SQL Server Management Studio de Objectverkenner 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.

      Schermopname van het installatievenster van de nieuwe database-upgradesessie.

    2. In het venster Instellingen worden in twee kolommen de huidige status van Query Store weergegeven in de doeldatabase en 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 instelling voor de drempelwaarde voor verlopen query's is twee keer de verwachte waarde voor de duur van de werkbelasting, in dagen. Dit komt doordat Query Store informatie moet bevatten over de workload basislijn en de workload na de upgrade van de database.

      Als u klaar bent, selecteert u Volgende.

      Schermopname van het venster Upgrade-instellingen voor nieuwe database.

      Belangrijk

      De voorgestelde maximale grootte is een willekeurige waarde die geschikt kan zijn voor een korte workload. Het kan echter onvoldoende zijn om informatie over de workloads van de basislijn en de upgrade na de database te bewaren voor intensieve workloads, namelijk wanneer er 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.

    Schermopname van het venster Voor het afstemmen van nieuwe database-upgrades.

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-upgrade, en selecteert u Sessies bewaken.

  2. De pagina sessiebeheer lijst huidige en eerdere sessies op voor de database in de reikwijdte van. 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.

    Schermopname van de pagina QTA-sessiebeheer.

    Notitie

    Sessie verwijderen verwijdert alle gegevens die zijn opgeslagen voor de geselecteerde sessie. Het verwijderen van een gesloten sessie verwijdert echter niet eerder geïmplementeerde planhandleidingen. Als u een sessie verwijdert die planhandleidingen heeft geïmplementeerd, kunt u QTA niet gebruiken om terug te draaien. 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 workload cyclus uit te voeren, zodat Query Store een basislijn kan verzamelen. Zodra de workload is afgerond, controleert u de optie Gereed met het uitvoeren van de taak 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.

      Schermopname van QTA Stap 2 Substep 1.

    2. Upgrade database vraagt om toestemming voor het upgraden van het databasecompatibiliteitsniveau naar het gewenste doel. Als u naar de volgende substap wilt gaan, selecteert u Ja.

      Schermopname van QTA Stap 2 Substep 2: compatibiliteitsniveau van de database upgraden.

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

      Schermopname van QTA Stap 2 Substap 2.

    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 weergegeven moeten worden. Zodra de uitvoering van de werkbelasting is voltooid, controleert u Voltooid met de uitvoering van de werkbelasting en selecteert u Volgende.

      Schermopname van QTA Stap 2 Substap 3.

      De lijst bevat de volgende informatie:

      • Vraag-ID

      • zoekopdrachttekst: Transact-SQL verklaring die kan worden uitgevouwen door de knop ... te selecteren.

      • Uitvoeringen: Geeft het aantal uitvoeringen van die query weer voor de gehele 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.

      • Tuneerbaar: Waar of Onwaar, afhankelijk van of de query in aanmerking komt voor experimentatie.

  4. Analyse bekijken stelt u in staat om te selecteren welke query's u wilt gebruiken om te experimenteren en optimalisatiemogelijkheden te ontdekken. De te tonen query's 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.

    Queries waarvoor Tunable is ingesteld op False kunnen niet worden geselecteerd voor experimenten.

    Belangrijk

    Een prompt adviseert dat zodra QTA naar de experimentatiefase gaat, terugkeren naar de pagina Analyse weergeven niet mogelijk is. 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.

    Schermopname van QTA Stap 3.

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

    De lijst bevat de volgende informatie:

    • Vraag-ID

    • zoekopdrachttekst: Transact-SQL verklaring die kan worden uitgevouwen door de knop ... te selecteren.

    • 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: hiermee geeft u de procentwijziging voor de geselecteerde metrische waarde op 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.

    • Kan worden ingezet: True of False, afhankelijk van of de voorgestelde queryoptimalisatie als plangids kan worden ingezet.

    Schermopname van QTA Stap 4.

  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.

    Schermopname van QTA Stap 5.

    Als op een latere datum een voorgestelde optimalisatie moet worden teruggedraaid, selecteert u de relevante query en selecteert 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.

    Schermopname van QTA Stap 5 - Terugdraaien.

    Notitie

    Het verwijderen van een gesloten sessie verwijdert geen eerder geïmplementeerde planhandleidingen. Als u een sessie verwijdert die planhandleidingen heeft geïmplementeerd, kunt u QTA niet gebruiken om terug te draaien. 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.

Verwante inhoud