SQL Server定时自动抓取耗时SQL并归档数据脚本分享
第一步建库
USE [master]GOCREATE DATABASE [MonitorElapsedHighSQL]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/19 15:22:25 ******/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 ) 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('(.[email protected])[1]', 'nvarchar(128)') +'='+ parameter_list.param_node.value('(.[email protected])[1]', 'nvarchar(max)') AS paramlist, ISNULL(@plan_xml.value('([email protected])[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] 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('(.[email protected])[1]', 'nvarchar(128)') +'='+ parameter_list.param_node.value('(.[email protected])[1]', 'nvarchar(max)') AS paramlist, ISNULL(@plan_xml.value('([email protected])[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
第五步创建AutocaptureElapsedHighSQL作业
USE [msdb]GO/****** 对象: Job [自动抓取耗时SQL] 脚本日期: 07/29/2014 15:44:57 ******/BEGIN TRANSACTIONDECLARE @ReturnCode INTSELECT @ReturnCode = 0/****** 对象: JobCategory [[Uncategorized (Local)]]] 脚本日期: 07/29/2014 15:44:57 ******/IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)BEGINEXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackENDDECLARE @jobId BINARY(16)EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'AutocaptureElapsedHighSQL', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'自动抓取耗时SQL', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'sa', @job_id = @jobId OUTPUTIF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback/****** 对象: Step [execute usp_checkElapsedHighSQL script] 脚本日期: 07/29/2014 15:44:58 ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_name=N'AutocaptureElapsedHighSQL', @step_name=N'execute usp_checkElapsedHighSQL script', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'exec [dbo].[usp_checkElapsedHighSQL] null', --调用存储过程 @database_name=N'MonitorElapsedHighSQL', @flags=0IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_name=N'AutocaptureElapsedHighSQL', @name=N'ScheduleAutocaptureCheck', @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=4, @freq_subday_interval=1, --每一分钟抓取一次耗时SQL @freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20110224, @active_end_date=99991231, @active_start_time=200, @active_end_time=235959IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_name=N'AutocaptureElapsedHighSQL', @server_name = N'(local)'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave:
第六步创建ResetcheckElapsedHighSQLtbname作业
USE [msdb]GO/****** 对象: Job [定时改表名] 脚本日期: 07/29/2014 15:44:57 ******/BEGIN TRANSACTIONDECLARE @ReturnCode INTSELECT @ReturnCode = 0/****** 对象: JobCategory [[Uncategorized (Local)]]] 脚本日期: 07/29/2014 15:44:57 ******/IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)BEGINEXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackENDDECLARE @jobId BINARY(16)EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'ResetcheckElapsedHighSQLtbname', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'修改抓取耗时SQL的表名', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'sa', @job_id = @jobId OUTPUTIF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback/****** 对象: Step [execute usp_checkElapsedHighSQL script] 脚本日期: 07/29/2014 15:44:58 ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_name=N'ResetcheckElapsedHighSQLtbname', @step_name=N'execute usp_Resettbname script', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'exec [dbo].[usp_Resettbname] ', --调用存储过程 @database_name=N'MonitorElapsedHighSQL', @flags=0IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_name=N'ResetcheckElapsedHighSQLtbname', @name=N'Scheduleusp_Resettbname', @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=1, @freq_subday_interval=1, @freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20110224, @active_end_date=99991231, @active_start_time=235900, @active_end_time=235959IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_name=N'ResetcheckElapsedHighSQLtbname', @server_name = N'(local)'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave:
原理解释：
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
--调用示例--不提供参数，抓取最耗时的一个SQLEXEC [MonitorElapsedHighSQL].[dbo].[usp_checkElapsedHighSQL] NULL--提供sessionsid参数，抓取那个sessionid相关的SQLEXEC [MonitorElapsedHighSQL].[dbo].[usp_checkElapsedHighSQL] NULL
效果
USE [sss]GOWHILE 1=1BEGINDECLARE @test NVARCHAR(100)SET @test='你好'DECLARE @id intSET @id=2SELECT * FROM [sss].[dbo].[test] WHERE [id]=@idEXEC [dbo].[aa] @test =@testEXEC [dbo].[ab] @id=@idEND
SELECT * FROM [dbo].[ElapsedHigh]go
可以看到，参数也能抓取到，一般依靠sys.dm_exec_sql_text视图和sys.[fn_get_sql]()视图是无法获取到参数的
SQL Server profiler也是，它是整个RPC和Statement去抓
而且还会抓取当时的XML执行计划，点击它就能显示图形化的执行计划，这样对分析当时语句的执行情况非常有帮助
如有不对的地方，欢迎大家拍砖o(∩_∩)o
- 5楼htmlhtml
- mark~~~~~，，，可以一起交流吗，，技术群：452982916
- Re: 桦仔
- @我和小菜，引用厉害，学习，@曾祥旺，引用mark，@htmlhtml，引用mark~~~~~，，，可以一起交流吗，，技术群：452982916，可以的，谢谢
- 4楼曾祥旺
- mark
- 3楼muki
- 很强大.
- 2楼我和小菜
- 厉害，学习
- 1楼七不语v
- 学习了。很有用。
- Re: 桦仔
- @七不语v，引用学习了。很有用。，希望对您有用