1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139

create or alter procedure dbo.sp_who_azsqldb
as

-- active sessions
create table #Connections
(
	SessionId int not null
	,BlockingId int null
	,Host nvarchar(128) null
	,Program nvarchar(128) null
	,Login nvarchar(128) null
	,Status nvarchar(32) null
	,CommandType nvarchar(32) null
	,TotalTime bigint null
	,CPUTime bigint null
	,WaitTime bigint null
	,WaitType nvarchar(60) null
	,Reads bigint null
	,Writes bigint null
	,DOP int null
	,ParallelWorkers int null
	,IsUserProcess bit null
	,SQLHandle varbinary(64) null
	,SQLStart int null
	,index IX_SessionId nonclustered (SessionId)
	,index IX_BlockingId nonclustered (BlockingId)
);
​
insert into #Connections
(
	SessionId
	,Host
	,Program
	,Login
	,IsUserProcess
	,BlockingId
	,Status
	,CommandType
	,TotalTime
	,CPUTime
	,WaitTime
	,WaitType
	,Reads
	,Writes
	,DOP
	,ParallelWorkers
	,SQLHandle
	,SQLStart
)
select
	s.session_id
	,s.host_name
	,s.program_name
	,s.login_name
	,s.is_user_process
	,r.blocking_session_id
	,r.status
	,r.command
	,r.total_elapsed_time
	,r.cpu_time
	,r.wait_time
	,r.wait_type
	,r.reads + r.logical_reads
	,r.writes
	,r.dop
	,r.parallel_worker_count
	,r.sql_handle
	,r.statement_start_offset
from
	sys.dm_exec_connections as c
	join sys.dm_exec_sessions as s
		on s.session_id = c.session_id
	join sys.dm_exec_requests as r
		on r.session_id = s.session_id
where
	-- only include the parent connection
	c.parent_connection_id is null;

-- system processes
insert into #Connections
(
	SessionId
	,BlockingId
	,Login
	,Status
	,CommandType
	,CPUTime
	,WaitTime
	,WaitType
	,IsUserProcess
	,SQLHandle
	,SQLStart
)
select
	s.spid
	,s.blocked
	,s.loginame
	,s.status
	,nullif(s.cmd, '')
	,s.cpu
	,s.waittime
	,s.lastwaittype
	,0
	,s.sql_handle
	,s.stmt_start
from
	sys.sysprocesses as s
	left join #Connections as c
		on c.SessionId = s.spid
where
	s.hostprocess = ''
	and c.SessionId is null;
​
select
	c.SessionId
	,c.BlockingId
	,c.Host
	,c.Program
	,nullif(c.Login, '') as Login
	,c.Status
	,c.TotalTime
	,c.CPUTime
	,c.WaitTime
	,c.WaitType
	,c.Reads
	,c.Writes
	,c.DOP
	,c.ParallelWorkers
	,c.CommandType
	,object_schema_name(t.objectid, t.dbid) + '.' + object_name(t.objectid, t.dbid) as ObjectName
	-- only fetch the first 1000 characters; the entire statement isn't needed
	,substring(t.text, c.SQLStart / 2, 1000) as SQLStatement
from
	#Connections as c
	outer apply sys.dm_exec_sql_text(c.SQLHandle) as t
where
	c.IsUserProcess = 1
	-- include the blocking session even if it is a system process
	or exists (select 1 from #Connections cb where cb.BlockingId = c.SessionId );