一、查找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(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';