Rozmnożenie sesji Activity Monitora

Z Activity Monitora SQL Servera korzystam raczej rzadko – i tak właściwie nie pokazuje mi tego, co mnie interesuje poza I/O na bazie. Jedną z przypadłości Activity Monitora jest rozmnożenie sesji w sekcji „Proceses”, na które nie zwróciłbym uwagi, gdyby nie kolega, który zdziwił się, że widzi wiele sesji, mimo że serwer ma ustawiony parametr MAXDOP 2. Skąd to rozmnożenie? (Sprawdzane na SQL Server 2012 SP2)

Żeby zobaczyć w jaki sposób Activity Monitor sprawdza dane dla zakładki „Processes” odpalamy SQL Profilera, tworzymy standardową sesję z szablonu i ją uruchamiamy. W SSMS sprawdzamy ustawienie Max degree of parallelism (w moim przypadku 4) i puszczamy jakieś polecenie, które wygeneruje nam zapytania równoległe (ja wykorzystałem tworzenie tabel dbo.bigProduct oraz dbo.bigTransactionHistory ze pomocą skryptu Adama Machanica ze strony http://sqlblog.com/blogs/adam_machanic/archive/2011/10/17/thinking-big-adventure.aspx).

Zapytanie chodzi przez kilka minut, to jest czas, żeby spokojnie uruchomić Activity Monitor i rozwinąć zakładkę „Processes” (dopiero wtedy wygenerowane zostanie zapytanie o procesy jakie chodzą na serwerze). Profiler pokazał zapytanie jak niżej:

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)
ORDER BY s.session_id;

Kluczowe dla rozmnożenia są dwie tabele: sys.dm_os_tasks oraz sys.sysprocesses. Pierwsza pokazuje wszystkie równolegle uruchomione zadania przydzielone do zapytania (rozróżniane przez exec_context_id czy task_address), druga jest wykorzystywana do pokazania nazwy bazy, na której działa zapytanie – ale też pokazuje jeden rekord dla każdego zadania (kolumna ecid). Rozmnożenie wynika z tego, że do każdego pojedynczego rekordu związanego z uruchomioną sesją (sys.dm_exec_sessions) dołączane są wszystkie związane z nią zadania, na które zostało podzielone zapytanie (łączenie jest tylko po session_id).

W moim przypadku daje to pięć rekordów z sys.dm_os_tasks(MAXDOP 4 oznacza cztery wątki plus jeden kontrolujący). Dodając pięć wątków z sys.sysprocesses, które łączymy wyłącznie po numerach sesji mamy 5 x 5 = 25 rekordów do pokazania w Activity Monitorze. Dla zjawiska na serwerze z MAXDOP = 2 sesja pojawiała się dziewięciokrotnie (3 rekordy z sys.dm_exec_sessions x 3 rekordy z sys.sysprocesses).

• sql server
• ssms