Activity Monitor streikt

Das Telefon steht nicht still, weil der SQL Server die Abfragen nur noch im Schneckentempo verarbeitet. Jetzt schnell einen Blick in den Activity Monitor werfen, um den Verursacher der Störung zu ermitteln und.... ausgerechnet jetzt streikt er. Was nun?

  • Man nutzt die undokumentierte Funktion
    exec sp_who2;
  • Man versucht mit den dynamischen Management-Views
    SELECT * FROM sys.dm_exec_requests;
    SELECT * FROM sys.dm_exec_sessions;
    SELECT * FROM sys.dm_exec_query_memory_grants;
    ans Ziel zu kommen und kann dadurch ggf. Sessions ermitteln, die weitere Abfragen blockieren
  • Oder man versucht, die Abfrage, die der Activity Monitor normaler weise absetzt, in einer Query abzusetzen:
-- Ich habe die eigentliche Abfrage mit einem SELECT umschlossen, um zusätzliche Filter- und
-- Sortierbedingungen leichter "andocken" zu können.

SELECT * FROM (
  SELECT 
     [Session ID]    = s.session_id, 
     [User Process]  = CONVERT(CHAR(1), s.is_user_process),
     [Login]         = s.login_name,   
     [Database]      = case when p.dbid=0 then N'' else ISNULL(db_name(p.dbid),N'') end, 
     [Task State]    = ISNULL(t.task_state, N''), 
     [Command]       = ISNULL(r.command, N''), 
     [Application]   = ISNULL(s.program_name, N''), 
     [Wait Time (ms)]     = ISNULL(w.wait_duration_ms, 0),
     [Wait Type]     = ISNULL(w.wait_type, N''),
     [Wait Resource] = ISNULL(w.resource_description, N''), 
     [Blocked By]    = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
     [Head Blocker]  = 
          CASE 
              -- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
              WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1' 
              -- session is either not blocking someone, or is blocking someone but is blocked by another party
              ELSE ''
          END, 
     [Total CPU (ms)] = s.cpu_time, 
     [Total Physical I/O (MB)]   = (s.reads + s.writes) * 8 / 1024, 
     [Memory Use (KB)]  = s.memory_usage * (8192 / 1024), 
     [Open Transactions] = ISNULL(r.open_transaction_count,0), 
     [Login Time]    = s.login_time, 
     [Last Request Start Time] = s.last_request_start_time,
     [Host Name]     = ISNULL(s.host_name, N''),
     [Net Address]   = ISNULL(c.client_net_address, N''), 
     [Execution Context ID] = ISNULL(t.exec_context_id, 0),
     [Request ID] = ISNULL(r.request_id, 0),
     [Workload Group] = ISNULL(g.name, N'')
  FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
  LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
  LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
  LEFT OUTER JOIN 
  (
      -- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as 
      -- waiting for several different threads.  This will cause that thread to show up in multiple rows 
      -- in our grid, which we don't want.  Use ROW_NUMBER to select the longest wait for each thread, 
      -- and use it as representative of the other wait relationships this thread is involved in. 
      SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
      FROM sys.dm_os_waiting_tasks 
  ) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
  LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
  LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g ON (g.group_id = s.group_id)
  LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)
) SessionMonitor
-- Zusätzliche Filter
WHERE 
  SessionMonitor.[User Process] = 1                       -- Nur Benutzerprozesse anzeigen
  -- AND SessionMonitor.[Database]='DUMMY'                -- Nur Sessions zu bestimmten Datenbanken anzeigen
-- Zusätzliche Sortierung
ORDER BY [Database], [Session ID];