文章出處

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存儲過程

-- 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]存儲過程

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]存儲過程 

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]存儲過程

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]存儲過程

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

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

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

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

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 ，這樣就進行了歸檔

 

[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

可以看到，參數也能抓取到，一般依靠sys.dm_exec_sql_text視圖和sys.[fn_get_sql]()視圖是無法獲取到參數的

SQL Server profiler也是，它是整個RPC和Statement去抓

而且還會抓取當時的XML執行計劃，點擊它就能顯示圖形化的執行計劃，這樣對分析當時語句的執行情況非常有幫助

 

 

郵件效果

總結

目前腳本還是比較簡單，后續還需要對各個數據庫服務器的統計數據進行匯總，用web頁面顯示，這樣即使數據庫服務器再多也可以一目了然

每天通過發郵件，把統計表的內容發郵件給開發人員，指導他們調整SQL，減輕DBA的一些工作量

 

若有遺漏或失誤，請留言回復，謝謝！

 

如有不對的地方，歡迎大家拍磚o(∩_∩)o 


文章列表

arrow
arrow
    全站熱搜
    創作者介紹
    創作者 大師兄 的頭像
    大師兄

    IT工程師數位筆記本

    大師兄 發表在 痞客邦 留言(0) 人氣()

    E-mail轉寄