Blocker

-- Page IO
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%'

SELECT
[Session ID] = s.session_id,
[User Process] = CONVERT(CHAR(1), s.is_user_process),
[Login] = s.login_name,
[Database] = ISNULL(db_name(p.dbid), N''),
[Task State] = ISNULL(t.task_state, N''),
[Command] = ISNULL(r.command, N''),
[Application] = ISNULL(s.program_name, N''),
[Wait Time (ms)] = ISNULL(w.wait_duration_ms, 0),
[Wait Type] = ISNULL(w.wait_type, N''),
[Wait Resource] = ISNULL(w.resource_description, N''),
[Blocked By] = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
[Head Blocker] =
CASE
-- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'
-- session is either not blocking someone, or is blocking someone but is blocked by another party
ELSE ''
END,
[Total CPU (ms)] = s.cpu_time,
[Total Physical I/O (MB)] = (s.reads + s.writes) * 8 / 1024,
[Memory Use (KB)] = s.memory_usage * 8192 / 1024,
[Open Transactions] = ISNULL(r.open_transaction_count,0),
[Login Time] = s.login_time,
[Last Request Start Time] = s.last_request_start_time,
[Host Name] = ISNULL(s.host_name, N''),
[Net Address] = ISNULL(c.client_net_address, N''),
[Execution Context ID] = ISNULL(t.exec_context_id, 0),
[Request ID] = ISNULL(r.request_id, 0)--,
--[Workload Group] = ISNULL(g.name, N'')
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN
(
-- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
-- waiting for several different threads. This will cause that thread to show up in multiple rows
-- in our grid, which we don't want. Use ROW_NUMBER to select the longest wait for each thread,
-- and use it as representative of the other wait relationships this thread is involved in.
SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
FROM sys.dm_os_waiting_tasks
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
--LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g ON (g.group_id = s.group_id)--TAKE THIS dmv OUT TO WORK IN 2005
LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)
ORDER BY s.session_id;

select str(request_session_id, 4, 0) as spid,
CONVERT(varchar(30), db_name(resource_database_id)) as db_name,
case when resource_database_id = DB_ID() and resource_type = 'OBJECT'
then CONVERT(char(20), object_name(resource_associated_entity_id))
else CONVERT(char(20), resource_associated_entity_id)
end as object,
CONVERT(varchar(12), resource_type) as resrc_type,
CONVERT(varchar(12), request_type) as req_type,
CONVERT(char(1), request_mode) as mode,
CONVERT(varchar(255), resource_description) as resource_desc,
CONVERT(varchar(8), request_status) as status
from sys.dm_tran_locks
order by request_session_id, 3 desc

select CONVERT(char(4),session_id) as spid,
CONVERT(char(8),wait_duration_ms) as duration,
CONVERT(char(8),wait_type) as wait_type,
CONVERT(char(3),blocking_session_id) as blk,
resource_description
from sys.dm_os_waiting_tasks
where blocking_session_id is not null


-- sp_who2
DROP TABLE #Temp
CREATE TABLE #Temp (SPID varchar(100), Status varchar(100) NULL, Login varchar(100) NULL, HostName varchar(100) NULL,
BlkBy varchar(100) NULL, DBName varchar(100) NULL, Command varchar(100) NULL, CPUTime varchar(100) NULL, DiskIO varchar(100) NULL,
LastBatch varchar(100) NULL, ProgramName varchar(100) NULL, SPID2 varchar(100) NULL, RequestID varchar(100) NULL)

INSERT INTO #Temp
EXEC sp_who2

select * from #Temp