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

本主題適用於:是SQL Server (從 2008 開始)否Azure SQL Database否Azure SQL 資料倉儲 否平行處理資料倉儲 THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

提供有關目前使用者、 工作階段和處理程序的執行個體中的資訊 MicrosoftMicrosoft SQL Server Database EngineSQL Server Database EngineProvides information about current users, sessions, and processes in an instance of the MicrosoftMicrosoft SQL Server Database EngineSQL 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 = ] '登入' | 工作階段識別碼 | '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.

工作階段識別碼是屬於的工作階段識別碼 SQL ServerSQL Server執行個體。session ID is a session identification number belonging to the SQL ServerSQL Server instance. 工作階段識別碼smallintsession 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 DescriptionDescription
spidspid smallintsmallint 工作階段識別碼。Session ID.
ecidecid smallintsmallint 特定工作階段識別碼所關聯之給定執行緒的執行內容識別碼。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.

runningrunning. 工作階段正在執行一或多個批次。The session is running one or more batches. 啟用 Multiple Active Result Set (MARS) 之後,工作階段就可以執行多個批次。When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches. 如需詳細資訊,請參閱使用 Multiple Active Result Set (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.

pendingpending. 工作階段正在等候工作者執行緒變成可用狀態。The session is waiting for a worker thread to become available.

runnablerunnable. 在等候取得時間配量時,工作階段的工作位於排程器的可執行佇列中。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.

suspendedsuspended. 工作階段正在等候事件 (例如 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) 封鎖處理序的工作階段識別碼 (如果有)。Session ID for the blocking process, if one exists. 否則,這個資料行就是零。Otherwise, this column is zero.

當被遺棄的分散式交易封鎖了與指定工作階段識別碼相關的交易時,這個資料行會針對進行封鎖的被遺棄交易傳回 '-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.
cmd 命令cmd nchar(16)nchar(16) 針對處理序來執行的 Database EngineDatabase Engine 命令 ( Transact-SQLTransact-SQL 陳述式、內部 Database EngineDatabase Engine 處理序等等)。 Database EngineDatabase Engine command ( Transact-SQLTransact-SQL statement, internal Database EngineDatabase Engine process, and so on) executing for the process.
request_idrequest_id intint 在特定工作階段中執行的要求識別碼。ID for requests running in a specific session.

發生平行處理時,會針對特定的工作階段識別碼建立子執行緒。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.

所有被遺棄的分散式交易都會有指派的工作階段識別碼值 '-2'。All orphaned distributed transactions are assigned the session ID value of '-2'. 被遺棄的分散式交易是不與任何工作階段識別碼相關聯的分散式交易。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).

查詢sys.dm_exec_sessions sys.dm_exec_sessions 來分隔系統處理序與使用者處理序的資料行。Query the is_user_process column of sys.dm_exec_sessions to separate system processes from user processes.

PermissionsPermissions

需要有這部伺服器的 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. 顯示工作階段識別碼所識別的特定處理序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)