Dela via

Serverkonfiguration: optimera för ad hoc-arbetsbelastningar

Gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAnalysplattformssystem (PDW)

Alternativet optimize for ad hoc workloads används för att förbättra effektiviteten i plancachen för arbetsbelastningar som innehåller många ad hoc-batchar med enkel användning. När det här alternativet är inställt 1på lagrar databasmotorn en liten kompilerad plan i plancachen när en batch kompileras för första gången, i stället för den fullständiga kompilerade planen. Det här alternativet kan hjälpa till att minska minnesbelastningen genom att inte tillåta att plancachen fylls med kompilerade planer som inte återanvänds. Om du aktiverar det här alternativet kan det dock påverka din möjlighet att felsöka engångsplaner.

Med den kompilerade planstuben kan databasmotorn känna igen att den här ad hoc-batchen kompilerades tidigare och endast lagrar en kompilerad planstub. När den här batchen anropas (kompileras eller körs) igen kompilerar databasmotorn batchen, tar bort den kompilerade planens stub från plancachen och lägger till den fullständiga kompilerade planen i plancachen.

Du hittar kompilerade planstubbar genom att sys.dm_exec_cached_plans fråga katalogvyn och leta efter "Kompilerad plan" i cacheobjtype kolumnen. Stub har en unik plan_handle. Den kompilerade planstuben har ingen associerad körningsplan, och frågor om planreferensen returnerar inte en grafisk eller XML-showplan.

Spårningsflagga 8032 återställer parametrarna för cachegräns till RTM-inställningen SQL Server 2005 (9.x), som i allmänhet tillåter att cacheminnen blir större. Använd den här inställningen när ofta återanvända cacheposter inte passar in i cacheminnet och när optimize for ad hoc workloads alternativet inte kunde lösa problemet med plancache.

Varning

Spårningsflagga 8032 kan orsaka dåliga prestanda om stora cacheminnen gör mindre minne tillgängligt för andra minneskonsumenter, till exempel buffertpoolen.

Anmärkningar

optimize for ad hoc workloads 1 Alternativet påverkas endast av nya planer. Planer som redan finns i plancachen påverkas inte.

Om du vill påverka redan cachelagrade frågeplaner omedelbart måste plancachen rensas med HJÄLP av ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, annars måste SQL Server startas om.

Recommendations

Undvik att ha ett stort antal engångsplaner i plancachen. Vanliga orsaker är:

  • Datatyper av frågeparametrar som inte definieras konsekvent. Detta gäller särskilt längden på strängar men kan tillämpas på alla datatyper som har en maxlängd, en precision eller en skala. Om till exempel en parameter med namnet @Greeting skickas som nvarchar(10) på ett anrop och nvarchar(20) vid nästa anrop skapas separata planer för varje parameterstorlek.

  • Frågor som inte är parametriserade. Om en fråga har en eller flera parametrar för vilka hårdkodade värden skickas till databasmotorn kan det finnas ett stort antal frågeplaner för varje fråga. Planer kan finnas för varje kombination av frågeparameterdatatyper och längder som användes.

Om antalet engångsplaner tar en betydande del av SQL Server Database Engine-minnet på en OLTP-server, och dessa planer är ad hoc-planer, använder du det här serveralternativet för att minska minnesanvändningen med dessa objekt.

Om alternativet optimize for ad hoc workloads är aktiverat kan du inte visa körningsplaner för frågor med enkel användning, eftersom endast plan-stub cachelagras. Beroende på din miljö och arbetsbelastning kan du dra nytta av följande två funktioner:

  • Funktionen Query Store, som introducerades i SQL Server 2016 (13.x), hjälper dig att snabbt hitta prestandaskillnader som orsakas av ändringar i frågeplanen. Query Store är aktiverat som standard på nya databaser i SQL Server 2022 (16.x) och senare versioner.

  • Tvingad parameterisering kan förbättra prestandan för vissa databaser genom att minska frekvensen för frågekompileringar och omkompileringar. Databaser som drar nytta av framtvingad parameterisering har vanligtvis stora mängder samtidiga frågor från källor som till exempel kassaprogram.

    Framtvingad parameterisering kan orsaka prestandaproblem på grund av parameterkänslighet. Mer information finns i Undersöka och lösa parameterkänsliga problem. För SQL Server 2022 (16.x) och senare versioner kan du även aktivera optimering av parameterkänslig plan.

Examples

Kör följande fråga för att hitta antalet cachelagrade planer för enkel användning:

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;

Relaterat innehåll