Ha a plan cache tele van csak egyszer használt planekkel, akkor azok csak feleslegesen eszik a memóriát.
Az alábbi lekérdezés erre világít rá:
SELECT objtype AS [CacheType]
, count_big(*) AS [Total Plans]
, sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
, avg(cast(usecounts as bigint)) AS [Avg Use Count]
, sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs – USE Count 1]
, sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans – USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs – USE Count 1] DESC
Megkérnélek benneteket, akik éles, nagy terheltségű SQL szervert üzemeltetnek, hogy futtassátok le a fenti parancsot, és küldjétek el az eredményt, kíváncsi vagyok, hol-milyen eredmények jönnek vissza.
Could you hire me? Contact me if you like what I’ve done in this article and think I can create value for your company with my skills.
LEAVE A COMMENT
24 COMMENTS
http://imgur.com/EER6kim
na, mostmár csak azt kell tudni, hogy mikor szemetes.
Köszi az adatokat. A te plan cached kb. 500 megás. Ez ok lehet, ha van legalább 8 G RAM-ja az SQL szervernek. Csak az első két nagyot nézve 4,351 planből 2,687 csak egyszer volt használva, ezeket neveztem szemétnek. Ez a cache 61%-a. Azaz kb. a felére esne vissza a cache mérete, ha az optimize for ad hoc workload option be lenne kapcsolva. Ha sok ramod van ennek nincs jelentősége, de ha a proc cache jelentős részt eszik meg a RAM-ból, akkor ezzel az opcióval hatékonyabbá lehet tenni.
Ja, és még érdekes, mennyire sok sp hívás és plan újrahasznosítás történik. Ezekre erősen építenek az alkalmazások, nem annyira OR mapperesek?
http://i.imgur.com/fDeIjOD.jpg
Köszi, ez nagyon hasznos volt, mindig is érdekelt mi eszik 6+ GB RAM-ot az SQL szerveren:)
úgy gondolom az egyik multi-tenant EF6.1 app szorgos használatával hízik fel brutálisan. Átállítottam AdHoc-ra optimalizálásra, egyelőre nyugi van, alszanak az adminisztrátorok, holnap kiderül.
Koszi, ez mar combosabb cache. Atallitas utan azonnal leesett a cache merete? Ha nem, dbcc dropfreecache, de ezt ne nap kozben.
Szia Soci,
Köszi a fejtágítást! Már én is gondoltam, hogy ha ennyire fekete fehér hogy melyikre optimalizáljon akkor konkrétan minden lehetséges adhoc query-t átrakok sp-be.
igen, leesett – azóta nem is megy 10-15Mb fölé a stat szerint, pedig a használat napi szinten azonos. Egyébként az appok több rétegben cache-elik az adatot, így sql szinten ez totál fölösleges volt.
Liptak András: szerintem egyszerűbb átkapcsolni, mint mindent átírni.
Balint: 2G helyett 15M? Több lesz az majd, de remélhetőleg közel sem annyi, mint előtte. Ha kérhetem, majd pár nap múlva is nézd meg, és küldd el, meddig megy fel.
Balint: “Egyébként az appok több rétegben cache-elik az adatot, így sql szinten ez totál fölösleges volt.”
Ezt szerintem félreérted. Ez a procedure cache, nem pedig a buffer cache. Itt a végrehajtási tervek tárolódnak, nem az adatok.
dbcc dropfreecache??? ezt éles szerveren nem tenném! Ha cache bloat van azt több vonalon is lehet orvosolni, az egyik az optimize for ad-hoc workload beállítás.
Hali,
Az [Avg Use Count] oszlopot célszerű erre módosítani (nálunk elszállt “Arithmetic overflow error converting expression to data type int.” hibával): avg(cast(usecounts as bigint)) AS [Avg Use Count]
Zsolt
Janos: persze, ez csak egy mód, de ha nagy arányú az single plan-ek aránya, akkor könnyű vele könnyíteni a szerveren.
Kovacs Zsolt: köszi, átírom. Ezek szerint durva számok vannak nálatok.
Van, ami magas, de a vas kompenzál :) Viccet félre téve, nálunk is vannak “rosszul” megírt ezer éves alkalmazások, ahol egyes szervereken időnként felduzzasztják párszáz megára a singe plan-okat, hiába van bekapcsolva optimize for ad-hoc workload beállítás.
Csapjatok a kezemre, ha rosszul csinálom, de én ilyenkor simán használom a FREEPROCCACHE parancsot product környezetben, persze szigorúan mellé írva a plan_handle varbinary-t, amivel egyesével törölhető a felesleges plan. Példa a BOL-ból: DBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000);
http://imgur.com/8HXbLL8
Sziasztok!
Annyit kell tudni az adatbázisról, hogy 6-7 alkalmazás használja ismétlődő feladatokra. Az ismétlődő feladatokban az adatmennyiség változó, 1-2 nap egy ciklus.
Az sqlservr.exe working set-je 14,5 GB. Az Optimize for Ad-hoc Workloads ki van kapcsolva.
Zsolt: arra értettem, hogy fölösleges volt cache-elni a planeket :)
3 hét utáni helyzet:
http://i.imgur.com/3KaSkjR.png
memóriafoglalás 1-1.5GB (ez tök oké)
Bálint: köszi az infót, reméljük így is marad most már.
Gábor: 3 gigányi memória szabadulna fel, ha be lenne kapcsolva az adhoc.
Csendesebb időszakban érdemes lenne üríteni a plan cache-t:
DBCC FREESYSTEMCACHE(‘SQL Plans’)
Aztán érdekelne pár hét múlva, az adhoc bekapcsolása után mennyire mászik vissza a cache.
Kovacs Zsolt: pár száz mega azért nem sok, az normálisnak tűnik.
Akkor feltöltök én is egyet :)
http://imgur.com/DjFVO70
Kovacs Zsolt: ez picike, pár száz mega, nincs tele szeméttel. Köszi, hogy láttunk ilyet is.
Kilóra lehet, hogy pici, de az Adhoc single plan-ok mennyisége engem zavar. Csak még nem tudok vele mit kezdeni, de remélhetőleg rövidesen ez is megoldódik :)