--SQL Server Performance Investigation

/* View session resource and wait usage */
select hostname, spid, cmd,sp.program_name, sp.login_time, sp.last_batch,
		dbid, cpu_time, reads, writes, total_elapsed_time, lastwaittype, waittime, kpid, state
from sys.dm_os_threads ot
	right join sys.dm_os_workers ow on ot.thread_address = ow.thread_address
	right join sys.sysprocesses sp on sp.kpid = ot.os_thread_id
	right join sys.dm_exec_sessions on spid = session_id

	
--Look at requests
select * from sys.dm_exec_sessions
select * from sys.dm_exec_requests

-- Look at resource usage per session request
select es.host_name, es.session_id, login_time, login_name, program_name,
	es.cpu_time AS SessionCPUTime, er.cpu_time AS RequestCPUTime, 
	es.total_elapsed_time AS SessionElapsedTime, er.total_elapsed_time AS RequestElapsedTime,
	es.reads AS SessionReads, er.reads AS RequestReads, 
	es.writes AS SessionWrites, er.writes AS RequestWrites, 
	es.logical_reads AS SessionLogReads, er.logical_reads AS RequestLogReads, er.row_count
	 from sys.dm_exec_requests er
	 right join sys.dm_exec_sessions es on er.session_id = es.session_id
WHERE es.session_id > 50
order by es.host_name desc