DatabaseProcApplicationCreatedLinks
sybsystemprocssp_monitor_connection  31 Aug 14Defects Dependencies

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
DEFECTS
 MEST 4 Empty String will be replaced by Single Space 138
 MEST 4 Empty String will be replaced by Single Space 142
 MEST 4 Empty String will be replaced by Single Space 145
 MEST 4 Empty String will be replaced by Single Space 148
 MEST 4 Empty String will be replaced by Single Space 151
 MEST 4 Empty String will be replaced by Single Space 154
 MEST 4 Empty String will be replaced by Single Space 173
 MEST 4 Empty String will be replaced by Single Space 177
 MEST 4 Empty String will be replaced by Single Space 180
 MEST 4 Empty String will be replaced by Single Space 183
 MEST 4 Empty String will be replaced by Single Space 186
 MEST 4 Empty String will be replaced by Single Space 189
 MEST 4 Empty String will be replaced by Single Space 209
 MEST 4 Empty String will be replaced by Single Space 213
 MEST 4 Empty String will be replaced by Single Space 216
 MEST 4 Empty String will be replaced by Single Space 219
 MEST 4 Empty String will be replaced by Single Space 222
 MEST 4 Empty String will be replaced by Single Space 225
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 160
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 196
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 232
 TNOI 4 Table with no index master..monProcessActivity master..monProcessActivity
 TNOI 4 Table with no index master..monProcessSQLText master..monProcessSQLText
 TNOI 4 Table with no index master..sysprocesses master..sysprocesses
 MGTP 3 Grant to public master..sysprocesses  
 MGTP 3 Grant to public sybsystemprocs..sp_monitor_connection  
 MNER 3 No Error Check should check @@error after select into 97
 MNER 3 No Error Check should check @@error after update 101
 MNER 3 No Error Check should check @@error after select into 109
 MNER 3 No Error Check should check @@error after update 113
 MNER 3 No Error Check should check @@error after select into 136
 MNER 3 No Error Check should check return value of exec 165
 MNER 3 No Error Check should check @@error after select into 171
 MNER 3 No Error Check should check return value of exec 202
 MNER 3 No Error Check should check @@error after select into 207
 MNER 3 No Error Check should check return value of exec 237
 MUCO 3 Useless Code Useless Brackets in create proc 31
 MUCO 3 Useless Code Useless Brackets 47
 MUCO 3 Useless Code Useless Brackets 51
 MUCO 3 Useless Code Useless Brackets 55
 MUCO 3 Useless Code Useless Brackets 137
 MUCO 3 Useless Code Useless Brackets 139
 MUCO 3 Useless Code Useless Brackets 143
 MUCO 3 Useless Code Useless Brackets 146
 MUCO 3 Useless Code Useless Brackets 147
 MUCO 3 Useless Code Useless Brackets 149
 MUCO 3 Useless Code Useless Brackets 152
 MUCO 3 Useless Code Useless Brackets 168
 MUCO 3 Useless Code Useless Brackets 172
 MUCO 3 Useless Code Useless Brackets 174
 MUCO 3 Useless Code Useless Brackets 178
 MUCO 3 Useless Code Useless Brackets 181
 MUCO 3 Useless Code Useless Brackets 182
 MUCO 3 Useless Code Useless Brackets 184
 MUCO 3 Useless Code Useless Brackets 187
 MUCO 3 Useless Code Useless Brackets 204
 MUCO 3 Useless Code Useless Brackets 208
 MUCO 3 Useless Code Useless Brackets 210
 MUCO 3 Useless Code Useless Brackets 214
 MUCO 3 Useless Code Useless Brackets 217
 MUCO 3 Useless Code Useless Brackets 218
 MUCO 3 Useless Code Useless Brackets 220
 MUCO 3 Useless Code Useless Brackets 223
 MUCO 3 Useless Code Useless Brackets 246
 MUCO 3 Useless Code Useless Brackets 249
 QAPT 3 Access to Proxy Table master..monProcessSQLText 99
 QAPT 3 Access to Proxy Table master..monProcessActivity 111
 QCTC 3 Conditional Table Creation 136
 QCTC 3 Conditional Table Creation 171
 QCTC 3 Conditional Table Creation 207
 QJWT 3 Join or Sarg Without Index on temp table 105
 QJWT 3 Join or Sarg Without Index on temp table 159
 QJWT 3 Join or Sarg Without Index on temp table 160
 QJWT 3 Join or Sarg Without Index on temp table 195
 QJWT 3 Join or Sarg Without Index on temp table 196
 QJWT 3 Join or Sarg Without Index on temp table 231
 QJWT 3 Join or Sarg Without Index on temp table 232
 QNAJ 3 Not using ANSI Inner Join 157
 QNAJ 3 Not using ANSI Inner Join 192
 QNAJ 3 Not using ANSI Inner Join 228
 VNRD 3 Variable is not read @rtnstatus 117
 VUNU 3 Variable is not used @val 37
 VUNU 3 Variable is not used @this_spid 40
 MSUC 2 Correlated Subquery Marker 104
 MTR1 2 Metrics: Comments Ratio Comments: 29% 31
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 18 = 18dec - 2exi + 2 31
 MTR3 2 Metrics: Query Complexity Complexity: 106 31

DEPENDENCIES
PROCS AND TABLES USED
reads table master..monProcessActivity (1)  
writes table tempdb..#conn_cpu (1) 
calls proc sybsystemprocs..sp_monitor_verify_setup  
   calls proc sybsystemprocs..sp_monitor_getcfgnum  
      reads table master..sysconfigures (1)  
   reads table master..sysobjects (1)  
   calls proc sybsystemprocs..sp_monitor_getcfgval  
      reads table master..sysconfigures (1)  
      reads table master..syscurconfigs (1)  
read_writes table tempdb..#temp_P_SQLText (1) 
calls proc sybsystemprocs..sp_monitor_check_permission  
reads table master..monProcessSQLText (1)  
writes table tempdb..#conn_dsk (1) 
calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   read_writes table tempdb..#colinfo_af (1) 
   reads table tempdb..syscolumns (1)  
   reads table master..systypes (1)  
   calls proc sybsystemprocs..sp_autoformat  
   calls proc sybsystemprocs..sp_namecrack  
   reads table tempdb..systypes (1)  
read_writes table tempdb..#temp_P_Activity (1) 
writes table tempdb..#conn_elapse (1) 
reads table master..sysprocesses (1)  
calls proc sybsystemprocs..sp_monitor_verify_cfgval  
   calls proc sybsystemprocs..sp_monitor_getcfgval