This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing different scripts to find the running sessions and processes in SQL Server.
As a DBA, this is very much important to find all current running sessions and processes of SQL Server.
Below are three different ways for find the all running process of SQL Server.
First, Using SP_WHO & SP_WHO2:
The both commands are used to check and monitor current running connections and sessions of the SQL Server.
SP_WHO : provides information like: Session ID, Logged User ID, Host Name, Session Status, Blocked Process, Database Name, Command Details, Request ID.
SP_WHO2 : provides some additional information like : CPU Time by each session, no of Disk Reads, Last query execution time
Second, Using sysprocesses View:
1 2 3 4 5 6 7 |
SELECT DB_NAME(DBID) AS DataBaseName ,COUNT(DBID) AS NumberOfConnections ,LogiName FROM sys.sysprocesses WHERE DBID > 0 GROUP BY DBID, LogiName |
Third, Using dm_exec_requests:
1 2 3 4 5 6 7 8 9 |
SELECT der.session_id ,est.TEXT AS QueryText ,der.status ,der.blocking_session_id ,der.cpu_time ,der.total_elapsed_time FROM sys.dm_exec_requests AS der CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS est |