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

第一步建库和建表

USE [master]GOCREATE 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_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

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

USE [MonitorElapsedHighSQL]GO/****** Object:  StoredProcedure [dbo].[usp_checkElapsedHighSQL]    Script Date: 2015/6/23 17:16:56 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO--创建存储过程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 UNCO