Sql Server DMV Queries(Dynamic Management View)

IF OBJECT_ID('tempdb..#Who2') IS  NULL
  CREATE TABLE #Who2([SPID] int, [Status] SysName NULL,[Login] SysName NULL, [HostName] SysName NULL,[BlkBy] SysName NULL,[DBName] SysName NULL,[Command] SysName NULL,[CPUTime] int NULL,
   [DiskIO] int NULL,[LastBatch] SysName NULL,[ProgramName] SysName NULL,[SPID2] int NULL,[RequestId] int NULL)   
 
TRUNCATE TABLE #Who2 
INSERT #Who2 exec sp_Who2
Select * FROM #Who2 --where Status NOT IN('SUSPENDED','Sleeping') and SPID>50


--KILL 124    with statusonly


DECLARE @OpenQueries TABLE (cpu_time INT, logical_reads INT, session_id INT)

INSERT INTO @OpenQueries(cpu_time, logical_reads, session_id)

select r.cpu_time ,r.logical_reads, r.session_id

from sys.dm_exec_sessions as s inner join sys.dm_exec_requests as r

on s.session_id =r.session_id and s.last_request_start_time=r.start_time

where is_user_process = 1

and s.session_id <> @@SPID



waitfor delay '00:00:01'



select substring(h.text, (r.statement_start_offset/2)+1 , ((case r.statement_end_offset when -1 then datalength(h.text)  else r.statement_end_offset end - r.statement_start_offset)/2) + 1) as text

, r.cpu_time-t.cpu_time as CPUDiff , r.logical_reads-t.logical_reads as ReadDiff, r.wait_type, r.wait_time, r.last_wait_type, r.wait_resource, r.command, db_name(r.database_id) AS D

, r.blocking_session_id, r.granted_query_memory, r.session_id, r.reads, r.writes, r.row_count, s.[host_name], s.program_name, s.login_name from sys.dm_exec_sessions as s inner join sys.dm_exec_requests as r

on s.session_id =r.session_id and s.last_request_start_time=r.start_time

left join @OpenQueries as t on t.session_id=s.session_id

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) h

where is_user_process = 1

and s.session_id <> @@SPID

order by 3 DESC



SELECT st.text AS [SQL Text],w.session_id, w.wait_duration_ms,w.wait_type, w.resource_address, w.blocking_session_id, w.resource_description FROM sys.dm_os_waiting_tasks AS w

INNER JOIN sys.dm_exec_connections AS c ON w.session_id = c.session_id

CROSS APPLY (SELECT * FROM sys.dm_exec_sql_text(c.most_recent_sql_handle))

AS st WHERE w.session_id > 50

AND w.wait_duration_ms > 0



select r.command,r.wait_resource,r.wait_type,qp.query_plan,[statement_text]=SUBSTRING(st.text, (r.statement_start_offset/2)+1,
                        ((CASE r.statement_end_offset

                            WHEN -1 THEN DATALENGTH(st.text)

                                ELSE r.statement_end_offset

                            END - r.statement_start_offset)/2) + 1),

        st.text

from sys.dm_exec_requests r

    outer apply sys.dm_exec_sql_text (r.sql_handle) st

    cross apply sys.dm_exec_query_plan(r.plan_handle) qp

    where wait_type like 'PAGE%'



USE master;

SELECT sess.session_id, sess.login_name, sess.group_id, grps.name , db_name(r.database_id) AS DB

FROM sys.dm_exec_sessions AS sess JOIN sys.dm_resource_governor_workload_groups AS grps

    ON sess.group_id = grps.group_id    LEFT OUTER  join sys.dm_exec_requests as r on sess.session_id =r.session_id --and sess.last_request_start_time=r.start_time

WHERE sess.session_id > 50 ORDER BY NAME desc