Uppgradera databaser med hjälp av Frågejusteringsassistenten

gäller för: SQL Server 2016 (13.x) och senare versioner Azure SQL Database Azure Synapse Analytics Analytics Platform System (PDW)

När du migrerar från en äldre version av SQL Server till SQL Server 2014 (12.x) eller senare, och uppgradering av databaskompatibilitetsnivån till den senaste tillgängliga, kan en arbetsbelastning exponeras för risken för prestandaregression. Detta är också möjligt i mindre utsträckning när du uppgraderar mellan SQL Server 2014 (12.x) och alla nyare versioner.

Från och med SQL Server 2014 (12.x), och med varje ny version, är alla ändringar av frågeoptimeraren kopplade till den senaste databaskompatibilitetsnivån, så körningsplaner ändras inte direkt vid uppgraderingen, utan i stället när en användare ändrar COMPATIBILITY_LEVEL databasalternativet till den senaste tillgängliga. Mer information om ändringar i frågeoptimeraren som introducerades i SQL Server 2014 (12.x) finns i Cardinality Estimator. Mer information om kompatibilitetsnivåer och hur de kan påverka uppgraderingar finns i Kompatibilitetsnivåer och uppgraderingar av databasmotorn.

Den här gating-funktionen som tillhandahålls av databasens kompatibilitetsnivå i kombination med Query Store ger dig en bra kontroll över frågeprestandan i uppgraderingsprocessen om uppgraderingen följer det rekommenderade arbetsflödet som visas nedan. Mer information om det rekommenderade arbetsflödet för att uppgradera kompatibilitetsnivån finns i Ändra databaskompatibilitetsläget och Använd Query Store-.

Den här kontrollen över uppgraderingar förbättrades ytterligare med SQL Server 2017 (14.x) där automatisk justering introducerades och gör det möjligt att automatisera det sista steget i det rekommenderade arbetsflödet ovan.

Från och med SQL Server Management Studio v18 vägleder den nya funktionen Query Tuning Assistant (QTA) användare genom det rekommenderade arbetsflödet för att hålla prestandastabilitet under uppgraderingar till nyare SQL Server-versioner, enligt beskrivningen i avsnittet Behåll prestandastabilitet under uppgraderingen till nyare SQL Server- av Query Store-användningsscenarier. QTA återställs dock inte till en tidigare känd bra plan enligt det sista steget i det rekommenderade arbetsflödet. I stället spårar QTA eventuella regressioner som finns i Query Store regresserade frågor vy och itererar genom möjliga permutationer av tillämpliga optimerarmodellvariationer så att en ny bättre plan kan skapas.

Viktig

QTA genererar inte användararbetsbelastning. Om du kör QTA i en miljö som inte används av dina program, ska du försäkra dig om att du fortfarande kan köra en representativ testarbetsbelastning på den SQL Server-databasmotor som är målet, på annat sätt.

Arbetsflödet för Sökfrågeoptimeringsassistenten

Startpunkten för QTA förutsätter att en databas från en tidigare version av SQL Server flyttas (via CREATE DATABASE ... FÖR ATTACH eller RESTORE) till en nyare version av SQL Server Database Engine, och databaskompatibilitetsnivån före uppgraderingen ändras inte omedelbart. QTA vägleder genom följande steg:

1. Konfigurera Query Store enligt rekommenderade inställningar för arbetsbelastningens varaktighet (i dagar) som angetts av användaren. Tänk på varaktigheten för arbetsbelastningen som matchar din vanliga konjunkturcykel.
2. Begär att starta den nödvändiga arbetsbelastningen så att Query Store kan samla in en baslinje för arbetsbelastningsdata (om ingen är tillgänglig ännu).
3. Uppgradera till måldatabasens kompatibilitetsnivå som användaren har valt.
4. Begär att ett andra pass av arbetsbelastningsdata samlas in för jämförelse och regressionsidentifiering.
5. Iterera genom eventuella regressioner som hittas baserat på Query Store regresserade frågor vy, experimentera genom att samla in körningsstatistik om möjliga permutationer av tillämpliga optimerarmodellvariationer och mäta resultatet.
6. Rapportera om de uppmätta förbättringarna och tillåt eventuellt att ändringarna sparas med hjälp av planguiderna .

Mer information om hur du kopplar en databas finns i Database Detach och Attach.

Se nedan hur QTA endast ändrar de sista stegen i det rekommenderade arbetsflödet för att uppgradera kompatibilitetsnivån med hjälp av Query Store som visas ovan. I stället för att ha möjlighet att välja mellan den nuvarande ineffektiva körningsplanen och den senast kända fungerande körningsplanen, presenterar QTA justeringsalternativ som är specifikt anpassade för de valda regresserade frågorna, för att skapa ett förbättrat tillstånd med optimerade körningsplaner.

QTA Tuning internt sökområde

QTA inriktar sig på endast SELECT frågor som kan köras via Query Store. Parametriserade frågor är berättigade om den kompilerade parametern är känd. Frågeställningar som beror på körningstidkonstruktioner som temporära tabeller eller tabellvariabler är inte berättigade för närvarande.

QTA riktar in sig på kända möjliga mönster för frågeregressioner på grund av ändringar i Cardinality Estimator (CE) versioner. När du till exempel uppgraderar en databas från SQL Server 2012 (11.x) och databaskompatibilitetsnivå 110 till SQL Server 2017 (14.x) och databaskompatibilitetsnivå 140 kan vissa frågor komma tillbaka eftersom de har utformats specifikt för att fungera med CE-versionen som fanns i SQL Server 2012 (11.x) (CE 70). Det betyder inte att återställning från CE 140 till CE 70 är det enda alternativet. Om endast en specifik ändring i den nyare versionen introducerar regressionen är det möjligt att antyda att frågan bara använder den relevanta delen av den tidigare CE-versionen som fungerade bättre för den specifika frågan, samtidigt som alla andra förbättringar av nyare CE-versioner används. Och tillåt även andra frågeställningar i arbetet som inte har regresserat att dra nytta av nyare CE-förbättringar.

Ce-mönstren som söks av QTA är följande:

• Oberoende vs. Korrelation: Om antagandet om oberoende ger bättre uppskattningar för den specifika frågan, orsakar frågetipset USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES') att SQL Server genererar en körningsplan genom att använda minsta selektivitet när den uppskattar AND-predikat för filtren för att ta hänsyn till korrelation. Mer information finns i USE HINT query hints och versioner av CE.
• Simple Containment vs. Base Containment: Om en annan kopplings inneslutning ger bättre uppskattningar för den specifika frågan, gör frågetipset USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS') att SQL Server genererar en körningsplan med hjälp av antagandet Enkel inneslutning i stället för standardantagandet för bas inneslutning. För mer information, se användningsrekommendationer för frågor och versioner av CE.
• Fleruttalande tabellvärdefunktion (MSTVF) fast kardinalitetsgissning på 100 rader vs. 1 rad: Om den standardmässiga fasta uppskattningen för TVF av 100 rader inte resulterar i en mer effektiv plan än att använda den fasta uppskattningen för TVF av 1 rad (motsvarande standarden under frågeoptimeringsmodellen CE i SQL Server 2008 R2 (10.50.x) och tidigare versioner), sedan används frågetipset QUERYTRACEON 9488 för att generera en körningsplan. Mer information om MSTVFs finns i Create User-defined Functions (Database Engine).

Not

Om de snävare omfattade tipsen inte ger tillräckligt bra resultat för de berättigade frågorna, betraktas även fullständig användning av CE 70 som en sista utväg genom att använda frågetipset USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION') för att skapa en körplan.

Viktig

Alla tips tvingar fram vissa beteenden som kan åtgärdas i framtida SQL Server-uppdateringar. Vi rekommenderar att du bara använder tips när det inte finns något annat alternativ och planerar att gå tillbaka till tipsad kod vid varje ny uppgradering. Genom att tvinga fram beteenden kan du hindra din arbetsbelastning från att dra nytta av förbättringar som introduceras i nyare versioner av SQL Server.

Starta Query Tuning Assistant för databasuppgraderingar

QTA är en sessionsbaserad funktion som lagrar sessionstillstånd i msqta schemat för användardatabasen där en session skapas för första gången. Flera justeringssessioner kan skapas på en enskild databas över tid, men endast en aktiv session kan finnas för en viss databas.

Skapa en databasuppgraderingssession

1. I SQL Server Management Studio öppnar du Object Explorer och ansluter till databasmotorn.

2. För databasen som är avsedd att uppgradera databasens kompatibilitetsnivå högerklickar du på databasnamnet, väljer Uppgifter, väljer Databasuppgraderingoch väljer Ny databasuppgraderingssession.

3. I fönstret QTA-guiden krävs två steg för att konfigurera en session:

   1. I fönstret Installation konfigurerar du Query Store för att samla in motsvarande en fullständig affärscykel med arbetsbelastningsdata som ska analyseras och finjusteras.

      • Ange den förväntade arbetsbelastningens varaktighet i dagar (minst 1 dag). Detta används för att föreslå rekommenderade Query Store-inställningar för att preliminärt tillåta att hela baslinjen samlas in. Det är viktigt att samla in en bra baslinje för att säkerställa att eventuella regresserade frågor som hittas efter att databasens kompatibilitetsnivå har ändrats kan analyseras.
      • Ange den avsedda måldatabaskompatibilitetsnivån som användardatabasen ska vara på när QTA-arbetsflödet har slutförts. När du är klar väljer du Nästa.

      

   2. I fönstret Inställningar visas två kolumner som visar Aktuellt tillstånd för Query Store i måldatabasen, samt de Rekommenderade inställningarna.

      • De rekommenderade inställningarna är markerade som standard, men om du väljer alternativknappen i kolumnen Aktuell godkänns aktuella inställningar och den aktuella Query Store-konfigurationen kan finjusteras.
      • Den föreslagna inställningen Stale Query Threshold är dubbelt så många dagar som den förväntade arbetsbelastningens varaktighet. Det beror på att Query Store måste lagra information om baslinjearbetsbelastningen och arbetsbelastningen efter databasuppgradering. När du är klar väljer du Nästa.

      

      Viktig

      Den föreslagna Maximal storlek är ett godtyckligt värde som kan passa för en kort tids arbetsbelastning. Tänk dock på att det kan vara otillräckligt att lagra information om arbetsbelastningar för baslinje- och uppgradering efter databas för mycket intensiva arbetsbelastningar, nämligen när många olika planer kan genereras. Om du förväntar dig att detta kommer att vara fallet anger du ett högre värde som är lämpligt.

4. Fönstret Tuning avslutar sessionskonfigurationen och instruerar nästa steg att öppna och fortsätta med sessionen. När du är klar väljer du Slutför.

   

Köra arbetsflödet för databasuppgradering

1. För databasen som är avsedd att uppgradera databasens kompatibilitetsnivå högerklickar du på databasnamnet, väljer Uppgifter, väljer Databasuppgraderingoch väljer Övervaka sessioner.

2. Sidan för sessionhantering visar aktuella och tidigare sessioner för databasen inom det aktuella området. Välj önskad session och välj Information.

   Notera

   Om den aktuella sessionen inte finns väljer du knappen Uppdatera.

   Listan innehåller följande information:

   • session-ID
   • Sessionsnamn: Systemgenererat namn som består av databasnamnet, datum och tidpunkt då sessionen skapades.
   • Status: Status för sessionen (aktiv eller stängd).
   • Beskrivning: Systemgenererad och består av den användarvalda måldatabasens kompatibilitetsnivå och antalet dagar för affärscykelns arbetsbelastning.
   • Time Started: Datum och tid då sessionen skapades.

   

   Notera

   Ta bort session tar bort alla data som lagras för den valda sessionen. Men att ta bort en stängd session inte ta bort några tidigare distribuerade planguider. Om du tar bort en session som har distribuerat planguider, kan du inte använda QTA för att återställa ändringarna. Sök i stället efter planguider med hjälp av sys.plan_guides-systemtabellen och ta bort manuellt med hjälp av sp_control_plan_guide.

3. Startpunkten för en ny session är datainsamlingssteget.

   Not

   Knappen Sessioner återgår till sidan sessionshantering och lämnar den aktiva sessionen as-is.

   Det här steget har tre understeg:

   1. Baseline Data Collection begär att användaren kör den representativa arbetsbelastningscykeln så att Query Store kan samla in en baslinje. När arbetsbelastningen har slutförts kontrollerar du Klar med arbetsbelastningen och väljer Nästa.

      Anteckning

      QTA-fönstret kan stängas medan arbetsbelastningen körs. Om du återgår till sessionen som förblir aktiv vid ett senare tillfälle, återupptas sessionen från samma steg där den slutade.

      

   2. Upgrade Database frågar efter behörighet att uppgradera databasens kompatibilitetsnivå till önskat mål. Om du vill gå vidare till nästa understeg väljer du Ja.

      

      Följande sida bekräftar att databasens kompatibilitetsnivå har uppgraderats.

      

   3. Observerad datainsamling begär att användaren kör den representativa arbetsbelastningscykeln igen, så att Query Store kan samla in en baslinje för jämförelse som ska användas för att söka efter optimeringsmöjligheter. När arbetsbelastningen körs använder du knappen Uppdatera för att fortsätta uppdatera listan över regresserade frågor, om några hittades. Ändra Frågor för att visa värde för att begränsa antalet frågor som visas. Listans ordning påverkas av Mått (Varaktighet eller CpuTime) och Aggregatfunktion (Medelvärde är standard). Välj också hur många frågor som ska visas. När arbetsbelastningen har slutförts kontrollerar du Klar med arbetsbelastningskörningen och väljer Nästa.

      

      Listan innehåller följande information:

      • fråge-ID
      • Frågetext: Transact-SQL-instruktion som kan expanderas genom att välja knappen ....
      • Runs: Visar antalet körningar av frågan för hela arbetsbelastningssamlingen.
      • Baseline Metric: Det valda måttet (Varaktighet eller CpuTime) i ms för baslinjedatainsamlingen före uppgraderingen av databasens kompatibilitet.
      • Observed Metric: Den valda metriken (Varaktighet eller CPU-tid) i ms för datainsamlingen efter uppgraderingen av databasens kompatibilitet.
      • % Ändra: Procentändring för det valda måttet mellan tillståndet före och efter uppgraderingen av databasens kompatibilitet. Ett negativt tal representerar mängden uppmätt regression för frågan.
      • Tunable: True eller False beroende på om frågan är berättigad till experimentering.

4. View Analysis tillåter val av vilka frågor som ska experimenteras och hitta optimeringsmöjligheter. De frågeuttrycken som visar-värdet utgör omfånget för berättigade frågor att experimentera med. När önskade frågor har markerats väljer du Nästa för att starta experimenteringen.

   Anmärkning

   Frågor med Tunable = False kan inte väljas för experimentering.

   Viktig

   Ett meddelande meddelar att när QTA flyttas till experimenteringsfasen, kan man inte återgå till Visa analys-sidan.
   Om du inte väljer alla berättigade frågor innan du går över till experimenteringsfasen måste du skapa en ny session vid ett senare tillfälle och upprepa arbetsflödet. Detta kräver återställning av databaskompatibilitetsnivån till det tidigare värdet.

   

5. Visa resultat tillåter val av vilka frågor som ska distribuera den föreslagna optimeringen som en planguide.

   Listan innehåller följande information:

   • sökfråge-ID
   • Frågetext: Transact-SQL-instruktion som kan expanderas genom att välja knappen ....
   • Status: Visar aktuellt experimenteringstillstånd för frågan.
   • Baseline Metric: Det valda mätvärdet (varaktighet eller CpuTime) i ms för fråge som körs i steg 2 understeg 3, representerar den regresserade frågan efter uppgraderingen av databasens kompatibilitet.
   • Observerat mått: Det valda måttet (Varaktighet eller CpuTime) i ms för frågan efter experimentering, för en tillräckligt bra föreslagen optimering.
   • % Ändra: Procentändring för det valda måttet mellan tillståndet före och efter experimentet, vilket representerar mängden uppmätt förbättring för frågan med den föreslagna optimeringen.
   • Frågealternativ: Länk till den föreslagna indikationen som förbättrar frågekörningens prestandamått.
   • kan distribuera: True eller False beroende på om den föreslagna frågeoptimeringen kan distribueras som en planguide.

   

6. Verifiering visar distributionsstatus för tidigare valda frågor för den här sessionen. Listan på den här sidan skiljer sig från föregående sida genom att ändra kolumnen Kan distribuera till Kan återställa. Den här kolumnen kan vara True eller False beroende på om den använda frågeoptimeringen kan återställas och dess planeringsguide tas bort.

   

   Om det vid ett senare tillfälle finns ett behov av att återställa en föreslagen optimering väljer du relevant fråga och väljer Återställning. Den här frågeplansguiden tas bort och listan uppdateras för att ta bort den återställda frågan. Observera i bilden nedan att fråga 8 har tagits bort.

   

   Not

   Om du tar bort en stängd session inte ta bort några tidigare distribuerade planguider. Om du tar bort en session som har distribuerat planguider kan du inte använda QTA för att återställa till ett tidigare tillstånd. Sök i stället efter planguider med hjälp av sys.plan_guides-systemtabellen och ta bort manuellt med hjälp av sp_control_plan_guide.

Behörigheter

Kräver medlemskap i rollen db_owner.

Se även

• Kompatibilitetsnivåer och uppgraderingar av databasmotorn
• verktyg för prestandaövervakning och justering
• Övervakning av prestanda med hjälp av Query Store
• ändra kompatibilitetsläget för databasen och använda Query Store-
• Spårningsflaggor
• ANVÄND söktips för frågor
• Kardinalitetsestimator
• Automatisk justering
• Använd SQL Server Query Tuning Assistant