Notitie
Voor toegang tot deze pagina is autorisatie vereist. U kunt proberen u aan te melden of de directory te wijzigen.
Voor toegang tot deze pagina is autorisatie vereist. U kunt proberen de mappen te wijzigen.
Van toepassing op:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAnalytics Platform System (PDW)
De optie optimize for ad hoc workloads wordt gebruikt om de efficiëntie van de plancache te verbeteren voor workloads met veel ad-hocbatches voor eenmalig gebruik. 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.
De gecompileerde plan-stub zorgt ervoor dat de Database Engine kan herkennen dat deze ad-hocbatch eerder is gecompileerd, en slaat alleen een gecompileerde plan-stub op. 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. Aan de gecompileerde planstub is geen uitvoeringsplan gekoppeld, en het opvragen van de plan-handle retourneert geen grafisch showplan of XML-showplan.
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 optie optimize for ad hoc workloads op 1 heeft alleen invloed op nieuwe plannen; plannen die al in de plancache staan, worden niet beïnvloed.
Als u onmiddellijk effect wilt hebben op queryplannen die al in de cache staan, moet de plancache worden gewist met ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, of SQL Server moet 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
@Greetingbijvoorbeeld 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 geparametriseerd zijn. 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;