GO
SETANSI_PADDING ON
GO
CREATETABLE[dbo].[dc_info_SessionConn](
[batch_id][int]NULL,
[session_id][int]NULL,
[blocking_session_id][int]NULL,
[textdata][varchar](max) NULL,
[login_name][varchar](30) NULL,
[host_name][varchar](100) NULL,
[database_name][varchar](30) NULL,
[program_name][varchar](200) NULL,
[command][varchar](100) NULL,
[status][varchar](20) NULL,
[cpu_time][int]NULL,
[memory_usage_kb][int]NULL,
[reads][int]NULL,
[writes][int]NULL,
[transaction_isolation_level][int]NULL,
[connect_time][datetime]NULL,
[last_read][datetime]NULL,
[last_write][datetime]NULL,
[net_transport][varchar](20) NULL,
[client_net_address][varchar](30) NULL,
[client_tcp_port][int]NULL,
[local_tcp_port][int]NULL,
[start_time][datetime]NULL,
[wait_time][int]NULL,
[last_wait_type][varchar](100) NULL,
[wait_resource][varchar](1000) NULL,
[open_transaction_count][int]NULL,
[create_date][datetime]NULL
) ON[PRIMARY]
GO
SETANSI_PADDING OFF
GO
2. 定义性能收集的存储过程：
USE[master]
GO
--数据库的阻塞和锁信息，该SP可以记录session中显示不出的信息
createprocedure[dbo].[usp_blocker_info](@batch_idint)
as
begin
setnocount on
declare@spidsmallint,@blockedsmallint
declare@c_SQLnvarchar(4000),@b_SQLnvarchar(4000)
declare@idint
--保存Sysprocesses 的内容