获得客户端详细信息以及每个进程的sql语句

原创

ocpyang 2012-02-08 09:30:00 博主文章分类：sqlserver ©著作权

文章标签 sql session join null transactions tsql 文章分类 数据库

©著作权归作者所有：来自51CTO博客作者ocpyang的原创作品，请联系作者获取转载授权，否则将追究法律责任

db性能下降时很多朋友都想监控到是哪个客户端、哪个用户、哪台客户端发起的什么会话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' )

--3.创建保存结果的临时表

CREATE TABLE #jttest(
[会话ID] [smallint] NOT NULL,
[用户名] [nvarchar](128) NOT NULL,
[数据库] [nvarchar](128) NOT NULL,
[状态] [nvarchar](60) NOT NULL,
[命令] [nvarchar](16) NOT NULL,
[应用软件] [nvarchar](128) NOT NULL,
[等待时间] [bigint] NOT NULL,
[等待类型] [nvarchar](60) NOT NULL,
[客户机名] [nvarchar](128) NOT NULL,
[IP地址] [varchar](48) NOT NULL,
[TSQL] [varchar](4000) NULL
)

--4.将#tmp中值导入到临时表变量#jttest

INSERT INTO #jttest
           ([会话ID]
           ,[用户名]
           ,[数据库]
           ,[状态]
           ,[命令]
           ,[应用软件]
           ,[等待时间]
           ,[等待类型]
           ,[客户机名]
           ,[IP地址]
           )
           SELECT

           [会话ID]
           ,[用户名]
           ,[数据库]
           ,[状态]
           ,[命令]
           ,[应用软件]
           ,[等待时间]
           ,[等待类型]
           ,[客户机名]
           ,[IP地址]
           FROM #tmp

--5.获得每个spid对应的TSQL语句

create   table   #tmp01(
EventType   nvarchar(100),
Parameters   Int,
EventInfo   nvarchar(max)
)
declare @cursid int
declare cur cursor for select [会话ID] from #jttest
open cur
fetch next from cur into @cursid
while @@FETCH_STATUS=0
begin
insert   into   #tmp01   exec   prtest   @cursid
UPDATE #jttest SET TSQL=(SELECT EventInfo FROM #tmp01 )
WHERE 会话ID=@cursid
truncate table #tmp01
fetch next from cur into @cursid
end
close cur
deallocate cur
DROP TABLE #tmp01