修改4

chi8708 · Nov 30, 2022 · 0f212ee · 0f212ee
1 parent 9188d3e
commit 0f212ee
Showing 1 changed file with 15 additions and 1 deletion.
diff --git a/4.查询正在运行sql.sql b/4.查询正在运行sql.sql
@@ -3,7 +3,7 @@ SELECT TOP 10 [session_id],[request_id],[start_time] AS '
 AS 'sql语句', DB_NAME([database_id]) AS '数据库名',[blocking_session_id] AS '正在阻塞其他会话的会话ID',
 [wait_type] AS '等待资源类型',[wait_time] AS '等待时间',[wait_resource] AS '等待的资源',[reads] AS '物理读次数',
 [writes] AS '写次数',[logical_reads] AS '逻辑读次数',[row_count] AS '返回结果行数'FROM sys.[dm_exec_requests] AS der 
-CROSS APPLY sys.dm_exec_sql_text(der.[sql_handle]) AS dest WHERE [session_id]>50 AND DB_NAME(der.[database_id])='TestDB'  ORDER BY [cpu_time] DESC
+CROSS APPLY sys.dm_exec_sql_text(der.[sql_handle]) AS dest WHERE [session_id]>50 AND DB_NAME(der.[database_id])='Gemmymis'  ORDER BY [cpu_time] DESC
 
 --KILL 255
 
@@ -22,3 +22,17 @@ KILL 318
 --DBCC DBREINDEX('Table1')
 
 
+
+SELECT 
+ DB_NAME(a.[dbid]) AS 存在阻塞所在的数据库,
+ a.spid AS 被阻塞的线程,
+ a.blocked AS 产生阻塞的线程,
+ a.waittime AS 等待时间,
+ a.waitresource AS 等待资源,
+ a.[status] AS 线程状态,
+ (SELECT [name] FROM sys.all_objects WHERE [object_id] = rt.[objectid]) AS [OBJ],
+ rt.[text] AS 被阻塞的语句
+FROM sys.sysprocesses AS a
+CROSS APPLY sys.dm_exec_sql_text(a.[sql_handle]) AS rt
+WHERE a.[status] = 'suspended' -- 延缓；暂停
+ORDER BY a.blocked,a.spid;