-- 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