合 SQL Server查询会话、锁、杀会话

发布日期 2023年2月3日 · 已更新 2023年2月3日

0 205 1

👉 本文共约40278个字，系统预计阅读时间或需152分钟。

sys.dm_exec_sessions
权限
备注
关系基数
示例
A. 查找连接到服务器的用户
B. 查找长时间运行的游标
C. 查找具有打开事务的空闲会话
D. 查找有关查询自己的连接的信息
sys.dm_exec_requests
备注
权限
示例
A. 查找用于运行批处理的查询文本
B. 查找运行的批处理持有的所有锁
C. 查找当前阻塞的所有请求
D. 按 CPU 对现有请求进行排序
sys.sysprocesses
备注
sys.dm_tran_locks
权限
备注
锁定
资源详细信息
示例
A. 将 sys.dm_tran_locks 与其他工具一起使用
B. 将会话信息链接到操作系统线程
查询当前正在执行的SQL
参考

sys.dm_exec_sessions

返回SQL Server上每个经过身份验证的会话一行。 sys.dm_exec_sessions 是一个服务器范围视图，显示有关所有活动用户连接和内部任务的信息。此信息包含客户端版本、客户端程序名称、客户端登录时间、登录用户、当前会话设置等。使用 sys.dm_exec_sessions 可首先查看当前系统负载并确定感兴趣的会话，然后使用其他动态管理视图或动态管理功能了解有关该会话的详细信息。

、和动态管理视图映射到已弃用的 sys.sysprocesses 系统兼容性视图。sys.dm_exec_requestssys.dm_exec_sessionssys.dm_exec_connections

备注

若要从 Azure Synapse Analytics 或 Analytics Platform System (PDW) 中的专用 SQL 池调用此池，请参阅 sys.dm_pdw_nodes_exec_sessions。对于无服务器 SQL 池，请使用 sys.dm_exec_sessions。

列名称	数据类型	说明和特定于版本的信息
session_id	smallint	标识与每个活动主连接关联的会话。不可为 Null。
login_time	datetime	建立会话的时间。不可为 Null。在查询此 DMV 时，显示尚未完成登录的会话，登录时间为 `1900-01-01`。
host_name	nvarchar(128)	特定于会话的客户端工作站名称。对于内部会话，该值为 NULL。可以为 Null。安全说明：客户端应用程序提供工作站名称，并可能提供不准确的数据。不要依赖HOST_NAME作为安全功能。
program_name	nvarchar(128)	启动会话的客户端程序的名称。对于内部会话，该值为 NULL。可以为 Null。
host_process_id	int	启动会话的客户端程序的进程 ID。对于内部会话，该值为 NULL。可以为 Null。
client_version	int	客户端连接到服务器所用接口的 TDS 协议版本。对于内部会话，该值为 NULL。可以为 Null。
client_interface_name	nvarchar(32)	客户端用于与服务器通信的库/驱动程序的名称。对于内部会话，该值为 NULL。可以为 Null。
security_id	varbinary(85)	与登录名关联的 Microsoft Windows 安全 ID。不可为 Null。
login_name	nvarchar(128)	当前执行的会话所使用的 SQL Server 登录名。有关创建此会话的原始登录名，请参阅 original_login_name。可以是SQL Server经过身份验证的登录名，也可以是经过 Windows 身份验证的域用户名。不可为 Null。
nt_domain	nvarchar(128)	适用于：SQL Server 2008 (10.0.x) 及更高版本客户端的 Windows 域（如果使用 Windows 身份验证或可信连接进行会话）。对于内部会话和非域用户，该值为 NULL。可以为 Null。
nt_user_name	nvarchar(128)	适用于：SQL Server 2008 (10.0.x) 及更高版本客户端的 Windows 用户名（如果使用 Windows 身份验证或可信连接进行会话）。对于内部会话和非域用户，该值为 NULL。可以为 Null。
status	nvarchar(30)	会话的状态。可能的值： Running - 当前正在运行一个或多个请求 Sleeping - 当前没有运行任何请求休眠 - 由于连接池，会话已重置，现在处于预登录状态。 Preconnect - 会话在资源调控器分类器中。不可为 Null。
context_info	varbinary(128)	会话的 CONTEXT_INFO 值。上下文信息由用户使用 SET CONTEXT_INFO 语句设置。可以为 Null。
cpu_time	int	此会话使用的 CPU 时间（以毫秒为单位）。不可为 Null。
memory_usage	int	该会话所占用的 8 KB 内存页数。不可为 Null。
total_scheduled_time	int	计划内含请求的会话的执行所耗用的总计时间（毫秒）。不可为 Null。
total_elapsed_time	int	自会话建立以来已耗用的时间（毫秒）。不可为 Null。
endpoint_id	int	与会话关联的端点的 ID。不可为 Null。
last_request_start_time	datetime	最近一次会话请求的开始时间。这包括当前正在执行的请求。不可为 Null。
last_request_end_time	datetime	最近一次会话请求的完成时间。可以为 Null。
reads	bigint	在该会话期间该会话中的请求所执行的读取次数。不可为 Null。
Writes	bigint	在该会话期间该会话中的请求所执行的写入次数。不可为 Null。
logical_reads	bigint	在此会话期间，此会话中的请求执行的逻辑读取数。不可为 Null。
is_user_process	bit	如果会话是系统会话，则为 0。否则为 1。不可为 Null。
text_size	int	会话的 TEXTSIZE 设置。不可为 Null。
语言	nvarchar(128)	会话的 LANGUAGE 设置。可以为 Null。
date_format	nvarchar(3)	会话的 DATEFORMAT 设置。可以为 Null。
date_first	smallint	会话的 DATEFIRST 设置。不可为 Null。
quoted_identifier	bit	会话的 QUOTED_IDENTIFIER 设置。不可为 Null。
arithabort	bit	会话的 ARITHABORT 设置。不可为 Null。
ansi_null_dflt_on	bit	会话的 ANSI_NULL_DFLT_ON 设置。不可为 Null。
ansi_defaults	bit	会话的 ANSI_DEFAULTS 设置。不可为 Null。
ansi_warnings	bit	会话的 ANSI_WARNINGS 设置。不可为 Null。
ansi_padding	bit	会话的 ANSI_PADDING 设置。不可为 Null。
ansi_nulls	bit	会话的 ANSI_NULLS 设置。不可为 Null。
concat_null_yields_null	bit	会话的 CONCAT_NULL_YIELDS_NULL 设置。不可为 Null。
transaction_isolation_level	smallint	会话的事务隔离级别。 0 = 未指定 1 = ReadUncommitted 2 = 已提交读取 3 = RepeatableRead 4 = 可序列化 5 = 快照不可为 Null。
lock_timeout	int	会话的 LOCK_TIMEOUT 设置。该值以毫秒计。不可为 Null。
deadlock_priority	int	会话的 DEADLOCK_PRIORITY 设置。不可为 Null。
row_count	bigint	到目前为止会话返回的行数。不可为 Null。
prev_error	int	会话返回的最近一个错误的 ID。不可为 Null。
original_security_id	varbinary(85)	与original_login_name关联的 Microsoft Windows 安全 ID。不可为 Null。
original_login_name	nvarchar(128)	SQL Server客户端用于创建此会话的登录名。可以是SQL Server经过身份验证的登录名、经过 Windows 身份验证的域用户名或包含的数据库用户。在初始连接后，会话可能经过许多隐式或显式上下文切换。例如，如果使用 EXECUTE AS 。不可为 Null。
last_successful_logon	datetime	适用于：SQL Server 2008 (10.0.x) 及更高版本当前会话开始前 original_login_name 上一次成功登录的时间。
last_unsuccessful_logon	datetime	适用于：SQL Server 2008 (10.0.x) 及更高版本当前会话开始前，original_login_name 上一次登录失败的时间。
unsuccessful_logons	bigint	适用于：SQL Server 2008 (10.0.x) 及更高版本在 last_successful_logon 和 login_time 之间 original_login_name 的登录失败次数。
group_id	int	此会话所属工作负荷组的 ID。不可为 Null。
database_id	smallint	适用于：SQL Server 2012 (11.x) 及更高版本每个会话的当前数据库的 ID。
authenticating_database_id	int	适用于：SQL Server 2012 (11.x) 及更高版本对主体进行身份验证的数据库的 ID。对于登录名，该值将为 0。对于包含数据库用户，该值将为包含数据库的数据库 ID。
open_transaction_count	int	适用于：SQL Server 2012 (11.x) 及更高版本每个会话的打开事务数。
pdw_node_id	int	适用于：Azure Synapse Analytics、Analytics Platform System (PDW) 此分发所在节点的标识符。
page_server_reads	bigint	适用范围：Azure SQL 数据库超大规模在此会话期间，此会话中的请求执行的页服务器读取数。不可为 Null。

权限

每个人都可以查看自己的会话信息。

SQL Server：需要VIEW SERVER STATESQL Server权限才能查看服务器上的所有会话。

SQL 数据库：需要VIEW DATABASE STATE查看与当前数据库的所有连接。 VIEW DATABASE STATE 无法在数据库中授予 master 。

备注

启用 通用条件符合性启用 服务器配置选项后，登录统计信息将显示在以下列中。

last_successful_logon
last_unsuccessful_logon
unsuccessful_logons

如果未启用此选项，这些列将返回 null 值。有关如何设置此服务器配置选项的详细信息，请参阅通用条件符合性已启用服务器配置选项。

Azure SQL 数据库上的管理员连接将看到每个经过身份验证的会话一行。结果集中显示的“sa”会话对会话的用户配额没有任何影响。非管理员连接将仅看到与其数据库用户会话相关的信息。

关系基数

源	功能	对于/应用	关系
`sys.dm_exec_sessions`	sys.dm_exec_requests	session_id	一对零或一对多
`sys.dm_exec_sessions`	sys.dm_exec_connections	session_id	一对零或一对多
`sys.dm_exec_sessions`	sys.dm_tran_session_transactions	session_id	一对零或一对多
`sys.dm_exec_sessions`	sys.dm_exec_cursors (session_id \|0)	session_id CROSS APPLY OUTER APPLY	一对零或一对多
`sys.dm_exec_sessions`	sys.dm_db_session_space_usage	session_id	一对一

示例

A. 查找连接到服务器的用户

下例将查找连接到服务器的用户并返回每个用户的会话数。

SELECT login_name,
    COUNT(session_id) AS session_count
FROM sys.dm_exec_sessions
GROUP BY login_name;

SELECT login_name,

COUNT(session_id) AS session_count

FROM sys.dm_exec_sessions

GROUP BY login_name;

B. 查找长时间运行的游标

下例将查找打开时间超过指定时间段的游标、创建游标的用户以及游标所在的会话。

USE master;
GO

SELECT creation_time,
    cursor_id,
    name,
    c.session_id,
    login_name
FROM sys.dm_exec_cursors(0) AS c
INNER JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
WHERE DATEDIFF(mi, c.creation_time, GETDATE()) > 5;
GO

USE master;

SELECT creation_time,

cursor_id,

name,

c.session_id,

login_name

FROM sys.dm_exec_cursors(0) AS c

INNER JOIN sys.dm_exec_sessions AS s

ON c.session_id = s.session_id

WHERE DATEDIFF(mi, c.creation_time, GETDATE()) > 5;

C. 查找具有打开事务的空闲会话

下例将查找具有已打开事务的空闲会话。空闲会话是当前未运行请求的会话。

SELECT s.*
FROM sys.dm_exec_sessions AS s
WHERE EXISTS (
        SELECT *
        FROM sys.dm_tran_session_transactions AS t
        WHERE t.session_id = s.session_id
    )
    AND NOT EXISTS (
        SELECT *
        FROM sys.dm_exec_requests AS r
        WHERE r.session_id = s.session_id
    );

SELECT s.*

FROM sys.dm_exec_sessions AS s

WHERE EXISTS (

SELECT *

FROM sys.dm_tran_session_transactions AS t

WHERE t.session_id = s.session_id

)

AND NOT EXISTS (

SELECT *

FROM sys.dm_exec_requests AS r

WHERE r.session_id = s.session_id

);

D. 查找有关查询自己的连接的信息

以下示例收集有关查询自己的连接的信息：

SELECT c.session_id,
    c.net_transport,
    c.encrypt_option,
    c.auth_scheme,
    s.host_name,
    s.program_name,
    s.client_interface_name,
    s.login_name,
    s.nt_domain,
    s.nt_user_name,
    s.original_login_name,
    c.connect_time,
    s.login_time
FROM sys.dm_exec_connections AS c
INNER JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
WHERE c.session_id = @@SPID;

SELECT c.session_id,

c.net_transport,

c.encrypt_option,

c.auth_scheme,

s.host_name,

s.program_name,

s.client_interface_name,

s.login_name,

s.nt_domain,

s.nt_user_name,

s.original_login_name,

c.connect_time,

s.login_time

FROM sys.dm_exec_connections AS c

INNER JOIN sys.dm_exec_sessions AS s

ON c.session_id = s.session_id

WHERE c.session_id = @@SPID;

sys.dm_exec_requests

返回有关在 SQL Server 中正在执行的每个请求的信息。有关请求的详细信息，请参阅线程和任务体系结构指南。

备注

要从 Azure Synapse Analytics 或 Analytics Platform System (PDW) 中的专用 SQL 池中调用它，请参阅 sys.dm_pdw_exec_requests (Transact-SQL)。对于无服务器 SQL 池，请使用 sys.dm_exec_requests。

列名称	数据类型	说明
session_id	smallint	与此请求相关的会话的 ID。不可为 null。
request_id	int	请求的 ID。在会话的上下文中是唯一的。不可为 null。
start_time	datetime	请求到达时的时间戳。不可为 null。
status	nvarchar(30)	请求的状态。可以是以下其中一个值：背景正在运行可运行 Sleeping Suspended 不可为 null。
命令	nvarchar(32)	标识正在处理的命令的当前类型。常用命令类型包括以下值： SELECT INSERT UPDATE DELETE BACKUP LOG BACKUP DATABASE DBCC FOR 可通过结合使用 sys.dm_exec_sql_text 和与请求对应的 sql_handle 检索请求的文本。内部系统进程将基于它们所执行任务的类型来设置该命令。这些任务可以包括以下值： LOCK MONITOR CHECKPOINTLAZY WRITER 不可为 null。
sql_handle	varbinary(64)	是唯一标识查询所属的批处理或存储过程的令牌。可以为 Null。
statement_start_offset	int	以字节为单位表示当前正在执行的批处理或持久化对象的当前正在执行的语句的起始位置，从 0 开始。可以与 `sql_handle`、`statement_end_offset` 和 `sys.dm_exec_sql_text` 动态管理函数一起使用，以便为请求检索当前正在执行的语句。可以为 Null。
statement_end_offset	int	以字节为单位表示当前正在执行的批处理或持久化对象的当前正在执行的语句的结束位置，从 0 开始。可以与 `sql_handle`、`statement_start_offset` 和 `sys.dm_exec_sql_text` 动态管理函数一起使用，以便为请求检索当前正在执行的语句。可以为 Null。
plan_handle	varbinary(64)	是唯一标识当前正在执行的批处理的查询执行计划的令牌。可以为 Null。
database_id	smallint	对其执行请求的数据库的 ID。不可为 null。
user_id	int	提交请求的用户的 ID。不可为 null。
connection_id	uniqueidentifier	请求到达时所采用的连接的 ID。可以为 Null。
blocking_session_id	smallint	正在阻塞请求的会话的 ID。如果此列为 NULL 或等于 0，则表示请求未被阻塞，或阻塞会话的会话信息不可用（或无法进行标识）。有关详细信息，请参阅了解并解决 SQL Server 阻塞问题。 -2 = 阻塞资源由孤立的分布式事务拥有。 -3 = 阻塞资源由延迟的恢复事务拥有。 -4 = 由于内部闩锁状态转换而导致此时无法确定阻塞闩锁所有者的会话 ID。 -5 = 无法确定阻塞闩锁所有者的会话 ID，因为此闩锁类型 (未跟踪它，例如 SH 闩锁) 。就其本身而言，blocking_session_id -5 不会指示性能问题。 -5 指示会话正在等待异步操作完成。在引入 -5 之前，同一会话已显示 blocking_session_id 0，尽管它仍处于等待状态。根据工作负载，观察 -5 作为 blocking_session_id 的情况可能很常见。
wait_type	nvarchar(60)	如果请求当前被阻塞，则此列返回等待类型。可以为 Null。有关等待类型的信息，请参阅。
wait_time	int	如果请求当前被阻塞，则此列返回当前等待的持续时间（以毫秒为单位）。不可为 null。
last_wait_type	nvarchar(60)	如果此请求先前已经阻塞，则此列返回上次等待的类型。不可为 null。
wait_resource	nvarchar(256)	如果请求当前被阻塞，则此列返回请求当前等待的资源。不可为 null。
open_transaction_count	int	为此请求打开的事务数。不可为 null。
open_resultset_count	int	为此请求打开的结果集的个数。不可为 null。
transaction_id	bigint	在其中执行此请求的事务的 ID。不可为 null。
context_info	varbinary(128)	会话的 CONTEXT_INFO 值。可以为 Null。
percent_complete	real	为以下命令完成的工作的百分比： ALTER INDEX REORGANIZE AUTO_SHRINK 选项（带 ALTER DATABASE） BACKUP DATABASE DBCC CHECKDB DBCC CHECKFILEGROUP DBCC CHECKTABLE DBCC INDEXDEFRAG DBCC SHRINKDATABASE DBCC SHRINKFILE RECOVERY RESTORE DATABASE ROLLBACK TDE ENCRYPTION 不可为 null。
estimated_completion_time	bigint	仅限内部。不可为 null。
cpu_time	int	请求所使用的 CPU 时间（毫秒）。不可为 null。
total_elapsed_time	int	请求到达后经过的总时间（毫秒）。不可为 null。
scheduler_id	int	正在计划此请求的计划程序的 ID。可以为 Null。
task_address	varbinary(8)	分配给与此请求关联的任务的内存地址。可以为 Null。
reads	bigint	此请求执行的读取数。不可为 null。
Writes	bigint	此请求执行的写入数。不可为 null。
logical_reads	bigint	此请求已经执行的逻辑读取数。不可为 null。
text_size	int	此请求的 TEXTSIZE 设置。不可为 null。
语言	nvarchar(128)	该请求的语言设置。可以为 Null。
date_format	nvarchar(3)	该请求的 DATEFORMAT 设置。可以为 Null。
date_first	smallint	该请求的 DATEFIRST 设置。不可为 null。
quoted_identifier	bit	1 = QUOTED_IDENTIFIER 对于该请求是 ON。否则返回 0。不可为 null。
arithabort	bit	1 = ARITHABORT 设置对于该请求是 ON。否则返回 0。不可为 null。
ansi_null_dflt_on	bit	1 = ANSI_NULL_DFLT_ON 设置对于该请求是 ON。否则返回 0。不可为 null。
ansi_defaults	bit	1 = ANSI_DEFAULTS 设置对于该请求是 ON。否则返回 0。不可为 null。
ansi_warnings	bit	1 = ANSI_WARNINGS 设置对于该请求是 ON。否则返回 0。不可为 null。
ansi_padding	bit	1 = ANSI_PADDING 设置对于该请求是 ON。否则返回 0。不可为 null。
ansi_nulls	bit	1 = ANSI_NULLS 设置对于该请求是 ON。否则返回 0。不可为 null。
concat_null_yields_null	bit	1 = CONCAT_NULL_YIELDS_NULL 设置对于该请求是 ON。否则返回 0。不可为 null。
transaction_isolation_level	smallint	创建此请求的事务时使用的隔离级别。不可为 null。 0 = 未指定 1 = 未提交读取 2 = 已提交读取 3 = 可重复 4 = 可序列化 5 = 快照
lock_timeout	int	此请求的锁定超时时间（毫秒）。不可为 null。
deadlock_priority	int	请求的 DEADLOCK_PRIORITY 设置。不可为 null。
row_count	bigint	已由此请求返回到客户端的行数。不可为 null。
prev_error	int	在执行请求期间发生的最后一个错误。不可为 null。
nest_level	int	正在对请求执行的代码的嵌套级别。不可为 null。
granted_query_memory	int	为执行该请求的查询而分配的页数。不可为 null。
executing_managed_code	bit	指示特定请求当前是否正在执行公共语言运行时对象，例如例程、类型和触发器。只要某个公共语言运行时对象在堆栈中，就会设置此值，甚至从公共语言运行时中运行 Transact-SQL 时，也会设置。不可为 null。
group_id	int	此查询所属工作负荷组的 ID。不可为 null。
query_hash	binary(8)	对查询计算的二进制哈希值，用于标识具有类似逻辑的查询。可以使用查询哈希确定仅仅是文字值不同的查询的聚合资源使用情况。
query_plan_hash	binary(8)	对查询执行计划计算的二进制哈希值，用于标识类似的查询执行计划。可以使用查询计划哈希查找具有类似执行计划的查询的累积成本。
statement_sql_handle	varbinary(64)	适用于：SQL Server 2014 (12.x) 及更高版本。单个查询的 SQL 句柄。如果数据库未启用查询存储，则此列为 NULL。
statement_context_id	bigint	适用于：SQL Server 2014 (12.x) 及更高版本。 sys.query_context_settings 的可选外键。如果数据库未启用查询存储，则此列为 NULL。
dop	int	适用于：SQL Server 2016 (13.x) 及更高版本。查询的并行度
parallel_worker_count	int	适用于：SQL Server 2016 (13.x) 及更高版本。如果这是并行查询，则为保留的并行辅助角色数。
external_script_request_id	uniqueidentifier	适用于：SQL Server 2016 (13.x) 及更高版本。与当前请求关联的外部脚本请求 ID。
is_resumable	bit	适用于：SQL Server 2017 (14.x) 及更高版本。指示请求是否为可恢复的索引操作。
page_resource	binary(8)	适用于：SQL Server 2019 (15.x) 如果 `wait_resource` 列包含页面，则为页面资源的 8 字节十六进制表示形式。有关详细信息，请参阅 sys.fn_PageResCracker。
page_server_reads	bigint	适用范围：Azure SQL 数据库超大规模此请求执行的页服务器读取数。不可为 null。

备注

要执行在 SQL Server 以外的代码（例如，扩展存储过程和分布式查询），则必须在非抢先计划程序的控制范围以外执行该线程。若要这样做，工作线程将切换到抢先模式。由此动态管理视图返回的时间值不包括在抢先模式下花费的时间。

在行模式下执行并行请求时，SQL Server 将分配工作线程，来协调负责完成已向其分配的任务的工作线程。在此 DMV 中，只有协调器线程对请求可见。协调器线程的 reads、writes、logical_reads 和 row_count 列未更新。仅更新协调器线程的 wait_type、wait_time、last_wait_type、wait_resource 和 granted_query_memory 列。有关详细信息，请参阅线程和任务体系结构指南。

权限

如果用户对服务器具有 VIEW SERVER STATE 权限，则该用户可以查看 SQL Server 实例上所有正在执行的会话；否则，该用户只能查看当前会话。 VIEW SERVER STATE 不能在 Azure SQL 数据库中授予，因此 sys.dm_exec_requests 总是限制于当前连接。

在 Always-On 方案中，如果次要副本设置为“仅读意向”，则与次要副本的连接必须通过添加 applicationintent=readonly 在连接字符串参数中指定其应用程序意向。否则，即使存在 VIEW SERVER STATE 权限，对可用性组中的数据库的 sys.dm_exec_requests 访问检查也不会通过。

示例

A. 查找用于运行批处理的查询文本

以下示例查询 sys.dm_exec_requests 以查找感兴趣的查询并从输出复制其 sql_handle。

SELECT * FROM sys.dm_exec_requests;  
GO

1 2	SELECT * FROM sys.dm_exec_requests; GO

然后，为了获取语句文本，将复制的 sql_handle 与系统函数 sys.dm_exec_sql_text(sql_handle) 一起使用。

SELECT * FROM sys.dm_exec_sql_text(< copied sql_handle >);  
GO

1 2	SELECT * FROM sys.dm_exec_sql_text(< copied sql_handle >); GO

B. 查找运行的批处理持有的所有锁

以下示例查询 sys.dm_exec_requests 以查找感兴趣的批处理并从输出复制其 transaction_id。

SELECT * FROM sys.dm_exec_requests;  
GO

1 2	SELECT * FROM sys.dm_exec_requests; GO

然后，为了查找锁信息，将复制的 transaction_id 与系统函数 sys.dm_tran_locks 一起使用。

SELECT * FROM sys.dm_tran_locks
WHERE request_owner_type = N'TRANSACTION'
    AND request_owner_id = < copied transaction_id >;
GO

SELECT * FROM sys.dm_tran_locks

WHERE request_owner_type = N'TRANSACTION'

AND request_owner_id = < copied transaction_id >;

C. 查找当前阻塞的所有请求

以下示例查询 sys.dm_exec_requests 以查找有关被阻塞的请求的信息。

SELECT session_id, status, blocking_session_id  
    , wait_type, wait_time, wait_resource
    , transaction_id
FROM sys.dm_exec_requests
WHERE status = N'suspended';  
GO

SELECT session_id, status, blocking_session_id

, wait_type, wait_time, wait_resource

, transaction_id

FROM sys.dm_exec_requests

WHERE status = N'suspended';

D. 按 CPU 对现有请求进行排序

SELECT 
   req.session_id
   , req.start_time
   , cpu_time 'cpu_time_ms'
   , object_name(st.objectid,st.dbid) 'ObjectName' 
   , substring
      (REPLACE
        (REPLACE
          (SUBSTRING
            (ST.text
            , (req.statement_start_offset/2) + 1
            , (
               (CASE statement_end_offset
                  WHEN -1
                  THEN DATALENGTH(ST.text)  
                  ELSE req.statement_end_offset
                  END
                    - req.statement_start_offset)/2) + 1)
       , CHAR(10), ' '), CHAR(13), ' '), 1, 512)  AS statement_text  
FROM sys.dm_exec_requests AS req  
   CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as ST
   ORDER BY cpu_time desc;
GO

SELECT

req.session_id

, req.start_time

, cpu_time 'cpu_time_ms'

, object_name(st.objectid,st.dbid) 'ObjectName'

, substring

(REPLACE

(SUBSTRING

(ST.text

, (req.statement_start_offset/2) + 1

, (

(CASE statement_end_offset

WHEN -1

THEN DATALENGTH(ST.text)

ELSE req.statement_end_offset

END

- req.statement_start_offset)/2) + 1)

, CHAR(10), ' '), CHAR(13), ' '), 1, 512) AS statement_text

FROM sys.dm_exec_requests AS req

CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as ST

ORDER BY cpu_time desc;

sys.sysprocesses

包含有关在 SQL Server 实例上运行的进程的信息。这些进程可以是客户端进程或系统进程。若要访问 sysprocesses，您必须位于 master 数据库上下文中，或者必须使用由三部分构成的名称 master.dbo.sysprocesses。

重要

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！

将此 SQL Server 2000 系统表作为一个视图包含进来是为了保持向后兼容性。建议您改用最新的 SQL Server 系统视图。若要查找等效的系统视图，请参阅将系统表映射到系统视图 (Transact-SQL) 。后续版本的 Microsoft SQL Server 将删除该功能。请避免在新的开发工作中使用该功能，并着手修改当前还在使用该功能的应用程序。

列名称	数据类型	说明
spid	smallint	SQL Server会话 ID。
kpid	smallint	Windows 线程 ID。
blocked	smallint	正在阻塞请求的会话的 ID。如果此列为 NULL，则表示请求未被阻塞，或锁定会话的会话信息不可用（或无法进行标识）。 -2 = 阻塞资源由孤立的分布式事务拥有。 -3 = 阻塞资源由延迟的恢复事务拥有。 -4 = 由于内部闩锁状态转换而无法确定阻塞闩锁所有者的会话 ID。
waittype	binary(2)	保留。
waittime	bigint	当前等待时间（毫秒）。 0 = 进程不等待。
lastwaittype	nchar(32)	指示上次或当前等待类型名称的字符串。
waitresource	nchar(256)	锁资源的文本化表示法。
dbid	smallint	当前正由进程使用的数据库 ID。
uid	smallint	执行命令的用户 ID。如果用户数和角色数超过 32,767，则发生溢出或返回 NULL。
cpu	int	进程的累计 CPU 时间。无论 SET STATISTICS TIME 选项是 ON 还是 OFF，都为所有进程更新该项。
physical_io	bigint	进程的累计磁盘读取和写入。
memusage	int	当前为此进程分配的过程缓存中的页数。一个负数，表示进程正在释放由另一个进程分配的内存。
login_time	datetime	客户端进程登录到服务器的时间。
last_batch	datetime	客户端进程上次执行远程存储过程调用或 EXECUTE 语句的时间。
ecid	smallint	用于唯一标识代表单个进程进行操作的子线程的执行上下文 ID。
open_tran	smallint	进程的打开事务数。
状态	nchar(30)	进程 ID 状态。可能的值包括：休眠 = SQL Server正在重置会话。 running = 会话正在运行一个或多个批。多个活动的结果集 (MARS) 启用后，会话可以运行多个批。有关详细信息，请参阅使用多重活动结果集 (MARS)。 background = 会话正在运行后台任务，例如死锁检测。 rollback = 会话正在进行事务回滚。 pending = 会话正在等待工作线程变为可用。 runnable = 会话中的任务在等待获取时间量子时位于计划程序可运行队列中。 spinloop = 会话中的任务正在等待旋转锁释放。 suspended = 会话正在等待 I/O 等事件完成。
sid	binary(86)	用户的全局唯一标识符 (GUID)。
hostname	nchar(128)	工作站的名称。
program_name	nchar(128)	应用程序的名称。
hostprocess	nchar(10)	工作站进程 ID 号。
cmd	nchar (52)	当前正在执行的命令。
nt_domain	nchar(128)	客户端的 Windows 域（如果使用 Windows 身份验证）或可信连接的 Windows 域。
nt_username	nchar(128)	进程的 Windows 用户名（如果使用 Windows 身份验证）或可信连接的 Windows 用户名。
net_address	nchar(12)	为每个用户工作站上的网络适配器分配的唯一标识符。当用户登录时，该标识符插入 net_address 列。
net_library	nchar(12)	用于存储客户端网络库的列。每个客户端进程都在网络连接上进入。网络连接有一个与这些进程关联的网络库，该网络库使得这些进程可以建立连接。
loginame	nchar(128)	登录名。
context_info	binary(128)	使用 SET CONTEXT_INFO 语句存储在批中的数据。
sql_handle	binary(20)	表示当前正在执行的批或对象。注意此值派生自对象的批地址或内存地址。此值不是使用基于哈希的SQL Server算法计算的。
stmt_start	int	为指定 sql_handle 运行当前 SQL 语句的起始偏移量。
stmt_end	int	所指定 sql_handle 的当前 SQL 语句的结束偏移量。 -1 指出当前语句为指定的 sql_handle 运行到 fn_get_sql 函数返回结果的结尾。
request_id	int	请求 ID。用于标识在特定会话中运行的请求。
page_resource	binary(8)	适用于：SQL Server 2019 (15.x) 如果 `waitresource` 列包含页面，则为页面资源的 8 字节十六进制表示形式。

备注

如果用户在服务器上具有 VIEW SERVER STATE 权限，则用户将在 SQL Server 实例中看到所有正在执行的会话;否则，用户将仅看到当前会话。

sys.dm_tran_locks

返回有关SQL Server中当前活动的锁管理器资源的信息。向锁管理器发出的已授予锁或正等待授予锁的每个当前活动请求分别对应一行。

结果集中的列大体分为两组：资源组和请求组。资源组说明正在进行锁请求的资源，请求组说明锁请求。

备注

若要从 Azure Synapse Analytics 或 Analytics Platform System (PDW) 调用此名称，请使用名称sys.dm_pdw_nodes_tran_locks。 Azure Synapse Analytics 中的无服务器 SQL 池不支持此语法。

列名称	数据类型	说明
resource_type	nvarchar(60)	表示资源类型。该值可以是下列值之一：DATABASE、FILE、OBJECT、PAGE、KEY、EXTENT、RID、APPLICATION、METADATA、HOBT 或 ALLOCATION_UNIT。
resource_subtype	nvarchar(60)	表示 resource_type 的子类型。从技术角度而言，可以在未持有父类型的非子类型化锁的情况下获取子类型锁。不同的子类型之间以及与非子类型化的父类型之间都不会发生冲突。并非所有资源类型都有子类型。
resource_database_id	int	此资源位于其范围之内的数据库的 ID。由锁管理器处理的所有资源均按该数据库 ID 划分范围。
resource_description	nvarchar(256)	资源的说明，其中只包含从其他资源列中无法获取的信息。
resource_associated_entity_id	bigint	数据库中与资源相关联的实体的 ID。该值可以是对象 ID、Hobt ID 或分配单元 ID，具体视资源类型而定。
resource_lock_partition	Int	已分区锁资源的锁分区 ID。对于未分区锁资源，该值为 0。
request_mode	nvarchar(60)	请求的模式。对于已授予的请求，为已授予模式；对于等待请求，为正在请求的模式。 NULL = 不授予对资源的访问权限。用作占位符。 Sch-S (架构稳定性) = 确保架构元素（如表或索引）在架构元素上保留架构稳定性锁时，不会删除架构元素。 Sch-M (架构修改) = 必须由任何要更改指定资源的架构的会话持有。确保没有其他会话正在引用所指示的对象。 S (共享) = 向持有会话授予对资源的共享访问权限。 U (更新) = 指示在可能最终更新的资源上获取的更新锁。用于防止常见形式的死锁，这类死锁在多个会话锁定资源并且稍后可能更新资源时发生。 X (独占) = 向持有会话授予对资源的独占访问权限。 IS (意向共享) = 指示打算在锁层次结构中的某个从属资源上放置 S 锁。 IU (意向更新) = 指示打算在锁层次结构中的某个从属资源上放置 U 锁。 IX (意向独占) = 指示打算在锁层次结构中的某个从属资源上放置 X 锁。 SIU (共享意向更新) = 指示对资源的共享访问，目的是获取锁层次结构中从属资源的更新锁。 SIX (共享意向独占) = 指示对资源的共享访问，目的是获取锁层次结构中从属资源的独占锁。 UIX (Update Intent 独占) = 指示更新锁保留资源，目的是获取锁层次结构中从属资源的独占锁。 BU = 由批量操作使用。 RangeS_S (共享Key-Range和共享资源锁) = 指示可序列化的范围扫描。 RangeS_U (共享Key-Range和更新资源锁) = 指示可序列化的更新扫描。 RangeI_N (插入Key-Range和 Null 资源锁) = 用于在将新键插入索引之前测试范围。 RangeI_S = Key-Range 转换锁，由RangeI_N和 S 锁重叠创建。 RangeI_U = Key-Range 转换锁，由RangeI_N和 U 锁重叠创建。 RangeI_X = Key-Range 转换锁，由RangeI_N和 X 锁重叠创建。 RangeX_S = Key-Range 转换锁，由RangeI_N和RangeS_S重叠创建。锁。 RangeX_U = Key-Range 转换锁，由RangeI_N和RangeS_U锁重叠创建。 RangeX_X (独占Key-Range和独占资源锁) = 这是更新某个范围内的密钥时使用的转换锁。
request_type	nvarchar(60)	请求类型。该值为 LOCK。
request_status	nvarchar(60)	该请求的当前状态。可能的值有 GRANTED、CONVERT、WAIT、LOW_PRIORITY_CONVERT、LOW_PRIORITY_WAIT 或 ABORT_BLOCKERS。有关低优先级等待和中止阻止程序的详细信息，请参阅 ALTER INDEX (Transact-SQL) low_priority_lock_wait 部分。
request_reference_count	smallint	返回同一请求程序已请求该资源的近似次数。
request_lifetime	int	标识为仅供参考。不支持。不保证以后的兼容性。
request_session_id	int	当前拥有该请求的会话 ID。对于分布式事务和绑定事务，拥有请求的会话 ID 可能不同。该值为 -2 时，指示该请求属于孤立的分布式事务。该值为 -3 时，指示请求属于延迟的恢复事务，例如因其回滚未能成功完成而延迟恢复该回滚的事务。
request_exec_context_id	int	当前拥有该请求的进程的执行上下文 ID。
request_request_id	int	当前拥有该请求的进程的请求 ID（批处理 ID）。每当事务的多个活动的结果集 (MARS) 连接更改时，该值便会更改。
request_owner_type	nvarchar(60)	拥有请求的实体类型。锁管理器请求可由各种实体所拥有。可能的值有： TRANSACTION = 请求由事务所有。 CURSOR = 请求由游标所有。 SESSION = 请求由用户会话所有。 SHARED_TRANSACTION_WORKSPACE = 请求由事务工作区的共享部分所有。 EXCLUSIVE_TRANSACTION_WORKSPACE = 请求由事务工作区的排他部分所有。 NOTIFICATION_OBJECT = 请求归内部SQL Server组件所有。此组件已经请求锁管理器在有其他组件等待获取锁时进行通知。 FileTable 功能是使用此值的一个组件。注意：工作区在内部用于为登记的会话保留锁。
request_owner_id	bigint	此请求的特定所有者的 ID。当事务是请求的所有者时，此值包含事务 ID。当 FileTable 是请求的所有者时，request_owner_id 具有以下值之一： -4 ：FileTable 已获取数据库锁。-3 ：FileTable 已采用表锁。其他值：该值表示文件句柄。此值在 Transact-SQL) 的动态管理视图中也显示为sys.dm_filestream_non_transacted_handles (fcb_id。
request_owner_guid	uniqueidentifier	此请求的特定所有者的 GUID。该值仅供分布式事务使用，在该事务中，该值与事务的 MS DTC GUID 相对应。
request_owner_lockspace_id	nvarchar(32)	标识为仅供参考。不支持。不保证以后的兼容性。该值表示请求程序的锁空间 ID。锁空间 ID 确定两个请求程序是否相互兼容以及在两者冲突的模式下是否可以向其授予锁。
lock_owner_address	varbinary(8)	用于跟踪该请求的内部数据结构的内存地址。该列可以与 sys.dm_os_waiting_tasks 中的 resource_address 列联接。
pdw_node_id	int	适用于：Azure Synapse Analytics、Analytics Platform System (PDW) 此分发所在节点的标识符。

权限

对于 SQL Server 和 SQL 托管实例，需要 VIEW SERVER STATE 权限。

在 SQL 数据库“基本”、“S0”和“S1”服务目标中，对于“弹性池”中的数据库，服务器管理员帐户、Azure Active Directory 管理员帐户或##MS_ServerStateReader##服务器角色中的成员身份是必需的。对于所有其他 SQL 数据库服务目标，需要数据库的 VIEW DATABASE STATE 权限或 ##MS_ServerStateReader## 服务器角色中的成员身份。

备注

已授予请求状态指示已将资源上的锁授予请求程序。等待请求指示尚未授予请求。 request_status 列返回下列等待请求类型：

转换请求状态指示已向请求程序授予对资源的请求，并且请求程序当前正在等待升级到要授予的初始请求。
等待请求状态指示请求程序当前未持有对资源的已授予请求。

由于 sys.dm_tran_locks 从锁管理器的内部数据结构填充，因此维护该信息不会给常规处理带来额外的开销。具体化该视图需要访问锁管理器的内部数据结构。这可能会略微影响服务器中的常规处理。这些影响应该很难察觉，并且应该只会影响频繁使用的资源。由于该视图中的数据与活动的锁管理器状态相对应，因此该数据可能会随时更改，并且在获取和释放锁时会相应地添加和删除行。由于保护锁管理器结构的完整性的性质，查询此视图的应用程序可能会遇到不可预知的性能。该视图不包含历史信息。

仅当所有资源组列都相等时，才对同一资源执行两个请求。

您可以使用下列工具控制读取操作的锁定：

SET TRANSACTION ISOLATION LEVEL 用于指定会话的锁定级别。有关详细信息，请参阅 SET TRANSACTION ISOLATION LEVEL (Transact-SQL)。
锁定表提示用于指定在 FROM 子句中表的单个引用的锁定级别。有关语法和限制，请参阅表提示 (Transact-SQL) 。

使用一个会话 ID 运行的资源可以有多个已授予锁。在一个会话下运行的不同实体可以拥有同一资源的锁，并且相关信息显示在 sys.dm_tran_locks 所返回的 request_owner_type 和 request_owner_id 列中。如果存在属于同一 request_owner_type 的多个实例，则使用 request_owner_id 列区分每个实例。对于分布式事务，request_owner_type 和 request_owner_guid 列将显示不同的实体信息。

例如，会话 S1 拥有 Table1 的共享锁，而在会话 S1 下运行的事务 T1 也拥有 Table1 的共享锁。在这种情况下，sys.dm_tran_locks 所返回的 resource_description 列将显示同一资源的两个实例。 request_owner_type 列将其中一个实例显示为会话，将另一个实例显示为事务。此外，resource_owner_id 列将具有不同的值。

在一个会话下运行的多个游标无法区分，被视为一个实体。

与会话 ID 值没有关联的分布式事务是孤立事务，向该事务分配的会话 ID 值为 -2。有关详细信息，请参阅 KILL (Transact-SQL)。

锁定

锁加在 SQL Server 资源上（如在一个事务中读取或修改的行），以防止各种事务并发使用资源。例如，如果一个排它 (X) 锁被一个事务加在某一表的某一行上，在这个锁被释放前，其他事务都不可以修改这一行。尽可能少使用锁可提高并发性，从而改善性能。

资源详细信息

下表列出了在 resource_associated_entity_id 列中表示的资源。

资源类型	资源说明	Resource_associated_entity_id
DATABASE	表示数据库。	不适用
FILE	表示数据库文件。此文件可以是数据文件，也可以是日志文件。	不适用
OBJECT	表示数据库对象。此对象可以是数据表、视图、存储过程、扩展存储过程或任何具有对象 ID 的对象。	对象 ID
PAGE	表示数据文件中的单页。	HoBt ID。此值与 sys.partitions.hobt_id 相对应。 PAGE 资源并不总是有 HoBt ID，因为 HoBt ID 是可由调用方提供的额外信息，而有些调用方不能提供该信息。
KEY	表示索引中的一行。	HoBt ID。此值与 sys.partitions.hobt_id 相对应。
EXTENT	表示数据文件区。区是由八个连续页构成的组。	不适用
RID	表示堆中的物理行。	HoBt ID。此值与 sys.partitions.hobt_id 相对应。 RID 资源并不总是有 HoBt ID，因为 HoBt ID 是可由调用方提供的额外信息，而有些调用方不能提供该信息。
APPLICATION	表示指定了应用程序的资源。	不适用
METADATA	表示元数据信息。	不适用
HOBT	表示堆或 B 树。它们是基本访问路径结构。	HoBt ID。此值与 sys.partitions.hobt_id 相对应。
ALLOCATION_UNIT	表示一组相关页，如索引分区。每个分配单元都包含一个索引分配映射 (IAM) 链。	分配单元 ID。此值与 sys.allocation_units.allocation_unit_id 相对应。

备注

SQL Server 文档在提到索引时一般使用 B 树这个术语。在行存储索引中，SQL Server 实现了 B+ 树。这不适用于列存储索引或内存中数据存储。有关详细信息，请查看 SQL Server 索引体系结构和设计指南。

下表列出了与每个资源类型相关联的子类型。

ResourceSubType	同步
ALLOCATION_UNIT.BULK_OPERATION_PAGE	用于批处理操作的预先分配的页。
ALLOCATION_UNIT.PAGE_COUNT	在延迟删除操作期间的分配单元页计数统计信息。
DATABASE.BULKOP_BACKUP_DB	数据库备份与大容量操作。
DATABASE.BULKOP_BACKUP_LOG	数据库日志备份与大容量操作。
DATABASE.CHANGE_TRACKING_CLEANUP	更改跟踪清除任务。
DATABASE.CT_DDL	数据库和表级更改跟踪 DDL 操作。
DATABASE.CONVERSATION_PRIORITY	Service Broker 会话优先级操作，如 CREATE BROKER PRIORITY。
DATABASE.DDL	数据定义语言 (DDL) 操作与文件组操作（如删除）。
DATABASE.ENCRYPTION_SCAN	TDE 加密同步。
DATABASE.PLANGUIDE	计划引导同步。
DATABASE.RESOURCE_GOVERNOR_DDL	资源调控器操作的 DDL 操作，例如 ALTER RESOURCE POOL。
DATABASE.SHRINK	数据库收缩操作。
DATABASE.STARTUP	用于数据库启动同步。
FILE.SHRINK	文件收缩操作。
HOBT.BULK_OPERATION	下列隔离级别下的优化堆大容量加载操作与并发扫描：快照、未提交读和使用行版本控制的已提交读。
HOBT.INDEX_REORGANIZE	堆或索引重组操作。
OBJECT.COMPILE	存储过程编译。
OBJECT.INDEX_OPERATION	索引操作。
OBJECT.UPDSTATS	表的统计信息更新。
METADATA.ASSEMBLY	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.ASSEMBLY_CLR_NAME	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.ASSEMBLY_TOKEN	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.ASYMMETRIC_KEY	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.AUDIT	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.AUDIT_ACTIONS	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.AUDIT_SPECIFICATION	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.AVAILABILITY_GROUP	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.CERTIFICATE	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.CHILD_INSTANCE	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.COMPRESSED_FRAGMENT	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.COMPRESSED_ROWSET	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.CONVERSTATION_ENDPOINT_RECV	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.CONVERSTATION_ENDPOINT_SEND	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.CONVERSATION_GROUP	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.CONVERSATION_PRIORITY	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.CREDENTIAL	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.CRYPTOGRAPHIC_PROVIDER	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.DATA_SPACE	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.DATABASE	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.DATABASE_PRINCIPAL	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.DB_MIRRORING_SESSION	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.DB_MIRRORING_WITNESS	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.DB_PRINCIPAL_SID	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.ENDPOINT	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.ENDPOINT_WEBMETHOD	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.EXPR_COLUMN	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.EXPR_HASH	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.FULLTEXT_CATALOG	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.FULLTEXT_INDEX	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.FULLTEXT_STOPLIST	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.INDEX_EXTENSION_SCHEME	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.INDEXSTATS	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.INSTANTIATED_TYPE_HASH	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.MESSAGE	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.METADATA_CACHE	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.PARTITION_FUNCTION	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.PASSWORD_POLICY	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.PERMISSIONS	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.PLAN_GUIDE	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.PLAN_GUIDE_HASH	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.PLAN_GUIDE_SCOPE	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.QNAME	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.QNAME_HASH	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.REMOTE_SERVICE_BINDING	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.ROUTE	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.SCHEMA	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.SECURITY_CACHE	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.SECURITY_DESCRIPTOR	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.SEQUENCE	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.SERVER_EVENT_SESSIONS	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.SERVER_PRINCIPAL	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.SERVICE	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.SERVICE_BROKER_GUID	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.SERVICE_CONTRACT	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.SERVICE_MESSAGE_TYPE	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.STATS	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.SYMMETRIC_KEY	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.USER_TYPE	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.XML_COLLECTION	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.XML_COMPONENT	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.XML_INDEX_QNAME	标识为仅供参考。不支持。不保证以后的兼容性。

下表提供每个资源类型的 resource_description 列的格式。

资源	格式	描述
DATABASE	不适用	resource_database_id 列中已提供数据库 ID。
FILE	file_id<>	此资源所表示的文件 ID。
OBJECT	object_id<>	此资源所表示的对象 ID。此对象可以是 sys.objects 中列出的任何对象，不仅仅是表。
PAGE	<>file_id：	表示此资源所表示的页的文件和页 ID。
KEY		表示行中由此资源表示的键列的哈希。
EXTENT	<>file_id：	表示此资源所表示的区的文件和页 ID。区 ID 与区中的第一页的页 ID 相同。
RID	<>file_id：：	表示此资源所表示的行的页 ID 和行 ID。请注意，如果关联的对象 ID 为 99，则此资源表示 IAM 链的第一个 IAM 页上的八个混合页槽之一。
APPLICATION	：<最多 32 个字符>： ()	表示用于划分此应用程序锁资源范围的数据库主体的 ID。还包含与此应用程序锁资源相对应的资源字符串，最多包含其中的 32 个字符。在某些情况下，因不再提供完整字符串而只能显示 2 个字符。只有在恢复过程中重新获取的应用程序锁处于数据库恢复期间才会发生此行为。哈希值表示与此应用程序锁资源相对应的完整资源字符串的哈希。
HOBT	不适用	作为 resource_associated_entity_id 提供的 HoBt ID。
ALLOCATION_UNIT	不适用	作为 resource_associated_entity_id 提供的分配单元 ID。
METADATA.ASSEMBLY	assembly_id = A	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.ASSEMBLY_CLR_NAME	$qname_id = Q	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.ASSEMBLY_TOKEN	assembly_id = A, $token_id	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.ASSYMMETRIC_KEY	asymmetric_key_id = A	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.AUDIT	audit_id = A	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.AUDIT_ACTIONS	device_id = D, major_id = M	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.AUDIT_SPECIFICATION	audit_specification_id = A	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.AVAILABILITY_GROUP	availability_group_id = A	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.CERTIFICATE	certificate_id = C	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.CHILD_INSTANCE	$hash = H1:H2:H3	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.COMPRESSED_FRAGMENT	object_id = O , compressed_fragment_id = C	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.COMPRESSED_ROW	object_id = O	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.CONVERSTATION_ENDPOINT_RECV	$hash = H1:H2:H3	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.CONVERSTATION_ENDPOINT_SEND	$hash = H1:H2:H3	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.CONVERSATION_GROUP	$hash = H1:H2:H3	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.CONVERSATION_PRIORITY	conversation_priority_id = C	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.CREDENTIAL	credential_id = C	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.CRYPTOGRAPHIC_PROVIDER	provider_id = P	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.DATA_SPACE	data_space_id = D	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.DATABASE	database_id = D	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.DATABASE_PRINCIPAL	principal_id = P	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.DB_MIRRORING_SESSION	database_id = D	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.DB_MIRRORING_WITNESS	$hash = H1:H2:H3	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.DB_PRINCIPAL_SID	$hash = H1:H2:H3	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.ENDPOINT	endpoint_id = E	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.ENDPOINT_WEBMETHOD	$hash = H1:H2:H3	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.FULLTEXT_CATALOG	fulltext_catalog_id = F	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.FULLTEXT_INDEX	object_id = O	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.EXPR_COLUMN	object_id = O, column_id = C	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.EXPR_HASH	object_id = O, $hash = H	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.FULLTEXT_CATALOG	fulltext_catalog_id = F	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.FULLTEXT_INDEX	object_id = O	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.FULLTEXT_STOPLIST	fulltext_stoplist_id = F	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.INDEX_EXTENSION_SCHEME	index_extension_id = I	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.INDEXSTATS	object_id = O, index_id or stats_id = I	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.INSTANTIATED_TYPE_HASH	user_type_id = U, hash = H	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.MESSAGE	message_id = M	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.METADATA_CACHE	$hash = H1:H2:H3	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.PARTITION_FUNCTION	function_id = F	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.PASSWORD_POLICY	principal_id = P	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.PERMISSIONS	class = C	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.PLAN_GUIDE	plan_guide_id = P	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA. PLAN_GUIDE_HASH	$hash = H1:H2:H3	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA. PLAN_GUIDE_SCOPE	scope_id = S	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.QNAME	$qname_id = Q	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.QNAME_HASH	$qname_scope_id = Q, $qname_hash = H	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.REMOTE_SERVICE_BINDING	remote_service_binding_id = R	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.ROUTE	route_id = R	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.SCHEMA	schema_id = S	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.SECURITY_CACHE	$hash = H1:H2:H3	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.SECURITY_DESCRIPTOR	sd_id = S	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.SEQUENCE	$seq_type = S, object_id = O	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.SERVER	server_id = S	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.SERVER_EVENT_SESSIONS	event_session_id = E	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.SERVER_PRINCIPAL	principal_id = P	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.SERVICE	service_id = S	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.SERVICE_BROKER_GUID	$hash = H1:H2:H3	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.SERVICE_CONTRACT	service_contract_id = S	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.SERVICE_MESSAGE_TYPE	message_type_id = M	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.STATS	object_id = O, stats_id = S	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.SYMMETRIC_KEY	symmetric_key_id = S	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.USER_TYPE	user_type_id = U	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.XML_COLLECTION	xml_collection_id = X	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.XML_COMPONENT	xml_component_id = X	标识为仅供参考。不支持。不保证以后的兼容性。
METADATA.XML_INDEX_QNAME	object_id = O, $qname_id = Q	标识为仅供参考。不支持。不保证以后的兼容性。

以下 XEvent 与分区 SWITCH 和联机索引重新生成相关。有关语法的信息，请参阅 ALTER TABLE (Transact-SQL) 和 ALTER INDEX (Transact-SQL) 。

lock_request_priority_state
process_killed_by_abort_blockers
ddl_with_wait_at_low_priority

通过添加**partition_number 和 partition_id，扩展了用于联机索引操作的现有 XEvent progress_report_online_index_operation**。

示例

A. 将 sys.dm_tran_locks 与其他工具一起使用

以下示例处理更新操作被另一个事务阻塞的情况。使用 sys.dm_tran_locks 和其他工具，可提供有关锁定资源的信息。

USE tempdb;  
GO  

-- Create test table and index.  
CREATE TABLE t_lock  
    (  
    c1 int, c2 int  
    );  
GO  

CREATE INDEX t_lock_ci on t_lock(c1);  
GO  

-- Insert values into test table  
INSERT INTO t_lock VALUES (1, 1);  
INSERT INTO t_lock VALUES (2,2);  
INSERT INTO t_lock VALUES (3,3);  
INSERT INTO t_lock VALUES (4,4);  
INSERT INTO t_lock VALUES (5,5);  
INSERT INTO t_lock VALUES (6,6);  
GO  

-- Session 1  
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  

BEGIN TRAN  
    SELECT c1  
        FROM t_lock  
        WITH(holdlock, rowlock);  

-- Session 2  
BEGIN TRAN  
    UPDATE t_lock SET c1 = 10

USE tempdb;

-- Create test table and index.

CREATE TABLE t_lock

(

c1 int, c2 int

);

CREATE INDEX t_lock_ci on t_lock(c1);

-- Insert values into test table

INSERT INTO t_lock VALUES (1, 1);

INSERT INTO t_lock VALUES (2,2);

INSERT INTO t_lock VALUES (3,3);

INSERT INTO t_lock VALUES (4,4);

INSERT INTO t_lock VALUES (5,5);

INSERT INTO t_lock VALUES (6,6);

-- Session 1

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRAN

SELECT c1

FROM t_lock

WITH(holdlock, rowlock);

-- Session 2

BEGIN TRAN

UPDATE t_lock SET c1 = 10

下面的查询将显示锁信息。 <dbid> 的值应该替换为 sys.databases 的 database_id。

SELECT resource_type, resource_associated_entity_id,  
    request_status, request_mode,request_session_id,  
    resource_description   
    FROM sys.dm_tran_locks  
    WHERE resource_database_id = <dbid>

SELECT resource_type, resource_associated_entity_id,

request_status, request_mode,request_session_id,

resource_description

FROM sys.dm_tran_locks

WHERE resource_database_id = <dbid>

下面的查询使用前一个查询中的 resource_associated_entity_id 返回对象信息。必须在连接到包含此对象的数据库时执行此查询。

复制

SELECT object_name(object_id), *  
    FROM sys.partitions  
    WHERE hobt_id=<resource_associated_entity_id>

SELECT object_name(object_id), *

FROM sys.partitions

WHERE hobt_id=<resource_associated_entity_id>

下面的查询将显示阻塞信息。

SELECT   
        t1.resource_type,  
        t1.resource_database_id,  
        t1.resource_associated_entity_id,  
        t1.request_mode,  
        t1.request_session_id,  
        t2.blocking_session_id  
    FROM sys.dm_tran_locks as t1  
    INNER JOIN sys.dm_os_waiting_tasks as t2  
        ON t1.lock_owner_address = t2.resource_address;

SELECT

t1.resource_type,

t1.resource_database_id,

t1.resource_associated_entity_id,

t1.request_mode,

t1.request_session_id,

t2.blocking_session_id

FROM sys.dm_tran_locks as t1

INNER JOIN sys.dm_os_waiting_tasks as t2

ON t1.lock_owner_address = t2.resource_address;

通过回滚事务来释放资源。

-- Session 1  
ROLLBACK;  
GO  

-- Session 2  
ROLLBACK;  
GO

-- Session 1

ROLLBACK;

-- Session 2

ROLLBACK;

B. 将会话信息链接到操作系统线程

下面的示例返回将会话 ID 与 Windows 线程 ID 相关联的信息。可以在 Windows 性能监视器中监视线程的性能。该查询不返回当前正在休眠的会话 ID。

SELECT STasks.session_id, SThreads.os_thread_id  
    FROM sys.dm_os_tasks AS STasks  
    INNER JOIN sys.dm_os_threads AS SThreads  
        ON STasks.worker_address = SThreads.worker_address  
    WHERE STasks.session_id IS NOT NULL  
    ORDER BY STasks.session_id;  
GO

SELECT STasks.session_id, SThreads.os_thread_id

FROM sys.dm_os_tasks AS STasks

INNER JOIN sys.dm_os_threads AS SThreads

ON STasks.worker_address = SThreads.worker_address

WHERE STasks.session_id IS NOT NULL

ORDER BY STasks.session_id;

查询当前正在执行的SQL


-- 当前正在执行的SQL
SELECT  session_id spid,
    db_name( dbid ) AS dbname,
    transaction_id,
    wait_type,
    last_wait_type,
    wait_resource,
    start_time,
    status,
    estimated_completion_time,
    cpu_time,
    logical_reads,
    command,
    TEXT,
    open_transaction_count,
    open_resultset_count,
    percent_complete,
    'kill '+cast(session_id as varchar) kill1
FROM    sys.dm_exec_requests r CROSS apply sys.dm_exec_sql_text ( r.sql_handle ) s 
WHERE session_id <>@@spid
order by start_time desc

GO

-- 当前正在执行的SQL(包括等待的SQL、当前正在堵塞与被堵塞SQL)
SELECT
    spid,
    blocked  blocking,
    P.dbid,
    db_name(P.dbid) dbname,
    waittime,
    lastwaittype,
    waitresource,
    open_tran,
    status,
    cpu,
    physical_io,
    memusage,
    login_time,
    last_batch,
    hostname,
    [program_name],
    hostprocess,
    cmd,
    nt_domain,
    nt_username,
    net_address,
    net_library,
    loginame,
    sql_handle,
    TEXT ,
    'kill '+cast(spid as varchar) kill1
FROM master.dbo.sysprocesses P CROSS apply sys.dm_exec_sql_text ( P.sql_handle ) s 
WHERE P.spid <>@@spid 
and (P.status !='sleeping' or (P.status='sleeping' and P.spid IN(select blocked from master.dbo.sysprocesses nb where blocked > 0 )))

GO

-- 当前正在执行的SQL

SELECT session_id spid,

db_name( dbid ) AS dbname,

transaction_id,

wait_type,

last_wait_type,

wait_resource,

start_time,

status,

estimated_completion_time,

cpu_time,

logical_reads,

command,

TEXT,

open_transaction_count,

open_resultset_count,

percent_complete,

'kill '+cast(session_id as varchar) kill1

FROM sys.dm_exec_requests r CROSS apply sys.dm_exec_sql_text ( r.sql_handle ) s

WHERE session_id <>@@spid

order by start_time desc

-- 当前正在执行的SQL(包括等待的SQL、当前正在堵塞与被堵塞SQL)

SELECT

spid,

blocked blocking,

P.dbid,

db_name(P.dbid) dbname,

waittime,

lastwaittype,

waitresource,

open_tran,

status,

cpu,

physical_io,

memusage,

login_time,

last_batch,

hostname,

[program_name],

hostprocess,

cmd,

nt_domain,

nt_username,

net_address,

net_library,

loginame,

sql_handle,

TEXT ,

'kill '+cast(spid as varchar) kill1

FROM master.dbo.sysprocesses P CROSS apply sys.dm_exec_sql_text ( P.sql_handle ) s

WHERE P.spid <>@@spid

and (P.status !='sleeping' or (P.status='sleeping' and P.spid IN(select blocked from master.dbo.sysprocesses nb where blocked > 0 )))

参考

https://learn.microsoft.com/zh-cn/sql/relational-databases/system-compatibility-views/sys-sysprocesses-transact-sql?view=sql-server-ver16
https://learn.microsoft.com/zh-cn/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-sessions-transact-sql?source=recommendations&view=sql-server-ver16

打赏赞(1)

标签： MSSQL SQL Server 杀会话查会话会话信息

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

1、声明：本网站有部分文章整合或转载自网络，文章著作权归属原作者或原团队；若有侵权，深表歉意，请联系本站站长进行处理，谢谢！！！
2、本站提供数据库的培训和考证业务，培训包含但不限于Oracle、MySQL、PostgreSQL、SQL Server、国产数据库等，考证包括但不限于Oracle OCP、Oracle OCM、MySQL OCP、PGCA、PGCE、PGCM等，私聊QQ646634621或微信dbaup66，非诚勿扰，谢谢！！！
3、麦老师新建了《西安DBA数据架构师》微信群，有志同道合的朋友可以私聊我入群，我们一起聊技术，聊生活，空了可以喝酒约饭。
4、为了方便大家更有针对性的讨论学习数据库，故新建数据库分类群，包括Oracle群、MySQL群、SQL Server群、PG&GP群、国产&信创群。志同道合的朋友可以入群聊技术聊人生，捣乱的勿进，喷子勿进，极端人士勿进，谢谢。

2024 年 11 月
一	二	三	四	五	六	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

合 SQL Server查询会话、锁、杀会话

sys.dm_exec_sessions

权限

备注

关系基数

示例

A. 查找连接到服务器的用户

B. 查找长时间运行的游标

C. 查找具有打开事务的空闲会话

D. 查找有关查询自己的连接的信息

sys.dm_exec_requests

备注

权限

示例

A. 查找用于运行批处理的查询文本

B. 查找运行的批处理持有的所有锁

C. 查找当前阻塞的所有请求

D. 按 CPU 对现有请求进行排序

sys.sysprocesses

备注

sys.dm_tran_locks

权限

备注

锁定

资源详细信息

示例

A. 将 sys.dm_tran_locks 与其他工具一起使用

B. 将会话信息链接到操作系统线程

查询当前正在执行的SQL

参考

相关文章

您可能还喜欢...

ora2pg支持将Oracle、MySQL、SQL Server迁移到PostgreSQL数据库

MSSQL查询表报错“SQL Server 检测到基于一致性的逻辑 I/O 错误 校验和不正确”

MSSQL查询数据库大小

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

MSSQL查询表报错“SQL Server 检测到基于一致性的逻辑 I/O 错误校验和不正确”

发表回复取消回复