• sqlServer 检测慢 sql

    部署监控：
    部署慢SQL与死锁跟踪
    Skip to end of metadata
    仅SQL Server 2012及以上版本可用.
    执行前请确保路径正确！
    慢SQL定义：执行时间超过0.5秒即定义为慢SQL，会被捕获。
    SSMS中新建查询窗口，将下面代码贴上后执行。
    该代码会新建一个[YX_Monitor]库，库中包含[DeadlockDetail]、[SlowSqlDetail]、[SlowSqlReadLog]、[BlockDetail]四个表
    DeadlockDetail：死锁明细记录数据，可查看死锁相关信息。
    SlowSqlDetail：慢SQL明细数据，可查看所有慢SQL的执行情况。
    SlowSqlReadLog：监控读取记录表，仅用于监控识别数据读取。
    BlockDetail：记录阻塞信息。

    用前必读：
    脚本初始部分有一段注释，这个注释中的内容是通过打开SQL Server的CMD调用开关，调用CMD命令来创建保存跟踪文件的文件夹。此命令通常用于我们无法远程上服务器本机创建文件夹路径时使用。
    如果需要使用，请在打开开关时注意[show advanced options] 与[xp_cmdshell]开关的状态，0为关闭，1为开启。使用后请恢复原位。

    -----------------------------------------------------------------------------------------
------------执行前请确保路径 D:\TraceFile  存在，如要更改路径请先更改后再执行------------
-----------------------------------------------------------------------------------------
/***
如不能远程到服务器，使用xp_cmdshell创建文件夹路径
-- 开启
exec sp_configure 'show advanced options',1
go
reconfigure with override
go
exec sp_configure 'xp_cmdshell',1
go
reconfigure with override
go
       
exec sys.xp_cmdshell 'dir D:\TraceFile' --查看文件夹
exec sys.xp_cmdshell 'mkdir D:\TraceFile' --新建文件夹
exec sys.xp_cmdshell 'rd D:\TraceFile' --删除文件夹
       
--关闭
exec sp_configure 'xp_cmdshell',0
go
reconfigure with override
go
exec sp_configure 'show advanced options',0
go
reconfigure with override
go
       
***/
----0.阻塞阈值设定
exec sp_configure 'show advanced options',1
reconfigure with override
go
exec sp_configure 'xp_cmdshell',1
reconfigure with override
go
EXEC sys.sp_configure N'blocked process threshold (s)', N'5'
reconfigure with override
GO
exec sp_configure 'xp_cmdshell',0
reconfigure with override
go
exec sp_configure 'show advanced options',0
reconfigure with override
go
    
----1.建库
use master
go
if(select name from sys.databases where name='YX_Monitor') is null
begin
    CREATE DATABASE YX_Monitor
       
       
    ALTER DATABASE [YX_Monitor] MODIFY FILE ( NAME = N'YX_Monitor', SIZE = 65536KB , FILEGROWTH = 65536KB )
    ALTER DATABASE [YX_Monitor] MODIFY FILE ( NAME = N'YX_Monitor_log', SIZE = 65536KB , FILEGROWTH = 65536KB )
end
go
       
----2.建表
use YX_Monitor
go
       
if object_id('YX_Monitor.dbo.SlowSqlReadLog') is null
BEGIN
    CREATE TABLE [dbo].[SlowSqlReadLog](
        [TransactionNumber] [bigint] IDENTITY(1,1) NOT NULL,
        [LogServer] [nvarchar](100) NULL,
        [LogTime] [datetime] NULL CONSTRAINT [DF_SlowSqlReadLog_LogTime]  DEFAULT (getdate()),
        [Last_Event_Time] [datetime2](7) NULL,
    CONSTRAINT [PK_SlowSqlReadLog] PRIMARY KEY CLUSTERED
    (
        [TransactionNumber] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
end
       
       
if object_id('YX_Monitor.dbo.SlowSqlDetail') is null
BEGIN
           
    CREATE TABLE [dbo].[SlowSqlDetail](
        [TransactionNumber] [bigint] NULL,
        [LogServer] [nvarchar](100) NULL,
        [EventTime] [datetime2](7) NULL,
        [EventName] [nvarchar](128) NULL,
        [statement] [nvarchar](max) NULL,
        [Sql_Text] [nvarchar](max) NULL,
        [Cpu] [bigint] NULL,
        [Logical_Reads] [bigint] NULL,
        [Physical_reads] [bigint] NULL,
        [Writes] [bigint] NULL,
        [Duration_ms] [bigint] NULL,
        [username] [nvarchar](128) NULL,
        [DatabaseName] [nvarchar](128) NULL,
        [ClientHostName] [nvarchar](128) NULL,
        [ClientAppName] [nvarchar](128) NULL,
        [SessionId] [int] NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
       
    create clustered index CIX_SlowSqlDetail_TransactionNumber on SlowSqlDetail(TransactionNumber,EventTime)
end
       
if object_id('yx_monitor.dbo.DeadlockDetail') is null
begin
    CREATE TABLE [dbo].[DeadlockDetail](
        [EventTime] [datetime2](7) NULL,
        [LogServer] [varchar](30) NULL,
        [InputBuffer] [nvarchar](max) NULL,
        [lockMode] [varchar](10) NULL,
        [spid] [int] NULL,
        [hostname] [varchar](50) NULL,
        [clientapp] [varchar](100) NULL,
        [transactionname] [varchar](50) NULL,
        [status] [varchar](20) NULL,
        [waitresource] [varchar](200) NULL
    )
       
    create clustered index CIX_DeadlockDetail_EventTime on DeadlockDetail([EventTime],[LogServer])
end
       
     
if object_id('YX_Monitor.dbo.BlockedDetail') is null
BEGIN
           
    create table BlockedDetail(
        EventTime datetime2,
        LogServer varchar(30),
        SPID int,
        Process_Type varchar(10),
        [Status] varchar(20),
        BlockedTime_ms bigint,
        LockMode varchar(10),
        WaitResource varchar(100),
        InputBuffer nvarchar(max),
        ClientApp varchar(100),
        HostName varchar(50)
    )
       
    create clustered index CIX_BlockedDetail_EventTime on BlockedDetail(EventTime)
end
     
go
       
       
----3.扩展事件慢SQL会话
DECLARE @sessionName NVARCHAR(100),@createAndBegin BIT,@fileLocation NVARCHAR(100),@sql NVARCHAR(max)
    ,@cpuFilter NVARCHAR(100),@durationFilter_s decimal(4,2),@durationFilter_us NVARCHAR(100),@maxSizeMB NVARCHAR(10),@rolloverNum NVARCHAR(10)
    ,@rpc_completed TINYINT,@sp_statement_completed TINYINT,@sql_batch_completed TINYINT,@sql_statement_completed TINYINT
       
       
SELECT @sessionName=N'DB_SlowSql'
    ,@fileLocation=N'D:\TraceFile\'+@sessionName+'.xel'
    ,@cpuFilter=1000
    ,@durationFilter_s=0.5
    ,@maxSizeMB=3
    ,@rolloverNum=1
    ,@createAndBegin=1
    ,@rpc_completed=1
    ,@sp_statement_completed=0
    ,@sql_batch_completed=1
    ,@sql_statement_completed=0
    ,@durationFilter_us=cast(@durationFilter_s*1000*1000 as int)
       
IF(@rpc_completed+@sp_statement_completed+@sql_batch_completed+@sql_statement_completed)=0
BEGIN
    RAISERROR('至少选择一种跟踪事件!',16,3)
    RETURN
END
       
IF exists(SELECT * FROM sys.server_event_sessions WHERE name=@SessionName)
BEGIN
    RAISERROR('警告：扩展事件会话 %s 已存在，无法创建重命名会话！该步骤已跳过！',10,1,@SessionName)
    RETURN
END
ELSE
BEGIN
       
SET @sql=N'
CREATE EVENT SESSION ['+@SessionName+'] ON SERVER
'
       
IF(@rpc_completed=1)
BEGIN
    SET @sql=@sql+'ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.client_app_name
    ,sqlserver.client_hostname
    ,sqlserver.database_id
    ,sqlserver.database_name
    ,sqlserver.request_id
    ,sqlserver.session_id
    ,sqlserver.sql_text
    ,sqlserver.username)
    WHERE ([cpu_time]>='+@cpuFilter+' and duration>='+@durationFilter_us+')),'
END
IF(@sp_statement_completed=1)
BEGIN
    SET @sql=@sql+'ADD EVENT sqlserver.sp_statement_completed(
    ACTION(sqlserver.client_app_name
    ,sqlserver.client_hostname
    ,sqlserver.database_id
    ,sqlserver.database_name
    ,sqlserver.request_id
    ,sqlserver.session_id
    ,sqlserver.sql_text
    ,sqlserver.username)
    WHERE ([cpu_time]>='+@cpuFilter+' and duration>='+@durationFilter_us+')),'
END
IF(@sql_batch_completed=1)
BEGIN
    SET @sql=@sql+'ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1)
    ACTION(sqlserver.client_app_name
    ,sqlserver.client_hostname
    ,sqlserver.database_id
    ,sqlserver.database_name
    ,sqlserver.request_id
    ,sqlserver.session_id
    ,sqlserver.sql_text
    ,sqlserver.username)
    WHERE ([cpu_time]>='+@cpuFilter+' and duration>='+@durationFilter_us+')),'
END
IF(@sql_statement_completed=1)
BEGIN
    SET @sql=@sql+'ADD EVENT sqlserver.sql_statement_completed (
    ACTION(sqlserver.client_app_name
    ,sqlserver.client_hostname
    ,sqlserver.database_id
    ,sqlserver.database_name
    ,sqlserver.request_id
    ,sqlserver.session_id
    ,sqlserver.sql_text
    ,sqlserver.username)
    WHERE ([cpu_time]>='+@cpuFilter+' and duration>='+@durationFilter_us+')),'
END
       
--去除最后的逗号
SELECT @sql=left(@sql,LEN(@sql)-1)
       
SET @sql=@sql+'ADD TARGET package0.event_file(SET filename=N'''+@fileLocation+''',max_file_size=('+@maxSizeMB+'),max_rollover_files=('+@rolloverNum+'))'
set @sql=@sql+'WITH (MAX_MEMORY=8192 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_NODE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)'
       
       
IF(@CreateAndBegin=1)
    SELECT @sql=@sql+'
ALTER EVENT SESSION ['+@SessionName+'] ON SERVER STATE = start;'
       
PRINT @sql
EXEC(@sql)
       
end
       
GO
       
       
----4.扩展事件死锁会话
DECLARE @sessionName NVARCHAR(100),@createAndBegin BIT,@fileLocation NVARCHAR(100),@sql NVARCHAR(max)
    ,@maxSizeMB NVARCHAR(10),@rolloverNum NVARCHAR(10)
           
SELECT @sessionName=N'DB_Deadlock'
    ,@fileLocation=N'D:\TraceFile\'+@sessionName+'.xel'
    ,@maxSizeMB=3
    ,@rolloverNum=1
    ,@createAndBegin=1
       
       
       
IF exists(SELECT * FROM sys.server_event_sessions WHERE name=@SessionName)
BEGIN
    RAISERROR('警告：扩展事件会话 %s 已存在，无法创建重命名会话！该步骤已跳过！',10,1,@SessionName)
    RETURN
END
ELSE
BEGIN
       
SET @sql=N'
CREATE EVENT SESSION ['+@SessionName+'] ON SERVER
'
       
SET @sql=@sql+'ADD EVENT sqlserver.xml_deadlock_report(
    ACTION(sqlserver.client_app_name
    ,sqlserver.client_hostname
    ,sqlserver.database_id
    ,sqlserver.database_name
    ,sqlserver.request_id
    ,sqlserver.session_id
    ,sqlserver.sql_text
    ,sqlserver.username))
'
SET @sql=@sql+'ADD TARGET package0.event_file(SET filename=N'''+@fileLocation+''',max_file_size=('+@maxSizeMB+'),max_rollover_files=('+@rolloverNum+'))'
set @sql=@sql+'WITH (MAX_MEMORY=8192 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_NODE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)'
       
       
IF(@CreateAndBegin=1)
    SELECT @sql=@sql+'
ALTER EVENT SESSION ['+@SessionName+'] ON SERVER STATE = start;'
       
PRINT @sql
EXEC(@sql)
       
end
GO
     
     
----5.扩展事件阻塞会话
DECLARE @sessionName NVARCHAR(100),@createAndBegin BIT,@fileLocation NVARCHAR(100),@sql NVARCHAR(max)
    ,@maxSizeMB NVARCHAR(10),@rolloverNum NVARCHAR(10)
           
SELECT @sessionName=N'DB_Blocked'
    ,@fileLocation=N'D:\TraceFile\'+@sessionName+'.xel'
    ,@maxSizeMB=3
    ,@rolloverNum=1
    ,@createAndBegin=1
     
IF exists(SELECT * FROM sys.server_event_sessions WHERE name=@SessionName)
BEGIN
    RAISERROR('警告：扩展事件会话 %s 已存在，无法创建重命名会话！该步骤已跳过！',10,1,@SessionName)
    RETURN
END
ELSE
BEGIN
       
SET @sql=N'
CREATE EVENT SESSION ['+@SessionName+'] ON SERVER
'
       
SET @sql=@sql+'ADD EVENT sqlserver.blocked_process_report(
    ACTION(sqlserver.client_app_name
    ,sqlserver.client_hostname
    ,sqlserver.database_id
    ,sqlserver.database_name
    ,sqlserver.session_id
    ,sqlserver.sql_text
    ,sqlserver.username))
'
SET @sql=@sql+'ADD TARGET package0.event_file(SET filename=N'''+@fileLocation+''',max_file_size=('+@maxSizeMB+'),max_rollover_files=('+@rolloverNum+'))'
set @sql=@sql+'WITH (MAX_MEMORY=8192 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_NODE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)'
       
       
IF(@CreateAndBegin=1)
    SELECT @sql=@sql+'
ALTER EVENT SESSION ['+@SessionName+'] ON SERVER STATE = start;'
       
PRINT @sql
EXEC(@sql)
       
end
GO
     
/***
新增CPU利用率记录
***/
use YX_Monitor
go
if OBJECT_ID('CpuUsage') is not null
drop table CpuUsage
   
create table CpuUsage(
    EventTime varchar(16),
    SQLUsage int,
    OtherUsage int,
    TotalUsage int,
    SystemIdle int
)
       
CREATE CLUSTERED INDEX CIX_CpuUsage_EventTime on CpuUsage(EventTime)
go
   
----5.创建JOB -- 为确保作业正确，会删除老JOB然后新建JOB
USE [msdb]
GO
     
if(select count(1) from msdb.dbo.sysjobs where name='(每90秒) 读取监控文件')>0
begin
    declare @job_id uniqueidentifier
    select @job_id = job_id from msdb.dbo.sysjobs where name='(每90秒) 读取监控文件'
       
    EXEC msdb.dbo.sp_delete_job @job_id=@job_id, @delete_unused_schedule=1
end
go
     
/****** Object:  Job [(每90秒) 读取监控文件]    Script Date: 2022-11-14 9:12:44 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 2022-11-14 9:12:44 ******/
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'(每90秒) 读取监控文件',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'(每90秒) 读取监控文件',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [slowsql]    Script Date: 2022-11-14 9:12:44 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'slowsql',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=3,
        @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'use YX_Monitor
go
--pepare data
declare @fileLocation nvarchar(128)
     
select @fileLocation=cast(esf.value as nvarchar(128)) from sys.server_event_sessions es
inner join sys.server_event_session_fields esf
on es.event_session_id=esf.event_session_id
where esf.name=''filename''
and es.name =''DB_SlowSql''
select @fileLocation=left(@fileLocation,LEN(@fileLocation)-4)+''*.xel''
     
set QUOTED_IDENTIFIER on
     
if object_id(''tempdb..#tmp'') is not null
    drop table #tmp
     
;WITH events_cte AS (
    SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP), xevents.event_data.value(''(event/@timestamp)[1]'', ''datetime2'')) AS [EventTime]
        ,xevents.event_data.value(''(event/@name)[1]'', ''nvarchar(128)'') AS [EventName]
        , xevents.event_data.value(''(event/data[@name="statement"]/value)[1]'', ''nvarchar(max)'') AS [statement]
        , xevents.event_data.value(''(event/action[@name="sql_text"]/value)[1]'', ''nvarchar(max)'') AS [Sql_Text]
        , xevents.event_data.value(''(event/data[@name="cpu_time"]/value)[1]'', ''bigint'') / 1000 AS [CPU]
        , xevents.event_data.value(''(event/data[@name="logical_reads"]/value)[1]'', ''bigint'') AS [Logical_Reads]
        , xevents.event_data.value(''(event/data[@name="physical_reads"]/value)[1]'', ''bigint'') AS [Physical_reads]
        , xevents.event_data.value(''(event/data[@name="writes"]/value)[1]'', ''bigint'') AS [Writes]
        , xevents.event_data.value(''(event/data[@name="duration"]/value)[1]'', ''bigint'') / 1000 AS [Duration_ms]
        , xevents.event_data.value(''(event/action[@name="username"]/value)[1]'', ''nvarchar(128)'') AS [username]
        , xevents.event_data.value(''(event/action[@name="database_name"]/value)[1]'', ''nvarchar(128)'') AS [DatabaseName]
        , xevents.event_data.value(''(event/action[@name="client_hostname"]/value)[1]'', ''nvarchar(128)'') AS [ClientHostName]
        , xevents.event_data.value(''(event/action[@name="client_app_name"]/value)[1]'', ''nvarchar(128)'') AS [ClientAppName]
        , xevents.event_data.value(''(event/action[@name="session_id"]/value)[1]'', ''nvarchar(128)'') AS [SessionId]
    FROM sys.fn_xe_file_target_read_file( @fileLocation, NULL, NULL, NULL)
    CROSS APPLY ( SELECT CAST(event_data AS XML ) AS event_data) xevents
)
SELECT *
into #tmp
FROM events_cte
ORDER BY [EventTime] DESC;
     
     
declare @LogSrever nvarchar(128),@last_Event_Time datetime2,@transactionNumber bigint,@before_last_Event_Time datetime2
     
select @before_last_Event_Time=(select top 1 last_Event_Time
    from SlowSqlReadLog
    where LogServer=@@SERVERNAME
    order by LogTime desc)
     
select @LogSrever=@@SERVERNAME,@last_Event_Time=(select top 1 EventTime
    from #tmp
    ORDER BY [EventTime] DESC)
     
if (isnull(@before_last_Event_Time,'''')<@Last_event_time)
begin
    --SlowSqlReadLog
    insert into SlowSqlReadLog(LogServer,Last_event_time)
    select @LogSrever,@Last_event_time
     
    --SlowSqlDetail
    select @transactionNumber=SCOPE_IDENTITY()
     
    insert into SlowSqlDetail(
    TransactionNumber
    ,LogServer
    ,EventTime
    ,EventName
    ,statement
    ,Sql_Text
    ,Cpu
    ,Logical_Reads
    ,Physical_reads
    ,Writes
    ,Duration_ms
    ,username
    ,DatabaseName
    ,ClientHostName
    ,ClientAppName
    ,SessionId)
    select @transactionNumber
    ,@LogSrever
    ,EventTime
    ,EventName
    ,statement
    ,Sql_Text
    ,Cpu
    ,Logical_Reads
    ,Physical_reads
    ,Writes
    ,Duration_ms
    ,username
    ,DatabaseName
    ,ClientHostName
    ,ClientAppName
    ,SessionId
    from #tmp
    where EventTime between isnull(@before_last_Event_Time,'''') and @last_Event_Time
end
     
',
        @database_name=N'YX_Monitor',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [deadlock]    Script Date: 2022-11-14 9:12:44 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'deadlock',
        @step_id=2,
        @cmdexec_success_code=0,
        @on_success_action=3,
        @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'use YX_Monitor
go
--pepare data
declare @fileLocation nvarchar(128)
     
select @fileLocation=cast(esf.value as nvarchar(128)) from sys.server_event_sessions es
inner join sys.server_event_session_fields esf
on es.event_session_id=esf.event_session_id
where esf.name=''filename''
and es.name =''DB_Deadlock''
select @fileLocation=left(@fileLocation,LEN(@fileLocation)-4)+''*.xel''
     
set QUOTED_IDENTIFIER on
     
if object_id(''tempdb..#tmpDeadLock'') is not null
    drop table #tmpDeadLock
     
select CAST(event_data AS XML ) AS event_data
into #tmpDeadLock
from  sys.fn_xe_file_target_read_file( @fileLocation, NULL, NULL, NULL)
     
--select * from #tmpDeadLock
     
declare @eventXML xml,@maxTime datetime2
     
select @maxTime=isnull(max(EventTime),'''')
from DeadlockDetail
     
DECLARE XML_Cursor CURSOR
LOCAL STATIC FORWARD_ONLY
READ_ONLY
FOR
SELECT event_data from #tmpDeadLock
OPEN XML_Cursor
FETCH NEXT FROM XML_Cursor INTO @eventXML
WHILE @@fetch_status = 0
BEGIN
    declare @process xml,@resource xml,@eventTime datetime2
     
    select @eventTime=DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP), t1.c1.value(''@timestamp'', ''datetime2''))
    from @eventXML.nodes(''/event'') as T1(C1)
     
     
    if(@eventTime>@maxTime)
    begin
        select
        @process=t1.c1.query(''./process-list'')
        from @eventXML.nodes(''/event/data/value/deadlock'') as T1(C1)
     
        insert into DeadlockDetail
        select @eventTime AS [EventTime]
        ,@@SERVERNAME as [LogServer]
        ,T1.C1.value(''(./inputbuf)[1]'',''varchar(max)'') as inputbuffer
        ,T1.C1.value(''@lockMode'',''varchar(100)'') as lockMode
        ,T1.C1.value(''@spid'',''varchar(100)'') as spid
        ,T1.C1.value(''@hostname'',''varchar(100)'') as hostname
        ,T1.C1.value(''@clientapp'',''varchar(100)'') as clientapp
        ,T1.C1.value(''@transactionname'',''varchar(100)'') as transactionname
        ,T1.C1.value(''@status'',''varchar(100)'') as status
        ,T1.C1.value(''@waitresource'',''varchar(100)'') as waitresource
        from @process.nodes(''/process-list/process'') as T1(C1)
    end
     
    FETCH NEXT FROM XML_Cursor INTO @eventXML
END
CLOSE XML_Cursor
DEALLOCATE XML_Cursor
     
',
        @database_name=N'YX_Monitor',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [blocked]    Script Date: 2022-11-14 9:12:44 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'blocked',
        @step_id=3,
        @cmdexec_success_code=0,
        @on_success_action=3,
        @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'use YX_Monitor
go
--pepare data
declare @fileLocation nvarchar(128)
     
select @fileLocation=cast(esf.value as nvarchar(128)) from sys.server_event_sessions es
inner join sys.server_event_session_fields esf
on es.event_session_id=esf.event_session_id
where esf.name=''filename''
and es.name =''DB_Blocked''
select @fileLocation=left(@fileLocation,LEN(@fileLocation)-4)+''*.xel''
     
set QUOTED_IDENTIFIER on
     
if object_id(''tempdb..#tmpBlocked'') is not null
    drop table #tmpBlocked
     
select CAST(event_data AS XML ) AS event_data
into #tmpBlocked
from  sys.fn_xe_file_target_read_file( @fileLocation, NULL, NULL, NULL)
     
--select * from #tmpBlocked
     
declare @eventXML xml,@maxTime datetime2
     
select @maxTime=isnull(max(EventTime),'''')
from BlockedDetail
     
DECLARE XML_Cursor CURSOR
LOCAL STATIC FORWARD_ONLY
READ_ONLY
FOR
SELECT event_data from #tmpBlocked
OPEN XML_Cursor
FETCH NEXT FROM XML_Cursor INTO @eventXML
WHILE @@fetch_status = 0
BEGIN
    declare @blocked xml,@blocking xml,@resource xml,@eventTime datetime2,@duration_ms bigint
     
    select @eventTime=DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP), t1.c1.value(''@timestamp'', ''datetime2''))
    from @eventXML.nodes(''/event'') as T1(C1)
     
    select @duration_ms= t1.c1.value(''(./value)[1]'',''bigint'')
    from @eventXML.nodes(''/event/data[@name="duration"]'') as T1(C1)
     
    if(@eventTime>@maxTime)
    begin
        select
        @blocked=t1.c1.query(''blocked-process'')
        from @eventXML.nodes(''/event/data[@name="blocked_process"]/value/blocked-process-report'') as T1(C1)
     
        select
        @blocking=t1.c1.query(''blocking-process'')
        from @eventXML.nodes(''/event/data[@name="blocked_process"]/value/blocked-process-report'') as T1(C1)
     
        insert into BlockedDetail
        select @eventTime as EventTime
        ,@@SERVERNAME as [LogServer]
        ,T1.C1.value(''@spid'',''varchar(100)'') as spid
        ,''blocked'' AS PROCESS_TYPE
        ,T1.C1.value(''@status'',''varchar(100)'') as status
        ,@duration_ms/1000 as BlockedTime_ms
        ,T1.C1.value(''@lockMode'',''varchar(100)'') as lockMode
        ,T1.C1.value(''@waitresource'',''varchar(100)'') as waitresource
        ,T1.C1.value(''(./inputbuf)[1]'',''varchar(max)'') as inputbuffer
        ,T1.C1.value(''@clientapp'',''varchar(100)'') as clientapp
        ,T1.C1.value(''@hostname'',''varchar(100)'') as hostname
        from @blocked.nodes(''/blocked-process/process'') as T1(C1)
        union all
        select @eventTime as EventTime
        ,@@SERVERNAME as [LogServer]
        ,T1.C1.value(''@spid'',''varchar(100)'') as spid
        ,''blocking'' AS PROCESS_TYPE
        ,T1.C1.value(''@status'',''varchar(100)'') as status
        ,@duration_ms/1000 as BlockedTime_ms
        ,T1.C1.value(''@lockMode'',''varchar(100)'') as lockMode
        ,T1.C1.value(''@waitresource'',''varchar(100)'') as waitresource
        ,T1.C1.value(''(./inputbuf)[1]'',''varchar(max)'') as inputbuffer
        ,T1.C1.value(''@clientapp'',''varchar(100)'') as clientapp
        ,T1.C1.value(''@hostname'',''varchar(100)'') as hostname
        from @blocking.nodes(''/blocking-process/process'') as T1(C1)
    end
    FETCH NEXT FROM XML_Cursor INTO @eventXML
END
CLOSE XML_Cursor
DEALLOCATE XML_Cursor
     
',
        @database_name=N'master',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [cpuusage]    Script Date: 2022-11-14 9:12:44 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'cpuusage',
        @step_id=4,
        @cmdexec_success_code=0,
        @on_success_action=3,
        @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'set QUOTED_IDENTIFIER on
DECLARE @ts_now BIGINT;
SET  @ts_now= ( SELECT  cpu_ticks / ( cpu_ticks / ms_ticks ) FROM sys.dm_os_sys_info WITH ( NOLOCK ));
    
    
MERGE INTO YX_Monitor.dbo.CpuUsage a
USING
(
SELECT convert(varchar(16),DATEADD(ms, -1*(@ts_now-[timestamp]), GETDATE()),120) AS EventTime
    ,SQLProcessUtilization                                  AS SQLUsage
    ,100 - SystemIdle - SQLProcessUtilization               AS OtherUsage
    ,100 - SystemIdle                                       AS TotalUsage
    ,SystemIdle                                             AS SystemIdle
 FROM   (
    SELECT    record.value(''(./Record/@id)[1]'', ''int'') AS record_id ,
    record.value(''(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]'',''int'') AS [SystemIdle] ,
    record.value(''(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]'',''int'') AS [SQLProcessUtilization] ,
    [timestamp]
    FROM(
        SELECT    [timestamp] ,
        CONVERT(XML, record) AS [record]
        FROM      sys.dm_os_ring_buffers WITH ( NOLOCK )
        WHERE     ring_buffer_type = N''RING_BUFFER_SCHEDULER_MONITOR''
        AND record LIKE N''%%''
    ) AS x
) AS y
)  b
    
ON ( a.EventTime=b.EventTime)
WHEN NOT MATCHED THEN
INSERT (EventTime,SQLUsage,OtherUsage,TotalUsage,SystemIdle) VALUES(b.EventTime,b.SQLUsage,b.OtherUsage,b.TotalUsage,b.SystemIdle);
   
   
',
        @database_name=N'master',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [clearHistory]    Script Date: 2022-11-14 9:12:44 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'clearHistory',
        @step_id=5,
        @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'delete YX_Monitor.dbo.SlowSqlDetail
where eventtime 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_id=@jobId, @name=N'1',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=2,
        @freq_subday_interval=90,
        @freq_relative_interval=0,
        @freq_recurrence_factor=0,
        @active_start_date=20220101,
        @active_end_date=99991231,
        @active_start_time=0,
        @active_end_time=235959,
        @schedule_uid=N'a53b9137-2256-4675-b5d1-fa13902cdc4f'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
   
   
   
    
    
/****
以下部分为慢SQL汇总统计部分
****/
use YX_Monitor
go
if object_id('SlowSqlExecLine') is not null
    drop table SlowSqlExecLine
    
create table SlowSqlExecLine(
    EventDate date,
    ClientAppName varchar(128),
    TotalCounts int,
    Line50 int,
    Line95 int
)
    
create clustered index CIX_SlowSqlExecLine_EventDate_ClientAppName on SlowSqlExecLine(EventDate,ClientAppName)
go
    
if object_id('SlowSqlExecTime') is not null
    drop table SlowSqlExecTime
    
create table SlowSqlExecTime(
    EventDate date,
    ClientAppName varchar(128),
    Counts int,
    [0_1sec] int,
    [1_2sec] int,
    [2_5sec] int,
    [5_10sec] int,
    [10+s] int
)
create clustered index CIX_SlowSqlExecTime_EventDate_ClientAppName on SlowSqlExecTime(EventDate,ClientAppName)
go
    
    
        
----创建JOB -- 为确保作业正确，会删除老JOB然后新建JOB
USE [msdb]
GO
      
if(select count(1) from msdb.dbo.sysjobs where name='(02:00)统计前一天的慢SQL')>0
begin
    declare @job_id uniqueidentifier
    select @job_id = job_id from msdb.dbo.sysjobs where name='(02:00)统计前一天的慢SQL'
        
    EXEC msdb.dbo.sp_delete_job @job_id=@job_id, @delete_unused_schedule=1
end
go
    
USE [msdb]
GO
    
/****** Object:  Job [(02:00)统计前一天的慢SQL]    Script Date: 2022-11-12 16:56:29 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 2022-11-12 16:56:29 ******/
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'(02:00)统计前一天的慢SQL',
        @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
/****** Object:  Step [1_execline]    Script Date: 2022-11-12 16:56:29 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'1_execline',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=3,
        @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'declare @today date=getdate()-1
    
    
insert into yx_monitor.dbo.SlowSqlExecLine
SELECT distinct @today,ClientAppName,count(1) OVER (PARTITION BY ClientAppName) as TotalCounts
,PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY Duration_ms)
OVER (PARTITION BY ClientAppName) AS Line50
,PERCENTILE_DISC(0.95) WITHIN GROUP (ORDER BY Duration_ms)
OVER (PARTITION BY ClientAppName) AS Line95
FROM (select (case
    when ClientAppName like ''%系统%'' then substring(ClientAppName,0,charindex(''系统'',clientappname)+2)
    when ClientAppName like ''%[[]%'' then substring(ClientAppName,0,charindex(''['',clientappname))
    else ClientAppName end) as ClientAppName,Duration_ms
from yx_monitor.dbo.SlowSqlDetail
where ClientAppName not like ''%SQLAgent%'' and EventTime between @today and dateadd(day,1,@today)
)x
order by TotalCounts desc',
        @database_name=N'master',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [2_exectime]    Script Date: 2022-11-12 16:56:29 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'2_exectime',
        @step_id=2,
        @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'declare @day date=getdate()-1
    
    
insert into YX_Monitor.dbo.SlowSqlExecTime
    
select @day,ClientAppName,count(1) as Counts
,sum(case when duration_ms<=1000 then 1 else 0 end) as ''0_1sec''
,sum(case when duration_ms > 1000 and duration_ms<=2000 then 1 else 0 end) as ''1_2sec''
,sum(case when duration_ms > 2000 and duration_ms<=5000 then 1 else 0 end) as ''2_5sec''
,sum(case when duration_ms > 5000 and duration_ms<=10000 then 1 else 0 end) as ''5_10sec''
,sum(case when duration_ms >10000 then 1 else 0 end) as ''10+s''
from (
select (case
    when ClientAppName like ''%系统%'' then substring(ClientAppName,0,charindex(''系统'',clientappname)+2)
    when ClientAppName like ''%[[]%'' then substring(ClientAppName,0,charindex(''['',clientappname))
    else ClientAppName end) as ClientAppName,Duration_ms
from YX_Monitor.dbo.SlowSqlDetail
where ClientAppName not like ''%SQLAgent%'' and EventTime between @day and dateadd(day,1,@day)
) x
group by ClientAppName
order by counts desc',
        @database_name=N'master',
        @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_id=@jobId, @name=N'0200',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=1,
        @freq_subday_interval=0,
        @freq_relative_interval=0,
        @freq_recurrence_factor=0,
        @active_start_date=20221112,
        @active_end_date=99991231,
        @active_start_time=20000,
        @active_end_time=235959,
        @schedule_uid=N'9dad6154-a685-4f52-9da4-539b232135db'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109
    • 110
    • 111
    • 112
    • 113
    • 114
    • 115
    • 116
    • 117
    • 118
    • 119
    • 120
    • 121
    • 122
    • 123
    • 124
    • 125
    • 126
    • 127
    • 128
    • 129
    • 130
    • 131
    • 132
    • 133
    • 134
    • 135
    • 136
    • 137
    • 138
    • 139
    • 140
    • 141
    • 142
    • 143
    • 144
    • 145
    • 146
    • 147
    • 148
    • 149
    • 150
    • 151
    • 152
    • 153
    • 154
    • 155
    • 156
    • 157
    • 158
    • 159
    • 160
    • 161
    • 162
    • 163
    • 164
    • 165
    • 166
    • 167
    • 168
    • 169
    • 170
    • 171
    • 172
    • 173
    • 174
    • 175
    • 176
    • 177
    • 178
    • 179
    • 180
    • 181
    • 182
    • 183
    • 184
    • 185
    • 186
    • 187
    • 188
    • 189
    • 190
    • 191
    • 192
    • 193
    • 194
    • 195
    • 196
    • 197
    • 198
    • 199
    • 200
    • 201
    • 202
    • 203
    • 204
    • 205
    • 206
    • 207
    • 208
    • 209
    • 210
    • 211
    • 212
    • 213
    • 214
    • 215
    • 216
    • 217
    • 218
    • 219
    • 220
    • 221
    • 222
    • 223
    • 224
    • 225
    • 226
    • 227
    • 228
    • 229
    • 230
    • 231
    • 232
    • 233
    • 234
    • 235
    • 236
    • 237
    • 238
    • 239
    • 240
    • 241
    • 242
    • 243
    • 244
    • 245
    • 246
    • 247
    • 248
    • 249
    • 250
    • 251
    • 252
    • 253
    • 254
    • 255
    • 256
    • 257
    • 258
    • 259
    • 260
    • 261
    • 262
    • 263
    • 264
    • 265
    • 266
    • 267
    • 268
    • 269
    • 270
    • 271
    • 272
    • 273
    • 274
    • 275
    • 276
    • 277
    • 278
    • 279
    • 280
    • 281
    • 282
    • 283
    • 284
    • 285
    • 286
    • 287
    • 288
    • 289
    • 290
    • 291
    • 292
    • 293
    • 294
    • 295
    • 296
    • 297
    • 298
    • 299
    • 300
    • 301
    • 302
    • 303
    • 304
    • 305
    • 306
    • 307
    • 308
    • 309
    • 310
    • 311
    • 312
    • 313
    • 314
    • 315
    • 316
    • 317
    • 318
    • 319
    • 320
    • 321
    • 322
    • 323
    • 324
    • 325
    • 326
    • 327
    • 328
    • 329
    • 330
    • 331
    • 332
    • 333
    • 334
    • 335
    • 336
    • 337
    • 338
    • 339
    • 340
    • 341
    • 342
    • 343
    • 344
    • 345
    • 346
    • 347
    • 348
    • 349
    • 350
    • 351
    • 352
    • 353
    • 354
    • 355
    • 356
    • 357
    • 358
    • 359
    • 360
    • 361
    • 362
    • 363
    • 364
    • 365
    • 366
    • 367
    • 368
    • 369
    • 370
    • 371
    • 372
    • 373
    • 374
    • 375
    • 376
    • 377
    • 378
    • 379
    • 380
    • 381
    • 382
    • 383
    • 384
    • 385
    • 386
    • 387
    • 388
    • 389
    • 390
    • 391
    • 392
    • 393
    • 394
    • 395
    • 396
    • 397
    • 398
    • 399
    • 400
    • 401
    • 402
    • 403
    • 404
    • 405
    • 406
    • 407
    • 408
    • 409
    • 410
    • 411
    • 412
    • 413
    • 414
    • 415
    • 416
    • 417
    • 418
    • 419
    • 420
    • 421
    • 422
    • 423
    • 424
    • 425
    • 426
    • 427
    • 428
    • 429
    • 430
    • 431
    • 432
    • 433
    • 434
    • 435
    • 436
    • 437
    • 438
    • 439
    • 440
    • 441
    • 442
    • 443
    • 444
    • 445
    • 446
    • 447
    • 448
    • 449
    • 450
    • 451
    • 452
    • 453
    • 454
    • 455
    • 456
    • 457
    • 458
    • 459
    • 460
    • 461
    • 462
    • 463
    • 464
    • 465
    • 466
    • 467
    • 468
    • 469
    • 470
    • 471
    • 472
    • 473
    • 474
    • 475
    • 476
    • 477
    • 478
    • 479
    • 480
    • 481
    • 482
    • 483
    • 484
    • 485
    • 486
    • 487
    • 488
    • 489
    • 490
    • 491
    • 492
    • 493
    • 494
    • 495
    • 496
    • 497
    • 498
    • 499
    • 500
    • 501
    • 502
    • 503
    • 504
    • 505
    • 506
    • 507
    • 508
    • 509
    • 510
    • 511
    • 512
    • 513
    • 514
    • 515
    • 516
    • 517
    • 518
    • 519
    • 520
    • 521
    • 522
    • 523
    • 524
    • 525
    • 526
    • 527
    • 528
    • 529
    • 530
    • 531
    • 532
    • 533
    • 534
    • 535
    • 536
    • 537
    • 538
    • 539
    • 540
    • 541
    • 542
    • 543
    • 544
    • 545
    • 546
    • 547
    • 548
    • 549
    • 550
    • 551
    • 552
    • 553
    • 554
    • 555
    • 556
    • 557
    • 558
    • 559
    • 560
    • 561
    • 562
    • 563
    • 564
    • 565
    • 566
    • 567
    • 568
    • 569
    • 570
    • 571
    • 572
    • 573
    • 574
    • 575
    • 576
    • 577
    • 578
    • 579
    • 580
    • 581
    • 582
    • 583
    • 584
    • 585
    • 586
    • 587
    • 588
    • 589
    • 590
    • 591
    • 592
    • 593
    • 594
    • 595
    • 596
    • 597
    • 598
    • 599
    • 600
    • 601
    • 602
    • 603
    • 604
    • 605
    • 606
    • 607
    • 608
    • 609
    • 610
    • 611
    • 612
    • 613
    • 614
    • 615
    • 616
    • 617
    • 618
    • 619
    • 620
    • 621
    • 622
    • 623
    • 624
    • 625
    • 626
    • 627
    • 628
    • 629
    • 630
    • 631
    • 632
    • 633
    • 634
    • 635
    • 636
    • 637
    • 638
    • 639
    • 640
    • 641
    • 642
    • 643
    • 644
    • 645
    • 646
    • 647
    • 648
    • 649
    • 650
    • 651
    • 652
    • 653
    • 654
    • 655
    • 656
    • 657
    • 658
    • 659
    • 660
    • 661
    • 662
    • 663
    • 664
    • 665
    • 666
    • 667
    • 668
    • 669
    • 670
    • 671
    • 672
    • 673
    • 674
    • 675
    • 676
    • 677
    • 678
    • 679
    • 680
    • 681
    • 682
    • 683
    • 684
    • 685
    • 686
    • 687
    • 688
    • 689
    • 690
    • 691
    • 692
    • 693
    • 694
    • 695
    • 696
    • 697
    • 698
    • 699
    • 700
    • 701
    • 702
    • 703
    • 704
    • 705
    • 706
    • 707
    • 708
    • 709
    • 710
    • 711
    • 712
    • 713
    • 714
    • 715
    • 716
    • 717
    • 718
    • 719
    • 720
    • 721
    • 722
    • 723
    • 724
    • 725
    • 726
    • 727
    • 728
    • 729
    • 730
    • 731
    • 732
    • 733
    • 734
    • 735
    • 736
    • 737
    • 738
    • 739
    • 740
    • 741
    • 742
    • 743
    • 744
    • 745
    • 746
    • 747
    • 748
    • 749
    • 750
    • 751
    • 752
    • 753
    • 754
    • 755
    • 756
    • 757
    • 758
    • 759
    • 760
    • 761
    • 762
    • 763
    • 764
    • 765
    • 766
    • 767
    • 768
    • 769
    • 770
    • 771
    • 772
    • 773
    • 774
    • 775
    • 776
    • 777
    • 778
    • 779
    • 780
    • 781
    • 782
    • 783
    • 784
    • 785
    • 786
    • 787
    • 788
    • 789
    • 790
    • 791
    • 792
    • 793
    • 794
    • 795
    • 796
    • 797
    • 798
    • 799
    • 800
    • 801
    • 802
    • 803
    • 804
    • 805
    • 806
    • 807
    • 808
    • 809
    • 810
    • 811
    • 812
    • 813
    • 814
    • 815
    • 816
    • 817
    • 818
    • 819
    • 820
    • 821
    • 822
    • 823
    • 824
    • 825
    • 826
    • 827
    • 828
    • 829
    • 830
    • 831
    • 832
    • 833
    • 834
    • 835
    • 836
    • 837
    • 838
    • 839
    • 840
    • 841
    • 842
    • 843
    • 844
    • 845
    • 846
    • 847
    • 848
    • 849
    • 850
    • 851
    • 852
    • 853
    • 854
    • 855
    • 856
    • 857
    • 858
    • 859
    • 860
    • 861
    • 862
    • 863
    • 864
    • 865
    • 866
    • 867
    • 868
    • 869
    • 870
    • 871
    • 872
    • 873
    • 874
    • 875
    • 876
    • 877
    • 878
    • 879
    • 880
    • 881
    • 882
    • 883
    • 884
    • 885
    • 886
    • 887
    • 888
    • 889
    • 890
    • 891
    • 892
    • 893
    • 894
    • 895
    • 896
    • 897
    • 898
    • 899
    • 900
    • 901
    • 902
    • 903
    • 904
    • 905
    • 906
    • 907
    • 908
    • 909
    • 910
    • 911
    • 912
    • 913
    • 914
    • 915
    • 916
    • 917
    • 918
    • 919
    • 920
    • 921
    • 922
    • 923
    • 924
    • 925
    • 926
    • 927
    • 928
    • 929
    • 930
    • 931
    • 932
    • 933
    • 934
    • 935
    • 936
    • 937
    • 938
    • 939
    • 940
    • 941
    • 942
    • 943
    • 944
    • 945
    • 946
    • 947
    • 948
    • 949
    • 950
    • 951
    • 952
    • 953
    • 954
    • 955
    • 956
    • 957
    • 958
    • 959
    • 960
    • 961
    • 962
    • 963

    例：
    [show advanced options] 原始状态为1（开）
    [xp_cmdshell]原始状态为0（关）
    使用完毕后，关闭[xp_cmdshell]，不关闭[show advanced options]。

    sqlServer 实时 检测慢 sql

    WITH sess AS
(
  SELECT
  es.session_id,
  database_name = DB_NAME(er.database_id),
  er.cpu_time,
  er.reads,
  er.writes,
  er.logical_reads,
  login_name,
  er.status,
  blocking_session_id,
  wait_type,
  wait_resource,
  wait_time,
  individual_query = SUBSTRING (qt.text, (er.statement_start_offset/2)+1, ((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)+1),
  parent_query = qt.text,
  program_name,
  host_name,
  nt_domain,
  start_time,
  --DATEDIFF(MS,er.start_time,GETDATE()) as duration,
  (SELECT query_plan FROM sys.dm_exec_query_plan(er.plan_handle)) AS query_plan
  FROM
  sys.dm_exec_requests er
  INNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id
  CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
  WHERE
  es.session_id > 50
  AND es.session_Id NOT IN (@@SPID)
)
SELECT
  *
FROM
  sess
UNION ALL SELECT
  es.session_id,
  database_name = '',
  0,
  0,
  0,
  0,
  login_name,
  es.status,
  0,
  '',
  '',
  '',
  qt.text,
  parent_query = qt.text,
  program_name,
  host_name,
  nt_domain,
  es.last_request_start_time,
  --DATEDIFF(MS,es.last_request_start_time,GETDATE()) as duration,
  NULL AS query_plan
FROM
  sys.dm_exec_sessions es
  INNER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id
  CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle)as qt
WHERE
  ec.most_recent_session_id IN
  (
  SELECT blocking_session_id FROM sess WHERE blocking_session_id NOT IN(SELECT DISTINCT session_id FROM sess)
  )
ORDER BY
  cpu_time desc


  --实时查看依赖锁关系--

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70

    90秒，获取慢sql。

    /****** SSMS 的 SelectTopNRows 命令的脚本  ******/
SELECT TOP (5000) [EventTime]
      ,[SPID]
      ,[Process_Type]
      ,[Status]
      ,[BlockedTime_ms]
      ,[LockMode]
      ,[WaitResource]
      ,[InputBuffer]
      ,[ClientApp]
      ,[HostName]
  FROM [YX_Monitor].[dbo].[BlockedDetail]
  order by eventtime desc


  ----  90秒记录一次慢SQL ----

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
  • 相关阅读:
    Axure RP中的相关概念及高保真原型构建方法
    如何在 Windows 上安装 Docker Desktop
    QEMU热迁移中的VHOST-USER-NVME连接问题
    牛客网-《刷C语言百题》第二期
    Java语言知识大盘点（期末总复习）三
    自学 6 个月 Java 找到了一份 15K 的工作，师弟的方式值得推荐给大家
    对于Redis，如何根据业务需求配置是否允许远程访问？
    多模态论文串讲
    通过itextpdf向PDF文件最后一页添加图片
    Linux系统调优详解（九）——sar查看系统整体运行状态
  • 原文地址：https://blog.csdn.net/zhanglinlang/article/details/132834119