Find currently running session/process in SQL Server database/instance with help of sys.dm_exec_requests and sys.dm_exec_sessions

As a DBA, it is mandatory to keep an eye on your database and server too, especially during the business hour. SQL Server providing very helpful Dynamic Management View to help in this situation. Dynamic Management View (DMV) are as follow:

sys.dm_exec_requests
sys.dm_exec_sessions

I have created one SQL Server view with the help of following script which I used to use, often, during business hours to check whether everything is going smooth or not.

 

CREATE VIEW WhatIsGoingOn
AS
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
,DATEDIFF(minute, last_request_start_time, getdate()) 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

Now, whenever I want to see the status whether anything is blocking to my query/queries or which query/SP is being executed from which time. How many reads/writes happening by each objects. if there is a task like backup/restore, I can even see, how many percent complete by just executing one simple SELECT statement:

 

SELECT * FROM WhatIsGoingOn

If you wish you can, even, use this VIEW in your .NET web page/windows application so that you don’t even need to connect to database directly to see the status.

 

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations

11 thoughts on “Find currently running session/process in SQL Server database/instance with help of sys.dm_exec_requests and sys.dm_exec_sessions

  1. Amedar Consulting Group

    I was just seeking this information for a while. After 6 hours of continuous Googleing, at last I got it in your website. I wonder what’s the lack of Google strategy that don’t rank this type of informative websites in top of the list. Normally the top web sites are full of garbage.

  2. Pingback: sys.dm_exec_requests and sys.dm_exec_sessions to find who are logged in to your SQL Server

  3. Pingback: Find Blocking in SQL Server and use it to send an alert

  4. Wiedza i życie

    Thanks for any other fantastic post. The place else could anybody get that kind of information in such a perfect method of writing? I’ve a presentation next week, and I am at the look for such information.

  5. Pingback: Performance Counter in SQL Server

  6. Francesco Stigsell

    Nice post at SQL Server, sys.dm_exec_requests, sys.dm_exec_Sessions, find currently running process SQL Server database. I was checking continuously this blog and I’m impressed! Extremely useful info specially the last part :) I care for such information a lot. I was looking for this particular info for a very long time. Thank you and best of luck.

  7. Pingback: Capture long running query in SQL Server

  8. Pingback: Monitor user login connected to SQL Server

Comments are closed.