Försämrade frågeprestanda efter uppgradering från SQL Server 2012 eller tidigare till SQL Server 2014 eller senare

När du har uppgraderat SQL Server från 2012 eller en tidigare version till 2014 eller en senare version kan det uppstå följande problem: de flesta av de ursprungliga frågorna körs bra, men några av dina frågor körs långsammare än i den tidigare versionen. Även om det finns många möjliga orsaker och bidragande faktorer är en relativt vanlig orsak ändringarna i modellen kardinalitetsuppskattning (CE) efter uppgraderingen. Betydande ändringar har införts i CE-modellerna med början i SQL Server 2014.

Den här artikeln innehåller felsökningssteg och lösningar för problem med frågeprestanda som uppstår när standard-CE används, men som inte inträffar när du använder den äldre CE-filen.

Kommentar

Om alla frågor körs långsammare efter uppgraderingen gäller förmodligen inte de felsökningssteg som beskrivs i den här artikeln för din situation.

Felsökning: Identifiera om CE-ändringar är problemet och ta reda på orsaken

Steg 1: Identifiera om standard-CE används

1. Välj en fråga som körs långsammare efter uppgraderingen.
2. Kör frågan och samla in körningsplanen.
3. Från körningsplanen Fönstret Egenskaper kontrollerar du CardinalityEstimationModelVersion.
4. Värdet 70 anger den äldre CE-koden och värdet 120 eller högre anger användningen av standard-CE.

Om den äldre CE-filen används är CE-ändringarna inte orsaken till prestandaproblemet. Om standard-CE används går du till nästa steg.

Steg 2: Identifiera om Frågeoptimeraren kan generera en bättre plan med hjälp av den äldre CE

Kör frågan med den äldre CE-koden. Om det presterar bättre än att använda standard-CE går du till nästa steg. Om prestandan inte förbättras är INTE CE-ändringarna orsaken.

Steg 3: Ta reda på varför frågan presterar bättre med den äldre CE

Testa de olika CE-relaterade frågetipsen för din fråga. För SQL Server 2014 använder du motsvarande spårningsflaggor 4137, 9472 och 4139 för att testa frågan. Ta reda på vilka tips eller spårningsflaggor som påverkar prestandan baserat på dessa tester.

Åtgärd

Försök att lösa problemet på något av följande sätt:

• Optimera frågan.

  Det är förståeligt att det inte alltid går att skriva om frågor, men särskilt när det bara finns några få frågor som kan skrivas om bör den här metoden vara det första valet. Optimalt skrivna frågor fungerar bättre oavsett CE-versioner.

• Använd frågetips som identifieras i steg 3.

  Med den här riktade metoden kan andra arbetsbelastningar dra nytta av standardantaganden och förbättringar av CE. Dessutom är det ett mer robust alternativ än att skapa en planguide. Och det kräver inte Query Store (QDS), till skillnad från att tvinga fram en plan (det mest robusta alternativet).

• Framtvinga en bra plan.

  Det här är ett bra alternativ och kan användas för att rikta specifika frågor. Att tvinga fram en plan kan göras med hjälp av en planguide eller QDS. QDS är vanligtvis enklare att använda.

• Använd databasomfattande konfiguration för att framtvinga äldre CE.

  Det här är en mindre prioriterad metod eftersom det är en databasomfattande inställning och gäller för alla frågor mot den här databasen. Ändå är det ibland nödvändigt när en riktad metod inte är genomförbar. Det är verkligen det enklaste alternativet att implementera.

• Använd spårningsflagga 9481 för att tvinga äldre CE globalt. Det gör du genom att använda DBCC TRACEON eller ange spårningsflaggan som en startparameter.

  Det här är den minst riktade metoden och bör endast användas som en tillfällig åtgärd när du inte kan tillämpa något av de andra alternativen.

Alternativ för att aktivera äldre CE

Frågenivå: Använd alternativet Frågetips eller QUERYTRACEON

• För SQL Server 2016 SP1 och senare versioner använder du tips FORCE_LEGACY_CARDINALITY_ESTIMATION för din fråga, till exempel:

  SELECT * FROM Table1
  WHERE Col1 = 10
  OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));
  

• Aktivera spårningsflagga 9481 för att tvinga fram en äldre CE-plan. Här är ett exempel:

  SELECT * FROM Table1
  WHERE Col1 = 10
  OPTION (QUERYTRACEON 9481)
  

Databasnivå: Ange omfångsbegränsad konfiguration eller kompatibilitetsnivå

• Ändra databasomfattningskonfigurationen för SQL Server 2016 och senare versioner:

    --Force a specific database to use legacy CE
    ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;
  
    -- Validate what databases use legacy CE
    SELECT name, value
        FROM sys.database_scoped_configurations 
    WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';
  

• Ändra kompatibilitetsnivån för databasen. Det är det enda databasnivåalternativet som är tillgängligt för SQL Server 2014. Observera att den här ändringen påverkar mer än bara CE. Om du vill fastställa effekten av ändringar på kompatibilitetsnivå går du till ALTER DATABASE-kompatibilitetsnivån (Transact-SQL) och undersöker tabellerna "Skillnader" i den.

  ALTER DATABASE <YourDatabase>
  SET COMPATIBILITY_LEVEL = 110  -- set it to SQL Server 2012 level
  

Kommentar

Den här ändringen påverkar alla frågor som körs i kontexten för databasen som konfigurationen ändras för, såvida inte en övergripande spårningsflagga eller frågetips används. Frågor som presterar bättre på grund av standard-CE kan regressera.

Servernivå: Använd spårningsflagga

Använd spårningsflagga 9481 för att framtvinga serveromfattande äldre CE:

--Turn on 
DBCC TRACEON(9481, -1)
--Validate
DBCC TRACESTATUS

Kommentar

Den här ändringen påverkar alla frågor som körs i kontexten för SQL Server-instansen om inte en åsidosättande spårningsflagga eller frågetips används. Frågor som presterar bättre på grund av standard-CE kan regressera.

Vanliga frågor och svar

F1: Jag är intresserad av att uppgradera till en nyare version av SQL Server, och jag är bekymrad över kardinalitetsestimatorprestandaregressioner. Vilken uppgraderingsplanering rekommenderas för att minimera problem?

För befintliga databaser som körs på lägre kompatibilitetsnivåer beskrivs det rekommenderade arbetsflödet för att uppgradera frågeprocessorn till en högre kompatibilitetsnivå i Ändra databaskompatibilitetsläge och Använd användningsscenarier för frågearkiv och frågearkiv. Den metod som introduceras i artikeln gäller för flytt till 130 eller högre för SQL Server och Azure SQL Database.

F2: Jag har inte tid att testa för CE-ändringar. Vad kan jag göra i det här fallet?

För befintliga program och arbetsbelastningar rekommenderar vi inte att du flyttar till standard-CE förrän tillräcklig regressionstestning har utförts. Om du fortfarande är osäker rekommenderar vi att du fortfarande uppgraderar SQL Server och går över till den senaste tillgängliga kompatibilitetsnivån. Som en försiktighetsåtgärd aktiverar du även spårningsflagga 9481 för SQL Server 2014 eller konfigurerar den LEGACY_CARDINALITY_ESTIMATION databasomfattande konfigurationen ON för SQL Server 2016 och senare versioner tills du har möjlighet att testa.

F3: Finns det några nackdelar med att använda den äldre CE permanent?

Framtida förbättringar och korrigeringar av kardinalitetsestimatorer är centrerade kring nyare versioner. Version 70 är ett acceptabelt mellanliggande tillstånd. Men efter noggrann testning rekommenderar vi att du så småningom går över till en nyare CE-version för att dra nytta av de senaste CE-korrigeringarna. Det finns en hög sannolikhet för ändringar i frågeplanen när du flyttar från den äldre CE-koden, så testa innan du gör ändringar i produktionssystemen. Ändringarna kan förbättra frågeprestanda i många fall, men i vissa fall kan frågeprestanda försämras.

Viktigt!

Standard-CE är den huvudsakliga kodsökvägen som kommer att få framtida investeringar och djupare testtäckning på lång sikt, så planera inte att använda den äldre CE på obestämd tid.

F4: Jag har tusentals databaser och vill inte aktivera LEGACY_CARDINALITY_ESTIMATION manuellt för var och en. Finns det en alternativ metod?

För SQL Server 2014 aktiverar du spårningsflagga 9481 för att använda äldre CE för alla databaser oavsett kompatibilitetsnivå. För SQL Server 2016 och senare versioner kör du följande fråga för att iterera via databaser. Inställningen aktiveras även när databasen återställs eller kopplas till en annan server.

SELECT [name], 0 AS [isdone]
INTO #tmpDatabases
FROM master.sys.databases WITH (NOLOCK)
WHERE database_id > 4 AND source_database_id IS NULL AND is_read_only = 0

DECLARE @dbname sysname, @sqlcmd NVARCHAR(500);

WHILE (SELECT COUNT([name]) FROM #tmpDatabases WHERE isdone = 0) > 0
BEGIN
    SELECT TOP 1 @dbname = [name] FROM #tmpDatabases WHERE isdone = 0

    SET @sqlcmd = 'USE ' + QUOTENAME(@dbname) + '; 
        IF (SELECT [value] FROM sys.database_scoped_configurations WHERE [name] = ''LEGACY_CARDINALITY_ESTIMATION'') = 0
        ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;'
 
    BEGIN TRY
        EXECUTE sp_executesql @sqlcmd
    END TRY
    BEGIN CATCH
        SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState, ERROR_MESSAGE() AS ErrorMessage;
    END CATCH

    UPDATE #tmpDatabases
    SET isdone = 1
    WHERE [name] = @dbname
END;

För Azure SQL Database kan du skapa ett supportärende för att aktivera den här spårningsflaggan på prenumerationsnivå, men inte på servernivå.

Q5: Kommer körning med äldre CE hindra mig från att få åtkomst till nya funktioner?

Även med LEGACY_CARDINALITY_ESTIMATION aktiverat får du fortfarande åtkomst till de senaste funktionerna som ingår i versionen av SQL Server och den associerade databaskompatibilitetsnivån. Till exempel kan en databas med LEGACY_CARDINALITY_ESTIMATION aktiverad som körs på databaskompatibilitetsnivå 140 på SQL Server 2017 fortfarande dra nytta av funktionsfamiljen för anpassningsbar frågebearbetning .

F6: När kommer den äldre CE:en att gå ur supporten?

Vi har inga planer på att sluta stödja den äldre CE:en just nu. Framtida förbättringar och korrigeringar av kardinalitetsestimatorer är dock centrerade kring nyare versioner av CE.

F7: Jag har bara ett fåtal frågor som regresserar med standard-CE, men de flesta frågeprestanda är desamma eller till och med förbättrade. Vad ska jag göra?

Ett mer detaljerat alternativ till den serveromfattande spårningsflaggan 9481 eller den LEGACY_CARDINALITY_ESTIMATION databasomfångskonfigurationen är användningen av den frågeomfattande USE HINT-konstruktionen. Mer information finns i USE HINT query hint argument in SQL Server 2016 and USE HINT (Använd tips för tips i SQL Server 2016 och USE HINT).

Kommentar

Det finns också ett QUERYTRACEON alternativ med spårningsflagga 9481, men du bör överväga att använda i USE HINT stället, eftersom det är renare semantiskt och inte kräver särskilda behörigheter.

USE HINT FORCE_LEGACY_CARDINALITY_ESTIMATION gör att du kan ange CE-modellen för frågeoptimeraren till version 70, oavsett databasens kompatibilitetsnivå. Se Frågenivå: Använd alternativet Frågetips eller QUERYTRACEON.

Om det bara finns en fråga som är problematisk med standard-CE kan du tvinga fram en äldre CE-plan som lagras i Query Store eller användas FORCE_LEGACY_CARDINALITY_ESTIMATION tillsammans med en planguide.

F8: Kommer problemet att åtgärdas i produkten om frågeprestandan har regresserats på grund av en planändring som är relaterad till betydande över- eller underberäkningar när standard-CE används?

CE är ett komplext problem och algoritmerna förlitar sig på mindre än perfekta data som är tillgängliga för uppskattningar, till exempel statistik för tabeller och index. Det finns ingen information för vissa färdiga konstruktioner som tabellvärdesfunktioner (TVF:er) och modeller baserade på många antaganden (till exempel korrelation eller oberoende för predikat och kolumner, enhetlig datadistribution, inneslutning och så vidare).

Med tanke på de obegränsade kombinationerna av kundscheman, data och arbetsbelastningar är det nästan omöjligt att välja modeller som fungerar för alla fall. Vissa ändringar i standard-CE kan innehålla buggar (som andra program kan) och kan åtgärdas, men andra problem orsakas av en modelländring.

Ändringar i CE-versioner, särskilt från 70 till 120, omfattar många olika alternativ för modeller som används. När du till exempel beräknar filter antar du en viss korrelationsnivå mellan predikaten eftersom det i praktiken ofta finns en sådan korrelation, och CE-modell 70 skulle underskatta resultaten i sådana fall. Även om dessa ändringar testades för många arbetsbelastningar och förbättrade många frågor, var den äldre CE-koden en bättre matchning för vissa andra frågor, och därmed med standard-CE kan prestandaregressioner observeras.

Tyvärr anses det inte vara en bugg. I sådana situationer kan du använda en lösning som att justera frågan, precis som du behövde göra med den äldre CE-filen om frågeprestanda inte är acceptabelt eller tvingar fram en tidigare CE-modell eller en specifik körningsplan.

F9: Finns det någon resurs som kan lära sig mer om kardinalitetsändringarna i standard-CE och frågeprestandapåverkan?

Mer information finns i Optimera dina frågeplaner med SQL Server 2014 Cardinality Estimator och läs avsnittet "Vad har ändrats i SQL Server 2014?".