SQL SERVER Collection

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_counterswhere counter_name like ' numbers of deadlocksc% ';--query current blocking with C                        Te_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 joins 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.la             St_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.

The slowest SQL statement for SQL statements that view the most resource time for SQL Server Select (total_elapsed_time/execution_count)/1000 N ' Average time ms ', total_elapsed_time/ N ' Total time spent MS ', total_worker_time/1000 n ' Total CPU time MS ', Total_physical_reads N ' physical reads total number of times ', total_logical_reads/ Execution_count n ' Each logical read count ', Total_logical_reads n ' logical reads total number of times ', total_logical_writes n ' logical writes total number of times ', Execution_count n ' execution times ', SUBSTRING (St.text, (QS.STATEMENT_START_OFFSET/2) + 1, (case statement_end_offsetwhen-1 then datalength (st.text) ELSE Qs.statement_end_offset end-qs.statement_start_offset)/2) + 1) n ' execute statement ', Creation_time N ' statement compile time ', Last_execution_time n ' Last execution time ' fromsys.dm_exec_query_stats as Qs Cross APPLY sys.dm_exec_sql_text (qs.sql_handle) stwheresubstring (St.text, ( QS.STATEMENT_START_OFFSET/2) + 1, (Statement_end_offsetwhen-1 then datalength (st.text) ELSE Qs.statement_end_ Offset End-qs.statement_start_offset)/2) + 1) Not like '? tch% ' ORDER bytotal_elapsed_time/execution_count DESC; --The last SQL that consumes the most CPU:
SELECT TOP 20
total_worker_time/1000 as [total CPU time (ms)],execution_count [run times],
qs.total_worker_time/qs.execution_count/1000 as [average CPU time (ms)],
Last_execution_time as [last execution time],max_worker_time/1000 as [Maximum execution Time (ms)],
SUBSTRING (Qt.text,qs.statement_start_offset/2+1,
(case when qs.statement_end_offset =-1
Then Datalength (qt.text)
ELSE qs.statement_end_offset End-qs.statement_start_offset)/2 + 1)
As [syntax with CPU], Qt.text [full syntax],
Qt.dbid, Dbname=db_name (qt.dbid),
Qt.objectid,object_name (qt.objectid,qt.dbid) ObjectName
From Sys.dm_exec_query_stats Qs with (NOLOCK)
Cross apply Sys.dm_exec_sql_text (Qs.sql_handle) as Qt
WHERE execution_count>1
ORDER by Total_worker_time DESC


-The last SQL that consumes the most CPU on average:
SELECT TOP 20
total_worker_time/1000 as [total CPU time (ms)],execution_count [run times],
qs.total_worker_time/qs.execution_count/1000 as [average CPU time (ms)],
Last_execution_time as [last execution Time],min_worker_time/1000 as [min. Execution time (MS)],
max_worker_time/1000 as [Maximum execution Time (ms)],
SUBSTRING (Qt.text,qs.statement_start_offset/2+1,
(case when qs.statement_end_offset =-1
Then Datalength (qt.text)
ELSE qs.statement_end_offset End-qs.statement_start_offset)/2 + 1)
As [syntax with CPU], Qt.text [full syntax],
Qt.dbid, Dbname=db_name (qt.dbid),
Qt.objectid,object_name (qt.objectid,qt.dbid) ObjectName
From Sys.dm_exec_query_stats Qs with (NOLOCK)
Cross apply Sys.dm_exec_sql_text (Qs.sql_handle) as Qt
WHERE execution_count>1
ORDER by (qs.total_worker_time/qs.execution_count/1000) DESC

SQL SERVER Collection