Két tuti módszer a betöltési anomáliák azonosítására

Egyik ügyfelemnél Isten tudja miért elkezdtek hektikusan ingadozni a betöltési idők. Először csak néhány kiemelt napon lassult le az üzleti intelligencia rendszer betöltése, aztán eljött az az időszak, amikor már szinte egyetlen nap sem futott le a megadott időpontra a betöltés. Eljött hát az idő a beavatkozásra...

Az ETL folyamat időszükséglete

 

A fenti grafikon azt mutatja, hogy hogyan alakul a napi betöltés időszükséglete idősorosan, az elmúlt 13 hónapban. A grafikonról leolvashatjuk, hogy

  • A betöltés időszükséglete a (csúcsokat kivéve) novemberig szinte változatlan volt, novembertől azonban elkezdett nőni, és azóta is folyamatosan nő
  • február hónapban szinte alig volt olyan nap, amikor munkakezdésre előálltak volna a friss adatok. (a munkakezdés időpontját jelöli a piros vonal)
  • A betöltési idők szórása jelentősen, kb. 3 szorosára nőtt az utóbbi 3 hónapban az előző 8 hónaphoz képest.

Mindezekből tisztán látszik, hogy valami nincs rendben. Hardver hiba? A forrásrendszerek oldalán kell keresni az okokat? Vagy a megnövekedett adat mennyiség eredménye képen állt elő ez az állapot? Egyelőre nem tudni. De az már valószínűsíthető, hogy több tényező együttes megváltozása okozhatja a problémát.

Egy ilyen hektikusan változó rendszerben nagyon nehéz megkeresni a lassulás okát. Nem is tudnék általános szabályt mondani arra, hogy mit tegyen, ha hasonló helyzetbe kerül. Épp ezért olyan dolgokról fogok írni helyette, amire biztos hogy szüksége lesz.

Amikor elkezdi a nyomozást két kérdésre kell választ találnia:

  1. Melyik lekérdezések futottak sokáig
  2. Melyik lekérdezések futnak most.

Melyik lekérdezések futnak sokáig?

Az első, amire kíváncsiak leszünk, hogy vajon melyik lekérdezések futottak sokáig. Hiába van kifinomult naplózási technikánk, ha egy SSIS csomagban a data flow-ban párhuzamosan futnak a lekérdezések, akkor csak a data flow sebességét fogjuk tudni mérni, az egyes részfeladatok sebességét már nem.

De nem kell kétségbeesni, mert az SQL server cache-eli a lekérdezési terveket, így azokat remekül fel lehet használni annak eldöntésé, hogy melyik lekérdezések futnak sokáig:

SELECT creation_time

,last_execution_time

,total_physical_reads

,total_logical_reads

,total_logical_writes

, execution_count

, total_worker_time

, total_elapsed_time

, total_elapsed_time/execution_count avg_elapsed_time

,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,

((CASE statement_end_offset

WHEN -1 THEN DATALENGTH(st.text)

ELSE qs.statement_end_offset END

- qs.statement_start_offset)/2) + 1) AS statement_text

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

ORDER BY total_elapsed_time / execution_count DESC;

 

megj: Tudta, hogy SQL 2005-től össze lehet kapcsolni a táblákat és a tábla értékű (Table Valued function) függvényeket? Erre szolgál az APPLY kifejezés és a fenti lekérdezésben a sys.dm_exec_query_stats rendszernézetet kapcsoljuk össze a sys.dm_exec_sql_text függvénnyel.

Futó lekérdezések monitorozása

A másik dolog, amire szükségünk lesz, az egy monitor, amin keresztül nézhetjük, hogy melyik lekérdezések futnak, ki- kit blokkol, melyik lekérdezés kapott erőforrás hiány miatt „RESOURCE_SEMAPHORE”-t, stb.

Erre egy nagyon jó eszköz a master adatbázisban tárolt sysprocesses rendszertábla, amely - összekapcsolva a sys.dm_exec_sql_text() függvénnyel – megadja mindazon információkat, amelyre az optimalizációhoz szükségünk lesz.

Select sp.*, st.[text]

FROM master..sysprocesses AS sp

CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) as st

 

Sikeres stabilizálást!

Kővári Attila - BI projekt

Új hozzászólás