069. Find Running Process and Session in SQL Server

Performance Improvement in SQL, SQL Downloads, SQL SERVER, Sql Server Scripts
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
31
32
33
34
35
36
-- 1) Using SP_WHO
-- Provides information like: Session ID, Logged User ID, Host Name, Session Status, Blocked Process, Database Name, Command Details, Request ID.
 
SP_WHO
GO
 
-- 2) Using SP_WHO2
-- Provides some additional information like : CPU Time by each session, no of Disk Reads, Last query execution time
 
SP_WHO2
GO
 
 
-- 3) Usign sysprocesses View
  
SELECT
    DB_NAME(DBID) AS DataBaseName
    ,COUNT(DBID) AS NumberOfConnections
    ,LogiName
FROM sys.sysprocesses
WHERE DBID > 0
GROUP BY DBID, LogiName
GO
 
 
-- 4) Using dm_exec_requests
  
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