MSBI (SSIS/SSRS/SSAS) Online Training

Thursday, March 4, 2010

SSMS-Identify Backend Process in Sql Server


Hi,

While many process are running in sql server,if we want to see what are the processes are running on this server and what is the CPU utilization time,status and READ and WRITES full info we can see using below query.
Try to run it.
Rav's
Query:

------

SELECT r.session_id, s.HOST_NAME, wait_type, r.wait_resource,SUBSTRING(qt.text,(r.statement_start_offset/2) +1,(CASE WHEN r.statement_end_offset = -1THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2ELSE r.statement_end_offsetEND -r.statement_start_offset)/2)AS stmt_executing,r.blocking_session_id, s.PROGRAM_NAME,s.host_process_id, r.status, r.wait_time,r.cpu_time,r.total_elapsed_time,r.reads,r.writes,r.logical_reads, r.plan_handleFROM sys.dm_exec_requests rCROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt, sys.dm_exec_sessions sWHERE r.session_id > 50 and r.session_id=s.session_idORDER BY r.session_id, s.host_name, s.program_name, r.status