DMV sys.dm_os_waiting_tasks and sys.dm_exec_requests – Wait Type

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