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
第三步创建[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
第四步创建[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
第五步创建[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
第六步创建[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
第七步创建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:
第八步创建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:
第九步创建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:
第十步创建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:
原理解释：
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]
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