SQL Server定时自动抓取耗时SQL并归档数据脚本分享
第一步建库
USE [master] GO CREATE DATABASE [MonitorElapsedHighSQL] 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/18 15:16:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--创建存储过程
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_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 ,
@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]
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] ,
[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
第四步创建[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
第五步创建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:
原理解释：
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] NULL
效果
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去抓
如有不对的地方，欢迎大家拍砖o(∩_∩)o