Navigation

	Home Page
	Index
	All Pages

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

alter view dbo.vw_who2
as
select 
     BlockedBy          = coalesce(tl.request_session_id,'')
    ,ClientInterface    = coalesce(es.client_interface_name,' - ')
    ,CPUTime            = es.total_scheduled_time * .001
    ,DBName             = DB_NAME(coalesce(er.database_id,tl1.resource_database_id,' - '))
    ,HostName           = coalesce(es.host_name,' - ')
    ,IPAddress          = coalesce(ec.client_net_address,' - ')
    ,LastBatch          = es.last_request_start_time
    ,LockCount          = coalesce(dt.lockcount,0)
    ,LoginName          = es.login_name
    ,LoginTime          = es.login_time
    ,OpenTranCount      = coalesce(er.open_transaction_count,st.TranCount,0)
    ,ProgramName        = coalesce(es.program_name,' - ')
    ,Num8kPages         = es.memory_usage
    ,NumReads           = coalesce(ec.num_reads,'')
    ,NumWrites          = coalesce(ec.num_writes,'')
    ,SessionElapsedTime = es.total_elapsed_time *.001
    ,SPID               = es.session_id
    ,SubProcessID       = ROW_NUMBER() over (partition by es.session_id order by es.login_time)
    ,TaskStatus         = coalesce(ot.task_state,es.status,' - ')
    ,UserProcessID      = coalesce(es.host_process_id,' - ')
    ,WaitType           = coalesce(er.wait_type,wt.wait_type,er.last_wait_type,' - ')

    ,QueryText          = coalesce((
                            select text As [processing-instruction(definition)]
                            FROM sys.dm_exec_sql_text(ec.most_recent_sql_handle)
                            FOR XML PATH(''), TYPE
                            ),'')
                
    ,BlockingText       = coalesce((
                            select p.text
                            FROM (
                                select MIN(sql_handle) As sql_handle
                                FROM sys.dm_exec_requests r2
                                where r2.session_id = tl.request_session_id
                                ) As rb
                                CROSS APPLY (
                                    select text As [processing-instruction(definition)]
                                    FROM sys.dm_exec_sql_text(rb.sql_handle)
                                    FOR XML PATH(''), TYPE
                                    ) p (text)
                            ),'')
                
From 
    sys.dm_exec_sessions es

    left join sys.dm_exec_connections ec
        On ec.session_id = es.session_id

    left join sys.dm_os_waiting_tasks wt
        On wt.session_id = es.session_id

    left join sys.dm_os_tasks ot
        On es.session_id = ot.session_id

    left join sys.dm_tran_locks tl
        On wt.blocking_session_id = tl.request_session_id

    left join sys.dm_tran_locks tl1
        On ec.session_id = tl1.request_session_id

    left join sys.dm_exec_requests er
        On tl.request_session_id = er.session_id
        
    left join (
        select request_session_id,COUNT(request_session_id) As LockCount
        From sys.dm_tran_locks
        Group By request_session_id
        ) dt
        On ec.session_id = dt.request_session_id
        
    left join (
        select session_id,COUNT(session_id) As TranCount
        From sys.dm_tran_session_transactions
        Group By session_id
        ) st
        On ec.session_id = st.session_id
        
where 1=1
    and es.is_user_process >= 0
    And es.session_id <> 0