使用 t-SQL 获取 SQL Server 活动监视器的输出

Question

如何使用T-SQL获得SQL Server活动监视器的输出？？？

Answer 1

为了获得与活动监视器完全相同的输出；我已经创建了以下脚本

如果使用此脚本，则不需要sp_who2或活动监视器即可运行。

我创建的脚本将显示以下内容：

• [会话ID]
• [用户进程]
• [登录]
• [阻止人]
• [阻头器]
• [数据库名称]
• [任务状态]
• [命令]
• [statement_text]--它将显示当前正在执行的语句
• [command_text]-----它将显示存储过程的名称
• [总CPU(毫秒)]
• '运行时间(秒)'
• [等待时间(ms)]
• [等待类型]
• [等待资源]
• [内存使用量(KB)]
• [主机名]
• [网络地址]
• [工作量组]
• 【申请】

我的活动监视器的脚本如下：

/* ACTIVITY MONITOR'S OUTPUT along with statement_text and command_text */ /* Processes */ 
SELECT [Session ID] = s.session_id, 
[User Process] = CONVERT(CHAR(1), s.is_user_process), 
[Login] = s.login_name, 
[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,
[DatabaseName] = ISNULL(db_name(r.database_id), N''), 
[Task State] = ISNULL(t.task_state, N''), 
[Command] = ISNULL(r.command, N''), 
[statement_text] = Substring(st.TEXT, (r.statement_start_offset / 2) + 1, 
( ( CASE r.statement_end_offset WHEN - 1 THEN Datalength(st.TEXT)
ELSE r.statement_end_offset 
END - r.statement_start_offset ) / 2 ) + 1), ----It will display the statement which is being executed presently.
[command_text] =Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + Quotename(Object_name(st.objectid, st.dbid)), ''), -- It will display the Stored Procedure's Name.
[Total CPU (ms)] = r.cpu_time,
r.total_elapsed_time / (1000.0) 'Elapsed Time (in Sec)',
[Wait Time (ms)] = ISNULL(w.wait_duration_ms, 0),
[Wait Type] = ISNULL(w.wait_type, N''),
[Wait Resource] = ISNULL(w.resource_description, N''),
[Total Physical I/O (MB)] = (s.reads + s.writes) * 8 / 1024,
[Memory Use (KB)] = s.memory_usage * 8192 / 1024, 
--[Open Transactions Count] = 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] = N'',
[Application] = ISNULL(s.program_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.session_id = w.session_id)
AND w.row_num = 1 
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (r.session_id = r2.blocking_session_id) OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS st 
WHERE s.session_Id > 50 -- Ignore system spids.
ORDER BY s.session_id --,[Total CPU (ms)] desc ;

Answer 2

不太确定你在寻找什么，但这应该会给你在活动监视器上看到的类似的东西(不完全但相似)。

SELECT
P.spid,
RIGHT(CONVERT(VARCHAR, DATEADD(MS, DATEDIFF(MS, P.last_batch, GETDATE()), '1900-01-01'), 121), 12) AS [BATCH_DURATION],
P.program_name,
P.hostname AS HOST_NAME,
P.loginame AS LOGIN_NAME
FROM master.dbo.sysprocesses AS P
WHERE 
P.spid > 50 AND
P.status NOT IN ('background', 'sleeping') AND
P.cmd NOT IN 
(
'AWAITING COMMAND',
'MIRROR HANDLER',
'LAZY WRITER',
'CHECKPOINT SLEEP',
'RA MANAGER'
)
ORDER BY 2

我们寻找SPID > 50的原因是ID小于50的进程属于内部操作。任何大于50的值都应该用于用户操作。

此外，你可以在数据库上看到所有的阻塞等，你可以尝试这样的东西。

SELECT
db.name AS DB_NAME,
tl.request_session_id AS REQUESTING_SESSION,
wt.blocking_session_id AS BLOCKING_SESSION,
OBJECT_NAME(p.OBJECT_ID) AS BLOCKED_OBJECT,
tl.resource_type AS RESOURCE_TYPE,
h1.TEXT AS REQUEST_QUERY,
h2.TEXT AS BLOCKING_QUERY,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address =     wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id =     tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id =     wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

你可以把这些结合起来，得到你想要的东西。希望这能有所帮助。