Check Active Queries and Related Details

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