系统运维.数据库.性能分析常用SQL
【应用范围】
Sql Server数据库
【SQL脚本】
--查等待-- WITH [Waits] AS (SELECT wait_type, wait_time_ms/ 1000.0 AS [WaitS], (wait_time_ms - signal_wait_time_ms) / 1000.0 AS [ResourceS], signal_wait_time_ms / 1000.0 AS [SignalS], waiting_tasks_count AS [WaitCount], 100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS [Percentage], ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS [RowNum] FROM sys.dm_os_wait_stats WITH (NOLOCK) WHERE [wait_type] NOT IN ( N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE', N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'MEMORY_ALLOCATION_EXT', N'ONDEMAND_TASK_QUEUE', N'PREEMPTIVE_OS_LIBRARYOPS', N'PREEMPTIVE_OS_COMOPS', N'PREEMPTIVE_OS_CRYPTOPS', N'PREEMPTIVE_OS_PIPEOPS', N'PREEMPTIVE_OS_AUTHENTICATIONOPS', N'PREEMPTIVE_OS_GENERICOPS', N'PREEMPTIVE_OS_VERIFYTRUST', N'PREEMPTIVE_OS_FILEOPS', N'PREEMPTIVE_OS_DEVICEOPS', N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY', N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK', N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN', N'XE_DISPATCHER_WAIT', N'XE_LIVE_TARGET_TVF', N'XE_TIMER_EVENT') AND waiting_tasks_count > 0) SELECT MAX (W1.wait_type) AS [WaitType], CAST (MAX (W1.WaitS) AS DECIMAL (16,2)) AS [Wait_Sec], CAST (MAX (W1.ResourceS) AS DECIMAL (16,2)) AS [Resource_Sec], CAST (MAX (W1.SignalS) AS DECIMAL (16,2)) AS [Signal_Sec], MAX (W1.WaitCount) AS [Wait Count], CAST (MAX (W1.Percentage) AS DECIMAL (5,2)) AS [Wait Percentage], CAST ((MAX (W1.WaitS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgWait_Sec], CAST ((MAX (W1.ResourceS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgRes_Sec], CAST ((MAX (W1.SignalS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgSig_Sec] FROM Waits AS W1 INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNum GROUP BY W1.RowNum HAVING SUM (W2.Percentage) - MAX (W1.Percentage) < 99 -- percentage threshold OPTION (RECOMPILE); --查阻塞-- SELECT t1.resource_type AS [锁类型], DB_NAME(resource_database_id) AS [数据库名], t1.resource_associated_entity_id AS [阻塞资源对象],t1.resource_description as [资源描述信息], t1.request_mode AS [请求的锁], t1.request_session_id AS [等待会话], t2.wait_duration_ms AS [等待时间], (SELECT [text] FROM sys.dm_exec_requests AS r WITH (NOLOCK) CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) WHERE r.session_id = t1.request_session_id ) AS [等待会话执行的批SQL], (SELECT SUBSTRING(qt.[text],r.statement_start_offset/2, (CASE WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2 ELSE r.statement_end_offset END )/2) FROM sys.dm_exec_requests AS r WITH (NOLOCK) CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS qt WHERE r.session_id = t1.request_session_id ) AS [等待会话执行的SQL], t2.blocking_session_id AS [阻塞会话], (SELECT [text] FROM sys.sysprocesses AS p CROSS APPLY sys.dm_exec_sql_text(p.[sql_handle]) WHERE p.spid = t2.blocking_session_id ) AS [阻塞会话执行的批SQL] FROM sys.dm_tran_locks AS t1 WITH (NOLOCK) INNER JOIN sys.dm_os_waiting_tasks AS t2 WITH (NOLOCK) ON t1.lock_owner_address = t2.resource_address OPTION (RECOMPILE); --查IO-- SELECT TOP(20) DB_NAME(t.[dbid]) AS [Database Name], LEFT(t.[text], 50) AS [Short Query Text], qs.total_logical_reads AS [Total Logical Reads], qs.min_logical_reads AS [Min Logical Reads], qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads], qs.max_logical_reads AS [Max Logical Reads], qs.min_worker_time AS [Min Worker Time], qs.total_worker_time/qs.execution_count AS [Avg Worker Time], qs.max_worker_time AS [Max Worker Time], qs.min_elapsed_time AS [Min Elapsed Time], qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time], qs.max_elapsed_time AS [Max Elapsed Time], qs.execution_count AS [Execution Count], qs.creation_time AS [Creation Time] ,t.[text] AS [Complete Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK) CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE); --查CPU-- SELECT TOP(20) DB_NAME(t.[dbid]) AS [Database Name], LEFT(t.[text], 50) AS [Short Query Text], qs.total_worker_time AS [Total Worker Time], qs.min_worker_time AS [Min Worker Time], qs.total_worker_time/qs.execution_count AS [Avg Worker Time], qs.max_worker_time AS [Max Worker Time], qs.min_elapsed_time AS [Min Elapsed Time], qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time], qs.max_elapsed_time AS [Max Elapsed Time], qs.min_logical_reads AS [Min Logical Reads], qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads], qs.max_logical_reads AS [Max Logical Reads], qs.execution_count AS [Execution Count], qs.creation_time AS [Creation Time] ,t.[text] AS [Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK) CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE); --CPU高时查询最耗CPU的SQL DECLARE @sessionsCPU TABLE ( session_id INT NOT NULL, cpu_time BIGINT NULL , initialCPUFlag BIT NOT NULL ) INSERT INTO @sessionsCPU SELECT session_id ,sum(cpu_time) as cpu_time ,1 as initialCPUFlag FROM sys.dm_exec_requests WHERE session_id > 20 GROUP BY session_id WAITFOR DELAY '0:00:10' -- 等待10秒 INSERT INTO @sessionsCPU SELECT session_id ,sum(cpu_time) as cpu_time ,0 as initialCPUFlag FROM sys.dm_exec_requests WHERE session_id > 20 GROUP BY session_id; WITH total AS ( select initialCPUFlag ,sum(cpu_time) as cpu_time FROM @sessionsCPU GROUP BY initialCPUFlag ), delta AS ( SELECT s.cpu_time - f.cpu_time as total_cpu FROM total f CROSS JOIN total s WHERE f.initialCPUFlag = 1 AND s.initialCPUFlag = 0 ) SELECT i.session_id ,convert(numeric(5,2), (100. * (((i.cpu_time - l.cpu_time) * 1.) / (d.total_cpu * 1.)))) AS percentCPU ,convert(char(8), getdate() - r.start_time, 108) AS run_duration ,substring( st.text, (r.statement_start_offset/2)+1, ((CASE r.statement_end_offset WHEN -1 THEN datalength(st.text) ELSE r.statement_end_offset END - r.statement_start_offset)/2) + 1) AS statement_text ,st.text AS full_query ,pt.query_plan ,s.login_name ,s.host_name ,s.program_name FROM @sessionsCPU i INNER JOIN @sessionsCPU l ON i.session_id = l.session_id AND i.initialCPUFlag = 0 AND l.initialCPUFlag = 1 INNER JOIN sys.dm_exec_requests r ON i.session_id = r.session_id INNER JOIN sys.dm_exec_sessions s ON i.session_id = s.session_id CROSS JOIN delta d CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st cross apply sys.dm_exec_query_plan(r.plan_handle) as pt WHERE d.total_cpu <> 0 ORDER BY percentCPU DESC; --获取死锁-- --获取死锁信息 sql server 2008 r2 SELECT XEventData.XEvent.value('@timestamp', 'datetime2(3)'), cast(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as xml) FROM (SELECT CAST (target_data AS XML) AS TargetData FROM sys.dm_xe_session_targets st JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address WHERE [name] = 'system_health' AND st.target_name = 'ring_buffer') AS Data CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent) WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'; --or SELECT event.c.value('@timestamp', 'datetime2(3)'), datavalue.c.value('(./text())[1]', 'nvarchar(max)') AS DeadlockGraph FROM ( SELECT CAST(target_data AS XML) AS TargetData FROM sys.dm_xe_session_targets st JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address WHERE name = 'system_health' ) AS DATA CROSS APPLY TargetData.nodes('/RingBufferTarget/event') AS event(c) CROSS APPLY event.c.nodes ('data/value') AS datavalue(c) WHERE event.c.value('@name', 'varchar(4000)') = 'xml_deadlock_report' --or SELECT xed.value('@timestamp', 'datetime2(3)') as CreationDate, xed.query('.') AS XEvent FROM ( SELECT CAST([target_data] AS XML) AS TargetData FROM sys.dm_xe_session_targets AS st INNER JOIN sys.dm_xe_sessions AS s ON s.address = st.event_session_address WHERE s.name = N'system_health' AND st.target_name = N'ring_buffer' ) AS Data CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (xed) ORDER BY CreationDate DESC SELECT CAST(event_data.value('(event/data/value)[1]', 'varchar(max)') AS XML) AS DeadlockGraph FROM ( SELECT XEvent.query('.') AS event_data FROM ( -- Cast the target_data to XML SELECT CAST(target_data AS XML) AS TargetData FROM sys.dm_xe_session_targets st JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address WHERE name = 'system_health' AND target_name = 'ring_buffer' ) AS Data -- Split out the Event Nodes CROSS APPLY TargetData.nodes('RingBufferTarget/ event[@name="xml_deadlock_report"]') AS XEventData ( XEvent ) ) AS tab ( event_data ) -- Retrieve from Extended Events in 2012 SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph FROM ( SELECT XEvent.query('.') AS XEvent FROM ( SELECT CAST(target_data AS XML) AS TargetData FROM sys.dm_xe_session_targets st JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address WHERE s.name = 'system_health' AND st.target_name = 'ring_buffer' ) AS Data CROSS APPLY TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData ( XEvent ) ) AS src; SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph , XEvent.value('(/event/@timestamp)[1]','DATETIMEOFFSET') AS BeginTime FROM ( SELECT XEvent.query('.') AS XEvent FROM ( SELECT CAST(target_data AS XML) AS TargetData FROM sys.dm_xe_session_targets st JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address WHERE s.name = 'system_health' AND st.target_name = 'ring_buffer' ) AS Data CROSS APPLY TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData ( XEvent ) ) AS src ; ------------------------------------------------- DECLARE @SessionName SysName SELECT @SessionName = 'system_health' /* SELECT Session_Name = s.name, s.blocked_event_fire_time, s.dropped_buffer_count, s.dropped_event_count, s.pending_buffers FROM sys.dm_xe_session_targets t INNER JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address WHERE target_name = 'event_file' --*/ IF OBJECT_ID('tempdb..#Events') IS NOT NULL BEGIN DROP TABLE #Events END DECLARE @Target_File NVarChar(1000) , @Target_Dir NVarChar(1000) , @Target_File_WildCard NVarChar(1000) SELECT @Target_File = CAST(t.target_data as XML).value('EventFileTarget[1]/File[1]/@name', 'NVARCHAR(256)') FROM sys.dm_xe_session_targets t INNER JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address WHERE s.name = @SessionName AND t.target_name = 'event_file' SELECT @Target_Dir = LEFT(@Target_File, Len(@Target_File) - CHARINDEX('\', REVERSE(@Target_File))) SELECT @Target_File_WildCard = @Target_Dir + '\' + @SessionName + '_*.xel' --Keep this as a separate table because it's called twice in the next query. You don't want this running twice. SELECT DeadlockGraph = CAST(event_data AS XML) , DeadlockID = Row_Number() OVER(ORDER BY file_name, file_offset) INTO #Events FROM sys.fn_xe_file_target_read_file(@Target_File_WildCard, null, null, null) AS F WHERE event_data like '<event name="xml_deadlock_report%' ;WITH Victims AS ( SELECT VictimID = Deadlock.Victims.value('@id', 'varchar(50)') , e.DeadlockID FROM #Events e CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/victim-list/victimProcess') as Deadlock(Victims) ) , DeadlockObjects AS ( SELECT DISTINCT e.DeadlockID , ObjectName = Deadlock.Resources.value('@objectname', 'nvarchar(256)') FROM #Events e CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/resource-list/*') as Deadlock(Resources) ) SELECT * FROM ( SELECT e.DeadlockID , TransactionTime = Deadlock.Process.value('@lasttranstarted', 'datetime') , DeadlockGraph , DeadlockObjects = substring((SELECT (', ' + o.ObjectName) FROM DeadlockObjects o WHERE o.DeadlockID = e.DeadlockID ORDER BY o.ObjectName FOR XML PATH ('') ), 3, 4000) , Victim = CASE WHEN v.VictimID IS NOT NULL THEN 1 ELSE 0 END , SPID = Deadlock.Process.value('@spid', 'int') , ProcedureName = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)') , LockMode = Deadlock.Process.value('@lockMode', 'char(1)') , Code = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)') , ClientApp = CASE LEFT(Deadlock.Process.value('@clientapp', 'varchar(100)'), 29) WHEN 'SQLAgent - TSQL JobStep (Job ' THEN 'SQLAgent Job: ' + (SELECT name FROM msdb..sysjobs sj WHERE substring(Deadlock.Process.value('@clientapp', 'varchar(100)'),32,32)=(substring(sys.fn_varbintohexstr(sj.job_id),3,100))) + ' - ' + SUBSTRING(Deadlock.Process.value('@clientapp', 'varchar(100)'), 67, len(Deadlock.Process.value('@clientapp', 'varchar(100)'))-67) ELSE Deadlock.Process.value('@clientapp', 'varchar(100)') END , HostName = Deadlock.Process.value('@hostname', 'varchar(20)') , LoginName = Deadlock.Process.value('@loginname', 'varchar(20)') , InputBuffer = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)') FROM #Events e CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/process-list/process') as Deadlock(Process) LEFT JOIN Victims v ON v.DeadlockID = e.DeadlockID AND v.VictimID = Deadlock.Process.value('@id', 'varchar(50)') ) X --In a subquery to make filtering easier (use column names, not XML parsing), no other reason ORDER BY DeadlockID DESC CREATE TABLE #errorlog ( LogDate DATETIME , ProcessInfo VARCHAR(100) , [Text] VARCHAR(MAX) ); DECLARE @tag VARCHAR (MAX) , @path VARCHAR(MAX); INSERT INTO #errorlog EXEC sp_readerrorlog; SELECT @tag = text FROM #errorlog WHERE [Text] LIKE 'Logging%MSSQL\Log%'; DROP TABLE #errorlog; SET @path = SUBSTRING(@tag, 38, CHARINDEX('MSSQL\Log', @tag) - 29); SELECT CONVERT(xml, event_data).query('/event/data/value/child::*') AS DeadlockReport, switchoffset(CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]', 'datetime2'),'+08:00') AS Execution_Time FROM sys.fn_xe_file_target_read_file(@path + '\system_health*.xel', NULL, NULL, NULL) WHERE OBJECT_NAME like 'xml_deadlock_report'; --死锁分析-- DECLARE @SessionName SysName SELECT @SessionName = 'system_health' /* SELECT Session_Name = s.name, s.blocked_event_fire_time, s.dropped_buffer_count, s.dropped_event_count, s.pending_buffers FROM sys.dm_xe_session_targets t INNER JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address WHERE target_name = 'event_file' --*/ IF OBJECT_ID('tempdb..#Events') IS NOT NULL BEGIN DROP TABLE #Events END DECLARE @Target_File NVarChar(1000) , @Target_Dir NVarChar(1000) , @Target_File_WildCard NVarChar(1000) SELECT @Target_File = CAST(t.target_data as XML).value('EventFileTarget[1]/File[1]/@name', 'NVARCHAR(256)') FROM sys.dm_xe_session_targets t INNER JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address WHERE s.name = @SessionName AND t.target_name = 'event_file' SELECT @Target_Dir = LEFT(@Target_File, Len(@Target_File) - CHARINDEX('\', REVERSE(@Target_File))) SELECT @Target_File_WildCard = @Target_Dir + '\' + @SessionName + '_*.xel' --Keep this as a separate table because it's called twice in the next query. You don't want this running twice. SELECT DeadlockGraph = CAST(event_data AS XML) , DeadlockID = Row_Number() OVER(ORDER BY file_name, file_offset) INTO #Events FROM sys.fn_xe_file_target_read_file(@Target_File_WildCard, null, null, null) AS F WHERE event_data like '<event name="xml_deadlock_report%' ;WITH Victims AS ( SELECT VictimID = Deadlock.Victims.value('@id', 'varchar(50)') , e.DeadlockID FROM #Events e CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/victim-list/victimProcess') as Deadlock(Victims) ) , DeadlockObjects AS ( SELECT DISTINCT e.DeadlockID , ObjectName = Deadlock.Resources.value('@objectname', 'nvarchar(256)') FROM #Events e CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/resource-list/*') as Deadlock(Resources) ) SELECT * FROM ( SELECT e.DeadlockID , TransactionTime = Deadlock.Process.value('@lasttranstarted', 'datetime') , DeadlockGraph , DeadlockObjects = substring((SELECT (', ' + o.ObjectName) FROM DeadlockObjects o WHERE o.DeadlockID = e.DeadlockID ORDER BY o.ObjectName FOR XML PATH ('') ), 3, 4000) , Victim = CASE WHEN v.VictimID IS NOT NULL THEN 1 ELSE 0 END , SPID = Deadlock.Process.value('@spid', 'int') , ProcedureName = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)') , LockMode = Deadlock.Process.value('@lockMode', 'char(1)') , Code = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)') , ClientApp = CASE LEFT(Deadlock.Process.value('@clientapp', 'varchar(100)'), 29) WHEN 'SQLAgent - TSQL JobStep (Job ' THEN 'SQLAgent Job: ' + (SELECT name FROM msdb..sysjobs sj WHERE substring(Deadlock.Process.value('@clientapp', 'varchar(100)'),32,32)=(substring(sys.fn_varbintohexstr(sj.job_id),3,100))) + ' - ' + SUBSTRING(Deadlock.Process.value('@clientapp', 'varchar(100)'), 67, len(Deadlock.Process.value('@clientapp', 'varchar(100)'))-67) ELSE Deadlock.Process.value('@clientapp', 'varchar(100)') END , HostName = Deadlock.Process.value('@hostname', 'varchar(20)') , LoginName = Deadlock.Process.value('@loginname', 'varchar(20)') , InputBuffer = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)') FROM #Events e CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/process-list/process') as Deadlock(Process) LEFT JOIN Victims v ON v.DeadlockID = e.DeadlockID AND v.VictimID = Deadlock.Process.value('@id', 'varchar(50)') ) X --In a subquery to make filtering easier (use column names, not XML parsing), no other reason ORDER BY DeadlockID DESC CREATE TABLE #errorlog ( LogDate DATETIME , ProcessInfo VARCHAR(100) , [Text] VARCHAR(MAX) ); DECLARE @tag VARCHAR (MAX) , @path VARCHAR(MAX); INSERT INTO #errorlog EXEC sp_readerrorlog; SELECT @tag = text FROM #errorlog WHERE [Text] LIKE 'Logging%MSSQL\Log%'; DROP TABLE #errorlog; SET @path = SUBSTRING(@tag, 38, CHARINDEX('MSSQL\Log', @tag) - 29); SELECT CONVERT(xml, event_data).query('/event/data/value/child::*') AS DeadlockReport, switchoffset(CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]', 'datetime2'),'+08:00') AS Execution_Time FROM sys.fn_xe_file_target_read_file(@path + '\system_health*.xel', NULL, NULL, NULL) WHERE OBJECT_NAME like 'xml_deadlock_report';
---------------------------------------------------------------------------------------------------------
【金蝶云星空BOS二次开发案例演示】https://vip.kingdee.com/article/94751030918525696
系统运维.数据库.性能分析常用SQL
【应用范围】Sql Server数据库【SQL脚本】--查等待--WITH [Waits] AS (SELECT wait_type, wait_time_ms/ 1000.0 AS [WaitS], (wa...
点击下载文档
本文2024-09-23 03:59:11发表“云星空知识”栏目。
本文链接:https://wenku.my7c.com/article/kingdee-k3cloud-162368.html
您需要登录后才可以发表评论, 登录登录 或者 注册
最新文档
热门文章