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
goexec 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
beginCREATE DATABASE YX_MonitorALTER 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
goif object_id('YX_Monitor.dbo.SlowSqlReadLog') is null
BEGINCREATE 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]
endif object_id('YX_Monitor.dbo.SlowSqlDetail') is null
BEGINCREATE 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)
endif object_id('yx_monitor.dbo.DeadlockDetail') is null
beginCREATE 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])
endif object_id('YX_Monitor.dbo.BlockedDetail') is null
BEGINcreate 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)
endgo----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 TINYINTSELECT @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
BEGINRAISERROR('至少选择一种跟踪事件!',16,3)RETURN
ENDIF exists(SELECT * FROM sys.server_event_sessions WHERE name=@SessionName)
BEGINRAISERROR('警告：扩展事件会话 %s 已存在，无法创建重命名会话！该步骤已跳过！',10,1,@SessionName)RETURN
END
ELSE
BEGINSET @sql=N'
CREATE EVENT SESSION ['+@SessionName+'] ON SERVER
'IF(@rpc_completed=1)
BEGINSET @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)
BEGINSET @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)
BEGINSET @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)
BEGINSET @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>='+@du