Szeméttel teli plan cache

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.

24 Responses to “Szeméttel teli plan cache”

  1. Liptak Andras Says:

    http://imgur.com/EER6kim

    na, mostmár csak azt kell tudni, hogy mikor szemetes.

  2. Soczó Zsolt Says:

    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.

  3. Soczó Zsolt Says:

    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?

  4. balint Says:

    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.

  5. Soczó Zsolt Says:

    Koszi, ez mar combosabb cache. Atallitas utan azonnal leesett a cache merete? Ha nem, dbcc dropfreecache, de ezt ne nap kozben.

  6. Liptak Andras Says:

    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.

  7. balint Says:

    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.

  8. Soczó Zsolt Says:

    Liptak András: szerintem egyszerűbb átkapcsolni, mint mindent átírni.

  9. Soczó Zsolt Says:

    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.

  10. Soczó Zsolt Says:

    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.

  11. Soci (Soczó Zsolt) szakmai blogja » Blog Archive » SQL ad-hoc gyöngyszem Says:

    […] Előzmény. […]

  12. János Says:

    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.

  13. Kovacs Zsolt Says:

    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

  14. Soczó Zsolt Says:

    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.

  15. Soczó Zsolt Says:

    Kovacs Zsolt: köszi, átírom. Ezek szerint durva számok vannak nálatok.

  16. Kovacs Zsolt Says:

    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);

  17. Gabor Says:

    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.

  18. balint Says:

    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é)

  19. Soczó Zsolt Says:

    Bálint: köszi az infót, reméljük így is marad most már.

  20. Soczó Zsolt Says:

    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.

  21. Soczó Zsolt Says:

    Kovacs Zsolt: pár száz mega azért nem sok, az normálisnak tűnik.

  22. Kovacs Zsolt Says:

    Akkor feltöltök én is egyet :)

    http://imgur.com/DjFVO70

  23. Soczó Zsolt Says:

    Kovacs Zsolt: ez picike, pár száz mega, nincs tele szeméttel. Köszi, hogy láttunk ilyet is.

  24. Kovacs Zsolt Says:

    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 :)