Delen via

Verminderde queryprestaties na een upgrade van SQL Server 2012 of eerder naar versie 2014 of hoger

  • Artikel

Nadat u SQL Server hebt bijgewerkt van 2012 of een eerdere versie naar 2014 of een latere versie, kan het volgende probleem optreden: de meeste oorspronkelijke query's worden goed uitgevoerd, maar een paar van uw query's worden langzamer uitgevoerd dan in de vorige versie. Hoewel er veel mogelijke oorzaken en bijdragende factoren zijn, is een relatief veelvoorkomende oorzaak de wijzigingen in het CE-model (Cardinality Estimation ) na de upgrade. Belangrijke wijzigingen zijn geïntroduceerd in de CE-modellen die beginnen in SQL Server 2014.

Dit artikel bevat stappen en oplossingen voor het oplossen van problemen met queryprestaties die optreden bij het gebruik van de standaard-CE, maar die niet optreden wanneer u de verouderde CE gebruikt.

Notitie

Als alle query's na de upgrade langzamer worden uitgevoerd, zijn de stappen voor probleemoplossing die in dit artikel zijn geïntroduceerd, waarschijnlijk niet van toepassing op uw situatie.

Probleemoplossing: Vaststellen of CE-wijzigingen het probleem zijn en achterhalen wat de reden is

Stap 1: bepalen of de standaard-CE wordt gebruikt

  1. Kies een query die langzamer wordt uitgevoerd na de upgrade.
  2. Voer de query uit en verzamel het uitvoeringsplan.
  3. Controleer in het uitvoeringsplan venster Eigenschappen de CardinalityEstimationModelVersion. Zoek de CE-modelversie uit het uitvoeringsplan venster Eigenschappen.
  4. Een waarde van 70 geeft de verouderde CE aan en een waarde van 120 of hoger geeft het gebruik van de standaard-CE aan.

Als de verouderde CE wordt gebruikt, zijn de CE-wijzigingen niet de oorzaak van het prestatieprobleem. Als de standaard-CE wordt gebruikt, gaat u naar de volgende stap.

Stap 2: bepalen of Query Optimizer een beter plan kan genereren met behulp van de verouderde CE

Voer de query uit met de verouderde CE. Als het beter presteert dan het gebruik van de standaard CE, gaat u naar de volgende stap. Als de prestaties niet worden verbeterd, zijn de CE-wijzigingen niet de oorzaak.

Stap 3: Ontdek waarom de query beter presteert met de verouderde CE

Test de verschillende CE-gerelateerde queryhints voor uw query. Gebruik voor SQL Server 2014 de bijbehorende traceringsvlagken 4137, 9472 en 4139 om de query te testen. Bepaal welke hints of traceringsvlagmen de prestaties positief beïnvloeden op basis van deze tests.

Oplossing

Probeer een van de volgende methoden om dit probleem op te lossen:

  • Optimaliseer de query.

    Begrijpelijk, het is niet altijd mogelijk om query's te herschrijven, maar vooral wanneer er slechts een paar query's zijn die kunnen worden herschreven, moet deze benadering de eerste keuze zijn. Optimaal geschreven query's presteren beter, ongeacht CE-versies.

  • Gebruik queryhints die zijn geïdentificeerd in stap 3.

    Met deze gerichte benadering kunnen andere workloads profiteren van de standaard-CE-veronderstellingen en -verbeteringen. Daarnaast is het een krachtigere optie dan het maken van een planhandleiding. Er is geen QDS (Query Store) vereist, in tegenstelling tot het afdwingen van een plan (de meest robuuste optie).

  • Dwing een goed plan af.

    Dit is een gunstige optie en kan worden gebruikt om specifieke query's te targeten. Het afdwingen van een plan kan worden uitgevoerd met behulp van een planhandleiding of QDS. QDS is over het algemeen eenvoudiger te gebruiken.

  • Gebruik de configuratie met databasebereik om de verouderde CE af te dwingen.

    Dit is een minder voorkeursbenadering omdat het een databasebrede instelling is en van toepassing is op alle query's voor deze database. Toch is het soms noodzakelijk wanneer een gerichte aanpak niet haalbaar is. Het is zeker de eenvoudigste optie om te implementeren.

  • Gebruik traceringsvlag 9841 om verouderde CE wereldwijd af te dwingen. Hiervoor gebruikt u DBCC TRACEON of stelt u de traceringsvlag in als opstartparameter.

    Dit is de minst gerichte benadering en mag alleen worden gebruikt als tijdelijke beperking wanneer u geen van de andere opties kunt toepassen.

Opties voor het inschakelen van verouderde CE

Queryniveau: De optie QueryTRACEON of QueryTRACEON gebruiken

  • Gebruik voor SQL Server 2016 SP1 en latere versies hint FORCE_LEGACY_CARDINALITY_ESTIMATION voor uw query, bijvoorbeeld:

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

  • Schakel traceringsvlag 9481 in om een verouderd CE-plan af te dwingen. Hier volgt een voorbeeld:

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

Databaseniveau: bereikconfiguratie of compatibiliteitsniveau instellen

  • Voor SQL Server 2016 en latere versies wijzigt u de configuratie van databasebereik:

      --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';

  • Het compatibiliteitsniveau voor de database wijzigen. Dit is de enige optie op databaseniveau die beschikbaar is voor SQL Server 2014. Houd er rekening mee dat deze wijziging meer invloed heeft dan alleen de CE. Als u de impact van wijzigingen in compatibiliteitsniveaus wilt bepalen, gaat u naar HET compatibiliteitsniveau ALTER DATABASE (Transact-SQL) en bekijkt u de tabellen 'Verschillen' hierin.

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

Notitie

Deze wijziging is van invloed op alle query's die worden uitgevoerd binnen de context van de database waarvoor de configuratie wordt gewijzigd, tenzij een overschrijvende traceringsvlag of queryhint wordt gebruikt. Query's die beter presteren als gevolg van standaard-CE-regressies.

Serverniveau: traceringsvlag gebruiken

Gebruik traceringsvlag 9481 om verouderde CE voor de hele server af te dwingen:

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

Notitie

Deze wijziging is van invloed op alle query's die worden uitgevoerd binnen de context van het SQL Server-exemplaar, tenzij een overschrijvende traceringsvlag of queryhint wordt gebruikt. Query's die beter presteren als gevolg van standaard-CE-regressies.

Veelgestelde vragen

Voor bestaande databases die worden uitgevoerd op lagere compatibiliteitsniveaus, wordt de aanbevolen werkstroom voor het upgraden van de queryprocessor naar een hoger compatibiliteitsniveau beschreven in de databasecompatibiliteitsmodus wijzigen en gebruiksscenario's voor Query Store en Query Store gebruiken. De methodologie die in het artikel wordt geïntroduceerd, is van toepassing op verplaatsingen naar 130 of hoger voor SQL Server en Azure SQL Database.

V2: Ik heb geen tijd om te testen op CE-wijzigingen. Wat kan ik in dit geval doen?

Voor bestaande toepassingen en workloads raden we u niet aan om over te stappen op de standaard-CE totdat er voldoende regressietests zijn uitgevoerd. Als u nog steeds twijfelt, raden we u aan OM SQL Server nog steeds bij te werken en over te stappen op het meest recente compatibiliteitsniveau. Schakel als voorzorgsmaatregel ook traceringsvlag 9481 in voor SQL Server 2014 of configureer de configuratie van LEGACY_CARDINALITY_ESTIMATION databasebereik ON voor SQL Server 2016 en latere versies totdat u de mogelijkheid hebt om te testen.

V3: Zijn er nadelen van het permanent gebruik van de verouderde CE?

Toekomstige kardinaliteitsschatter-gerelateerde verbeteringen en oplossingen zijn gericht op recentere versies. Versie 70 is een acceptabele tussenliggende status. Na zorgvuldig testen raden we echter aan om uiteindelijk over te stappen op een recentere CE-versie om te profiteren van de meest recente CE-oplossingen. Er is een hoge kans op wijzigingen in het queryplan wanneer u overstapt van de verouderde CE. Test daarom voordat u wijzigingen aanbrengt in productiesystemen. De wijzigingen kunnen in veel gevallen de prestaties van query's verbeteren, maar in sommige gevallen kunnen de prestaties van query's afnemen.

Belangrijk

De standaard CE is het belangrijkste codepad dat toekomstige investeringen en diepere testdekking op de lange termijn ontvangt, dus plan niet voor onbepaalde tijd de verouderde CE te gebruiken.

V4: Ik heb duizenden databases en wil LEGACY_CARDINALITY_ESTIMATION niet handmatig inschakelen voor elke database. Is er een alternatieve methode?

Schakel voor SQL Server 2014 traceringsvlag 9481 in om de verouderde CE voor alle databases te gebruiken, ongeacht het compatibiliteitsniveau. Voor SQL Server 2016 en latere versies voert u de volgende query uit om door databases te doorlopen. De instelling wordt ingeschakeld, zelfs wanneer de database wordt hersteld of gekoppeld aan een andere 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;

Voor Azure SQL Database kunt u een ondersteuningsticket maken om deze traceringsvlag op abonnementsniveau in te schakelen, maar niet op serverniveau.

V5: Wordt uitgevoerd met de verouderde CE voorkomt u dat ik toegang krijg tot nieuwe functies?

Zelfs als LEGACY_CARDINALITY_ESTIMATION ingeschakeld, krijgt u nog steeds toegang tot de nieuwste functionaliteit die is opgenomen in de versie van SQL Server en het bijbehorende databasecompatibiliteitsniveau. Een database met LEGACY_CARDINALITY_ESTIMATION bijvoorbeeld ingeschakeld op databasecompatibiliteitsniveau 140 op SQL Server 2017 kan nog steeds profiteren van de functiefamilie voor adaptieve queryverwerking .

V6: Wanneer wordt de verouderde CE niet meer ondersteund?

We hebben op dit moment geen plannen om de verouderde CE te ondersteunen. Toekomstige kardinaliteitsschatter-gerelateerde verbeteringen en oplossingen zijn echter gericht op recentere versies van de CE.

V7: Ik heb slechts een paar query's die regresseren met de standaard CE, maar de meeste queryprestaties zijn hetzelfde of zelfs verbeterd. Wat moet ik doen?

Een gedetailleerder alternatief voor de traceringsvlag 9481 of de LEGACY_CARDINALITY_ESTIMATION configuratie met databasebereik is het gebruik van de hint-constructie met querybereik. Zie het argument HINT-queryhint GEBRUIKEN in SQL Server 2016 en USE HINT voor meer informatie.

Notitie

Er is ook een QUERYTRACEON optie met traceringsvlag 9481, maar u moet overwegen de USE HINT in plaats daarvan te gebruiken, omdat het schoner semantisch is en geen speciale machtigingen vereist.

USE HINT FORCE_LEGACY_CARDINALITY_ESTIMATION hiermee kunt u het CE-model voor queryoptimalisatie instellen op versie 70, ongeacht het compatibiliteitsniveau van de database. Zie queryniveau: de optie Queryhint of QUERYTRACEON gebruiken.

Als er slechts één query is die problematisch is met de standaard-CE, kunt u ook een verouderd CE-plan afdwingen dat is opgeslagen in Query Store of in combinatie met een planhandleiding gebruiken FORCE_LEGACY_CARDINALITY_ESTIMATION .

CE is een complex probleem en de algoritmen zijn afhankelijk van de minder dan perfecte gegevens die beschikbaar zijn voor schattingen, zoals statistieken voor tabellen en indexen. Er is geen informatie voor sommige out-of-model constructies zoals tabelwaardefuncties (TVF's) en modellen op basis van veel veronderstellingen (zoals correlatie of onafhankelijkheid van de predicaten en kolommen, uniforme gegevensdistributie, insluiting, enzovoort).

Gezien de onbeperkte combinaties van klantschema's, gegevens en workloads, is het bijna onmogelijk om modellen te kiezen die voor alle gevallen werken. Hoewel sommige wijzigingen in de standaard-CE fouten bevatten (zoals andere software wel) en kunnen worden opgelost, worden andere problemen veroorzaakt door een modelwijziging.

Wijzigingen in CE-versies, met name van 70 tot 120, bevatten veel verschillende keuzes voor gebruikte modellen. Bij het schatten van filters wordt bijvoorbeeld uitgegaan van een bepaald correlatieniveau tussen de predicaten, omdat in de praktijk een dergelijke correlatie vaak bestaat en ce-model 70 resultaten in dergelijke gevallen zou onderschatten. Hoewel deze wijzigingen zijn getest voor veel workloads en veel query's zijn verbeterd, was de verouderde CE voor sommige andere query's een betere overeenkomst en dus met de standaard-CE kunnen prestatieregressies worden waargenomen.

Helaas wordt het niet beschouwd als een bug. In dergelijke situaties kunt u een tijdelijke oplossing gebruiken, zoals het afstemmen van de query, net zoals u met de verouderde CE moet doen als de queryprestaties niet acceptabel zijn, of een eerder CE-model of een specifiek uitvoeringsplan afdwingen.

V9: Is er een resource voor meer informatie over de kardinaliteitswijzigingen in de standaard-CE en de impact van de queryprestaties?

Zie Uw queryplannen optimaliseren met de SQL Server 2014-kardinaliteitsschatter voor meer informatie en lees de sectie 'Wat is er gewijzigd in SQL Server 2014?'.