你的位置:首页 > 数据库

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


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] ASBEGINSET 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_handleFROM sys.dm_exec_requests erLEFT JOIN sys.dm_exec_sessions sesON ses.session_id = er.session_idLEFT JOIN sys.dm_exec_connections conON con.session_id = ses.session_idOUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qtOUTER APPLY (  SELECT execution_count = MAX(cp.usecounts)  FROM sys.dm_exec_cached_plans cp  WHERE cp.plan_handle = er.plan_handle) ecOUTER 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) lbWHERE er.sql_handle IS NOT NULLAND er.session_id != @@SPIDORDER 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

View Code

 

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

USE [MonitorElapsedHighSQL]GO/****** Object: StoredProcedure [dbo].[usp_checkElapsedHighSQL]  Script Date: 2015/6/18 15:16:21 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO--创建存储过程ALTER 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) ,           [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_ 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_ = CONVERT(FROM  sys.dm_exec_text_query_plan(@plan_handle, 0, -1)            END TRY            BEGIN CATCH              SELECT @plan_ = NULL            END CATCH;            WITH '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_.value('(//@StatementText)[1]', 'nvarchar(max)'), N'Unknown Statement') AS stmttext  FROM (SELECT @plan_ AS AS t    OUTER APPLY t.'//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 ,                @stmttext stmttext ,                [planstmttext] planstmttext ,                ( SELECT  [paramlist] + ' '                 FROM   @paramtb                 WHERE   [planstmttext] = A.[planstmttext]                FOR                 '')                ) 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_ = CONVERT(FROM  sys.dm_exec_text_query_plan(@plan_handle,                               0, -1)                END TRY                BEGIN CATCH                  SELECT @plan_ = NULL                END CATCH;                WITH '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_.value('(//@StatementText)[1]', 'nvarchar(max)'), N'Unknown Statement') AS stmttext  FROM (SELECT @plan_ AS AS t    OUTER APPLY t.'//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                         '')                        ) 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] ,                     [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)                     @now -- gettime - datetime                    )              END           END      END  END

View Code

 

第四步创建[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

View Code

 

第五步创建AutocaptureElapsedHighSQL作业

USE [msdb]GO/****** 对象: Job [自动抓取耗时SQL]  脚本日期: 07/29/2014 15:44:57 ******/BEGIN TRANSACTIONDECLARE @ReturnCode INTSELECT @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)BEGINEXEC @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 OUTPUTIF (@@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=0IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @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=235959IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_name=N'AutocaptureElapsedHighSQL', @server_name = N'(local)'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback:  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave:

View Code

 

第六步创建ResetcheckElapsedHighSQLtbname作业

USE [msdb]GO/****** 对象: Job [定时改表名]  脚本日期: 07/29/2014 15:44:57 ******/BEGIN TRANSACTIONDECLARE @ReturnCode INTSELECT @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)BEGINEXEC @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 OUTPUTIF (@@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=0IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @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=235959IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_name=N'ResetcheckElapsedHighSQLtbname', @server_name = N'(local)'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback:  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave:

View Code

 

 

原理解释:

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

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

 

效果

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

SELECT * FROM [dbo].[ElapsedHigh]go

可以看到,参数也能抓取到,一般依靠sys.dm_exec_sql_text视图和sys.[fn_get_sql]()视图是无法获取到参数的

SQL Server profiler也是,它是整个RPC和Statement去抓

 

 

如有不对的地方,欢迎大家拍砖o(∩_∩)o