В AX 2012 была добавлена возможность прописывать информацию о пользователе и его сессии в Аксапте в контекст SQL-соединения, за счет чего стало возможно со стороны СУБД понимать, какое соединение к кому относится, см.
Troubleshooting database performance [AX 2012]. Заполнение контекста чуть-чуть снижает производительность, но это с лихвой компенсируется удобством мониторинга. После перезапуска АОСа с новой настройкой указанную информацию о сессии, доступную через sys.dm_exec_sessions.context_info, можно разбирать, к примеру, так:
PHP код:
WITH DM_EXEC_SESSION_PARSE (session_id, ax_user, ax_s)
AS (
SELECT
session_id,
SUBSTRING(CAST(ss.CONTEXT_INFO as varchar(128)), 2, PATINDEX('% %',
LTRIM(CAST(ss.CONTEXT_INFO as varchar(128))))) as ax_user,
CAST(SUBSTRING(LTRIM(CAST(ss.CONTEXT_INFO as varchar(128))),
PATINDEX('% %', LTRIM(CAST(ss.CONTEXT_INFO as varchar(128)))) + 1,
PATINDEX('% %', SUBSTRING(LTRIM(CAST(ss.CONTEXT_INFO as varchar(128))),
PATINDEX('% %', LTRIM(CAST(ss.CONTEXT_INFO as varchar(128)))) + 1,
LEN(CAST(ss.CONTEXT_INFO as varchar(128))))) - 1) as int) as ax_s
FROM sys.dm_exec_sessions ss
WHERE program_name in ('Microsoft Dynamics AX')
AND CAST(CONTEXT_INFO as varchar(128)) > ''
),
DM_EXEC_SESSION_FILTER (session_id, database_id, [status], threads)
AS (
SELECT
s.session_id
,s.database_id
,s.[status]
,count(*) as threads
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r
ON (s.session_id = r.session_id AND r.sql_handle IS NOT NULL AND r.plan_handle IS NOT NULL)
OR (s.session_id = r.blocking_session_id)
JOIN sys.sysprocesses sp
ON s.session_id = sp.spid
GROUP BY s.session_id, s.database_id, s.status
)
SELECT
ss.session_id AS [sid]
,COALESCE(r.blocking_session_id, 0) AS [blck]
,ss.threads
,COALESCE(ss_ctx.ax_user, '') AS [ax_user]
,COALESCE(ss_ctx.ax_s, '') AS [ax_s]
,COALESCE(r.wait_time, 0) AS [wait_time]
,COALESCE(r.wait_type, '') AS [wait_type]
,COALESCE(r.command, '') AS [command]
,ss.status as [status]
,COALESCE(DB_NAME(ss.database_id), '') as [db_name]
,CASE WHEN r.plan_handle IS NOT NULL THEN COALESCE((SELECT TOP 1 query_plan FROM sys.dm_exec_query_plan(r.plan_handle)), '') ELSE '' END AS [query_plan]
,CASE WHEN r.sql_handle IS NOT NULL THEN COALESCE((SELECT TOP 1 text FROM sys.dm_exec_sql_text(r.sql_handle)), '') ELSE '' END AS [query]
FROM DM_EXEC_SESSION_FILTER ss
LEFT JOIN sys.sysprocesses sp
ON ss.session_id = sp.spid
AND sp.loginame <> ''
LEFT JOIN sys.dm_exec_requests r
ON ss.session_id = r.session_id
LEFT JOIN DM_EXEC_SESSION_PARSE ss_ctx
ON ss_ctx.session_id = ss.session_id
Код:
sid blck threads ax_user ax_s wait_time wait_type command status db_name query_plan query
---- ----- -------- -------- ----- ---------- ---------- -------- -------- -------- ------------------------------------ ----------
223 0 1 userId 123 0 SELECT running AX60_DB <ShowPlanXML xmlns="http://schema... SELECT ...