Mikor érdemes bekapcsolni az “optimize for ad hoc workloads” beállítást?

Egyre többször azt látom, hogy kétféle adatbázis fejelsztő van:

• tárolt eljárást – stored procedre – használó,
• ad hoc lekérdezéseket használó.

Az első esetben, az egyik nagy előny, hogy a végrehajtási tervek – execution plan – újra és újra felhasználhatóak, csak egyszer lesz a memóriában jó esetben az adott terv. A második esetben előfordulhat, hogy a végrehajtási terv csak egyszer kerül felhasználásra, de a memóriát azért elfoglalja. Ilyenkor jöhet szóba az optimize for ad hoc workloads beállítás.

Mire is jó ez?

Ezzel a beállítással a memória használatot lehet optimalizálni bizonyos mértékig: amikor egy batch/lekérdezés először elküldésre kerül, nem a teljes végrehajtási terv kerül eltárolásra a plan cache-ben, hanem csak egy “csonk” – plan stub. Ha a batch/lekérdezés többet nem kerül végrehajtásra, akkor a teljes végrehajtási terv nem foglalja feleslegesen a memóriát. Ha mégis elküldésre kerülne ugyan az a batch/lekérdezés, akkor már a teljes végrehajtási terv eltárolásra kerül.

Hogyan lehet lekérdezni a plan cache méretét?

A plan cache méretét, típus alapján a sys.dm_exec_cached_plans DMV segítségével lehet lekérdezni. A típus az objtype oszlopban található:

• Proc: stored procedure
• Prepared: prepared statement
• Adhoc: ad hoc lekérdezés. Erről is szól ez a bejegyzés igazán.
• stb.

Az alábbi lekrédezés segítségével, típusonként látható a darabszám és a méret a cache plan-ben.

 1SELECT
 2    [objtype],
 3    COUNT_BIG(*) AS [Plans],
 4    SUM(CAST([size_in_bytes] As decimal(18,2)))/1024/1024 AS [PlansMB]
 5FROM
 6    sys.dm_exec_cached_plans
 7GROUP BY
 8    [objtype]
 9ORDER BY
10    [Plans] DESC

Nekem az alábbi eredményt adta egy olyan szerveren, ahol még nincs beállítva az optimize for ad hoc workloads:

Az Adhoc típusú lekérdezésekből jó sok van. Most nézzük meg, hogy ezek közzül mennyi volt csak egy alkalommal használva és ezek mennyi memóriát foglalnak el:

 1SELECT
 2    [objtype],
 3    COUNT_BIG(*) AS [Plans],
 4    SUM(CAST([size_in_bytes] As decimal(18,2)))/1024/1024 AS [PlansMB],
 5    SUM(CASE [usecounts]
 6        WHEN 1 THEN 1
 7        ELSE 0 END)AS [SingleUsePlans],
 8    SUM(CASE [usecounts]
 9        WHEN 1 THEN CAST([size_in_bytes] As decimal(18,2))
10        ELSE 0 END)/1024/1024 AS [SingleUsePlansMB]
11FROM
12    sys.dm_exec_cached_plans
13GROUP BY
14    [objtype]
15ORDER BY
16    [Plans] DESC

Látható, hogy 99,5%-a az Adhoc lekérdezéseknek egyszer volt használva, csak a memóriát foglalja a teljes végrehajtási terv. Ilyenkor már érdemes bekapcsolni az optimize for ad hoc workloads beállítást.

Beállítás

Az alábbi kód segítségével beállíthatjuk, hogy csak egy “csonk” legyen eltárolva a plan cache-ben a végrehajtási tervből:

1EXEC sp_configure 'show advanced options', 1;
2RECONFIGURE;
3EXEC sp_configure 'optimize for ad hoc workloads', 1
4RECONFIGURE;

Fontos, hogy bekapcsolás után csak az új végrehajtási tervekre fog vonatkozni, a régiek ettől még ott maradnak. Ahhoz hogy a plan cache minden elemére vonatkozzon, ki kell törölni. Az alábbi parancs segítségével ez meg is történik:

1DBCC FREESYSTEMCACHE('SQL Plans');

Ezt a parancsot éles/production környezetben nem javaslom futtatni, de legalábbis csak “szülői” felügyelet mellett ajánlott ;-)

Pár órával később megnéztem ismét az Adhoc és prepred elemek memóriafoglalását és az alábbi eredményt kaptam:

Az első esetben ~4GB volt a teljes plan cache, amiből kb. 300MB volt újra felhasználva, a maradék 3,7GB feleslegesen foglalta a memóriát. Itt már arányaiban is látható, hogy a töredéke lett a memória használat, köszönhetően az optimize for ad hoc workloads beállításnak.

Memória “nyomás”

Érdemes megnézni, hogy van e un. “belső memória nyomás”. Ezt az alábbiak szerint lehet meghatározni (BOL erre vonatkozó cikke és a Plan Caching in SQL Server 2008 white paper alapján)

A visible memory a direkt címezhető memóriát jelenti, amit az SQL Server el tud érni. Az AWE memória nem tartozik ide. 32 bites rendszerekben ez a memória 2GB vagy a /3GB kapcsoló esetén 3GB. Windows Server 2008 és újabb verziók esetén a BCDEDIT.EXE /Set IncreaseUserVa 3072 parancs csinálja ugyan azt. 64 bites rendszereken ez nem probléma, a visible memory megegyezik a teljes memória mérettel.

Visszatérve a memória nyomásra, nézzük meg egy példán keresztül, hogy mikor lehet izgulni egy kicsit: legyen az SQL Server-ben 128 GB memória, de a max server memory 96GB-ra van beállítva. Így a plan cache “kritikus” mérete: 40,75 + 600,1 + 36*0,05 = 10,8GB

A fenti, példaként illusztrált rendszerben messze voltunk a kritikus mérettől, mégis érdemes volt beállítani ezt.

Az alábbi script segíthet meghatározni, hogy túl sok memóriát foglal e a plan cache (csak 64 bites rendszereken és SQL Server 2005 SP2 vagy újabb esetén):

 1DECLARE @ServerMemory int;
 2DECLARE @PlanCache int;
 3DECLARE @MaxPlanCache int;
 4 
 5SET @MaxPlanCache = 0;
 6SET @ServerMemory = (SELECT CAST([value_in_use] AS int) FROM sys.configurations WHERE [name] = 'max server memory (MB)');
 7SET @PlanCache = (SELECT SUM([size_in_bytes])/1024/1024 FROM sys.dm_exec_cached_plans);
 8 
 9SET @MaxPlanCache = @MaxPlanCache + (CASE WHEN @ServerMemory - 4096 >= 0 THEN 3072 ELSE @ServerMemory * 0.75 END);
10SET @MaxPlanCache = @MaxPlanCache + (CASE WHEN @ServerMemory > 4096 AND @ServerMemory - 65536 < 0 THEN (@ServerMemory - 4096) * 0.1
11                                          WHEN (@ServerMemory > 4096 AND @ServerMemory - 65536 = 0) OR @ServerMemory > 65536 THEN 61440 * 0.1
12                                          ELSE 0 END);
13SET @MaxPlanCache = @MaxPlanCache + (SELECT CASE WHEN @ServerMemory > 65536 THEN (@ServerMemory-65536) * 0.05 ELSE 0 END);
14 
15SELECT
16    @PlanCache As [PlanCacheMB],
17    @MaxPlanCache As [MaxPlanCacheMB],
18    CASE
19         WHEN CAST(100 * @PlanCache / @MaxPlanCache AS decimal(18,2)) > 50
20         AND CAST(100 * @PlanCache / @MaxPlanCache AS decimal(18,2)) < 70 THEN 'Warning: you are using about 50% of the max Plan Cache size'
21         WHEN CAST(100 * @PlanCache / @MaxPlanCache AS decimal(18,2)) > 70 THEN 'Critical: you are using about 70% of the max Plan Cache size'
22         ELSE  'Plan Cache size is ok'
23END;

Érdemes e beállítanom?

A rövid válaszom: attól függ! ;-) Az alábbi kód segíthet ennek a kérdésnek a megválaszolásában:

 1;WITH PlanCache AS
 2(
 3SELECT
 4    SUM(CAST([size_in_bytes] As decimal(18,2)))/1024/1024 AS [PlansMB],
 5    SUM(CASE [usecounts]
 6        WHEN 1 THEN CAST([size_in_bytes] As decimal(18,2))
 7        ELSE 0 END)/1024/1024 AS [SingleUsePlansMB]
 8FROM
 9    sys.dm_exec_cached_plans
10WHERE
11    [objtype] IN ('Adhoc', 'Prepared')
12)
13 
14SELECT
15    CASE
16        WHEN 1-[SingleUsePlansMB] / [PlansMB] < 0.25 
17        THEN 'Plan Reuse is low, optimize for ad hoc workload may be recommended'
18        ELSE 'optimize for ad hoc workload makes no difference'
19    END AS [Recommendation]
20FROM   
21    PlanCache

Azért még érdemes figyelembe venni a Plan Cache méretét is. Nagyon kis méretnél (<300MB)nem feltétlenül kell ez a beállítás.

A KÓD ÉS AZ INFORMÁCIÓK MINDENFÉLE GARANCIA NÉLKÜL "AS-IS" ÁLLNAK RENDELKEZÉSRE, A SZERZŐ SEMMIFÉLE - SEM KÖZVETLEN, SEM KÖZVETETT - FELELŐSSÉGET NEM VÁLLAL.