如何查找运行慢的sql脚本

一、查找Sql脚本的运行时间

1. 查询脚本

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;

2. 查询到的结果：

二、查询阻塞相关脚本

方式一：

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);

方式二：

select * from sysprocesses where   blocked<>0

三、查询死锁：

方式一：

--死锁分析--

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(