Delen via

Problemen oplossen met trage query's die worden beïnvloed door time-out van queryoptimalisatie

  • Artikel

Van toepassing op: SQL Server

In dit artikel wordt de time-out van Optimizer beschreven, hoe dit de prestaties van query's kan beïnvloeden en hoe u de prestaties kunt optimaliseren.

Wat is Time-out voor Optimizer?

SQL Server maakt gebruik van een QO (Query Optimizer) op basis van kosten. Zie De architectuurhandleiding voor queryverwerking voor meer informatie over QO. Een op kosten gebaseerde Query Optimizer selecteert een plan voor het uitvoeren van query's met de laagste kosten nadat er meerdere queryplannen zijn gemaakt en geëvalueerd. Een van de doelstellingen van SQL Server Query Optimizer is om een redelijke tijd te besteden aan queryoptimalisatie in vergelijking met het uitvoeren van query's. Het optimaliseren van een query moet veel sneller gaan dan het uitvoeren ervan. Om dit doel te bereiken, heeft QO een ingebouwde drempelwaarde voor taken die moeten worden overwogen voordat het optimalisatieproces wordt gestopt. Wanneer de drempelwaarde is bereikt voordat QO alle mogelijke plannen heeft overwogen, bereikt deze de time-outlimiet voor optimizer. Een time-out-gebeurtenis voor Optimizer wordt in het queryplan gerapporteerd als TimeOut onder Reden voor vroegtijdige beëindiging van instructieoptimalisatie. Het is belangrijk om te weten dat deze drempelwaarde niet is gebaseerd op de kloktijd, maar op het aantal mogelijkheden dat door de optimizer wordt overwogen. In de huidige SQL Server QO-versies worden meer dan een half miljoen taken overwogen voordat een time-out wordt bereikt.

De time-out van optimizer is ontworpen in SQL Server en in veel gevallen is dit geen factor die de queryprestaties beïnvloedt. In sommige gevallen kan de keuze van het SQL-queryplan echter negatief worden beïnvloed door de time-out van Optimizer, waardoor de queryprestaties trager kunnen zijn. Wanneer u dergelijke problemen ondervindt, kunt u het time-outmechanisme van Optimizer begrijpen en hoe complexe query's kunnen worden beïnvloed, u helpen bij het oplossen van problemen en het verbeteren van de querysnelheid.

Het resultaat van het bereiken van de drempelwaarde voor time-out voor optimizer is dat SQL Server niet alle mogelijkheden voor optimalisatie heeft overwogen. Dat wil gezegd hebben dat er plannen zijn gemist die kortere uitvoeringstijden kunnen opleveren. QO stopt bij de drempelwaarde en overweegt op dat moment het minst kostende queryplan, ook al zijn er mogelijk betere, onontgonnen opties. Houd er rekening mee dat het plan dat is geselecteerd nadat een optimizer-time-out is bereikt, een redelijke uitvoeringsduur voor de query kan opleveren. In sommige gevallen kan het geselecteerde plan echter resulteren in een queryuitvoering die suboptimaal is.

Hoe detecteert u een optimizer-time-out?

Hier volgen symptomen die wijzen op een time-out van Optimizer:

  • Complexe query

    U hebt een complexe query waarbij veel gekoppelde tabellen zijn betrokken (er zijn bijvoorbeeld acht of meer tabellen gekoppeld).

  • Trage query

    De query kan langzaam of langzamer worden uitgevoerd dan op een andere SQL Server versie of systeem.

  • Queryplan toont StatementOptmEarlyAbortReason=Timeout

    • Het queryplan wordt weergegeven StatementOptmEarlyAbortReason="TimeOut" in het XML-queryplan.

      <?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.518" Build="13.0.5201.2" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
  <Batch>
   <Statements>
    <StmtSimple  ..." StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut" ......>
    ...
   <Statements>
  <Batch>
<BatchSequence>

    • Controleer de eigenschappen van de meest linkse abonnementsoperator in Microsoft SQL Server Management Studio. U kunt zien dat de waarde van Reason For Early Termination of Statement Optimizationtime-out is.

      Schermopname van de time-out van het optimalisatieprogramma in het queryplan in SSMS.

Wat is de oorzaak van een time-out voor Optimizer?

Er is geen eenvoudige manier om te bepalen welke voorwaarden ervoor zorgen dat de optimalisatiedrempel wordt bereikt of overschreden. De volgende secties zijn enkele factoren die van invloed zijn op het aantal plannen dat door QO wordt verkend bij het zoeken naar het beste plan.

  • In welke volgorde moeten tabellen worden samengevoegd?

    Hier volgt een voorbeeld van de uitvoeringsopties voor joins met drie tabellen (Table1, Table2, Table3):

    • Samenvoegen Table1 met Table2 en het resultaat met Table3
    • Samenvoegen Table1 met Table3 en het resultaat met Table2
    • Samenvoegen Table2 met Table3 en het resultaat met Table1

    Opmerking: Hoe groter het aantal tabellen is, hoe groter de mogelijkheden zijn.

  • Welke heap- of binaire boomstructuur (HoBT) moet worden gebruikt om de rijen uit een tabel op te halen?

    • Geclusterde index
    • Niet-geclusterde index1
    • Niet-geclusterde index2
    • Tabel heap

  • Welke methode voor fysieke toegang moet worden gebruikt?

    • Indexzoeken
    • Indexscan
    • Tabelscan

  • Welke fysieke joinoperator moet u gebruiken?

    • Koppeling met geneste lussen (NJ)
    • Hash join (HJ)
    • Samenvoegen (MJ)
    • Adaptive join (vanaf SQL Server 2017 (14.x))

    Zie Joins voor meer informatie.

  • Delen van de query parallel of serieel uitvoeren?

    Zie Parallelle queryverwerking voor meer informatie.

Hoewel de volgende factoren het aantal in overweging genomen toegangsmethoden en dus de in overweging genomen mogelijkheden zullen verminderen:

  • Querypredicaten (filters in de WHERE component)
  • Bestaan van beperkingen
  • Combinaties van goed ontworpen en up-to-date statistieken

Opmerking: Het feit dat QO de drempelwaarde bereikt, betekent niet dat er een tragere query wordt uitgevoerd. In de meeste gevallen presteert de query goed, maar in sommige gevallen ziet u mogelijk een tragere uitvoering van de query.

Voorbeeld van hoe rekening wordt gehouden met de factoren

Ter illustratie nemen we een voorbeeld van een join tussen drie tabellen (t1, t2en ) en t3elke tabel heeft een geclusterde index en een niet-geclusterde index.

Bekijk eerst de fysieke jointypen. Het gaat hier om twee joins. En omdat er drie mogelijkheden voor fysieke join zijn (NJ, HJ en MJ), kan de query op 32 = 9 manieren worden uitgevoerd.

  1. NJ - NJ
  2. NJ - HJ
  3. NJ - MJ
  4. HJ - NJ
  5. HJ - HJ
  6. HJ - MJ
  7. MJ - NJ
  8. MJ - HJ
  9. MJ - MJ

Overweeg vervolgens de joinvolgorde, die wordt berekend met behulp van permutaties: P (n, r). De volgorde van de eerste twee tabellen maakt niet uit, dus er kunnen P(3,1) = 3 mogelijkheden zijn:

  • Koppelen t1 met t2 en vervolgens met t3
  • Koppelen t1 met t3 en vervolgens met t2
  • Koppelen t2 met t3 en vervolgens met t1

Bekijk vervolgens de geclusterde en niet-geclusterde indexen die kunnen worden gebruikt voor het ophalen van gegevens. Bovendien hebben we voor elke index twee toegangsmethoden: zoeken of scannen. Dat betekent dat er voor elke tabel 22 = 4 keuzes zijn. We hebben drie tabellen, dus er kunnen 43 = 64 keuzes zijn.

Ten slotte, gezien al deze voorwaarden, kunnen er 9*3*64 = 1728 mogelijke plannen zijn.

Nu gaan we ervan uit dat er n tabellen zijn gekoppeld aan de query en dat elke tabel een geclusterde index en een niet-geclusterde index heeft. Houd rekening met de volgende factoren:

  • Joinorders: P(n,n-2) = n!/2
  • Jointypen: 3n-1
  • Verschillende indextypen met zoek- en scanmethoden: 4n

Vermenigvuldig al deze hierboven en we kunnen het aantal mogelijke plannen ophalen: 2*n!*12n-1. Wanneer n = 4, is het getal 82.944. Wanneer n = 6, is het getal 358.318.080. Met de toename van het aantal tabellen dat bij een query is betrokken, neemt het aantal mogelijke plannen dus geometrisch toe. Als u bovendien de mogelijkheid van parallellisme en andere factoren opneemt, kunt u zich voorstellen hoeveel mogelijke plannen worden overwogen. Daarom is de kans groter dat een query met veel joins de time-outdrempel voor optimizer bereikt dan een query met minder joins.

Houd er rekening mee dat de bovenstaande berekeningen het slechtste scenario illustreren. Zoals we hebben aangegeven, zijn er factoren die het aantal mogelijkheden verminderen, zoals filterpredicaten, statistieken en beperkingen. Een filterpredicaat en bijgewerkte statistieken verminderen bijvoorbeeld het aantal methoden voor fysieke toegang, omdat het efficiënter kan zijn om een indexzoekfunctie te gebruiken dan een scan. Dit leidt ook tot een kleinere selectie joins, enzovoort.

Waarom zie ik een optimizer-time-out met een eenvoudige query?

Niets met Query Optimizer is eenvoudig. Er zijn veel mogelijke scenario's en de mate van complexiteit is zo hoog dat het moeilijk is om alle mogelijkheden te begrijpen. Query Optimizer kan de time-outdrempel dynamisch instellen op basis van de kosten van het plan dat in een bepaalde fase is gevonden. Als er bijvoorbeeld een plan wordt gevonden dat relatief efficiënt lijkt, kan de taaklimiet voor het zoeken naar een beter plan worden verlaagd. Daarom kan een onderschatte kardinaliteitschatting (CE) een scenario zijn voor het vroegtijdig behalen van een Optimizer-time-out. In dit geval ligt de focus van het onderzoek op CE. Het is een zeldzamer geval in vergelijking met het scenario over het uitvoeren van een complexe query die in de vorige sectie is besproken, maar het is mogelijk.

Oplossingen

Een time-out voor Optimizer die wordt weergegeven in een queryplan, betekent niet noodzakelijkerwijs dat dit de oorzaak is van de slechte queryprestaties. In de meeste gevallen hoeft u niets te doen aan deze situatie. Het queryplan waarmee SQL Server eindigt, kan redelijk zijn en de query die u uitvoert, presteert mogelijk goed. U weet misschien nooit dat er een time-out voor Optimizer is opgetreden.

Voer de volgende stappen uit als u wilt afstemmen en optimaliseren.

Stap 1: een basislijn instellen

Controleer of u dezelfde query met dezelfde gegevensset kunt uitvoeren op een andere build van SQL Server, met behulp van een andere CE-configuratie of op een ander systeem (hardwarespecificaties). Een leidend principe bij het afstemmen van prestaties is 'er is geen prestatieprobleem zonder een basislijn'. Daarom is het belangrijk om een basislijn voor dezelfde query op te stellen.

Stap 2: Zoek naar 'verborgen' voorwaarden die leiden tot de time-out van Optimizer

Bekijk uw query in detail om de complexiteit ervan te bepalen. Bij het eerste onderzoek is het mogelijk niet duidelijk dat de query complex is en veel joins omvat. Een veelvoorkomend scenario hier is dat het gaat om weergaven of tabelfuncties. Op het eerste gezicht kan de query bijvoorbeeld eenvoudig lijken omdat deze twee weergaven met elkaar koppelt. Maar wanneer u de query's in de weergaven bekijkt, ziet u mogelijk dat elke weergave zeven tabellen bevat. Als gevolg hiervan, wanneer de twee weergaven worden samengevoegd, eindigt u met een join van 14 tabellen. Als uw query gebruikmaakt van de volgende objecten, zoomt u in op elk object om te zien hoe de onderliggende query's erin eruitzien:

Voor al deze scenario's is de meest voorkomende oplossing om de query te herschrijven en op te splitsen in meerdere query's. Zie Stap 7: de query verfijnen voor meer informatie.

Subquery's of afgeleide tabellen

De volgende query is een voorbeeld waarin twee afzonderlijke sets query's (afgeleide tabellen) worden samengevoegd met 4-5 joins in elk. Na het parseren door SQL Server, wordt het echter gecompileerd in één query met acht tabellen gekoppeld.

SELECT ...
  FROM 
    ( SELECT ...
        FROM t1 
        JOIN t2 ON ...
        JOIN t3 ON ...
        JOIN t4 ON ...
        WHERE ...
    ) AS derived_table1
INNER JOIN
  ( SELECT ...
      FROM t5 
      JOIN t6 ON ...
      JOIN t7 ON ...
      JOIN t8 ON ...
      WHERE ...
  ) AS derived_table2 
ON derived_table1.Co1 = derived_table2.Co10 
AND derived_table1.Co2 = derived_table2.Co20

Algemene tabelexpressies (CTE's)

Het gebruik van meerdere algemene tabelexpressies (CTE's) is geen geschikte oplossing om een query te vereenvoudigen en time-outs voor Optimizer te voorkomen. Met meerdere CTE's wordt de complexiteit van de query alleen maar verhoogd. Daarom is het contraproductief om CTE's te gebruiken bij het oplossen van time-outs voor optimizer. CTE's lijken een query logisch te verbreken, maar ze worden gecombineerd tot één query en geoptimaliseerd als één grote samenvoeging van tabellen.

Hier volgt een voorbeeld van een CTE die wordt gecompileerd als één query met veel joins. Het lijkt erop dat de query op de my_cte een eenvoudige join met twee objecten is, maar in feite zijn er zeven andere tabellen gekoppeld in de CTE.

WITH my_cte AS (
  SELECT ...
    FROM t1 
    JOIN t2 ON ...
    JOIN t3 ON ...
    JOIN t4 ON ...
    JOIN t5 ON ...
    JOIN t6 ON ...
    JOIN t7 ON ...
    WHERE ... )

SELECT ...
  FROM my_cte 
  JOIN t8 ON ...

Weergaven

Zorg ervoor dat u de weergavedefinities hebt gecontroleerd en alle betrokken tabellen hebt weergegeven. Net als bij CTE's en afgeleide tabellen kunnen joins verborgen zijn in weergaven. Een join tussen twee weergaven kan bijvoorbeeld uiteindelijk één query zijn met acht tabellen:

CREATE VIEW V1 AS 
  SELECT ...
    FROM t1 
    JOIN t2 ON ...
    JOIN t3 ON ...
    JOIN t4 ON ...
    WHERE ...
GO

CREATE VIEW V2 AS 
  SELECT ...
    FROM t5 
    JOIN t6 ON ...
    JOIN t7 ON ...
    JOIN t8 ON ...
    WHERE ...
GO

SELECT ...
  FROM V1 
  JOIN V2 ON ...

Functies met tabelwaarde (TVF's)

Sommige joins zijn mogelijk verborgen in TFV's. In het volgende voorbeeld ziet u wat er wordt weergegeven als een join tussen twee TFV's, en een tabel kan een join met negen tabellen zijn.

CREATE FUNCTION tvf1() RETURNS TABLE
AS RETURN
  SELECT ...
    FROM t1 
    JOIN t2 ON ...
    JOIN t3 ON ...
    JOIN t4 ON ...
    WHERE ...
GO 

CREATE FUNCTION tvf2() RETURNS TABLE
AS RETURN
  SELECT ...
    FROM t5
    JOIN t6 ON ...
    JOIN t7 ON ...
    JOIN t8 ON ...
    WHERE ...
GO

SELECT ...
  FROM tvf1() 
  JOIN tvf2() ON ...
  JOIN t9 ON ...

Unie

Union-operators combineren de resultaten van meerdere query's in één resultatenset. Ze combineren ook meerdere query's tot één query. Dan krijgt u mogelijk één, complexe query. In het volgende voorbeeld krijgt u één queryplan met 12 tabellen.

SELECT ...
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...

UNION ALL

SELECT ...
  FROM t5 
  JOIN t6 ON ...
  JOIN t7 ON ...
  JOIN t8 ON ...

UNION ALL

SELECT ...
  FROM t9
  JOIN t10 ON ...
  JOIN t11 ON ...
  JOIN t12 ON ...

Stap 3: Als u een basislijnquery hebt die sneller wordt uitgevoerd, gebruikt u het bijbehorende queryplan

Als u vaststelt dat een bepaald basislijnplan dat u uit stap 1 krijgt, beter is voor uw query door te testen, gebruikt u een van de volgende opties om QO af te dwingen dat plan te selecteren:

Stap 4: opties voor abonnementen verminderen

Om de kans op een time-out van Optimizer te verminderen, vermindert u de mogelijkheden waarmee QO rekening moet houden bij het kiezen van een plan. Dit proces omvat het testen van de query met verschillende hintopties. Zoals bij de meeste beslissingen met QO, zijn de keuzes niet altijd deterministisch op het eerste gezicht, omdat er een grote verscheidenheid aan factoren is waarmee rekening moet worden gehouden. Daarom is er geen enkele gegarandeerde succesvolle strategie en kan het geselecteerde plan de prestaties van de geselecteerde query verbeteren of verlagen.

Een JOIN-bestelling afdwingen

Gebruik OPTION (FORCE ORDER) om de order-permutaties te elimineren:

SELECT ...
  FROM t1
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
OPTION (FORCE ORDER)

De JOIN-mogelijkheden verminderen

Als andere alternatieven niet hebben geholpen, probeert u de combinaties van queryplannen te verminderen door de keuzen van operators voor fysieke joins met joinhints te beperken. Bijvoorbeeld: OPTION (HASH JOIN, MERGE JOIN), OPTION (HASH JOIN, LOOP JOIN) of OPTION (MERGE JOIN).

Opmerking: U moet voorzichtig zijn met het gebruik van deze hints.

In sommige gevallen kan het beperken van het optimalisatieprogramma met minder join-opties ertoe leiden dat de beste joinoptie niet beschikbaar is en de query zelfs vertraagt. In sommige gevallen is een specifieke join ook vereist door een optimizer (bijvoorbeeld rijdoel) en kan de query geen plan genereren als die join geen optie is. Nadat u de joinhints voor een specifieke query hebt gericht, controleert u of u een combinatie vindt die betere prestaties biedt en de time-out van Optimizer elimineert.

Hier volgen twee voorbeelden van het gebruik van dergelijke hints:

  • Gebruik OPTION (HASH JOIN, LOOP JOIN) deze optie om alleen hash- en luskoppelingen toe te staan en samenvoegen in de query te voorkomen:

    SELECT ...
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...
OPTION (HASH JOIN, LOOP JOIN)

  • Een specifieke join tussen twee tabellen afdwingen:

    SELECT ...
  FROM t1 
  INNER MERGE JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...

Stap 5: CE-configuratie wijzigen

Probeer de CE-configuratie te wijzigen door te schakelen tussen verouderde CE en Nieuwe CE. Het wijzigen van de CE-configuratie kan ertoe leiden dat de QO een ander pad kiest wanneer SQL Server queryplannen evalueert en maakt. Dus zelfs als er een time-outprobleem met optimizer optreedt, is het mogelijk dat u een plan hebt dat beter presteert dan het plan dat is geselecteerd met behulp van de alternatieve CE-configuratie. Zie Het beste queryplan activeren (kardinaliteitsschatting) voor meer informatie.

Stap 6: Optimalisatiecorrecties inschakelen

Als u Query Optimizer-correcties niet hebt ingeschakeld, kunt u overwegen deze in te schakelen met behulp van een van de volgende twee methoden:

  • Serverniveau: gebruik traceringsvlag T4199.
  • Databaseniveau: gebruik ALTER DATABASE SCOPED CONFIGURATION ..QUERY_OPTIMIZER_HOTFIXES = ON of wijzig databasecompatibiliteitsniveaus voor SQL Server 2016 en latere versies.

De QO-oplossingen kunnen ertoe leiden dat de optimizer een ander pad in planverkenning neemt. Daarom kan het een beter queryplan kiezen. Zie voor meer informatie SQL Server query optimizer hotfix trace flag 4199 servicing model.

Stap 7: de query verfijnen

U kunt de query met meerdere tabellen opsplitsen in meerdere afzonderlijke query's met behulp van tijdelijke tabellen. Het opsplitsen van de query is slechts een van de manieren om de taak voor het optimalisatieprogramma te vereenvoudigen. Zie het volgende voorbeeld:

SELECT ...
  FROM t1
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...
  JOIN t6 ON ...
  JOIN t7 ON ...
  JOIN t8 ON ...

Als u de query wilt optimaliseren, probeert u de enkele query op te splitsen in twee query's door een deel van de joinresultaten in te voegen in een tijdelijke tabel:

SELECT ...
  INTO #temp1
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...

GO

SELECT ...
  FROM #temp1
  JOIN t5 ON ...
  JOIN t6 ON ...
  JOIN t7 ON ...
  JOIN t8 ON ...