sp_who (Transact-SQL)sp_who (Transact-SQL)

适用对象: 是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

提供有关当前用户、 会话和进程的实例中的信息MicrosoftMicrosoft SQL Server 数据库引擎SQL Server Database EngineProvides information about current users, sessions, and processes in an instance of the MicrosoftMicrosoft SQL Server 数据库引擎SQL Server Database Engine. 可以筛选信息以便只返回那些属于特定用户或特定会话的非空闲进程。The information can be filtered to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

  
sp_who [ [ @loginame = ] 'login' | session ID | 'ACTIVE' ]  

参数Arguments

[ @loginame = ] 'login' | session ID | 'ACTIVE' 用于筛选结果集。[ @loginame = ] 'login' | session ID | 'ACTIVE' Is used to filter the result set.

登录名sysname ,用于标识属于特定登录名的进程。login is sysname that identifies processes belonging to a particular login.

会话 ID是属于的会话标识号SQL ServerSQL Server实例。session ID is a session identification number belonging to the SQL ServerSQL Server instance. 会话 IDsmallintsession ID is smallint.

ACTIVE不包括等待下一个命令的用户的会话。ACTIVE excludes sessions that are waiting for the next command from the user.

如果没有提供任何值,则过程报告属于实例的所有会话。If no value is provided, the procedure reports all sessions belonging to the instance.

返回代码值Return Code Values

0(成功)或 1(失败)0 (success) or 1 (failure)

结果集Result Sets

sp_who返回的结果集包含以下信息。sp_who returns a result set with the following information.

“列”Column 数据类型Data type 描述Description
spidspid smallintsmallint 会话 ID。Session ID.
ecidecid smallintsmallint 与特定会话 ID 相关联的给定线程的执行上下文 ID。Execution context ID of a given thread associated with a specific session ID.

ECID = {0、 1、 2、 3...n},其中 0 始终表示主或父线程,并且 {1、 2、 3...n} 表示子线程。ECID = {0, 1, 2, 3, ...n}, where 0 always represents the main or parent thread, and {1, 2, 3, ...n} represent the subthreads.
statusstatus nchar(30)nchar(30) 进程状态。Process status. 可能的值有:The possible values are:

休眠dormant. SQL ServerSQL Server 正在重置会话。is resetting the session.

运行running. 会话正在运行一个或多个批。The session is running one or more batches. 多个活动的结果集 (MARS) 启用后,会话可以运行多个批。When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches. 有关详细信息,请参阅使用多个活动的结果集 (MARS)For more information, see Using Multiple Active Result Sets (MARS).

背景background. 会话正在运行一个后台任务,例如死锁检测。The session is running a background task, such as deadlock detection.

回滚rollback. 会话具有正在处理的事务回滚。The session has a transaction rollback in process.

挂起pending. 会话正在等待工作线程变为可用。The session is waiting for a worker thread to become available.

可运行runnable. 会话的任务在等待获取时间量程时位于计划程序的可运行队列中。The session's task is in the runnable queue of a scheduler while waiting to get a time quantum.

spinloopspinloop. 会话的任务正在等待调节锁变为可用。The session's task is waiting for a spinlock to become free.

挂起suspended. 会话正在等待事件(如 I/O)完成。The session is waiting for an event, such as I/O, to complete.
loginameloginame nchar(128)nchar(128) 与特定进程相关联的登录名。Login name associated with the particular process.
主机名hostname nchar(128)nchar(128) 每个进程的主机或计算机名。Host or computer name for each process.
blkblk char(5)char(5) 如果存在阻塞进程,则是该阻塞进程的会话 ID。Session ID for the blocking process, if one exists. 否则该列为零。Otherwise, this column is zero.

当与指定会话 ID 相关联的事务受到孤立分布式事务的阻塞时,该列将对阻塞孤立事务返回“-2”。When a transaction associated with a specified session ID is blocked by an orphaned distributed transaction, this column will return a '-2' for the blocking orphaned transaction.
dbnamedbname nchar(128)nchar(128) 进程使用的数据库。Database used by the process.
cmdcmd nchar(16)nchar(16) 为该进程执行的数据库引擎Database Engine命令(Transact-SQLTransact-SQL 语句、数据库引擎Database Engine进程等等)。数据库引擎Database Engine command (Transact-SQLTransact-SQL statement, internal 数据库引擎Database Engine process, and so on) executing for the process.
request_idrequest_id intint 特定会话中运行的请求的 ID。ID for requests running in a specific session.

如果是并行处理,则会为特定的会话 ID 创建子线程。In case of parallel processing, subthreads are created for the specific session ID. 主线程则以 spid = <xxx>ecid =0 表示。The main thread is indicated as spid = <xxx> and ecid =0. 其他子线程具有相同spid = <xxx>,但ecid > 0。The other subthreads have the same spid = <xxx>, but with ecid > 0.

备注Remarks

阻塞进程(可能含有排他锁)是控制其他进程所需要的资源的进程。A blocking process, which may have an exclusive lock, is one that is holding resources that another process needs.

所有孤立的分布式事务的会话 ID 都被赋予值“-2”。All orphaned distributed transactions are assigned the session ID value of '-2'. 孤立的分布式事务是不与任何会话 ID 关联的分布式事务。Orphaned distributed transactions are distributed transactions that are not associated with any session ID. 有关详细信息,请参阅 使用标记的事务一致地恢复相关的数据库的事务(完全恢复模式)For more information, see Use Marked Transactions to Recover Related Databases Consistently (Full Recovery Model).

查询is_user_process sys.dm_exec_sessions 以分隔系统进程从用户进程的列。Query the is_user_process column of sys.dm_exec_sessions to separate system processes from user processes.

权限Permissions

要求对服务器拥有 VIEW SERVER STATE 权限才能查看 SQL ServerSQL Server 实例上所有正在执行的会话。Requires VIEW SERVER STATE permission on the server to see all executing sessions on the instance of SQL ServerSQL Server. 否则,用户只能查看当前会话。Otherwise, the user sees only the current session.

示例Examples

A.A. 列出全部当前进程Listing all current processes

以下示例使用没有参数的 sp_who 来报告所有当前用户。The following example uses sp_who without parameters to report all current users.

USE master;  
GO  
EXEC sp_who;  
GO  

B.B. 列出特定用户的进程Listing a specific user's process

以下示例显示如何通过登录名查看有关单个当前用户的信息。The following example shows how to view information about a single current user by login name.

USE master;  
GO  
EXEC sp_who 'janetl';  
GO  

C.C. 显示所有活动进程Displaying all active processes

USE master;  
GO  
EXEC sp_who 'active';  
GO  

D.D. 显示会话 ID 标识的特定进程Displaying a specific process identified by a session ID

USE master;  
GO  
EXEC sp_who '10' --specifies the process_id;  
GO  

请参阅See Also

sp_lock (Transact-SQL) sp_lock (Transact-SQL)
sys.sysprocesses (Transact SQL) sys.sysprocesses (Transact-SQL)
系统存储过程 (Transact-SQL)System Stored Procedures (Transact-SQL)