This Query gives the current processes running in SQL Server. Also if you want to know what is stored in the Cache or what is running .
Once this is run you will get the following info.
wait_duration_ms – Means current wait of the query which is run at that time.
wait_type – Means the current wait type of the query
text – Has the Query in TEXT FORMAT
query_plan – Gives you the Query plan which could be very helpful to find some missing inxexes, etc and all that fun stuff.
Session_id, and all others(Columns) are pretty much self explanatory.
select
db_name(exec_re.database_id) databasename,
wait_tsk.wait_duration_ms,
wait_tsk.wait_type,
exec_txt.text,
exec_qpln.query_plan,
wait_tsk.session_id,
exec_ssion.cpu_time,
exec_ssion.memory_usage,
exec_ssion.logical_reads,
exec_ssion.total_elapsed_time,
exec_ssion.program_name,
exec_ssion.status
from
sys.dm_os_waiting_tasks wait_tsk
inner join sys.dm_exec_requests exec_re
on
wait_tsk.session_id = exec_re.session_id
inner join sys.dm_exec_sessions exec_ssion
on
exec_ssion.session_id = exec_re.session_id
cross apply sys.dm_exec_sql_text (exec_re.sql_handle) exec_txt
cross apply sys.dm_exec_query_plan (exec_re.plan_handle) exec_qpln
where
exec_ssion.is_user_process = 1
Posts
