Delen via

Serverconfiguratie: optimaliseren voor ad-hocworkloads

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAnalytics Platform System (PDW)

De optimize for ad hoc workloads optie wordt gebruikt om de efficiëntie van de plancache te verbeteren voor workloads die veel ad-hocbatches met één gebruik bevatten. Wanneer deze optie is ingesteld 1op, slaat de database-engine een kleine gecompileerde plan-stub op in de plancache wanneer een batch voor het eerst wordt gecompileerd in plaats van het volledige gecompileerde plan. Deze optie kan helpen om geheugenbelasting te verlichten door de plancache niet toe te staan om gevuld te raken met gecompileerde plannen die niet opnieuw worden gebruikt. Het inschakelen van deze optie kan echter van invloed zijn op de mogelijkheid om problemen met plannen voor eenmalig gebruik op te lossen.

Met de gecompileerde plan-stub kan de database-engine herkennen dat deze ad-hocbatch eerder is gecompileerd en wordt alleen een gecompileerde plan-stub opgeslagen. Wanneer deze batch opnieuw wordt aangeroepen (gecompileerd of uitgevoerd), compileert de Database Engine de batch, verwijdert de gecompileerde plan-stub uit de plancache en wordt het volledige gecompileerde plan toegevoegd aan de plancache.

U kunt gecompileerde plan-stubs vinden door een query uit te voeren op de sys.dm_exec_cached_plans catalogusweergave en te zoeken naar 'Gecompileerd plan' in de cacheobjtype kolom. De stub heeft een unieke plan_handle. Er is geen uitvoeringsplan gekoppeld aan de gecompileerde plan-stub en er wordt geen grafisch of XML-showplan geretourneerd door het uitvoeren van query's voor de plangreep.

Met traceringsvlag 8032 worden de parameters voor de cachelimiet teruggezet naar de RTM-instelling van SQL Server 2005 (9.x), waardoor caches over het algemeen groter kunnen zijn. Gebruik deze instelling wanneer vaak opnieuw gebruikte cachevermeldingen niet in de cache passen en wanneer de optimize for ad hoc workloads optie het probleem met de plancache niet kan oplossen.

Waarschuwing

Traceringsvlag 8032 kan slechte prestaties veroorzaken als grote caches minder geheugen beschikbaar maken voor andere geheugengebruikers, zoals de buffergroep.

Opmerkingen

Het instellen van de optimize for ad hoc workloads optie voor 1 alleen nieuwe plannen; plannen die zich al in de plancache bevinden, worden niet beïnvloed.

Als u direct van invloed wilt zijn op al in de cache opgeslagen queryplannen, moet de plancache worden gewist met ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE of moet SQL Server opnieuw worden opgestart.

Aanbevelingen

Vermijd een groot aantal plannen voor eenmalig gebruik in de plancache. Veelvoorkomende oorzaken zijn:

  • Gegevenstypen van queryparameters die niet consistent zijn gedefinieerd. Dit geldt met name voor de lengte van tekenreeksen, maar kan worden toegepast op elk gegevenstype dat een maxlength, een precisie of een schaal heeft. Als een parameter met de naam @Greeting bijvoorbeeld wordt doorgegeven als nvarchar(10) voor één aanroep en nvarchar(20) voor de volgende aanroep, worden er afzonderlijke plannen gemaakt voor elke parametergrootte.

  • Query's die niet worden geparameteriseerd. Als een query een of meer parameters heeft waarvoor in code vastgelegde waarden worden verzonden naar de database-engine, kan er een groot aantal queryplannen bestaan voor elke query. Er kunnen plannen bestaan voor elke combinatie van queryparametergegevenstypen en lengten die zijn gebruikt.

Als het aantal plannen voor eenmalig gebruik een aanzienlijk deel van het GEHEUGEN van SQL Server Database Engine op een OLTP-server neemt en deze plannen ad-hocplannen zijn, gebruikt u deze serveroptie om het geheugengebruik met deze objecten te verminderen.

Als de optimize for ad hoc workloads optie is ingeschakeld, kunt u geen uitvoeringsplannen voor query's voor eenmalig gebruik weergeven, omdat alleen de plan-stub in de cache is opgeslagen. Afhankelijk van uw omgeving en workload profiteert u mogelijk van de volgende twee functies:

  • De functie Query Store , geïntroduceerd in SQL Server 2016 (13.x), helpt u snel prestatieverschillen te vinden die worden veroorzaakt door wijzigingen in het queryplan. Query Store is standaard ingeschakeld voor nieuwe databases in SQL Server 2022 (16.x) en nieuwere versies.

  • Geforceerde parameterisatie kan de prestaties van bepaalde databases verbeteren door de frequentie van querycompilaties en hercompilaties te verminderen. Databases die profiteren van geforceerde parameterisatie, ervaren over het algemeen grote hoeveelheden gelijktijdige query's uit bronnen zoals point-of-sale-toepassingen.

    Geforceerde parameterisatie kan prestatieproblemen veroorzaken vanwege de gevoeligheid van parameters. Zie Parametergevoelige problemen onderzoeken en oplossen voor meer informatie. Voor SQL Server 2022 (16.x) en latere versies kunt u ook Optimalisatie van parametergevoelig plan inschakelen.

Voorbeelden

Voer de volgende query uit om het aantal in de cache opgeslagen abonnementen met één gebruik te vinden:

SELECT objtype,
    cacheobjtype,
    SUM(refcounts) AS AllRefObjects,
    SUM(CAST(size_in_bytes AS BIGINT)) / 1024 / 1024 AS SizeInMB
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc'
    AND usecounts = 1
GROUP BY objtype, cacheobjtype;

Verwante inhoud