SQL Server定时自动抓取耗时SQL并归档数据脚本分享

第一步建库

USE [master]
GOCREATE DATABASE [MonitorElapsedHighSQL]
GO

第二步创建sp_who3存储过程

-- http://sqlserverplanet.com/dba/a-better-sp_who2-using-dmvs-sp_who3USE [MonitorElapsedHighSQL]
GOCREATE PROCEDURE [dbo].[sp_who3] AS
BEGINSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSELECT
    SPID                = er.session_id
    ,BlkBy              = CASE WHEN lead_blocker = 1 THEN -1 ELSE er.blocking_session_id END
    ,ElapsedMS          = er.total_elapsed_time
    ,CPU                = er.cpu_time
    ,IOReads            = er.logical_reads + er.reads
    ,IOWrites           = er.writes
    ,Executions         = ec.execution_count
    ,CommandType        = er.command
    ,LastWaitType       = er.last_wait_type
    ,ObjectName         = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)
    ,SQLStatement       =
        SUBSTRING
        (
            qt.text,
            er.statement_start_offset/2,
            (CASE WHEN er.statement_end_offset = -1
                THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
                ELSE er.statement_end_offset
                END - er.statement_start_offset)/2
        )
    ,STATUS             = ses.STATUS
    ,[Login]            = ses.login_name
    ,Host               = ses.host_name
    ,DBName             = DB_Name(er.database_id)
    ,StartTime          = er.start_time
    ,Protocol           = con.net_transport
    ,transaction_isolation =
        CASE ses.transaction_isolation_level
            WHEN 0 THEN 'Unspecified'
            WHEN 1 THEN 'Read Uncommitted'
            WHEN 2 THEN 'Read Committed'
            WHEN 3 THEN 'Repeatable'
            WHEN 4 THEN 'Serializable'
            WHEN 5 THEN 'Snapshot'
        END
    ,ConnectionWrites   = con.num_writes
    ,ConnectionReads    = con.num_reads
    ,ClientAddress      = con.client_net_address
    ,Authentication     = con.auth_scheme
    ,DatetimeSnapshot   = GETDATE()
    ,plan_handle        = er.plan_handle
FROM sys.dm_exec_requests er
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
OUTER APPLY
(
    SELECT execution_count = MAX(cp.usecounts)
    FROM sys.dm_exec_cached_plans cp
    WHERE cp.plan_handle = er.plan_handle
) ec
OUTER APPLY
(
    SELECT
        lead_blocker = 1
    FROM master.dbo.sysprocesses sp
    WHERE sp.spid IN (SELECT blocked FROM master.dbo.sysprocesses)
    AND sp.blocked = 0
    AND sp.spid = er.session_id
) lb
WHERE er.sql_handle IS NOT NULL
AND er.session_id != @@SPID
ORDER BY
    CASE WHEN lead_blocker = 1 THEN -1 * 1000 ELSE -er.blocking_session_id END,
    er.blocking_session_id DESC,
    er.logical_reads + er.reads DESC,
    er.session_idEND

第三步创建[usp_checkElapsedHighSQL]存储过程

USE [MonitorElapsedHighSQL]
GO
/****** Object:  StoredProcedure [dbo].[usp_checkElapsedHighSQL]    Script Date: 2015/6/19 15:22:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO--创建存储过程
CREATE  PROCEDURE [dbo].[usp_checkElapsedHighSQL] ( @SessionID INT )
AS
    BEGIN        IF  ( SELECT  OBJECT_ID('MonitorElapsedHighSQL.dbo.ElapsedHigh') ) IS NULL
            BEGIN
                 CREATE TABLE [MonitorElapsedHighSQL].[dbo].[ElapsedHigh]
                    (
                      id INT IDENTITY(1, 1)   PRIMARY KEY ,
                      [SPID] SMALLINT ,
                      [ElapsedMS] INT ,
                      [IOReads] BIGINT ,
                      [IOWrites] BIGINT ,
                      [DBName] NVARCHAR(128) ,
                      [plan_handle] VARBINARY(64) ,
                      [paramlist] NVARCHAR(MAX) ,
                      [planstmttext] NVARCHAR(MAX) ,
                      [stmttext] NVARCHAR(MAX) ,
                      [xmlplan] XML,
                      [gettime] DATETIME
                    )
            END        IF  ( SELECT  OBJECT_ID('MonitorElapsedHighSQL.dbo.ElapsedHigh') ) IS NOT NULL
            BEGIN                SET NOCOUNT ON                 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED                DECLARE @Duration INT -- in milliseconds, 10000 = 10 sec
                DECLARE @now DATETIME
                DECLARE @plan_handle VARBINARY(64)
                DECLARE @ElapsedMS INT
                DECLARE @SPID INT
                DECLARE @IOReads BIGINT
                DECLARE @IOWrites BIGINT
                DECLARE @DBName NVARCHAR(128)
                DECLARE @planstmttext NVARCHAR(MAX)
                DECLARE @stmttext NVARCHAR(MAX)
                DECLARE @paramlist NVARCHAR(MAX)
                DECLARE @plan_xml XML
                DECLARE @paramtb TABLE
                    (
                      paramlist NVARCHAR(MAX) ,
                      planstmttext NVARCHAR(MAX)
                    )
                DECLARE @paramtb2 TABLE
                    (
                      paramlist NVARCHAR(MAX) ,
                      planstmttext NVARCHAR(MAX)
                    )                SELECT  @Duration = 10000  --★Do -- in milliseconds, 10000 = 10 sec                IF OBJECT_ID('tempdb..#ElapsedHigh') IS NOT NULL
                    BEGIN
                        DROP TABLE [#ElapsedHigh]  --删除临时表
                    END --建临时表
                CREATE TABLE [#ElapsedHigh]
                    (
                      [SPID] SMALLINT ,
                      [BlkBy] INT ,
                      [ElapsedMS] INT ,
                      [CPU] INT ,
                      [IOReads] BIGINT ,
                      [IOWrites] BIGINT ,
                      [Executions] INT ,
                      [CommandType] NVARCHAR(40) ,
                      [LastWaitType] NVARCHAR(60) ,
                      [ObjectName] NVARCHAR(1000) ,
                      [SQLStatement] NVARCHAR(MAX) ,
                      [STATUS] NVARCHAR(30) ,
                      [Login] NVARCHAR(128) ,
                      [Host] NVARCHAR(128) ,
                      [DBName] NVARCHAR(128) ,
                      [StartTime] DATETIME ,
                      [Protocol] NVARCHAR(40) ,
                      [transaction_isolation] NVARCHAR(100) ,
                      [ConnectionWrites] INT ,
                      [ConnectionReads] INT ,
                      [ClientAddress] VARCHAR(48) ,
                      [AUTHENTICATION] NVARCHAR(40) ,
                      [DatetimeSnapshot] DATETIME ,
                      [plan_handle] VARBINARY(64)
                    )--处理逻辑
                INSERT  INTO [#ElapsedHigh]
                        ( [SPID] ,
                          [BlkBy] ,
                          [ElapsedMS] ,
                          [CPU] ,
                          [IOReads] ,
                          [IOWrites] ,
                          [Executions] ,
                          [CommandType] ,
                          [LastWaitType] ,
                          [ObjectName] ,
                          [SQLStatement] ,
                          [STATUS] ,
                          [Login] ,
                          [Host] ,
                          [DBName] ,
                          [StartTime] ,
                          [Protocol] ,
                          [transaction_isolation] ,
                          [ConnectionWrites] ,
                          [ConnectionReads] ,
                          [ClientAddress] ,
                          [AUTHENTICATION] ,
                          [DatetimeSnapshot] ,
                          [plan_handle]
                        )
                        EXEC [MonitorElapsedHighSQL].[dbo].[sp_who3]        --如果传入的是会话ID 只显示所在会话ID的信息
                IF ( @SessionID IS NOT NULL AND @SessionID <> 0 )
                    BEGIN                         SELECT TOP 1
                                @ElapsedMS = [ElapsedMS] ,
                                @SPID = [SPID] ,
                                @plan_handle = [plan_handle] ,
                                @IOReads = [IOReads] ,
                                @IOWrites = [IOWrites] ,
                                @DBName = [DBName]
                        FROM    [#ElapsedHigh]
                        WHERE   [#ElapsedHigh].[SPID] = @SessionID                        SELECT  @stmttext = [text]  FROM    sys.fn_get_sql(@plan_handle)                        BEGIN TRY
        -- convert may fail due to exceeding 128 depth limit
                            SELECT  @plan_xml = CONVERT(XML, query_plan)
                            FROM    sys.dm_exec_text_query_plan(@plan_handle, 0, -1)
                        END TRY
                        BEGIN CATCH
                            SELECT  @plan_xml = NULL
                        END CATCH;                        WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
INSERT @paramtb ( [paramlist], [planstmttext] )
    SELECT
        parameter_list.param_node.value('(./@Column)[1]', 'nvarchar(128)') +'='+ parameter_list.param_node.value('(./@ParameterCompiledValue)[1]', 'nvarchar(max)')  AS paramlist,
        ISNULL(@plan_xml.value('(//@StatementText)[1]', 'nvarchar(max)'), N'Unknown Statement') AS stmttext
    FROM (SELECT @plan_xml AS xml_showplan) AS t
        OUTER APPLY t.xml_showplan.nodes('//sp:ParameterList/sp:ColumnReference') AS parameter_list (param_node)                        SELECT TOP 1
                                @SPID spid ,
                                @ElapsedMS ElapsedMS ,
                                @IOReads IOReads ,
                                @IOWrites IOReads ,
                                @DBName DBName ,
                                @plan_handle plan_handle ,
                                @plan_xml planxml,
                                @stmttext stmttext ,
                                [planstmttext] planstmttext ,
                                ( SELECT    [paramlist] + '  '
                                  FROM      @paramtb
                                  WHERE     [planstmttext] = A.[planstmttext]
                                FOR
                                  XML PATH('')
                                ) AS [paramlist]
                        FROM    @paramtb A
                        GROUP BY [planstmttext]                    END
                ELSE
        --如果没有对存储过程传入参数，那么显示耗时最多的那条SQL的信息
                    BEGIN                         SELECT TOP 1
                                @ElapsedMS = [ElapsedMS] ,
                                @SPID = [SPID] ,
                                @plan_handle = [plan_handle] ,
                                @IOReads = [IOReads] ,
                                @IOWrites = [IOWrites] ,
                                @DBName = [DBName]
                        FROM    [#ElapsedHigh]
                        ORDER BY [ElapsedMS] DESC                         SELECT  @stmttext = [text]  FROM    sys.fn_get_sql(@plan_handle)--抓取占用时间长的SQL
                        IF ( @ElapsedMS > @Duration )
                            BEGIN
                                SELECT  @now = GETDATE()                                BEGIN TRY
        -- convert may fail due to exceeding 128 depth limit
                                    SELECT  @plan_xml = CONVERT(XML, query_plan)
                                    FROM    sys.dm_exec_text_query_plan(@plan_handle,
                                                              0, -1)
                                END TRY
                                BEGIN CATCH
                                    SELECT  @plan_xml = NULL
                                END CATCH;                                WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
INSERT @paramtb ( [paramlist], [planstmttext] )
    SELECT
        parameter_list.param_node.value('(./@Column)[1]', 'nvarchar(128)') +'='+ parameter_list.param_node.value('(./@ParameterCompiledValue)[1]', 'nvarchar(max)')  AS paramlist,
        ISNULL(@plan_xml.value('(//@StatementText)[1]', 'nvarchar(max)'), N'Unknown Statement') AS stmttext
    FROM (SELECT @plan_xml AS xml_showplan) AS t
        OUTER APPLY t.xml_showplan.nodes('//sp:ParameterList/sp:ColumnReference') AS parameter_list (param_node)                                INSERT  @paramtb2( [planstmttext] , [paramlist])
                                        SELECT TOP 1
                                                [planstmttext] ,
                                                ( SELECT    [paramlist] + '  '
                                                  FROM      @paramtb
                                                  WHERE     [planstmttext] = A.[planstmttext]
                                                FOR
                                                  XML PATH('')
                                                ) AS [paramlist]
                                        FROM    @paramtb A
                                        GROUP BY [planstmttext]                                SELECT TOP 1
                                        @planstmttext = [planstmttext] ,
                                        @paramlist = [paramlist]
                                FROM    @paramtb2                                INSERT  INTO [MonitorElapsedHighSQL].[dbo].[ElapsedHigh]
                                        ( [SPID] ,
                                          [ElapsedMS] ,
                                          [IOReads] ,
                                          [IOWrites] ,
                                          [DBName] ,
                                          [plan_handle] ,
                                          [paramlist] ,
                                          [stmttext] ,
                                          [planstmttext] ,
                                          [xmlplan],
                                          [gettime]
                                        )
                                VALUES  ( @SPID , -- SPID - smallint
                                          @ElapsedMS , -- ElapsedMS - int
                                          @IOReads , -- IOReads - bigint
                                          @IOWrites , -- IOWrites - bigint
                                          @DBName , -- DBName - nvarchar(128)
                                          @plan_handle , -- plan_handle - varbinary(64)
                                          @paramlist , -- paramlist - nvarchar(max)
                                          @stmttext , -- stmttext - nvarchar(max)
                                          @planstmttext , -- planstmttext - nvarchar(max)
                                          @plan_xml ,  --plan_xml - xml
                                          @now  -- gettime - datetime
                                        )                            END
                    END            END    END

第四步创建[usp_Resettbname]存储过程

USE [MonitorElapsedHighSQL]
GO
--重设ElapsedHigh表名，进行归档
CREATE  PROCEDURE [dbo].[usp_Resettbname]
AS
    BEGIN         IF EXISTS ( SELECT  OBJECT_ID('MonitorElapsedHighSQL.dbo.ElapsedHigh') )
            BEGIN
               --kill掉数据库所有连接
                DECLARE @DBNAME NVARCHAR(100)
                DECLARE @SQL NVARCHAR(MAX)
                DECLARE @SPID NVARCHAR(100)
                DECLARE @OwnSPID NVARCHAR(100)
                DECLARE @TBNAME NVARCHAR(1000)                SELECT  @OwnSPID = @@SPID
                SET @DBNAME = 'MonitorElapsedHighSQL'                  DECLARE CurDBName CURSOR
                FOR
                    SELECT  [spid]
                    FROM    sys.sysprocesses
                    WHERE   [spid] >= 50
                            AND DBID = DB_ID(@DBNAME)                OPEN CurDBName
                FETCH NEXT FROM CurDBName INTO @SPID                WHILE @@FETCH_STATUS = 0
                    BEGIN
        --kill process 不kill掉本存储过程的spid
                        IF ( @SPID <> @OwnSPID )
                            BEGIN
                                SET @SQL = N'kill ' + @SPID
                                EXEC (@SQL)
                            END                         FETCH NEXT FROM CurDBName INTO @SPID
                    END
                CLOSE CurDBName
                DEALLOCATE CurDBName                SET @TBNAME='ElapsedHigh'+CONVERT(NVARCHAR(200), GETDATE(), 112)                 EXEC sys.[sp_rename] @objname = N'ElapsedHigh', -- nvarchar(1035)
                    @newname =@TBNAME    -- sysname            END    END

第五步创建AutocaptureElapsedHighSQL作业

USE [msdb]
GO
/****** 对象:  Job [自动抓取耗时SQL]    脚本日期: 07/29/2014 15:44:57 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** 对象:  JobCategory [[Uncategorized (Local)]]]    脚本日期: 07/29/2014 15:44:57 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackENDDECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'AutocaptureElapsedHighSQL',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'自动抓取耗时SQL',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** 对象:  Step [execute usp_checkElapsedHighSQL script]    脚本日期: 07/29/2014 15:44:58 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep  @job_name=N'AutocaptureElapsedHighSQL', @step_name=N'execute usp_checkElapsedHighSQL script',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'exec [dbo].[usp_checkElapsedHighSQL] null',  --调用存储过程
        @database_name=N'MonitorElapsedHighSQL',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_name=N'AutocaptureElapsedHighSQL', @name=N'ScheduleAutocaptureCheck',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=4,
        @freq_subday_interval=1, --每一分钟抓取一次耗时SQL
        @freq_relative_interval=0,
        @freq_recurrence_factor=0,
        @active_start_date=20110224,
        @active_end_date=99991231,
        @active_start_time=200,
        @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_name=N'AutocaptureElapsedHighSQL', @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

第六步创建ResetcheckElapsedHighSQLtbname作业

USE [msdb]
GO
/****** 对象:  Job [定时改表名]    脚本日期: 07/29/2014 15:44:57 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** 对象:  JobCategory [[Uncategorized (Local)]]]    脚本日期: 07/29/2014 15:44:57 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackENDDECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'ResetcheckElapsedHighSQLtbname',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'修改抓取耗时SQL的表名',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** 对象:  Step [execute usp_checkElapsedHighSQL script]    脚本日期: 07/29/2014 15:44:58 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep  @job_name=N'ResetcheckElapsedHighSQLtbname', @step_name=N'execute usp_Resettbname script',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'exec [dbo].[usp_Resettbname] ',  --调用存储过程
        @database_name=N'MonitorElapsedHighSQL',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_name=N'ResetcheckElapsedHighSQLtbname', @name=N'Scheduleusp_Resettbname',
    @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=1,
        @freq_subday_interval=1,
        @freq_relative_interval=0,
        @freq_recurrence_factor=0,
        @active_start_date=20110224,
        @active_end_date=99991231,
        @active_start_time=235900,
        @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_name=N'ResetcheckElapsedHighSQLtbname', @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

原理解释：

AutocaptureElapsedHighSQL作业每隔一分钟调用[usp_checkElapsedHighSQL]存储过程，而[usp_checkElapsedHighSQL]存储过程又会调用

sp_who3存储过程获取一些当前线上环境的信息，被记录到[ElapsedHigh]表里

ResetcheckElapsedHighSQLtbname作业会在每天的23点59分执行，调用[usp_Resettbname]存储过程， [usp_Resettbname]存储过程会将[ElapsedHigh]表

的表名修改为：表名+当天日期，例如：ElapsedHigh2015-6-19 ，这样就进行了归档

[usp_checkElapsedHighSQL] 存储过程有两种调用方式，一种是传入NULL，那么[usp_checkElapsedHighSQL] 存储过程就会抓取最耗时的那个session

如果传入spid，那么就会显示那个spid的session

--调用示例
--不提供参数，抓取最耗时的一个SQL
EXEC [MonitorElapsedHighSQL].[dbo].[usp_checkElapsedHighSQL] NULL--提供sessionsid参数，抓取那个sessionid相关的SQL
EXEC [MonitorElapsedHighSQL].[dbo].[usp_checkElapsedHighSQL] NULL

效果

USE [sss]
GOWHILE 1=1
BEGIN
DECLARE @test NVARCHAR(100)
SET @test='你好'
DECLARE @id int
SET @id=2
SELECT * FROM [sss].[dbo].[test] WHERE [id]=@id
EXEC [dbo].[aa] @test =@test
EXEC [dbo].[ab] @id=@idEND