SQL teljesítményelemzés morzsák

Az egyik visszatérő kérdés: mit nézzek, ha baj van? Erre több megközelítés is lehetséges: lehet az Indexet, majd elmúlik a baj; esetleg olvasgatni dokumentációt vagy guglizni; vagy álláskereső portált nézni; netán a szerver teljesítményét megvizsgálni.

Ja, de hogyan? Hát, elmondom, hogy én kb. miket csinálok, amikor döglődik az SQL. Ez részben szisztematikus, részben nem, ugyanis vannak dolgok, amiket lehet párhuzamosan csinálni, vagy felcserélhetőek, vagy esetleg egyik a másikat feleslegessé teszi, vagy valami tök más is beugorhat, ha valami tök mást látok. De mondjuk, hogy az esetek 85%-ában az általam üzemeltetett szerverek 90%-a ilyenekkel kezelhető. Ezek kis ötletgombócok, nem egy script, amit le kell futtatni.

Először is: ne csak performance countert nézzünk, hanem minden mást is. Tipikusan DMV-ket és perfocuntert. Az activity monitort csak indikátornak ajánlom: ha működik, akkor nem lehet nagy baj. Valahogy igen erőforrásigényes szegényke, és amikor jön a terhelés, rendszeresen aszongya, h timeout. Én a következő útvonalon haladok:

Kezdetnek írjuk fel egy papírra, és tegyük a monitor mellé: Memória, CPU, Diszk I/O. Ez a három dolog kell a SQL-nak. Egyszerű rendszer, egyszerű lelkeknek.

  • Oprendszer CPU és memória. Ha ki van ültetve a CPU vagy nincs szabad memória, akkor már világos, hogy a fizikai erőforrás fogyott el. Megosztott gépen kérdés, h mennyi jutott a SQL-nek.
  • Ha véletlenül működik az activity monitor, akkor a recent expensive queries gombócot érdemes megnézni, a végrehajtások számával együtt!
  • Megnézem, h hova lett az az erőforrás, amiből kifutottunk. Extrém esetekben lehet, h van szabad fizikai erőforrás, ebben az esetben a háromság harmadik lábát, a diszk I/O-t is érdemes megnézni. Azaz perfcounter, Physical disk – Avg Disk (Read/Write) queue Length. Emellett hasznos lehet a % Disk Read/Write time is, DE vegyük be a counterek közé a % Idle time-ot is, mert a % Disk Read/Write time hajlamos többet hazudni, mint a tényleges (azaz az idle a megbízható a történetben).
  • A háromból az egyik tuti nem jó. Megnézem hogy ki gyilkolja azt. Ezt különféle DMV-kből lehet kiszippantani, kezdjük barátunkkal, a sys.dm_exec_sessions nevűvel. A memory_usage oszlop adja magát, ez 8KB-os lapokban mondja meg a használatot, és vele a legegyszerűbb dolgozni. A cpu_usage, a reads és writes meg azért nehezebb, mert ott bizony változást kell számolni, mivel az aktuális érték semmitmondó magában. Kétmillió olvasás két hét alatt azért nem olyan sok például. Ezt persze a sysprocessesből is ki lehet venni, majdnem ugyanaz, nekem még erre van egy scriptem:
    if object_id('tempdb..##c1') is not null
    begin
    drop table ##c1 drop table ##c2
    end
    select session_id, login_time, sum(cpu_time) cpu, sum(reads) reads, sum(writes) writes into ##c1 from sys.dm_exec_sessions group by session_id, login_time
    waitfor delay '000:00:10'
    select session_id, login_time, sum(cpu_time) cpu, sum(reads) reads, sum(writes) writes  into ##c2 from sys.dm_exec_sessions s group by session_id, login_time
    select c2.cpu-c1.cpu cpudiff, c2.reads-c1.reads [read], c2.writes-c1.writes write, p.memory_usage/128 mem_used_MB, p.login_name, p.cpu_time, p.*
    from sys.dm_exec_sessions p
    join ##c1 c1
    on p.session_id= c1.session_id
    join ##c2 c2
    on p.session_id= c2.session_id
    where
    c2.cpu > c1.cpu
    or c2.writes > c1.writes
    or c2.reads > c1.reads
    or p.memory_usage > 15*128 -- 15 MB
    order by c2.cpu-c1.cpu desc
    -- majd tessék dropni a temptáblát. Azért nincs itt a végén, hátha bogarászni akarunk még bennük
    
  • Ekkorra már nagyjából illene, hogy legyen sejtésünk a drágaság forrásáról, legalább olyasmi, h melyik alkalmazás. Jöhet a DBCC INPUTBUFFER() vagy nekieshetünk profilerrel is, ízlés dolga. Aztán egyszer eljutunk egy objektumhoz. Ha tempdb, akkor valaki valami beteg joinokat csinál általában, nézzünk ilyen szemmel a futtatott kverire. Ha pedig vmi user tábla, akkor vagy full table scan futkos rajta sokat, vagy kéne venni memóriát.
  • Személyes tapasztalatom az, hogy az esetek 30%-ában valaki kiülteti a procit, azt a fenti script lokalizálja, aztán a kill paranccsal orvoslom. 70% az erős memóriahiányos állapot, ekkor meg a BOL-ból kiveszek két scriptet, a sys.dm_os_buffer_descriptors oldaláról: az első (azaz A.) megmondja, hogy mennyi memóriát esznek az adatbázisaim:
    		SELECT count(*)AS cached_pages_count
        ,CASE database_id 
            WHEN 32767 THEN 'ResourceDb' 
            ELSE db_name(database_id) 
            END AS Database_name
    FROM sys.dm_os_buffer_descriptors
    GROUP BY db_name(database_id) ,database_id
    ORDER BY cached_pages_count DESC;
    

    Itt nézzük meg, hogy az összeg meg a használt memória stimmelnek-e nagyjából. Pl. elszúrt alkalmazás tele tudja hányni a procedure cache-t, ami meghízik szép csendben két gigára, aztán néz az ember, mert az adatbázis lapoknak nem maradt hely. A B kveri meg ugyanezt tudja, adatbázis szinten. Itt is jól látszik, h ki lakik a memóriában, még az is, h melyik index (vagy a tábla maga). Ilyenkor lehet kiválóan procedure cache-t üríteni (DBCC CLEANPROCCACHE – ez az egész szerverre lefut, szóval odavághat kicsit a teljesítménynek, mert mindent újra kell fordítani, de itt talán már mindegy).

Majd valaki rúgjon oldalba, ha még idén nem írok a latchekről is valamit.

5 Comments

  1. Gubus:

    Szia!

    Az első scriptben van pár pici elírás, javítottam.

    G.

    If object_id (‘tempdb..##c1’) is not null
    begin
    drop table ##c1 drop table ##c2
    end
    select session_id, login_time, sum(cpu_time) cpu, sum(reads) reads, sum(writes) writes into ##c1 from sys.dm_exec_sessions group by session_id, login_time
    waitfor delay ‘000:00:10’
    select session_id, login_time, sum(cpu_time) cpu, sum(reads) reads, sum(writes) writes into ##c2 from sys.dm_exec_sessions s group by session_id, login_time
    select c2.cpu-c1.cpu cpudiff, c2.reads-c1.reads [read], c2.writes-c1.writes write, p.memory_usage/128 mem_used_MB, p.login_name, p.cpu_time, p.*
    from sys.dm_exec_sessions p
    join ##c1 c1
    on p.session_id= c1.session_id
    join ##c2 c2
    on p.session_id= c2.session_id
    where
    c2.cpu > c1.cpu
    or c2.writes > c1.writes
    or c2.reads > c1.reads
    or p.memory_usage > 15*128 — 15 MB
    order by c2.cpu-c1.cpu desc
    — majd tessék dropni a temptáblát. Azért nincs itt a végén, hátha bogarászni akarunk még bennük

  2. Erik:

    Köszönöm az észrevételt, javítottam. Valószínűleg mégsem akkora ötlet SQL blogot írni SQL szerver nélkül… :)

  3. Frank:

    Szia!

    Csak érdeklődnék, hogy várhatunk e bejegyzést ezzel kapcsolatban:
    “Majd valaki rúgjon oldalba, ha még idén nem írok a latchekről is valamit.”

    F.

  4. Erik:

    Szia!

    Ertem en a celzast, sajnos egyelore meg nem jutottam el abba a magassagba, hogy biztosan eletben maradjak. Ha stabilizaltam az allapotomat, mindenkeppen megteszem. Amugy en sem tudok rola sokat, de azt a nem sokat eredmenyesen hasznalom :)

    Erik

  5. Frank:

    Köszi szépen!!! akkor türelmesen várok tovább :)

Leave a comment