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

Artikel
12/14/2024

Van toepassing op: SQL Server

In dit artikel maakt u kennis met time-out voor optimizer, hoe dit van invloed kan zijn op de prestaties van query's en hoe u de prestaties kunt optimaliseren.

Wat is Time-out voor Optimizer?

SQL Server maakt gebruik van een op kosten gebaseerde Query Optimizer (QO). Zie de architectuurhandleiding voor queryverwerking voor informatie over QO. Een queryoptimalisatie op basis van kosten selecteert een queryuitvoeringsplan met de laagste kosten nadat deze meerdere queryplannen heeft gebouwd en geëvalueerd. Een van de doelstellingen van SQL Server Query Optimizer is om een redelijke tijd te besteden aan queryoptimalisatie in vergelijking met de uitvoering van query's. Het optimaliseren van een query moet veel sneller zijn 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 wordt bereikt voordat QO alle mogelijke plannen heeft overwogen, bereikt deze de time-outlimiet voor optimizer. Er wordt een time-out-gebeurtenis voor optimizer gerapporteerd in het queryplan als time-out onder Reden voor vroegtijdige beëindiging van instructieoptimalisatie. Het is belangrijk om te begrijpen dat deze drempelwaarde niet is gebaseerd op kloktijd, maar op het aantal mogelijkheden dat door de optimizer wordt overwogen. In de huidige QO-versies van SQL Server 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 van invloed is op de queryprestaties. In sommige gevallen kan de keuze van het SQL-queryplan echter negatief worden beïnvloed door de time-out van optimizer en kunnen tragere queryprestaties leiden. Wanneer u dergelijke problemen ondervindt, kunt u inzicht krijgen in het time-outmechanisme van Optimizer 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 van optimizer is dat SQL Server niet als de volledige set mogelijkheden voor optimalisatie heeft beschouwd. Dat wil bijvoorbeeld dat het plannen heeft gemist die kortere uitvoeringstijden kunnen opleveren. QO stopt bij de drempelwaarde en beschouwt op dat moment het minst kostenqueryplan, ook al zijn er mogelijk betere, niet-geïmplementeerde opties. Houd er rekening mee dat het plan dat is geselecteerd nadat een time-out voor optimizer is bereikt, een redelijke uitvoeringsduur voor de query kan opleveren. In sommige gevallen kan het geselecteerde plan echter leiden tot een suboptimale uitvoering van een query.

How to detect an Optimizer Timeout?

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

Complexe query

U hebt een complexe query met veel gekoppelde tabellen (bijvoorbeeld acht of meer tabellen worden samengevoegd).
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 planoperator in Microsoft SQL Server Management Studio. U ziet de waarde van Reason For Early Termination of Statement Optimization is TimeOut.

Wat veroorzaakt een time-out van de optimizer?

Er is geen eenvoudige manier om te bepalen welke voorwaarden ervoor zorgen dat de drempelwaarde voor de optimalisatie wordt bereikt of overschreden. De volgende secties zijn enkele factoren die van invloed zijn op het aantal plannen dat door QO wordt verkend wanneer u op zoek bent naar het beste plan.

In welke volgorde moeten tabellen worden samengevoegd?

Hier volgt een voorbeeld van de uitvoeropties van joins met drie tabellen (Table1, Table2, Table3):
- Deelnemen Table1 met Table2 en het resultaat met Table3
- Deelnemen Table1 met Table3 en het resultaat met Table2
- Deelnemen 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) kan worden gebruikt om de rijen uit een tabel op te halen?
- Geclusterde index
- Niet-geclusterde index1
- Niet-geclusterde index2
- Tabel heap
Welke fysieke toegangsmethode moet worden gebruikt?
- Indexzoeken
- Indexscan
- Tabelscan
Welke operator voor fysieke joins moet u gebruiken?
- Geneste lussen join (NJ)
- Hash-join (HJ)
- Join samenvoegen (MJ)
- Adaptieve join (beginnend met SQL Server 2017 (14.x))
Zie Joins voor meer informatie.
Onderdelen van de query parallel of serieel uitvoeren?

Zie Parallelle queryverwerking voor meer informatie.

Hoewel de volgende factoren het aantal gebruikte toegangsmethoden verminderen en dus de mogelijkheden die worden overwogen:

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 deze uiteindelijk een tragere query zal hebben. In de meeste gevallen presteert de query goed, maar in sommige gevallen ziet u mogelijk een tragere uitvoering van de query.

Voorbeeld van hoe de factoren worden overwogen

Laten we een voorbeeld nemen van een join tussen drie tabellen (t1, t2en t3) en elke tabel heeft een geclusterde index en een niet-geclusterde index.

Overweeg eerst de fysieke jointypen. Er zijn hier twee joins betrokken. En omdat er drie fysieke joinmogelijkheden (NJ, HJ en MJ) zijn, kan de query op 3² = 9 manieren worden uitgevoerd.

NJ - NJ
NJ - HJ
NJ - MJ
HJ - NJ
HJ - HJ
HJ - MJ
MJ - NJ
MJ - HJ
MJ - MJ

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

Deelnemen t1 met t2 en vervolgens met t3
Deelnemen t1 met t3 en vervolgens met t2
Deelnemen t2 met t3 en vervolgens met t1

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

Gezien al deze voorwaarden kunnen er ten slotte 9*3*64 = 1728 mogelijke plannen zijn.

Laten we nu aannemen dat er n tabellen aan de query zijn gekoppeld 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: 3^n-1
Verschillende indextypen met zoek- en scanmethoden: 4ⁿ

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

Houd er rekening mee dat de bovenstaande berekeningen het slechtste scenario illustreren. Zoals we hebben opgemerkt, 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 indexzoekbewerking te gebruiken dan een scan. Dit leidt ook tot een kleinere selectie joins, enzovoort.

Waarom zie ik een time-out voor optimizer 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 abonnement 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 verminderd. Daarom kan een onderschatte kardinaliteitsraming (CE) één scenario zijn voor het vroeg bereiken van een time-out van optimizer. In dit geval is de focus van het onderzoek CE. Het is een zeldzamer geval in vergelijking met het scenario over het uitvoeren van een complexe query die in de vorige sectie wordt besproken, maar dit 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 mogelijk niets te doen aan deze situatie. Het queryplan waarmee SQL Server eindigt, kan redelijk zijn en de query die u uitvoert, presteert mogelijk goed. Misschien weet u nooit dat u een time-out voor optimizer hebt aangetroffen.

Voer de volgende stappen uit als u de noodzaak vindt om af te stemmen en te 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 leidraad bij het afstemmen van prestaties is dat er geen prestatieprobleem is zonder een basislijn. Daarom is het belangrijk om een basislijn voor dezelfde query te maken.

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 weergaven of tabelwaardefuncties betrokken zijn. Op het oppervlak lijkt de query bijvoorbeeld eenvoudig te zijn omdat deze twee weergaven koppelt. Maar wanneer u de query's in de weergaven bekijkt, kan het zijn dat elke weergave zeven tabellen samenvoegt. Als 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 zou de meest voorkomende oplossing zijn 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 waarmee twee afzonderlijke sets query's (afgeleide tabellen) worden samengevoegd met 4-5 joins in elk. Na het parseren door SQL Server wordt deze echter gecompileerd in één query met acht gekoppelde tabellen.

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-out van Optimizer te voorkomen. Meerdere CTE's verhogen alleen de complexiteit van de query. Daarom is het contraproductief om CTE's te gebruiken bij het oplossen van time-outs van de optimalisatie. CTE's lijken logisch te breken, maar ze worden gecombineerd tot één query en geoptimaliseerd als één grote join van tabellen.

Hier volgt een voorbeeld van een CTE die wordt gecompileerd als één query met veel joins. Het lijkt erop dat de query voor de my_cte een eenvoudige join met twee objecten is, maar er zijn zelfs zeven andere tabellen die zijn gekoppeld aan 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 gekregen. Net als bij CTE's en afgeleide tabellen kunnen joins worden verborgen in weergaven. Een join tussen twee weergaven kan bijvoorbeeld uiteindelijk één query zijn waarbij acht tabellen betrokken zijn:

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

Tabelwaardefuncties (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 ...

Vakbond

Union-operators combineren de resultaten van meerdere query's in één resultatenset. Ze combineren ook meerdere query's in één query. Vervolgens krijgt u mogelijk één complexe query. In het volgende voorbeeld ziet 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 queryplan

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

Stap 4: Opties voor abonnementen verminderen

Als u de kans op een time-out voor optimizer wilt verminderen, vermindert u de mogelijkheden die QO moet overwegen bij het kiezen van een plan. Dit proces omvat het testen van de query met verschillende hintopties. Net als bij de meeste beslissingen met QO, zijn de keuzes niet altijd deterministisch op het oppervlak, omdat er een grote verscheidenheid aan factoren is die moeten worden overwogen. Er is dus geen enkele gegarandeerde geslaagde strategie en het geselecteerde plan kan de prestaties van de geselecteerde query verbeteren of verlagen.

Een JOIN-bestelling afdwingen

Gebruik OPTION (FORCE ORDER) dit 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 keuzes van operators voor fysieke joins te beperken met hints voor joins. Bijvoorbeeld: OPTION (HASH JOIN, MERGE JOIN), OPTION (HASH JOIN, LOOP JOIN) of OPTION (MERGE JOIN).

Opmerking: Wees voorzichtig bij het gebruik van deze hints.

In sommige gevallen kan het beperken van de optimizer met minder joinkeuzen ertoe leiden dat de beste join-optie niet beschikbaar is en de query mogelijk vertraagt. In sommige gevallen is een specifieke join vereist voor een optimizer (bijvoorbeeld het rijdoel) en kan de query een plan niet genereren als die join geen optie is. Controleer daarom, nadat u de joinhints voor een specifieke query hebt gericht, of u een combinatie vindt die betere prestaties biedt en de time-out voor optimizer elimineert.

Hier volgen twee voorbeelden van het gebruik van dergelijke hints:

Gebruik OPTION (HASH JOIN, LOOP JOIN) dit om alleen hash- en loop-joins toe te staan en samenvoeging 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. Als u de CE-configuratie wijzigt, kan de QO een ander pad kiezen wanneer SQL Server queryplannen evalueert en maakt. Dus zelfs als er een time-outprobleem voor optimizer optreedt, is het mogelijk dat u uiteindelijk een plan hebt dat beter presteert dan het plan dat is geselecteerd met behulp van de alternatieve CE-configuratie. Zie Hoe u het beste queryplan (kardinaliteitsraming) activeert voor meer informatie.

Stap 6: Fixes voor Optimizer inschakelen

Als u geen oplossingen voor Query Optimizer hebt ingeschakeld, kunt u deze inschakelen met behulp van een van de volgende twee methoden:

Serverniveau: traceringsvlag T4199 gebruiken.
Databaseniveau: databasecompatibiliteitsniveaus gebruiken ALTER DATABASE SCOPED CONFIGURATION ..QUERY_OPTIMIZER_HOTFIXES = ON of wijzigen 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 optimalere queryplanning kiezen. Zie voor meer informatie sql Server query optimizer hotfix trace flag 4199 servicing model.

Stap 7: De query verfijnen

Overweeg om de query met meerdere tabellen op te splitsen 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 de optimalisatie 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 ...

Delen via