Find the active sessions in SQL Server Instance in detailed
When ever performance of SQL Server has started degrading very first thing a DBA should do is
Check the current active sessions and their details like
a. CPU usage
b. Memory Usage
c. Query being execution
d. how long query is being executed
e. Any locks on sessions
f. who is the user of session
g. From where the connection is coming
h. When session is started
i. Reads / writes caused
j. session id
Above all the details can be retrieved easily from the DMV's of SQL Server. We just need to join in a very simple to accomplish the task.
Below is the query which i use in my day to day operational work to figure out bottle necks.
SELECT OBJECT_NAME(objectid) AS ObjectName ,SUBSTRING(stateText.TEXT, (statement_start_offset/2)+1,
((CASE statement_end_offset WHEN -1 THEN DATALENGTH(stateText.TEXT) ELSE statement_end_offset END - statement_start_offset)/2) + 1) AS statement_text ,DB_NAME(database_id) AS DatabaseName ,req.cpu_time AS CPU_Time ,CAST(CAST(DATEDIFF(second, last_request_start_time, GETDATE()) AS FLOAT)/60 AS DECIMAL(10,3)) AS RunningMinutes ,req.Percent_Complete ,sess.HOST_NAME AS RunningFrom ,LEFT(CLIENT_INTERFACE_NAME, 25) AS RunningBy ,sess.session_id AS SessionID ,req.blocking_session_id AS BlockingWith ,req.reads ,req.writes ,sess.[program_name] ,sess.login_name ,sess.status ,sess.last_request_start_time ,req.logical_reads
FROM sys.dm_exec_requests req INNER JOIN sys.dm_exec_sessions sess ON sess.session_id = req.session_id AND sess.is_user_process = 1 CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS stateText
I usually create a view using the above code in the DBA technical support database and query it easily when ever is needed.
Hope it will help.
