Egyre többször azt látom, hogy kétféle adatbázis fejelsztő van:
- tárolt eljárást – stored procedure – 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.
SELECT
[objtype],
COUNT_BIG(*) AS [Plans],
SUM(CAST([size_in_bytes] As decimal(18,2)))/1024/1024 AS [PlansMB]
FROM
sys.dm_exec_cached_plans
GROUP BY
[objtype]
ORDER BY
[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:
SELECT
[objtype],
COUNT_BIG(*) AS [Plans],
SUM(CAST([size_in_bytes] As decimal(18,2)))/1024/1024 AS [PlansMB],
SUM(CASE [usecounts]
WHEN 1 THEN 1
ELSE 0 END)AS [SingleUsePlans],
SUM(CASE [usecounts]
WHEN 1 THEN CAST([size_in_bytes] As decimal(18,2))
ELSE 0 END)/1024/1024 AS [SingleUsePlansMB]
FROM
sys.dm_exec_cached_plans
GROUP BY
[objtype]
ORDER BY
[Plans] DESC
Ennek az eredménye pedig az alábbi:
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:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'optimize for ad hoc workloads', 1
RECONFIGURE;
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:
DBCC 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: 4*0,75 + 60*0,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):
DECLARE @ServerMemory int;
DECLARE @PlanCache int;
DECLARE @MaxPlanCache int;
SET @MaxPlanCache = 0;
SET @ServerMemory = (SELECT CAST([value_in_use] AS int) FROM sys.configurations WHERE [name] = 'max server memory (MB)');
SET @PlanCache = (SELECT SUM([size_in_bytes])/1024/1024 FROM sys.dm_exec_cached_plans);
SET @MaxPlanCache = @MaxPlanCache + (CASE WHEN @ServerMemory - 4096 >= 0 THEN 3072 ELSE @ServerMemory * 0.75 END);
SET @MaxPlanCache = @MaxPlanCache + (CASE WHEN @ServerMemory > 4096 AND @ServerMemory - 65536 < 0 THEN (@ServerMemory - 4096) * 0.1
WHEN (@ServerMemory > 4096 AND @ServerMemory - 65536 = 0) OR @ServerMemory > 65536 THEN 61440 * 0.1
ELSE 0 END);
SET @MaxPlanCache = @MaxPlanCache + (SELECT CASE WHEN @ServerMemory > 65536 THEN (@ServerMemory-65536) * 0.05 ELSE 0 END);
SELECT
@PlanCache As [PlanCacheMB],
@MaxPlanCache As [MaxPlanCacheMB],
CASE
WHEN CAST(100 * @PlanCache / @MaxPlanCache AS decimal(18,2)) > 50
AND CAST(100 * @PlanCache / @MaxPlanCache AS decimal(18,2)) < 70 THEN 'Warning: you are using about 50% of the max Plan Cache size'
WHEN CAST(100 * @PlanCache / @MaxPlanCache AS decimal(18,2)) > 70 THEN 'Critical: you are using about 70% of the max Plan Cache size'
ELSE 'Plan Cache size is ok'
END;
É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:
;WITH PlanCache AS
(
SELECT
SUM(CAST([size_in_bytes] As decimal(18,2)))/1024/1024 AS [PlansMB],
SUM(CASE [usecounts]
WHEN 1 THEN CAST([size_in_bytes] As decimal(18,2))
ELSE 0 END)/1024/1024 AS [SingleUsePlansMB]
FROM
sys.dm_exec_cached_plans
WHERE
[objtype] IN ('Adhoc', 'Prepared')
)
SELECT
CASE
WHEN 1-[SingleUsePlansMB] / [PlansMB] < 0.25
THEN 'Plan Reuse is low, optimize for ad hoc workload may be recommended'
ELSE 'optimize for ad hoc workload makes no difference'
END AS [Recommendation]
FROM
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.