sp_who4

CREATE view vWho4
--with execute as 'usrspwho'
as
with cteWho4 as (
	select s.session_id as nSessionID,
		   r.session_id as nSessionReqID,
		   s.login_name as cLoginName ,
		   s.login_time as dLogin,
		   s.host_name  as cHostName ,
		   c.client_net_address as cIPClient,
		   s.program_name as cProgramName ,
		   r.start_time as dStartExec  ,
		   s.status as cStatus ,
		   r.command  as cCommandType ,
		   DB_NAME(r.database_id) as cDBName ,
		   r.blocking_session_id as nSessionIDBlocking ,
		   r.wait_type  as cWaitType ,
		   dateadd(millisecond,r.wait_time,'00:00:00:000')  as dWaitTime ,
		   r.open_transaction_count as nCntOpenTransaction ,
		   --cast(r.cpu_time/1000.0 as decimal(10,2))  as [nCpuTimeExec(s)]  ,
		   dateadd(millisecond,r.cpu_time,'00:00:00:000')  as dCpuTimeExec  ,
		   --cast(s.cpu_time/1000.0 as decimal(10,2))  as [nCpuTimeSession(s)]  ,
		   dateadd(millisecond,s.cpu_time,'00:00:00:000')  as dCpuTimeSession ,
		   --cast(r.total_elapsed_time/1000.0 as decimal(10,2)) as [nTotalElapsedTime(s)] ,
		   dateadd(millisecond,r.total_elapsed_time,'00:00:00:000')  as dTotalElapsedTime,
		   r.reads as nReadExec,
		   s.reads as nReadSession,
		   r.writes as nWriteExec  ,
		   r.writes as nWriteSession  ,
		   r.logical_reads as nLogicalReadsExec  ,
		   s.logical_reads as nLogicalReadsSession  ,
		   cast(s.memory_usage*8/1024.0  as decimal(10,2)) as [nMemoryUsage(mb)],
		   r.row_count  as nCntRowExec ,
		   s.row_count  as nCntRowSession ,
		   r.percent_complete as nPercentComplete ,
		   s.transaction_isolation_level as nTransactionIsolationLevel,
		   c.net_transport as cNetTransport,
		   s.client_version as cClienteVersion,
		   m.dop as nDOP,
		   m.request_time  as dRequestTime,
		   m.grant_time as  dGrantTime ,
		   cast(m.requested_memory_kb /1024.0/1024.0 as numeric(10,4) ) nRequestedMemoryGB,
		   cast(m.granted_memory_kb /1024.0/1024.0 as numeric(10,4) ) nGrantedMemoryGB,
		   cast(m.required_memory_kb /1024.0/1024.0 as numeric(10,4) ) nRequiredMemoryGB,
		   m.used_memory_kb as nUsedMemoryKB,
		   m.max_used_memory_kb nMaxUsedMemoryKB,
		   m.query_cost as nQueryCostEstimated,
		   m.timeout_sec as nTimeoutSec ,
		   substring(st.text,
					(r.statement_start_offset/2)+1,
					((case when r.statement_end_offset  = -1
						   then datalength(st.text)
						   else r.statement_end_offset
					  end - r.statement_start_offset
					 )/2
					) + 1
				   ) as cQuery,
		   st.text as cObjectQuery,
		   st.objectid nObjectID,
		   r.sql_handle,
		   r.plan_handle
	  from sys.dm_exec_sessions s
	  left join sys.dm_exec_connections c on s.session_id = c.session_id
	  left join sys.dm_exec_requests r on c.session_id = r.session_id and c.connection_id = r.connection_id
	  outer apply sys.dm_exec_sql_text(r.sql_handle) as st
	   left join sys.dm_exec_query_memory_grants m on m.session_id = s.session_id
	  where s.session_id > 0
) select nSessionID,nSessionReqID,cLoginName,dLogin,cHostName,cIPClient,cProgramName,
         dStartExec,cStatus,cCommandType,cDBName,nSessionIDBlocking,cWaitType,dWaitTime,
		 nCntOpenTransaction,dCpuTimeExec,dCpuTimeSession,dTotalElapsedTime,nReadExec,
		 nReadSession,nWriteExec,nWriteSession,nLogicalReadsExec,nLogicalReadsSession,
		 [nMemoryUsage(mb)],nCntRowExec,nCntRowSession,nPercentComplete,
		 nTransactionIsolationLevel,cNetTransport,cClienteVersion,
		 nDOP, dRequestTime,dGrantTime,nRequestedMemoryGB,nGrantedMemoryGB,nRequiredMemoryGB,nUsedMemoryKB,
		 nMaxUsedMemoryKB,nQueryCostEstimated,nTimeoutSec , cQuery,cObjectQuery,nObjectID , sql_handle , plan_handle
    from cteWho4
   where nSessionReqID is not null
      or nSessionID in (select distinct nSessionIDBlocking
                          from cteWho4
						 where nSessionIDBlocking > 0)

go
sp_ms_marksystemobject 'vWho4'
go

CREATE Procedure dbo.sp_who4
@id int = null
--with execute as 'usrspwho'
--with encryption
as
begin

   set transaction isolation level read uncommitted ;
   set nocount on

   declare @isadmin int = 0
   declare @dDateRef datetime = '1900-01-01 00:00:00.00'
   declare @iIDExecution bigint = cast(cast(GETDATE() as decimal(14,8) )*100000000 as bigint)

   if is_srvrolemember('sysadmin') = 1
       set @isadmin = 1
   /*
   drop table DBAMonitor.dbo.tMOVExecutionSP_WHO4
   */
   ;
   with cteSPWho as (
      select * from master.dbo.vWho4 s
       where s.nSessionID <> @@SPID
         and (s.nSessionID = @id or @id is null)
         and (IS_SRVROLEMEMBER('sysadmin',s.cLoginName) = 0 or @isadmin = 1)

   ) /*insert into dbamonitor.dbo.tMOVExecutionSP_WHO4(
       iIDExecution,nSessionID, cLoginName, dLogin, tLogin, cHostName, cIPClient, cProgramName, dStartExec, tExec, cStatus, cCommandType, cDBName, nSessionIDBlocking, cWaitType, tWait, nCntOpenTransaction, tCpuExec, tCpuSession, tTotalElapsed, nReadExec,
nReadSession, nWriteExec, nWriteSession, nLogicalReadsExec, nLogicalReadsSession, [nMemoryUsage(mb)], nCntRowExec, nCntRowSession, nPercentComplete, transaction_isolation_level, cNetTransport, cQuery, cObjectQuery, nObjectID, cClienteVersion
       )*/
       select @iIDExecution ,
              nSessionID,
              cLoginName,
              dLogin,
              case when DATEDIFF(HH,dLogin, GETDATE()) < 24
                   then cast(cast(GETDATE()-dLogin as time) as varchar(12))
                   else cast(DATEDIFF(DAY,dLogin, GETDATE()) as varchar(2))+'d '+cast(cast(GETDATE()-dLogin as time) as varchar(8))
              end as tLogin,
              cHostName,
              cIPClient,
              cProgramName,
              dStartExec,
              case when DATEDIFF(HH,dStartExec, GETDATE()) < 24
                   then cast(cast(GETDATE()-dStartExec as time) as varchar(12))
                   else cast(DATEDIFF(DAY,dStartExec, GETDATE()) as varchar(2))+'d '+cast(cast(GETDATE()-dStartExec as time) as varchar(8))
              end as tExec,
              cStatus,
              cCommandType,
              cDBName,
              nSessionIDBlocking,
              cWaitType,
              --nWaitTime,
              case when DATEDIFF(HH,dWaitTime, @dDateRef ) < 24
                   then cast(cast(dWaitTime-@dDateRef as time) as varchar(12))
                   else cast(DATEDIFF(DAY,dWaitTime , @dDateRef ) as varchar(2))+'d '+cast(cast(dWaitTime-@dDateRef as time) as varchar(8))
              end as tWait,
              nCntOpenTransaction,
              --dCpuTimeExec,
              case when DATEDIFF(HH,dCpuTimeExec, @dDateRef ) < 24
                   then cast(cast(dCpuTimeExec -@dDateRef as time) as varchar(12))
                   else cast(DATEDIFF(DAY,dCpuTimeExec , @dDateRef ) as varchar(2))+'d '+cast(cast(dCpuTimeExec -@dDateRef as time) as varchar(8))
              end as tCpuExec,
              case when DATEDIFF(HH,dCpuTimeSession, @dDateRef ) < 24
                   then cast(cast(dCpuTimeSession-@dDateRef as time) as varchar(12))
                   else cast(DATEDIFF(DAY,dCpuTimeSession, @dDateRef ) as varchar(2))+'d '+cast(cast(dCpuTimeSession-@dDateRef as time) as varchar(8))
              end as tCpuSession,
              --dTotalElapsedTime,
              case when DATEDIFF(HH,dTotalElapsedTime, @dDateRef ) < 24
                   then cast(cast(dTotalElapsedTime-@dDateRef as time) as varchar(8))
                   else cast(DATEDIFF(DAY,dTotalElapsedTime, @dDateRef) as varchar(2))+'d '+cast(cast(dTotalElapsedTime-@dDateRef as time) as varchar(8))
              end as tTotalElapsed,
              nReadExec,
              nReadSession,
              nWriteExec,
              nWriteSession,
              nLogicalReadsExec,
              nLogicalReadsSession,
              [nMemoryUsage(mb)],
            nCntRowExec,
              nCntRowSession,
              nPercentComplete,
              nTransactionIsolationLevel,
              cNetTransport,
              cQuery,
              cObjectQuery,
              nObjectID,
              cClienteVersion,
               nDOP,
			  dRequestTime,
			  dGrantTime,
			  nRequestedMemoryGB,
			  nGrantedMemoryGB,
			  nRequiredMemoryGB,
			  nUsedMemoryKB,
			  nMaxUsedMemoryKB,
			  nQueryCostEstimated,
			  nTimeoutSec
         from cteSPWho
   /*
   select nSessionID, cLoginName, dLogin, tLogin, cHostName, cIPClient, cProgramName,
          dStartExec, tExec, cStatus, cCommandType, cDBName, nSessionIDBlocking,
          cWaitType, tWait, nCntOpenTransaction, tCpuExec, tCpuSession, tTotalElapsed,
          nReadExec, nReadSession, nWriteExec, nWriteSession, nLogicalReadsExec,
          nLogicalReadsSession, [nMemoryUsage(mb)], nCntRowExec, nCntRowSession,
          nPercentComplete, transaction_isolation_level, cNetTransport,
          cQuery, cObjectQuery, nObjectID, cClienteVersion
     from DBAMonitor.dbo.tMOVExecutionSP_WHO4
    where iIDExecution = @iIDExecution
    order by nSessionID
   */
end


go
sp_ms_marksystemobject 'sp_who4'