1
2 /*
3 ** This stored procedure is a subordinate stored procedure that is invoked from
4 ** the parent stored procedure sp_monitor when "connection" is passed as the
5 ** argument.
6 **
7 ** This stored procedure provides performance metrics pertaining
8 ** to user connections. It provides this information for each user connection
9 ** that is active at the time the command is executed. A snapshot is collected
10 ** from the monitoring tables of interest and presented. The output for each
11 ** user connection is by default sorted based on the elapsed time. Elapsed time
12 ** is defined as sum of cpu time and wait time i.e. the effective cpu time
13 ** spent + the time spent waiting for acquiring resources needed for execution
14 ** of the query.
15 **
16 ** Different sort options include "elapsed time", "cpu" and "diskio"
17 **
18 ** This procedure uses tempdb. For this procedure to be run tempdb should
19 ** be sized depending on the number of active connections on the system and
20 ** the size of the queries they are executing.
21 **
22 ** Parameters:
23 ** @OrderBy - Parameter for ordering the output.
24 **
25 ** Returns:
26 ** 0 - upon sucessful completion
27 ** 1 - If an error resulted.
28 {
29 */
30
31 create procedure sp_monitor_connection(
32 @OrderBy varchar(30) = NULL
33 ) as
34
35 declare @upper_option varchar(100)
36 declare @option varchar(100)
37 declare @val int
38 declare @procname char(10)
39 declare @rtnstatus int
40 , @this_spid int -- trace this spid
41 , @whoami varchar(30)
42
43 select @procname = "connection"
44 , @whoami = object_name(@@procid, db_id('sybsystemprocs'))
45
46 exec @rtnstatus = sp_monitor_check_permission @whoami
47 if (@rtnstatus != 0)
48 goto error
49
50 exec @rtnstatus = sp_monitor_verify_setup @procname
51 if (@rtnstatus != 0)
52 goto error
53
54 exec @rtnstatus = sp_monitor_verify_cfgval @procname
55 if (@rtnstatus != 0)
56 goto error
57
58 /* Convert the parameters to upper case */
59 select @upper_option = upper(@OrderBy)
60
61 /* If the option is elapsed time, order by the sum of CPUTime and Wait Time */
62 if @upper_option = "ELAPSED TIME"
63 begin
64 select @option = "CPUTime+WaitTime"
65 end
66
67 /* If the option is cpu, order by CPUTime */
68 else if @upper_option = "CPU"
69 begin
70 select @option = "CPUTime"
71 end
72
73 /* If the option is diskio, order by PhysicalReads */
74 else if @upper_option = "DISKIO"
75 begin
76 select @option = "PhysicalReads"
77 end
78
79 /*
80 ** Put all the values in the monProcessSQLText and monProcessActivity
81 ** tables into respective #temp tables.
82 ** The reason this is done is to split the processing phase from
83 ** the collection phase in order to minimize the chances of losing
84 ** data on account of monitoring and SQL Processing at the same time
85 **
86 ** monProcessSQLText provides the SQL Text that is executed at the
87 ** instant the stored procedure is run
88 **
89 ** monProcessActivity provides detailed statistics about the processes
90 ** at the instant the stored procedure is executed
91 **
92 ** the mdaconfig built-in helps freeze monitoring when the data is being
93 ** populated into the temp tables.
94 */
95 select @rtnstatus = mdaconfig('freeze', 'begin')
96
97 /* Adaptive Server has expanded all '*' elements in the following statement */ select master..monProcessSQLText.SPID, master..monProcessSQLText.InstanceID, master..monProcessSQLText.KPID, master..monProcessSQLText.ServerUserID, master..monProcessSQLText.BatchID, master..monProcessSQLText.LineNumber, master..monProcessSQLText.SequenceInLine, master..monProcessSQLText.SQLText, DisplayStats = 0
98 into #temp_P_SQLText
99 from master..monProcessSQLText
100
101 update #temp_P_SQLText
102 set a.DisplayStats = 1
103 from #temp_P_SQLText a
104 where a.LineNumber = (select min(LineNumber) from #temp_P_SQLText b
105 where b.SPID = a.SPID)
106 and a.SequenceInLine = 1
107
108
109 /* Adaptive Server has expanded all '*' elements in the following statement */ select master..monProcessActivity.SPID, master..monProcessActivity.InstanceID, master..monProcessActivity.KPID, master..monProcessActivity.ServerUserID, master..monProcessActivity.CPUTime, master..monProcessActivity.WaitTime, master..monProcessActivity.PhysicalReads, master..monProcessActivity.LogicalReads, master..monProcessActivity.PagesRead, master..monProcessActivity.PhysicalWrites, master..monProcessActivity.PagesWritten, master..monProcessActivity.MemUsageKB, master..monProcessActivity.LocksHeld, master..monProcessActivity.TableAccesses, master..monProcessActivity.IndexAccesses, master..monProcessActivity.TempDbObjects, master..monProcessActivity.WorkTables, master..monProcessActivity.ULCBytesWritten, master..monProcessActivity.ULCFlushes, master..monProcessActivity.ULCFlushFull, master..monProcessActivity.ULCMaxUsage, master..monProcessActivity.ULCCurrentUsage, master..monProcessActivity.Transactions, master..monProcessActivity.Commits, master..monProcessActivity.Rollbacks, master..monProcessActivity.HeapMemoryInUseKB, master..monProcessActivity.HeapMemoryUsedHWM_KB, master..monProcessActivity.HeapMemoryReservedKB, master..monProcessActivity.HeapMemoryAllocs, master..monProcessActivity.Application, master..monProcessActivity.HostName, master..monProcessActivity.ClientName, master..monProcessActivity.ClientHostName, master..monProcessActivity.ClientApplName
110 into #temp_P_Activity
111 from master..monProcessActivity
112
113 update #temp_P_Activity
114 set LocksHeld = 0 where LocksHeld < 0
115
116 /* Unfreeze the MDA counters */
117 select @rtnstatus = mdaconfig('freeze', 'end')
118
119 /*
120 ** By default we report in the descending order of Elapsed time
121 ** which is taken as sum of CPUTime and WaitTime
122 **
123 ** The following is the core SQL that produces the o/p order
124 ** by the passed in option
125 **
126 ** This reports the following information
127 ** 1. The spid for which data is presented
128 ** 2. The LoginName for the spid
129 ** 3. The Elapsed time
130 ** 4. Number of locks held
131 ** 5. SQL being executed at that instant by the task.
132 */
133 if @option = "CPUTime+WaitTime"
134 begin
135
136 select case when
137 (st.DisplayStats = 1) then
138 convert(varchar(10), ps.SPID) else '' end as spid,
139 case when (st.DisplayStats = 1)
140 then
141 convert(varchar(30), suser_name(sysp.suid))
142 else '' end as LoginName,
143 case when (st.DisplayStats = 1)
144 then convert(varchar(10), (ps.CPUTime + ps.WaitTime))
145 else '' end as 'ElapsedTime',
146 case when (st.DisplayStats = 1)
147 then convert(varchar(10), (ps.CPUTime))
148 else '' end as 'CPU_Time',
149 case when (st.DisplayStats = 1) then
150 convert(char(10), ps.PhysicalReads)
151 else '' end as 'Physical_Reads',
152 case when (st.DisplayStats = 1)
153 then convert(varchar(10), ps.LocksHeld)
154 else '' end as 'LocksHeld',
155 st.SQLText as 'SQLText'
156 into #conn_elapse
157 from #temp_P_Activity ps,
158 #temp_P_SQLText st, master.dbo.sysprocesses sysp
159 where ps.SPID = st.SPID AND ps.SPID != @@spid AND
160 sysp.spid = ps.SPID
161 AND (ps.CPUTime + ps.WaitTime) != 0
162 order by (ps.CPUTime + ps.WaitTime) desc, ps.SPID,
163 st.LineNumber asc, st.SequenceInLine
164
165 exec sp_autoformat @fulltabname = '#conn_elapse'
166
167 end
168 else if (@option = "CPUTime")
169 begin
170 /* Core SQL that produces the o/p order by the passed in option */
171 select case when
172 (st.DisplayStats = 1) then
173 convert(varchar(10), ps.SPID) else '' end as spid,
174 case when (st.DisplayStats = 1)
175 then
176 convert(varchar(30), suser_name(sysp.suid))
177 else '' end as LoginName,
178 case when (st.DisplayStats = 1)
179 then convert(varchar(10), (ps.CPUTime + ps.WaitTime))
180 else '' end as 'ElapsedTime',
181 case when (st.DisplayStats = 1)
182 then convert(varchar(10), (ps.CPUTime))
183 else '' end as 'CPU_Time',
184 case when (st.DisplayStats = 1) then
185 convert(char(10), ps.PhysicalReads)
186 else '' end as 'Physical_Reads',
187 case when (st.DisplayStats = 1) then
188 convert(varchar(10), ps.LocksHeld)
189 else '' end as 'LocksHeld',
190 st.SQLText as 'SQLText'
191 into #conn_cpu
192 from #temp_P_Activity ps,
193 #temp_P_SQLText st,
194 master.dbo.sysprocesses sysp
195 where ps.SPID != @@spid AND ps.SPID = st.SPID AND
196 sysp.spid = ps.SPID
197 AND ps.CPUTime != 0
198 order by ps.CPUTime desc, ps.SPID,
199 st.LineNumber asc, st.SequenceInLine
200
201
202 exec sp_autoformat @fulltabname = '#conn_cpu'
203 end
204 else if (@option = "PhysicalReads")
205 begin
206 /* Core SQL that produces the o/p order by the passed in option */
207 select case when
208 (st.DisplayStats = 1) then
209 convert(varchar(10), ps.SPID) else '' end as spid,
210 case when (st.DisplayStats = 1)
211 then
212 convert(varchar(30), suser_name(sysp.suid))
213 else '' end as LoginName,
214 case when (st.DisplayStats = 1)
215 then convert(varchar(10), (ps.CPUTime + ps.WaitTime))
216 else '' end as 'ElapsedTime',
217 case when (st.DisplayStats = 1)
218 then convert(varchar(10), (ps.CPUTime))
219 else '' end as 'CPU_Time',
220 case when (st.DisplayStats = 1) then
221 convert(char(10), ps.PhysicalReads)
222 else '' end as 'Physical_Reads',
223 case when (st.DisplayStats = 1) then
224 convert(varchar(10), ps.LocksHeld)
225 else '' end as 'LocksHeld',
226 st.SQLText as 'SQLText'
227 into #conn_dsk
228 from #temp_P_Activity ps,
229 #temp_P_SQLText st,
230 master.dbo.sysprocesses sysp
231 where ps.SPID != @@spid AND ps.SPID = st.SPID AND
232 sysp.spid = ps.SPID
233 AND ps.PhysicalReads != 0
234 order by ps.PhysicalReads desc, ps.SPID,
235 st.LineNumber asc, st.SequenceInLine
236
237 exec sp_autoformat @fulltabname = '#conn_dsk'
238 end
239 else
240 begin
241 raiserror 19260, "sp_monitor 'help', 'connection'"
242 return 1
243 end
244
245
246 return (0)
247
248 error:
249 return (1) -- }
250
exec sp_procxmode 'sp_monitor_connection', 'AnyMode'
go
Grant Execute on sp_monitor_connection to public
go