Friday, 1 March 2013

Modified sp_who2


USE [master]



GO











IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwWho3]'))



DROP VIEW [dbo].[vwWho3]



GO











CREATE VIEW vwWho3



AS







SELECT



dess.session_id AS [SPID],CASE der.status WHEN 'background' THEN 'BACKGROUND' ELSE der.status END AS 'Status' ,



CONVERT(varchar(32),dess.original_login_name) AS [Login],


ISNULL(dess.host_name,'.') AS [HostName],CASE CONVERT(varchar(12),der.blocking_session_id) WHEN '0' THEN '.'



ELSE CONVERT(varchar(12),der.blocking_session_id) END AS BlkBy,



DB_NAME(der.database_id) AS DBName,



der.command AS 'Command',der.cpu_time AS [CPUTime],der.logical_reads AS [DiskIO],dess.last_request_start_time



AS [LastBatch],ISNULL(dess.program_name,'') AS [ProgramName],ISNULL(dest.text,'') AS 'CurrentQuery',



ISNULL(deqp.query_plan,'') AS 'CurrentPlan',rgwg.name AS [ResourceWorkgroupName]



FROM sys.dm_exec_requests der



INNER JOIN sys.resource_governor_workload_groups rgwg



ON



der.group_id = rgwg.group_id



INNER JOIN sys.dm_exec_sessions dess



ON



der.session_id = dess.session_id



OUTER APPLY sys.dm_exec_sql_text(der.sql_handle) dest



OUTER APPLY sys.dm_exec_query_plan(der.plan_handle) deqp




--now only run this
--SELECT * FROM master.dbo.vwwho3

No comments:

Post a Comment