Share via

Detecteerbare typen knelpunten in queryprestaties in SQL Server en Azure SQL Managed Instance

  • Artikel

Van toepassing op: SQL ServerAzure SQL Managed Instance

Wanneer u een prestatieknelpunt wilt oplossen, moet u eerst bepalen of het knelpunt optreedt terwijl de query actief of inactief is. Er zijn, afhankelijk hiervan, verschillende oplossingen van toepassing. Problemen en oplossingen met betrekking tot elk type probleem worden in dit artikel besproken.

U kunt DMV's van SQL Server gebruiken om deze typen prestatieknelpunten te detecteren.

Problemen met betrekking tot uitvoering: problemen met betrekking tot uitvoering zijn over het algemeen gerelateerd aan compilatieproblemen, wat resulteert in een suboptimaal queryplan of uitvoeringsproblemen met betrekking tot onvoldoende of overbelaste resources. Problemen met betrekking tot wachten: problemen met betrekking tot wachten hebben meestal betrekking op:

  • Vergrendelingen (blokkeren)
  • I/O
  • Conflicten met betrekking tot tempdb gebruik
  • Wachttijden voor geheugentoekenningen

Dit artikel gaat over SQL Server en Azure SQL Managed Instance. Zie ook detecteerbare typen knelpunten voor queryprestaties in Azure SQL Database.

Compilatieproblemen die resulteren in een suboptimaal queryplan

Een suboptimaal plan dat is gegenereerd door sql Query Optimizer kan de oorzaak zijn van trage queryprestaties. De SQL Query Optimizer kan een suboptimaal plan produceren vanwege een ontbrekende index, verouderde statistieken, een onjuiste schatting van het aantal rijen dat moet worden verwerkt of een onjuiste schatting van het vereiste geheugen. Als u weet dat de query in het verleden of op een ander exemplaar sneller is uitgevoerd, vergelijkt u de werkelijke uitvoeringsplannen om te zien of deze verschillen.

In het voorbeeld van het afstemmen en hinten van query's in het artikel Toepassingen en databases afstemmen voor prestaties ziet u de impact van een suboptimaal queryplan vanwege een geparameteriseerde query, hoe u deze voorwaarde kunt detecteren en hoe u een queryhint gebruikt om dit op te lossen.

Query's oplossen met suboptimale queryuitvoeringsplannen

In de volgende secties wordt beschreven hoe u query's kunt oplossen met een suboptimaal uitvoeringsplan voor query's.

Query's met Parameter Sensitive Plan (PSP)-problemen

Een probleem met een parametergevoelig plan (PSP) treedt op wanneer de query optimizer een queryuitvoeringsplan genereert dat alleen optimaal is voor een specifieke parameterwaarde (of set waarden) en het plan in de cache dan niet optimaal is voor parameterwaarden die worden gebruikt in opeenvolgende uitvoeringen. Plannen die niet optimaal zijn, kunnen vervolgens problemen met queryprestaties veroorzaken en de totale doorvoer van workloads verminderen.

Zie de architectuurhandleiding voor queryverwerking voor meer informatie over parametersniffing en queryverwerking.

Verschillende tijdelijke oplossingen kunnen PSP-problemen verhelpen. Aan elke tijdelijke oplossing zijn compromissen en nadelen gekoppeld:

  • Een nieuwe functie die is geïntroduceerd met SQL Server 2022 (16.x) is Optimalisatie van parametergevoelig plan, waarmee wordt geprobeerd de meeste suboptimale queryplannen te beperken die worden veroorzaakt door parametergevoeligheid. Dit is ingeschakeld met databasecompatibiliteitsniveau 160.
  • Gebruik de hint voor de RECOMPILE-query bij elke queryuitvoering. Deze tijdelijke oplossing ruilt compilatietijd en verhoogde CPU voor een betere kwaliteit van het plan. De RECOMPILE optie is vaak niet mogelijk voor workloads waarvoor een hoge doorvoer is vereist.
  • Gebruik de queryhint OPTION (OPTIMIZE FOR...) om de werkelijke parameterwaarde te overschrijven met een typische parameterwaarde die een plan produceert dat goed genoeg is voor de meeste mogelijkheden voor parameterwaarden. Deze optie vereist een goed begrip van optimale parameterwaarden en bijbehorende plankenmerken.
  • Gebruik de queryhint OPTION (OPTIMIZE FOR UNKNOWN) om de werkelijke parameterwaarde te overschrijven en in plaats daarvan het dichtheidsvectorgemiddelde te gebruiken. U kunt dit ook doen door de binnenkomende parameterwaarden in lokale variabelen vast te leggen en vervolgens de lokale variabelen binnen de predicaten te gebruiken in plaats van de parameters zelf te gebruiken. Voor deze oplossing moet de gemiddelde dichtheid goed genoeg zijn.
  • Schakel parameter-sniffing volledig uit met behulp van de DISABLE_PARAMETER_SNIFFING queryhint.
  • Gebruik de hint voor de KEEPFIXEDPLAN-query om hercompilaties in de cache te voorkomen. Bij deze tijdelijke oplossing wordt ervan uitgegaan dat het goed genoeg gangbare plan al in de cache is. U kunt ook automatische updates voor statistieken uitschakelen om de kans te verminderen dat het goede plan wordt verwijderd en een nieuw slecht plan wordt gecompileerd.
  • Dwing het plan af door expliciet gebruik te maken van de hint voor de USE PLAN-query door de query te herschrijven en de hint toe te voegen in de querytekst. Of stel een specifiek plan in met behulp van Query Store of door automatisch afstemmen in te schakelen.
  • Vervang de enkele procedure door een geneste set procedures die elk kunnen worden gebruikt op basis van voorwaardelijke logica en de bijbehorende parameterwaarden.
  • Dynamische tekenreeksuitvoeringsalternatieven maken voor een statische proceduredefinitie.

Als u queryhints wilt toepassen, wijzigt u de query of gebruikt u Query Store-hints om de hint toe te passen zonder codewijzigingen aan te brengen. Gebruik planhandleidingen in versies van SQL Server vóór SQL Server 2022.

Zie deze blogberichten voor meer informatie over het oplossen van PSP-problemen:

Compileeractiviteit die wordt veroorzaakt door onjuiste parameterisatie (Engelstalig)

Wanneer een query letterlijke waarden bevat, wordt de instructie automatisch door de database-engine geparameteraliseerd of wordt de instructie expliciet geparameteraliseerd om het aantal compilaties te verminderen. Een groot aantal compilaties voor een query met hetzelfde patroon, maar verschillende letterlijke waarden, kan leiden tot een hoog CPU-gebruik. En als u een query met letterlijke waarden slechts gedeeltelijk parameteriseert, wordt de query niet verder geparameterd door de database-engine.

Hier volgt een voorbeeld van een gedeeltelijk geparameteriseerde query:

SELECT *
FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE t1.c1 = @p1 AND t2.c2 = '961C3970-0E54-4E8E-82B6-5545BE897F8F';

In dit voorbeeld t1.c1 wordt @p1de t2.c2 GUID als letterlijke id gebruikt. Als u in dit geval de waarde voor c2wijzigt, wordt de query behandeld als een andere query en wordt er een nieuwe compilatie uitgevoerd. Als u compilaties in dit voorbeeld wilt verminderen, zou u ook de GUID parameteriseren.

De volgende query toont het aantal query's per query-hash om te bepalen of een query correct is geparameteriseerd:

SELECT TOP 10
  q.query_hash
  , count (distinct p.query_id ) AS number_of_distinct_query_ids
  , min(qt.query_sql_text) AS sampled_query_text
FROM sys.query_store_query_text AS qt
  JOIN sys.query_store_query AS q
     ON qt.query_text_id = q.query_text_id
  JOIN sys.query_store_plan AS p
     ON q.query_id = p.query_id
  JOIN sys.query_store_runtime_stats AS rs
     ON rs.plan_id = p.plan_id
  JOIN sys.query_store_runtime_stats_interval AS rsi
     ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE
  rsi.start_time >= DATEADD(hour, -2, GETUTCDATE())
  AND query_parameterization_type_desc IN ('User', 'None')
GROUP BY q.query_hash
ORDER BY count (distinct p.query_id) DESC;

Factoren die van invloed zijn op wijzigingen in het queryplan (Engelstalig)

Een hercompilatie van een queryuitvoeringsplan kan resulteren in een gegenereerd queryplan dat verschilt van het oorspronkelijke in de cache opgeslagen plan. Een bestaand oorspronkelijk plan kan om verschillende redenen automatisch opnieuw worden gecompileerd:

  • Er wordt naar wijzigingen in het schema verwezen door de query
  • Er wordt naar gegevenswijzigingen in de tabellen verwezen door de query
  • Opties voor querycontext zijn gewijzigd

Een gecompileerd plan kan om verschillende redenen uit de cache worden verwijderd, zoals:

  • Exemplaar wordt opnieuw opgestart
  • Configuratiewijzigingen in databasebereik
  • Geheugendruk
  • Expliciete aanvragen om de cache te wissen

Als u een HINT VOOR RECOMPILE gebruikt, wordt een plan niet in de cache opgeslagen.

Een hercompilatie (of nieuwe compilatie na verwijdering van de cache) kan nog steeds resulteren in het genereren van een queryuitvoeringsplan dat identiek is aan het origineel. Wanneer het plan verandert van het vorige of oorspronkelijke plan, zijn deze uitleg waarschijnlijk:

  • Gewijzigd fysiek ontwerp: nieuwe indexen hebben bijvoorbeeld effectiever betrekking op de vereisten van een query. De nieuwe indexen kunnen worden gebruikt voor een nieuwe compilatie als de queryoptimalisatie besluit dat het gebruik van die nieuwe index beter is dan het gebruik van de gegevensstructuur die oorspronkelijk is geselecteerd voor de eerste versie van de queryuitvoering. Eventuele fysieke wijzigingen in de objecten waarnaar wordt verwezen, kunnen leiden tot een nieuwe plankeuze tijdens het compileren.

  • Verschillen tussen serverresources: Wanneer een plan in het ene systeem verschilt van het plan in een ander systeem, kan de beschikbaarheid van resources, zoals het aantal beschikbare processors, van invloed zijn op welk plan wordt gegenereerd. Als één systeem bijvoorbeeld meer processors heeft, kan er een parallel plan worden gekozen. Zie De maximale mate van parallelle uitvoering van serverconfiguratie configureren voor meer informatie over parallelle uitvoering.

  • Verschillende statistieken: De statistieken die zijn gekoppeld aan de objecten waarnaar wordt verwezen, zijn mogelijk gewijzigd of kunnen wezenlijk afwijken van de statistieken van het oorspronkelijke systeem. Als de statistieken veranderen en er een hercompilatie plaatsvindt, worden de statistieken gebruikt die beginnen bij het wijzigen van de statistieken. De gegevensdistributies en frequenties van de herziene statistieken kunnen afwijken van die van de oorspronkelijke compilatie. Deze wijzigingen worden gebruikt om kardinaliteitschattingen te maken. (Kardinaliteitschattingen zijn het aantal rijen dat naar verwachting door de logische querystructuur stroomt.) Wijzigingen in kardinaliteitschattingen kunnen ertoe leiden dat u verschillende fysieke operators en bijbehorende orders van bewerkingen kiest. Zelfs kleine wijzigingen in statistieken kunnen resulteren in een gewijzigd uitvoeringsplan voor query's.

  • Gewijzigde versie van databasecompatibiliteit of kardinaliteitsschatterversie: Wijzigingen in het compatibiliteitsniveau van de database kunnen nieuwe strategieën en functies inschakelen die kunnen leiden tot een ander uitvoeringsplan voor query's. Naast het compatibiliteitsniveau van de database kan een uitgeschakelde of ingeschakelde traceringsvlag 4199 of een gewijzigde status van de configuratie binnen het databasebereik QUERY_OPTIMIZER_HOTFIXES ook invloed hebben op de keuzen van het queryuitvoeringsplan tijdens het compileren. Traceringsvlagken 9481 (verouderde CE forceren) en 2312 (standaard-CE forceren) hebben ook invloed op het plan.

Problemen met resourcelimieten in Azure SQL Managed Instance

Trage queryprestaties die niet zijn gerelateerd aan suboptimale queryplannen en ontbrekende indexen, zijn over het algemeen gerelateerd aan onvoldoende of overbelaste resources. Als het queryplan optimaal is, kan de query (en de database) de resourcelimieten voor het beheerde exemplaar bereiken. Een voorbeeld hiervan is een overtollige schrijfdoorvoer voor logboeken voor het serviceniveau.

Als u het probleem als onvoldoende resource identificeert, kunt u resources upgraden om de capaciteit van uw database te verhogen om de CPU-vereisten te absorberen. Zie Resourcelimieten voor servicelagen voor meer informatie over het schalen van een beheerd exemplaar

Prestatieproblemen die worden veroorzaakt door een verhoogd workloadvolume (Engelstalig)

Een toename van toepassingsverkeer en workloadvolume kan leiden tot een verhoogd CPU-gebruik. Maar u moet voorzichtig zijn om dit probleem goed te diagnosticeren. Als u een probleem met een hoog CPU-gebruik ziet, beantwoordt u deze vragen om te bepalen of de toename wordt veroorzaakt door wijzigingen in het workloadvolume:

  • Zijn de query's van de toepassing de oorzaak van het probleem met een hoog CPU-gebruik?

  • Voor de belangrijkste CPU-verbruikende query's die u kunt identificeren:

    • Zijn er meerdere uitvoeringsplannen gekoppeld aan dezelfde query? Als dat het geval is, waarom?
    • Waren de uitvoeringstijden consistent voor query's met hetzelfde uitvoeringsplan? Is het aantal uitvoeringen toegenomen? Zo ja, dan veroorzaakt de toename van de werkbelasting waarschijnlijk prestatieproblemen.

Kortom, als het queryuitvoeringsplan niet anders is uitgevoerd, maar het CPU-gebruik samen met het aantal uitvoeringen is toegenomen, heeft het prestatieprobleem waarschijnlijk te maken met een toename van de werkbelasting.

Het is niet altijd eenvoudig om een workloadvolumewijziging te identificeren die een CPU-probleem aanzet. Houd rekening met deze factoren:

  • Gewijzigd resourcegebruik: denk bijvoorbeeld aan een scenario waarin het CPU-gebruik gedurende een langere periode is verhoogd tot 80 procent. Alleen cpu-gebruik betekent niet dat het workloadvolume is gewijzigd. Regressies in het queryuitvoeringsplan en wijzigingen in gegevensdistributie kunnen ook bijdragen aan meer resourcegebruik, ook al wordt dezelfde workload uitgevoerd door de toepassing.

  • Het uiterlijk van een nieuwe query: een toepassing kan een nieuwe set query's op verschillende tijdstippen aandrijven.

  • Een toename of afname van het aantal aanvragen: dit scenario is de meest voor de hand liggende meting van een workload. Het aantal query's komt niet altijd overeen met meer resourcegebruik. Deze metrische waarde is echter nog steeds een belangrijk signaal, ervan uitgaande dat andere factoren ongewijzigd zijn.

  • Parallellisme: Overmatige parallelle uitvoering kan de prestaties van andere gelijktijdige werkbelastingen verergeren door andere query's van CPU- en werkthreadresources te verhongeren. Zie De maximale mate van parallelle uitvoering van serverconfiguratie configureren voor meer informatie over parallelle uitvoering.

Zodra u een suboptimaal plan en wachtende problemen hebt geëlimineerd die betrekking hebben op uitvoeringsproblemen, is het prestatieprobleem over het algemeen de query's waarschijnlijk op een bepaalde resource wachten. Problemen met betrekking tot het wachten kunnen worden veroorzaakt door:

  • Blokkeren:

    Een query kan deze vergrendeling van objecten in de database bevatten, terwijl anderen proberen toegang te krijgen tot dezelfde objecten. U kunt de blokkerende query's identificeren met behulp van DMV's. Zie Blokkeringsproblemen begrijpen en oplossen voor meer informatie.

  • IO-problemen

    De query's wachten mogelijk totdat de pagina's naar de gegevens of logboekbestanden worden geschreven. Controleer in dat geval de INSTANCE_LOG_RATE_GOVERNOR, WRITE_LOG, of PAGEIOLATCH_* wachtstatistieken in de DMV. Raadpleeg de DMV's gebruiken om IO-prestatieproblemen te identificeren.

  • Tempdb-problemen

    Als de workload gebruikmaakt van tijdelijke tabellen of als er sprake is van tempdb overloop in de plannen, kunnen de query's een probleem met tempdb doorvoer hebben. Als u dat verder wilt onderzoeken, bekijkt u tempdb-problemen.

  • Problemen met betrekking tot geheugen

    Als de workload onvoldoende geheugen heeft, dan kan de levensverwachting van de pagina afnemen of krijgen de query's mogelijk minder geheugen dan nodig is. In sommige gevallen lost de ingebouwde intelligentie in Query Optimizer geheugenproblemen op. Zie DMV's gebruiken om de problemen met geheugentoekenning te identificeren. Als er onvoldoende geheugenfouten optreden, dan raadpleegt u sys.dm_os_out_of_memory_events. Overweeg ook de laag voor de premium-serie geheugen van Azure SQL Managed Instance hardware met hogere verhouding van geheugen tot vCores.

Methoden om de belangrijkste wachtcategorieën weer te geven

Deze methoden worden vaak gebruikt om de belangrijkste categorieën wachttypen weer te geven:

  • Gebruik Query Store om wachtstatistieken voor elke query in de loop van de tijd te vinden. In Query Store worden wachttypen gecombineerd in wachtcategorieën. U vindt de toewijzing van wachtcategorieën voor wachttypen in sys.query_store_wait_stats.
  • Gebruik sys.dm_os_wait_stats om informatie te retourneren over alle wachttijden die zijn aangetroffen door threads die tijdens een querybewerking zijn uitgevoerd. U kunt deze geaggregeerde weergave gebruiken om prestatieproblemen met het Azure SQL Managed Instance- of SQL Server-exemplaar te diagnosticeren. Query's kunnen wachten op resources, wachtrijwachttijden of externe wachttijden.
  • Gebruik sys.dm_os_waiting_tasks om informatie te retourneren over de wachtrij met taken die op een resource wachten.

In scenario's met een hoog CPU-gebruik geven Query Store- en wachtstatistieken mogelijk niet het CPU-gebruik weer als:

  • Query's die veel CPU verbruiken, worden nog steeds uitgevoerd.
  • De query's die veel CPU verbruiken, werden uitgevoerd toen een failover plaatsvond.

DMV's die Query Store bijhouden en wachtstatistieken geven resultaten weer voor alleen voltooide en time-outquery's. Ze geven geen gegevens weer voor het uitvoeren van instructies totdat de instructies zijn voltooid. Gebruik de dynamische beheerweergave sys.dm_exec_requests om query's en de bijbehorende werktijd bij te houden.

Tip

Aanvullende hulpprogramma's:

Volgende stappen