b性能下降時很多朋友都想監控到是哪個客戶端、哪個用戶、哪台客戶端發起的什麼會話sql語句,
但是微軟自帶的要使用profiler才能實現,但是考慮性能問題,很多人不願意!
網上有 很多腳本能監控到客戶端信息,但是唯獨不能獲取客戶端進程的sql語句!
我自己寫了一個,供
參考:
--1.新建存儲過程
--create proc prtest
--@spid
int
--as
--dbcc inputbuffer (@spid)
--go
-- 2.將結果保存到臨時變量#tmp
SELECT [Session ID] AS 會話ID ,
[Login] AS 用戶名 ,
[Database] AS 數據庫
,
[Task State] AS 狀態 ,
[Command] AS 命令 ,
[Application] AS 應用軟件 ,
[Wait Time (ms)] AS 等待時間 ,
[Wait Type] AS 等待類型 ,
[Host Name] AS 客戶機名
,
[Net Address] AS IP地址 INTO #tmp
FROM ( SELECT [Session ID] =
s.session_id ,
[User Process] = CONVERT(CHAR(1), s.is_user_process) ,
[Login] = s.login_name ,
[Database] = ISNULL(DB_NAME
(p.dbid), N'') ,
[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
WHEN r2.session_id IS NOT NULL
AND ( r.blocking_session_id = 0
OR r.session_id IS
NULL
) THEN '1'
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 (
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 )
) t
WHERE t.Command IN ( 'SELECT',
'UPDATE', 'DELETE' )