Check Active Queries and Related Details

Share via:

Check Active Queries and Related Details

Many times DBAs need to check currently active queries on the DB servers and other details like estimated completion time, blockings, query plan, query text, host name, user login details etc.

Following query generate such detail for active queries.

 

SELECT re.session_id, sqltext.TEXT,
re.status, re.blocking_session_id as blk_sp_id,
re.command, re.wait_type, re.wait_time, (re.estimated_completion_time/1000/60) as est_min, re.percent_complete,
case
when re.estimated_completion_time !=0 then DATEADD(MS,re.estimated_completion_time,GETDATE())
else null
end as EstCompletionTime, se.[host_name], se.original_login_name, se.[program_name], re.start_time, sqlplan.query_plan
FROM sys.dm_exec_requests re
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS sqlplan
join sys.dm_exec_sessions se on se.session_id = re.session_id
where re.session_id > 50 and re.session_id <> @@SPID

 

Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates

KTEXPERTS is always active on below social media platforms.

Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (13 votes, average: 5.00 out of 5)
Loading...

One thought on “Check Active Queries and Related Details

Leave a Reply to sai Cancel reply