[实战]分钟级简易数据库执行日志

相信很多童鞋都知道数据库忽然性能下来的时候，在没有其他辅助工具的情况下，会登上去跑个sp_who2来看看就行什么东西block住了，那么其实就可以用这个东西来做文章了。直接贴段代码吧，核心的出自Kent之手，我只是把他变成个SP，并起了个SQL Schedule Job设置成每分钟跑一次，跑挂了不要紧，下一分钟再跑就行，我们要的只是简易日志。这样就不用说抢着时间点去跑sp_who2了。出来的效果大概是这样，最后一列eventinfo就是在跑什么，就连schedule job的step id都能出来。enjoy :) 

 

CREATE PROC [dbo].[usp_log_sp_who2] 
AS
BEGIN

DECLARE @tempTable TABLE (SPID INT,Status VARCHAR(255),
      [Login]  VARCHAR(255),HostName  VARCHAR(255),
      BlkBy  VARCHAR(255),DBName  VARCHAR(255),
      Command VARCHAR(255),CPUTime INT,
      DiskIO INT,LastBatch VARCHAR(255),
      ProgramName VARCHAR(255),SPID2 INT,
      REQUESTID INT, 
      [parameters] SMALLINT, eventinfo NVARCHAR(MAX));

INSERT INTO @tempTable (spid, [status], [login], hostname, blkby, dbname, command, cputime, diskio, lastbatch, programname, spid2, requestid) EXEC sp_who2
DECLARE @t1 TABLE (eventtype nvarchar(MAX), [parameters] SMALLINT, eventinfo NVARCHAR(MAX))
DECLARE @sql NVARCHAR(MAX) 
DECLARE @spid INT, @parameters smallint, @eventinfo NVARCHAR(MAX)
DECLARE curTempTable CURSOR FOR SELECT spid FROM @tempTable FOR UPDATE OF [parameters], [eventinfo]

OPEN curTempTable
FETCH curTempTable INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
   DELETE FROM @t1
   SET @sql = N'dbcc inputbuffer(' + CONVERT(NVARCHAR(MAX), @spid) + N')'
   INSERT @t1 EXEC(@sql)
   SELECT TOP 1 @parameters = [parameters], @eventinfo = [eventinfo] FROM @t1
   UPDATE @tempTable SET [parameters] = @parameters, [eventinfo] = @eventinfo WHERE CURRENT OF curTempTable
   FETCH NEXT FROM curTempTable INTO @spid
END
CLOSE curTempTable

DECLARE @now DATETIME = GETDATE()
DELETE LOG_SP_WHO2 WHERE [Branch]<DATEADD(day, -7, @now)
--这里-7其实随你喜欢，我只是需要一周内的日志而已，所以就把一周前的删除了

INSERT INTO LOG_SP_WHO2
SELECT @now, * FROM @tempTable 

END

--基础表
CREATE TABLE [dbo].[LOG_SP_WHO2](
    [Branch] [datetime] NULL,
    [SPID] [int] NULL,
    [Status] [varchar](255) NULL,
    [Login] [varchar](255) NULL,
    [HostName] [varchar](255) NULL,
    [BlkBy] [varchar](255) NULL,
    [DBName] [varchar](255) NULL,
    [Command] [varchar](255) NULL,
    [CPUTime] [int] NULL,
    [DiskIO] [int] NULL,
    [LastBatch] [varchar](255) NULL,
    [ProgramName] [varchar](255) NULL,
    [SPID2] [int] NULL,
    [REQUESTID] [int] NULL,
    [parameters] [smallint] NULL,
    [eventinfo] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

--索引的随君所愿，我这里只是建了个按时间的聚集索引（非唯一）
CREATE CLUSTERED INDEX [IX_LOG_SP_WHO2] ON [dbo].[LOG_SP_WHO2]
(
    [Branch] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO