SQL Server定时自动抓取耗时SQL并归档数据脚本分享-阿里云开发者社区

开发者社区> 杰克.陈> 正文

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

简介: 原文:SQL Server定时自动抓取耗时SQL并归档数据脚本分享 SQL Server定时自动抓取耗时SQL并归档数据脚本分享 第一步建库 USE [master] GO CREATE DATABASE [MonitorElapsedHighSQL] GO 第二步创建sp_who3存储过程 -- http://sqlserverplanet.
+关注继续查看
原文:SQL Server定时自动抓取耗时SQL并归档数据脚本分享

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
View Code

 

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

USE [MonitorElapsedHighSQL]
GO
/****** Object:  StoredProcedure [dbo].[usp_checkElapsedHighSQL]    Script Date: 2015/6/19 15:22:25 ******/
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
                    )
            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]
                        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

 

第五步创建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

 

 

原理解释:

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去抓

而且还会抓取当时的XML执行计划,点击它就能显示图形化的执行计划,这样对分析当时语句的执行情况非常有帮助

 

 

 

如有不对的地方,欢迎大家拍砖o(∩_∩)o 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SQL Server定时自动抓取耗时SQL并归档数据脚本分享
原文:SQL Server定时自动抓取耗时SQL并归档数据脚本分享 SQL Server定时自动抓取耗时SQL并归档数据脚本分享 第一步建库 USE [master] GO CREATE DATABASE [MonitorElapsedHighSQL] GO 第二步创建sp_who3存储过程 -- http://sqlserverplanet.
968 0
境外银行数据治理——思路分享
前言:在数据治理领域的文章大多都是从业务角度进行切入,本文从纯技术角度切入,介绍了数据血缘关系的图分析思路、领域建模层面构建中间层的思路。以及纯粹从词法分析和语法分析的统计学层面维度。
76 0
PowerDesigner-VBSrcipt-自动设置主键,外键名等(SQL Server)
原文:PowerDesigner-VBSrcipt-自动设置主键,外键名等(SQL Server) 在PowerDesigner中的设计SQL Server 数据表时,要求通过vbScript脚本实现下面的功能:   主键:pk_TableName 外键:fk_TableName_Foreig...
774 0
分享一个SQLSERVER脚本
原文:分享一个SQLSERVER脚本 分享一个SQLSERVER脚本 很多时候我们都需要计算数据库中各个表的数据量很每行记录所占用空间 这里共享一个脚本 CREATE TABLE #tablespaceinfo ( nameinfo VARCHAR(50) , ...
828 0
+关注
杰克.陈
一个安静的程序猿~
10427
文章
2
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载