Soci (Soczó Zsolt) szakmai blogja

2015.05.21.

Szeméttel teli plan cache

Filed under: Adatbázisok,SQL Server,Szakmai élet — Soczó Zsolt @ 19:17

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 Comments

  1. http://imgur.com/EER6kim

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

    Comment by Liptak Andras — 2015.05.21. @ 22:38

  2. 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.

    Comment by Soczó Zsolt — 2015.05.21. @ 22:59

  3. 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?

    Comment by Soczó Zsolt — 2015.05.21. @ 23:01

  4. 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.

    Comment by balint — 2015.05.21. @ 23:18

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

    Comment by Soczó Zsolt — 2015.05.22. @ 06:58

  6. 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.

    Comment by Liptak Andras — 2015.05.22. @ 12:11

  7. 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.

    Comment by balint — 2015.05.22. @ 12:18

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

    Comment by Soczó Zsolt — 2015.05.22. @ 12:39

  9. 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.

    Comment by Soczó Zsolt — 2015.05.22. @ 12:41

  10. 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.

    Comment by Soczó Zsolt — 2015.05.22. @ 12:42

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

    Pingback by Soci (Soczó Zsolt) szakmai blogja » Blog Archive » SQL ad-hoc gyöngyszem — 2015.05.22. @ 13:49

  12. 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.

    Comment by János — 2015.05.26. @ 00:46

  13. 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

    Comment by Kovacs Zsolt — 2015.05.26. @ 17:34

  14. 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.

    Comment by Soczó Zsolt — 2015.05.26. @ 18:06

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

    Comment by Soczó Zsolt — 2015.05.26. @ 18:07

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

    Comment by Kovacs Zsolt — 2015.05.26. @ 20:59

  17. 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.

    Comment by Gabor — 2015.06.05. @ 13:08

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

    Comment by balint — 2015.06.05. @ 15:31

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

    Comment by Soczó Zsolt — 2015.06.05. @ 16:06

  20. 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.

    Comment by Soczó Zsolt — 2015.06.05. @ 16:16

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

    Comment by Soczó Zsolt — 2015.06.05. @ 16:18

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

    http://imgur.com/DjFVO70

    Comment by Kovacs Zsolt — 2015.06.08. @ 19:51

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

    Comment by Soczó Zsolt — 2015.06.08. @ 21:22

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

    Comment by Kovacs Zsolt — 2015.06.11. @ 18:45

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

Powered by WordPress