What SQL is Executing

--- What SQL is Executing

-- select GETDATE()
-- select * from sysprocesses

use master  --  master..xp_readerrorlog 1 -- sp_who2 72 85 kill 201 --  dbcc inputbuffer(76) -- kill 59 dbcc opentran sp_lock dbcc tracestatus(-1)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select    --  DBCC MEMORYSTATUS dbcc inputbuffer(58) DBCC TRACESTATUS (-1)
      r.session_id,
   s.loginame,
      r.start_time,
      s.hostname,
      --r.blocking_session_id,
   s.blocked,
      r.command,
      db_name(r.database_id) as Database_name,
      r.wait_type,
      --r.wait_time,
      --r.open_transaction_count,
      r.cpu_time,
      r.total_elapsed_time as 'time(ms)',
      r.percent_complete,
      SUBSTRING(t.text, (r.statement_start_offset/2)+1,
        ((CASE r.statement_end_offset
          WHEN -1 THEN DATALENGTH(t.text)
         ELSE r.statement_end_offset
         END - r.statement_start_offset)/2) + 1) AS CurrentQuery,
   t.text as ParentQuery     
from sys.dm_exec_requests r
 cross apply sys.dm_exec_sql_text(r.sql_handle) t
 join sys.sysprocesses s on (r.session_id = s.spid)
where r.session_id > 50
and r.session_id != @@spid

--  select * from  sys.dm_exec_requests  select * from sys.sysprocesses

/*
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --  master..xp_readerrorlog 1 -- sp_who2 active 79 kill 70 --  dbcc inputbuffer(216) -- kill 216

    -- What SQL Statements Are Currently Running?
    SELECT [Spid] =    session_Id
    ,start_time
 , ecid
 , [Database] = DB_NAME(sp.dbid)
 , [User] = nt_username
 , [Status] = er.status
 , [Wait] = wait_type
 /*, [Individual Query] = SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2) + 1)*/
 ,[Parent Query] = qt.text
 , Program = program_name
 , Hostname
 , nt_domain
 , start_time
    FROM sys.dm_exec_requests er
    INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
    WHERE session_Id > 50              -- Ignore system spids.
    AND session_Id NOT IN (@@SPID)     -- Ignore this current statement.
    ORDER BY 1, 2
*/

--select * from sys.dm_os_waiting_tasks
/*
SELECT * FROM sys.dm_exec_query_memory_grants where grant_time is null

SELECT mg.granted_memory_kb, mg.session_id, t.text, qp.query_plan
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
ORDER BY 1 DESC OPTION (MAXDOP 1)

--waits grouped together as a percentage of all waits on the system, in decreasing order

WITH Waits AS
    (SELECT
        wait_type,
        wait_time_ms / 1000.0 AS WaitS,
        (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
        signal_wait_time_ms / 1000.0 AS SignalS,
        waiting_tasks_count AS WaitCount,
        100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
        ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
    FROM sys.dm_os_wait_stats
    WHERE wait_type NOT IN (
        'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
        'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
        'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
        'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
        'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
        'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE',
        'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES',
        'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK')
    )
SELECT
    W1.wait_type AS WaitType,
    CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
    CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
    CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
    W1.WaitCount AS WaitCount,
    CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage,
    CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S,
    CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_S,
    CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgSig_S
FROM Waits AS W1
    INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) - W1.Percentage < 95; -- percentage threshold
GO

*/