/fdata%2F2470622%2Favatar-blog-1074652166-tmpphpjYbJuw.jpg){kind=avatar}
IO und CPU Auslastung durch einzelne Prozesse
Wie man alle Prozesse mit deren ausgeführten SQL Statements auflistet, hatte ich schon mal ausgeführt.
Die nächste Frage, die sich daraus ergibt ist, wer von denen verursacht die meiste Last auf dem Server und zwingt ihn in die Knie?
Mit einem statischen SELECT kann man es nicht ermitteln, aber eine einfache Variante zur Ermittlung ist das folgende Vorgehen:
- Man erstellt zunächst einen Snapshot von den aktuellen Prozessen, bei dem auch die Werte für CPU und IO Auslastung je Prozess enthalten ist
- Dann wartet man etwas, umd den Prozessen auch Zeit zum arbeiten zu geben
- Nun vergleicht man den vorherigen Snapshot mit dem aktuellen Zustand der Prozesse
Zum Vergleichen muss man also den Snapshot mit sys.sysprocesses JOINen. Dabei nur über die SPID zu gehen, ist zu ungenau, den in der Wartezeit könnte der Prozesse beendet worden und die gleiche SPID an einen andere Verbindung vergeben worden sein. Deshalb JOIN ich zusätzlich noch über die SID (User GUID), ECID (Kontext ID) und Login-Zeit.
Ausgegeben werden nur Prozesse, wo entweder der Wert von Cpu oder Physical_Io sich geändert hat.
Was jeder noch für sich entscheiden muss, ist die Sortierung; ich habe hier die Sortierung nach der Cpu Last gewählt, was nicht in jedem Fall der wichtigste Faktor für eine Auslastung darstellt.
Hat man einen alten, schwachen Rechner, wird vielleicht die Cpu den Falschenhals darstellen; bei einer langsamen IO und deren Anbindung wohl eher der Wert. Leider kann man die beiden Werte nicht in Relation bringen (CPU = Zeit, IO = Anzahl), so dass man die Werte nicht sinnvoll kombinieren kann, um es als einen Wert zur Sortierung zu nutzen.
-- Selektion, welcher Prozess am meisten CPU /IO Last verursacht IF NOT OBJECT_ID('tempdb..#processes') IS NULL
DROP TABLE #processes GO -- Aktuellen Snapshot erstellen SELECT spid, login_time, ecid, sid,
cpu, physical_io INTO #processes
FROM sys.sysprocesses
GO -- Kurz = 5 Sek warten, bis die Differenzwerte gebildet werden WAITFOR DELAY '00:00:05'
GO -- Selektion der Prozesse nach CPU/IO Last SELECT ACT.cpu - PST.cpu AS CpuDiff
,ACT.physical_io - PST.physical_io AS IoDiff ,ACT.spid ,DB.name AS DataBaseName ,ACT.hostname ,ACT.program_name ,ACT.loginame ,ACT.cmd ,EST.text AS SQLStatement FROM sys.sysprocesses AS ACT
INNER JOIN #processes AS PST ON ACT.spid = PST.spid AND ACT.ecid = PST.ecid AND ACT.sid = PST.sid AND ACT.login_time = PST.login_time INNER JOIN sys.databases AS DB ON ACT.dbid = DB.database_id CROSS APPLY sys.dm_exec_sql_text(ACT.sql_handle) AS EST WHERE (ACT.cpu - PST.cpu) > 0
OR (ACT.physical_io - PST.physical_io) > 0 ORDER BY CpuDiff DESC, ACT.cpu DESC
,IoDiff DESC, ACT.physical_io DESC GO DROP TABLE #processes
GO
/idata%2F2470622%2FDESCRIBE_SC1.jpg)
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
