存储过程的监控表DBA_proc_log

----------------------- 创建监控表 ----------------------------------创建存储过程(普通SQL也可以，但要改程序了) 记录表IF OBJECT_ID('[dbo].[DBA_proc_log]') IS NOT NULLDROP TABLE [dbo].[DBA_proc_log]GOCREATE TABLE [dbo].[DBA_proc_log]([logId] [bigint] IDENTITY(1,1) NOT NULL,[procName] [varchar](200) NOT NULL,[beginTime] [datetime] NOT NULL,[endTime] [datetime] NOT NULL,[elapsedSeconds]  AS (case when [endTime]<[beginTime] then (-1) else datediff(second,[beginTime],[endTime]) end),[errMsg] [nvarchar](max) NOT NULL,[remark] [nvarchar](max) NOT NULL,[succeeded]  AS (case when [endTime]<[beginTime] OR [errMsg]<>'' then (0) else (1) end),[checked] [bit] NOT NULL,PRIMARY KEY CLUSTERED ([logId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]goALTER TABLE [dbo].[DBA_proc_log] ADD  DEFAULT (getdate()) FOR [beginTime]ALTER TABLE [dbo].[DBA_proc_log] ADD  DEFAULT ('1900-01-01') FOR [endTime]ALTER TABLE [dbo].[DBA_proc_log] ADD  DEFAULT ('') FOR [errMsg]ALTER TABLE [dbo].[DBA_proc_log] ADD  DEFAULT ('') FOR [remark]ALTER TABLE [dbo].[DBA_proc_log] ADD  DEFAULT ((0)) FOR [checked]GO-------------------------  如何添加到存储过程 ------------------------------存储过程的开始部分添加DECLARE @logId BIGINTINSERT INTO [dbo].[DBA_proc_log]([procName]) VALUES('Proc_User_Online_GetList') --按实际的存储过程名SET @logId=@@IDENTITY;--存储过程中间部分--。。。--存储过程结尾部分UPDATE [dbo].[DBA_proc_log] SET endTime=GETDATE() WHERE logId=@logId----------------------- 查看两天以来，存储过程以小时分组的执行情况 --------------------------------;with t as (select procName,Convert(char(13),beginTime,120) as timeHour,avg(datediff(ms,beginTime,endTime)) as [avg_ms],min(datediff(ms,beginTime,endTime)) as [min_ms],max(datediff(ms,beginTime,endTime)) as [max_ms],count(1) as cntfrom dba_proc_log with(nolock)where beginTime>dateadd(day,-1, convert(char(10),getdate(),120))and procName='Proc_User_Online_GetList'and datepart(hour, beginTime) between 8 and 18and elapsedSeconds>-1group by procName,Convert(char(13),beginTime,120))select *,(select top 1 convert(char(5),beginTime,14) from dba_proc_log as b with(nolock) where datediff(ms,b.beginTime,b.endTime)=t.max_msand Convert(char(13),b.beginTime,120)=t.timeHour) as maxTimeSegfrom t order by left(timeHour,10) desc,[max_ms] desc