计算机数据

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

第一步建库

USE [master]GOCREATE DATABASE [MonitorElapsedHighSQL]GO

第二步创建sp_巴黎人游戏官网,who3存储过程

巴黎人电玩 1巴黎人电玩 2

-- 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]存储过程

巴黎人电玩 3巴黎人电玩 4

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]存储过程 

巴黎人电玩 5巴黎人电玩 6

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作业

巴黎人电玩 7巴黎人电玩 8

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作业

巴黎人电玩 9巴黎人电玩 10

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 ,这样就进行了归档

巴黎人电玩 11

 

[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

巴黎人电玩 12

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

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

 

 

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

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

第一步建库和建表

USE [master]
GO

CREATE DATABASE [MonitorElapsedHighSQL]
GO

--建表

USE [MonitorElapsedHighSQL]
GO


 --1、表[SQLCountStatisticsByDay]
  --抓取到的sql语句数量
CREATE TABLE [dbo].[SQLCountStatisticsByDay]
    (
      id INT IDENTITY(1, 1)  PRIMARY KEY ,
      [SQLCount] INT ,
      [gettime] DATETIME
    )

CREATE INDEX [Idx_SQLCountStatisticsByDay_SQLCount] ON [MonitorElapsedHighSQL].[dbo].[SQLCountStatisticsByDay]([SQLCount])
CREATE INDEX [Idx_SQLCountStatisticsByDay_gettime] ON [MonitorElapsedHighSQL].[dbo].[SQLCountStatisticsByDay]([gettime])
GO



 --2、表[MostElapsedStatisticsByDay]
 --每条不同的sql耗时最多
CREATE TABLE [dbo].[MostElapsedStatisticsByDay]
    (
      id INT IDENTITY(1, 1)
             PRIMARY KEY ,
      [ElapsedMS] INT ,
      [IOReads] BIGINT ,
      [IOWrites] BIGINT ,
      [DBName] NVARCHAR(128) ,
      [paramlist] NVARCHAR(MAX) ,
      [planstmttext] NVARCHAR(MAX) ,
      [stmttext] NVARCHAR(MAX) ,
      [xmlplan] XML ,
      [gettime] DATETIME
    )

CREATE INDEX [Idx_MostElapsedStatisticsByDay_ElapsedMS] ON [MonitorElapsedHighSQL].[dbo].[MostElapsedStatisticsByDay]([ElapsedMS])
CREATE INDEX [Idx_MostElapsedStatisticsByDay_gettime] ON [MonitorElapsedHighSQL].[dbo].[MostElapsedStatisticsByDay]([gettime])
GO


 --3、表[MostIOReadStatisticsByDay]
--每条不同的sql的IOread最多
CREATE TABLE [dbo].[MostIOReadStatisticsByDay]
    (
      id INT IDENTITY(1, 1)
             PRIMARY KEY ,
      [IOReads] BIGINT ,
      [DBName] NVARCHAR(128) ,
      [paramlist] NVARCHAR(MAX) ,
      [planstmttext] NVARCHAR(MAX) ,
      [stmttext] NVARCHAR(MAX) ,
      [xmlplan] XML ,
      [gettime] DATETIME
    )

CREATE INDEX [Idx_MostIOReadStatisticsByDay_IOReads] ON [MonitorElapsedHighSQL].[dbo].[MostIOReadStatisticsByDay]([IOReads])
CREATE INDEX [Idx_MostIOReadStatisticsByDay_gettime] ON [MonitorElapsedHighSQL].[dbo].[MostIOReadStatisticsByDay]([gettime])
GO


 --4、表[MostIOWriteStatisticsByDay]
--每条不同的sql的IOwrite最多
CREATE TABLE [dbo].[MostIOWriteStatisticsByDay]
    (
      id INT IDENTITY(1, 1)
             PRIMARY KEY ,
      [IOWrites] BIGINT ,
      [DBName] NVARCHAR(128) ,
      [paramlist] NVARCHAR(MAX) ,
      [planstmttext] NVARCHAR(MAX) ,
      [stmttext] NVARCHAR(MAX) ,
      [xmlplan] XML ,
      [gettime] DATETIME
    )

CREATE INDEX [Idx_MostIOWriteStatisticsByDay_IOWrites] ON [MonitorElapsedHighSQL].[dbo].[MostIOWriteStatisticsByDay]([IOWrites])
CREATE INDEX [Idx_MostIOWriteStatisticsByDay_gettime] ON [MonitorElapsedHighSQL].[dbo].[MostIOWriteStatisticsByDay]([gettime])
GO


 --5、表[sp_executesqlCountStatisticsByDay]
--使用sp_executesql的sql有多少条
CREATE TABLE [dbo].[sp_executesqlCountStatisticsByDay]
    (
      id INT IDENTITY(1, 1)
             PRIMARY KEY ,
      [sp_executesqlCount] INT ,
      [DBName] NVARCHAR(128) ,
      [planstmttext] NVARCHAR(MAX) ,
      [gettime] DATETIME
    )

CREATE INDEX [Idx_sp_executesqlCountStatisticsByDay_sp_executesqlCount] ON [MonitorElapsedHighSQL].[dbo].[sp_executesqlCountStatisticsByDay]([sp_executesqlCount])
CREATE INDEX [Idx_sp_executesqlCountStatisticsByDay_gettime] ON [MonitorElapsedHighSQL].[dbo].[sp_executesqlCountStatisticsByDay]([gettime])
GO

 

 

第二步创建sp_who3存储过程

巴黎人电玩 13巴黎人电玩 14

-- http://sqlserverplanet.com/dba/a-better-sp_who2-using-dmvs-sp_who3

USE [MonitorElapsedHighSQL]
GO

CREATE PROCEDURE [dbo].[sp_who3] 

AS
BEGIN

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
    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_id

END

View Code

 

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

巴黎人电玩 15巴黎人电玩 16

USE [MonitorElapsedHighSQL]
GO
/****** Object:  StoredProcedure [dbo].[usp_checkElapsedHighSQL]    Script Date: 2015/6/23 17:16:56 ******/
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
                    )

                CREATE INDEX [Idx_ElapsedHigh_ElapsedMS] ON [MonitorElapsedHighSQL].[dbo].[ElapsedHigh]([ElapsedMS])
                CREATE INDEX [Idx_ElapsedHigh_IOReads] ON [MonitorElapsedHighSQL].[dbo].[ElapsedHigh]([IOReads])

            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] WHERE [#ElapsedHigh].[DBName] NOT IN('master','distribution','model','msdb','tempdb')
                        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

View Code

 

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

巴黎人电玩 17巴黎人电玩 18

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

 

第五步创建[usp_StatisticsTask]存储过程

巴黎人电玩 19巴黎人电玩 20

USE [MonitorElapsedHighSQL]
GO
/****** Object:  StoredProcedure [dbo].[usp_StatisticsTask]    Script Date: 2015/6/24 18:05:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--创建存储过程
CREATE   PROCEDURE [dbo].[usp_StatisticsTask]
AS
    BEGIN

        IF ( ( SELECT   OBJECT_ID('MonitorElapsedHighSQL.dbo.SQLCountStatisticsByDay')
             ) IS NULL
             AND ( SELECT   OBJECT_ID('MonitorElapsedHighSQL.dbo.MostElapsedStatisticsByDay')
                 ) IS NULL
             AND ( SELECT   OBJECT_ID('MonitorElapsedHighSQL.dbo.MostIOReadStatisticsByDay')
                 ) IS NULL
             AND ( SELECT   OBJECT_ID('MonitorElapsedHighSQL.dbo.MostIOWriteStatisticsByDay')
                 ) IS NULL
             AND ( SELECT   OBJECT_ID('MonitorElapsedHighSQL.dbo.sp_executesqlCountStatisticsByDay')
                 ) IS NULL
           )
            BEGIN
                RETURN 1

            END
        ELSE
            BEGIN
                --最耗时SQL
                INSERT  INTO [dbo].[MostElapsedStatisticsByDay]
                        ( [ElapsedMS] ,
                          [IOReads] ,
                          [IOWrites] ,
                          [DBName] ,
                          [paramlist] ,
                          [planstmttext] ,
                          [stmttext] ,
                          [xmlplan] ,
                          [gettime]
                        )
                        SELECT  [ElapsedMS] ,
                                [IOReads] ,
                                [IOWrites] ,
                                [DBName] ,
                                [paramlist] ,
                                [planstmttext] ,
                                [stmttext] ,
                                [xmlplan] ,
                                GETDATE()
                        FROM    ( SELECT    ROW_NUMBER() OVER ( PARTITION BY spid ORDER BY [ElapsedMS] DESC ) rowid ,
                                            *
                                  FROM      [ElapsedHigh]
                                  WHERE     [DBName] NOT IN ( 'MASTER',
                                                              'MODEL', 'MSDB',
                                                              'ReportServer',
                                                              'ReportServerTempDB',
                                                              'distribution' )
                                ) t
                        WHERE   rowid = 1



--读IO最多SQL
                INSERT  INTO [dbo].[MostIOReadStatisticsByDay]
                        ( [IOReads] ,
                          [DBName] ,
                          [paramlist] ,
                          [planstmttext] ,
                          [stmttext] ,
                          [xmlplan] ,
                          [gettime]
                        )
                        SELECT  [IOReads] ,
                                [DBName] ,
                                [paramlist] ,
                                [planstmttext] ,
                                [stmttext] ,
                                [xmlplan] ,
                                GETDATE()
                        FROM    ( SELECT    ROW_NUMBER() OVER ( PARTITION BY spid ORDER BY [IOReads] DESC ) rowid ,
                                            *
                                  FROM      [ElapsedHigh]
                                  WHERE     [DBName] NOT IN ( 'MASTER',
                                                              'MODEL', 'MSDB',
                                                              'ReportServer',
                                                              'ReportServerTempDB',
                                                              'distribution' )
                                ) t
                        WHERE   rowid = 1



--写IO最多SQL
                INSERT  INTO [dbo].[MostIOWriteStatisticsByDay]
                        ( [IOWrites] ,
                          [DBName] ,
                          [paramlist] ,
                          [planstmttext] ,
                          [stmttext] ,
                          [xmlplan] ,
                          [gettime]
                        )
                        SELECT  [IOWrites] ,
                                [DBName] ,
                                [paramlist] ,
                                [planstmttext] ,
                                [stmttext] ,
                                [xmlplan] ,
                                GETDATE()
                        FROM    ( SELECT    ROW_NUMBER() OVER ( PARTITION BY spid ORDER BY [IOWrites] DESC ) rowid ,
                                            *
                                  FROM      [ElapsedHigh]
                                  WHERE     [DBName] NOT IN ( 'MASTER',
                                                              'MODEL', 'MSDB',
                                                              'ReportServer',
                                                              'ReportServerTempDB',
                                                              'distribution' )
                                ) t
                        WHERE   rowid = 1



--统计sp_executesql次数
                DECLARE @tbsp_executesqlCountStatisticsByDay TABLE
                    (
                      [DBName] [nvarchar](128) ,
                      [planstmttext] [nvarchar](MAX)
                    )
                DECLARE @sp_executesqlCount INT

                INSERT  INTO @tbsp_executesqlCountStatisticsByDay
                        ( [DBName] ,
                          [planstmttext] 
                        )
                        SELECT  [DBName] ,
                                [planstmttext]
                        FROM    ( SELECT    ROW_NUMBER() OVER ( PARTITION BY spid ORDER BY [IOWrites] DESC ) rowid ,
                                            *
                                  FROM      [ElapsedHigh]
                                  WHERE     [planstmttext] LIKE '(@%'
                                            AND [DBName] NOT IN ( 'MASTER',
                                                              'MODEL', 'MSDB',
                                                              'ReportServer',
                                                              'ReportServerTempDB',
                                                              'distribution' )
                                ) t
                        WHERE   rowid = 1

                SELECT  @sp_executesqlCount = COUNT(*)
                FROM    @tbsp_executesqlCountStatisticsByDay

                INSERT  INTO [dbo].[sp_executesqlCountStatisticsByDay]
                        ( [sp_executesqlCount] ,
                          [DBName] ,
                          [planstmttext] ,
                          [gettime]
                        )
                        SELECT  @sp_executesqlCount ,
                                [DBName] ,
                                [planstmttext] ,
                                GETDATE()
                        FROM    @tbsp_executesqlCountStatisticsByDay



--统计一共有多少SQL被抓取
                INSERT  INTO [dbo].[SQLCountStatisticsByDay]
                        ( [SQLCount] ,
                          [gettime]
                        )
                        SELECT  COUNT(DISTINCT ( [planstmttext] )) ,
                                GETDATE()
                        FROM    [dbo].[ElapsedHigh]
                        WHERE   [DBName] NOT IN ( 'MASTER', 'MODEL', 'MSDB',
                                                  'ReportServer',
                                                  'ReportServerTempDB',
                                                  'distribution' )


            END



    END

View Code

 

第六步创建[usp_SendStatisticsMail]存储过程

巴黎人电玩 21巴黎人电玩 22

USE [MonitorElapsedHighSQL]
GO

--对统计数据定时发邮件
CREATE  PROCEDURE [dbo].[usp_SendStatisticsMail]
AS
    BEGIN

        --定义变量
        DECLARE @SQL NVARCHAR(MAX)
        DECLARE @SQLConcat NVARCHAR(MAX)
        DECLARE @infoConcat NVARCHAR(MAX)
        DECLARE @finalSQL NVARCHAR(MAX)


        DECLARE @DBID NVARCHAR(MAX)
        DECLARE @servername NVARCHAR(200)
        DECLARE @date DATETIME

        DECLARE @sqlversion NVARCHAR(200)
        DECLARE @uptime NVARCHAR(200)


        --1.数据库版本信息
        SELECT  @sqlversion = @@version


        --2.数据库服务器已运行时间信息
        SELECT  @uptime = CONVERT(NVARCHAR(200), DATEDIFF(DAY, sqlserver_start_time, GETDATE()))
        FROM    sys.dm_os_sys_info WITH ( NOLOCK )
        OPTION  ( RECOMPILE )



        --3.查看数据库服务器名
        SELECT  @servername = LTRIM(@@servername)


        SET @date = GETDATE()
        SET @SQL = ' '
        SET @SQLConcat = ' '
        SET @infoConcat = ' '



        IF ( @servername IS NOT NULL AND @servername <> '' )
            BEGIN
                SET @infoConcat = '<h3><font color="#FF0000">主机名:' + @ServerName + '</font></h3></br>'
            END

        IF ( @uptime IS NOT NULL  AND @uptime <> '' )
            BEGIN
                SET @infoConcat = @infoConcat + '<h4>数据库服务器已运行天数:' + @uptime  + '天</h4></br>' 
            END

        IF ( @sqlversion IS NOT NULL AND @sqlversion <> '' )
            BEGIN
                SET @infoConcat = @infoConcat + '<h4>数据库版本信息:' + @sqlversion + '</h4></br>'
            END


      -----------------------------------------------------------


        SET @SQL = N'<H3>[' + @servername + ']_前5条不同的最耗时SQL 表名:[MostElapsedStatisticsByDay] ------   邮件发出时间:' + CONVERT(NVARCHAR(200), @date, 120) + '</H3>'
            + '<table border="1">' + N'<tr>
<th>[id]</th>
<th>[耗时]</th>
<th>[IO读次数]</th>
<th>[IO写次数]</th>
<th>[数据库名称]</th>
<th>[执行计划SQL]</th>
<th>[日期]</th>
</tr>' + CAST(( SELECT TOP 5
                        [id] AS 'td' ,
                        '' ,
                        [ElapsedMS] AS 'td' ,
                        '' ,
                        [IOReads] AS 'td' ,
                        '' ,
                        [IOWrites] AS 'td' ,
                        '' ,
                        [DBName] AS 'td' ,
                        '' ,
                        LEFT([planstmttext], 100) AS 'td' ,
                        '' ,
                        CONVERT(DATE, [gettime]) AS 'td' ,
                        ''
                FROM    [dbo].[MostElapsedStatisticsByDay]
                WHERE   DATEPART(DAY, [gettime]) = DATEPART(DAY, GETDATE()) AND DATEPART(MONTH , [gettime]) = DATEPART(MONTH, GETDATE()) AND DATEPART(YEAR, [gettime]) = DATEPART(YEAR, GETDATE())
                ORDER BY [ElapsedMS] DESC
              FOR
                XML PATH('tr') ,
                    ELEMENTS-- TYPE 
              ) AS NVARCHAR(MAX)) + N'</table>';
        PRINT @SQL

        IF ( @SQL IS NOT NULL
             AND @SQL <> ''
           )
            BEGIN
                SET @SQLConcat = @SQL + @SQLConcat

            END




      --------------------------------------------------------



        SET @SQL = N'<H3>[' + @servername + ']_前5条I/O read最多的SQL 表名:[MostIOReadStatisticsByDay]------   邮件发出时间:' + CONVERT(NVARCHAR(200), @date, 120) + '</H3>'
            + '<table border="1">' + N'<tr>
<th>[id]</th>
<th>[IO读次数]</th>
<th>[数据库名称]</th>
<th>[执行计划SQL]</th>
<th>[日期]</th>
</tr>' + CAST(( SELECT TOP 5
                        [id] AS 'td' ,
                        '' ,
                        [IOReads] AS 'td' ,
                        '' ,
                        [DBName] AS 'td' ,
                        '' ,
                        LEFT([planstmttext], 100) AS 'td' ,
                        '' ,
                        CONVERT(DATE, [gettime]) AS 'td' ,
                        ''
                FROM    [dbo].[MostIOReadStatisticsByDay]
                WHERE   DATEPART(DAY, [gettime]) = DATEPART(DAY, GETDATE()) AND DATEPART(MONTH , [gettime]) = DATEPART(MONTH, GETDATE()) AND DATEPART(YEAR, [gettime]) = DATEPART(YEAR, GETDATE())
                ORDER BY [IOReads] DESC
              FOR
                XML PATH('tr') ,
                    ELEMENTS-- TYPE 
              ) AS NVARCHAR(MAX)) + N'</table>';



        IF ( @SQL IS NOT NULL
             AND @SQL <> ''
           )
            BEGIN
                SET @SQLConcat = @SQL + @SQLConcat

            END

--      -----------------------------------------------------



        SET @SQL = N'<H3>[' + @servername + ']_前5条I/O write最多的SQL 表名:[MostIOWriteStatisticsByDay]------   邮件发出时间:'+ CONVERT(NVARCHAR(200), @date, 120) + '</H3>'
            + '<table border="1">' + N'<tr>
<th>[id]</th>
<th>[IO写次数]</th>
<th>[数据库名称]</th>
<th>[执行计划SQL]</th>
<th>[日期]</th>
</tr>' + CAST(( SELECT TOP 5
                        [id] AS 'td' ,
                        '' ,
                        [IOWrites] AS 'td' ,
                        '' ,
                        [DBName] AS 'td' ,
                        '' ,
                        LEFT([planstmttext], 100) AS 'td' ,
                        '' ,
                        CONVERT(DATE, [gettime]) AS 'td' ,
                        ''
                FROM    [dbo].[MostIOWriteStatisticsByDay]
                WHERE   DATEPART(DAY, [gettime]) = DATEPART(DAY, GETDATE()) AND DATEPART(MONTH , [gettime]) = DATEPART(MONTH, GETDATE()) AND DATEPART(YEAR, [gettime]) = DATEPART(YEAR, GETDATE())
                ORDER BY [IOWrites] DESC
              FOR
                XML PATH('tr') ,
                    ELEMENTS-- TYPE 
              ) AS NVARCHAR(MAX)) + N'</table>';



        IF ( @SQL IS NOT NULL
             AND @SQL <> ''
           )
            BEGIN
                SET @SQLConcat = @SQL + @SQLConcat

            END

--      -------------------------------------------------------




        SET @SQL = N'<H3>[' + @servername + ']_前5条使用sp_executesql执行的SQL 表名:[sp_executesqlCountStatisticsByDay]------   邮件发出时间:'+ CONVERT(NVARCHAR(200), @date, 120) + '</H3>'
            + '<table border="1">' + N'<tr>
<th>[id]</th>
<th>[sp_executesql调用次数]</th>
<th>[数据库名称]</th>
<th>[执行计划SQL]</th>
<th>[日期]</th>
</tr>' + CAST(( SELECT TOP 5
                        [id] AS 'td' ,
                        '' ,
                        [sp_executesqlCount] AS 'td' ,
                        '' ,
                        [DBName] AS 'td' ,
                        '' ,
                        LEFT([planstmttext], 100) AS 'td' ,
                        '' ,
                        CONVERT(DATE, [gettime]) AS 'td' ,
                        ''
                FROM    [dbo].[sp_executesqlCountStatisticsByDay]
                WHERE   DATEPART(DAY, [gettime]) = DATEPART(DAY, GETDATE()) AND DATEPART(MONTH , [gettime]) = DATEPART(MONTH, GETDATE()) AND DATEPART(YEAR, [gettime]) = DATEPART(YEAR, GETDATE())
                ORDER BY [sp_executesqlCount] DESC
              FOR
                XML PATH('tr') ,
                    ELEMENTS-- TYPE 
              ) AS NVARCHAR(MAX)) + N'</table>';


        IF ( @SQL IS NOT NULL
             AND @SQL <> ''
           )
            BEGIN
                SET @SQLConcat = @SQL + @SQLConcat

            END

--      --------------------------------------------------------

        SET @SQL = N'<H3>[' + @servername+ ']_SQL语句数量 表名:[SQLCountStatisticsByDay]------   邮件发出时间:' + CONVERT(NVARCHAR(200), @date, 120) + '</H3>'
            + '<table border="1">' + N'<tr>
<th>[id]</th>
<th>[SQL数量]</th>
<th>[日期]</th>
</tr>' + CAST(( SELECT  [id] AS 'td' ,
                        '' ,
                        [SQLCount] AS 'td' ,
                        '' ,
                        CONVERT(DATE, [gettime]) AS 'td' ,
                        ''
                FROM    [dbo].[SQLCountStatisticsByDay]
                WHERE   DATEPART(DAY, [gettime]) = DATEPART(DAY, GETDATE()) AND DATEPART(MONTH , [gettime]) = DATEPART(MONTH, GETDATE()) AND DATEPART(YEAR, [gettime]) = DATEPART(YEAR, GETDATE())
              FOR
                XML PATH('tr') ,
                    ELEMENTS-- TYPE 
              ) AS NVARCHAR(MAX)) + N'</table>';


        IF ( @SQL IS NOT NULL
             AND @SQL <> ''
           )
            BEGIN
                SET @SQLConcat = @SQL + @SQLConcat

            END

      -----------------------------------------------

        IF ( @infoConcat IS NOT NULL AND @infoConcat <> '' AND @SQLConcat IS NOT NULL  AND @SQLConcat <> '')
            BEGIN
                SET @finalSQL = @infoConcat + '</br></br>' + @SQLConcat
                EXEC [msdb].[dbo].[sp_send_dbmail] @profile_name = 'SQLServer',
                    @recipients = 'dba@xx.com', -- varchar(max) --收件人
                    @subject = N'SQL Server 实例SQL语句抓取统计信息', -- nvarchar(255) 标题
                    @body_format = 'HTML', -- varchar(20) 正文格式可选值:text html
                    @body = @finalSQL
            END





    END

View Code

 

 

第七步创建AutocaptureElapsedHighSQL作业

巴黎人电玩 23巴黎人电玩 24

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 QuitWithRollback

END

DECLARE @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:

View Code

 

第八步创建ResetcheckElapsedHighSQLtbname作业

巴黎人电玩 25巴黎人电玩 26

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 QuitWithRollback

END

DECLARE @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:

View Code

 

第九步创建StatisticsforElapsedHigh作业

巴黎人电玩 27巴黎人电玩 28

USE [msdb]
GO
/****** 对象:  Job [定时统计[ElapsedHigh]表数据]    脚本日期: 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 QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'StatisticsforElapsedHigh', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'统计[MonitorElapsedHighSQL]库里的[ElapsedHigh]表各项数据', 
        @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'StatisticsforElapsedHigh', @step_name=N'execute usp_StatisticsTask 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_StatisticsTask] ',  --调用存储过程
        @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'StatisticsforElapsedHigh', @name=N'Scheduleusp_StatisticsTask', 
    @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=235000, 
        @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_name=N'StatisticsforElapsedHigh', @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

View Code

 

第十步创建ScheduleSendStatisticsMail作业

巴黎人电玩 29巴黎人电玩 30

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 QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'ScheduleSendStatisticsMail', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'定时发统计邮件', 
        @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'ScheduleSendStatisticsMail', @step_name=N'execute usp_SendStatisticsMail 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_SendStatisticsMail]',  --调用存储过程
        @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'ScheduleSendStatisticsMail', @name=N'Scheduleusp_SendStatisticsMail', 
    @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=235500, 
        @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_name=N'ScheduleSendStatisticsMail', @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

View Code

 

 

 

原理解释:

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

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

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

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

巴黎人电玩 31

巴黎人电玩 32

 

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

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

--调用示例
--不提供参数,抓取最耗时的一个SQL
EXEC [MonitorElapsedHighSQL].[dbo].[usp_checkElapsedHighSQL] NULL


--提供sessionsid参数,抓取那个sessionid相关的SQL
EXEC [MonitorElapsedHighSQL].[dbo].[usp_checkElapsedHighSQL] 182

 

StatisticsforElapsedHigh作业每天会在23点50分对[ElapsedHigh]表里的数据进行统计,把数据放进去五张统计表里

ScheduleSendStatisticsMail作业会取出五张统计表里的数据并发送邮件,让DBA知道当天数据库有哪些慢SQL

 

效果

USE [sss]
GO

WHILE 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=@id

END

SELECT * FROM [dbo].[ElapsedHigh]
go

巴黎人电玩 33

巴黎人电玩 34

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

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

而且还会抓取当时的XML执行计划,点击它就能显示图形化的执行计划,这样对分析当时语句的执行情况非常有帮助

巴黎人电玩 35

 

 

邮件效果

巴黎人电玩 36


总结

目前脚本还是比较简单,后续还需要对各个数据库服务器的统计数据进行汇总,用web页面显示,这样即使数据库服务器再多也可以一目了然

每天通过发邮件,把统计表的内容发邮件给开发人员,指导他们调整SQL,减轻DBA的一些工作量

 

若有遗漏或失误,请留言回复,谢谢!

 

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

其他新闻
  • ▓ 套件简介 PEAR最重要的就是拥有许多的程式库可供开发者免费引用,这些程式库会被包裹成套件,而且每个套件中都会放入一个名为package.xml的档案.这是PEAR团队的规定,这个档案纪...
    2020-02-02
  • 安装完基本套件后,当开发者需要使用基本套件以外的其他套件时,会需要安装套件,若不需要某个套件时,就会想要移除套件,若套件版本过於老旧,则需要升级套件,也就是說开发...
    2020-02-02
  • 安装pear -------------- pear是PHP的扩展和应用程序库,包含了很多有用的类,安装好php5.0后,pear实际上并没有被安装,安装的方法如下: 1.在php目录中双击go-pear.bat。...
    2020-02-02
友情链接

公司名称巴黎人电玩
版权所有:Copyright © 2015-2019 http://www.zhongqiangjy.com. 巴黎人电玩有限公司 版权所有

友情链接

Copyright © 2015-2019 http://www.zhongqiangjy.com. 巴黎人电玩有限公司 版权所有
公司地址http://www.zhongqiangjy.com