SQL Server 2008 database to view deadlock, plug-in statements

Deadlocks and blockages have always been the focus of performance test execution.

Here are the monitored SQL Server databases I've compiled, whether there are deadlocks or blocked SQL statements during the performance test, and it's relatively ready to leave.

--Number of deadlocks per second

SELECT * from Sys.dm_os_performance_counters WHERE counter_name like ' number of deadlocksc% ';

--Query Current blocking

With Cte_sid (Bsid, SID, sql_handle)
As (SELECT blocking_session_id,
session_id,
Sql_handle
From sys.dm_exec_requests
WHERE blocking_session_id <> 0
UNION All
SELECT a.blocking_session_id,
A.SESSION_ID,
A.sql_handle
From Sys.dm_exec_requests A
JOIN cte_sid B on a.session_id = B.bsid
)
SELECT C.bsid,
C.sid,
S.login_name,
S.host_name,
S.status,
S.cpu_time,
S.memory_usage,
S.last_request_start_time,
S.last_request_end_time,
S.logical_reads,
S.row_count,
Q.text
From Cte_sid C
JOIN sys.dm_exec_sessions S on c.sid = s.session_id
Cross APPLY sys.dm_exec_sql_text (c.sql_handle) Q
ORDER by Sid

In the stress test process, the continuous press F5 key to execute the above SQL statement, if there is deadlock or blockage phenomenon, will be listed in the execution results. If each successive execution of SQL, there is a deadlock or blockage occurs, indicating that the deadlock or blockage is more serious.