DatabaseProcApplicationCreatedLinks
sybsystemprocssp_monitor_connection  14 déc. 14Defects Propagation 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 or replace 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_verify_setup @procname
47        if (@rtnstatus != 0)
48            goto error
49    
50        exec @rtnstatus = sp_monitor_verify_cfgval @procname
51        if (@rtnstatus != 0)
52            goto error
53    
54        /* Convert the parameters to upper case */
55        select @upper_option = upper(@OrderBy)
56    
57        /* If the option is elapsed time, order by the sum of CPUTime and Wait Time */
58        if @upper_option = "ELAPSED TIME"
59        begin
60            select @option = "CPUTime+WaitTime"
61        end
62    
63        /* If the option is cpu, order by CPUTime */
64        else if @upper_option = "CPU"
65        begin
66            select @option = "CPUTime"
67        end
68    
69        /* If the option is diskio, order by PhysicalReads */
70        else if @upper_option = "DISKIO"
71        begin
72            select @option = "PhysicalReads"
73        end
74    
75        /* 
76        ** Put all the values in the monProcessSQLText and monProcessActivity
77        ** tables into respective #temp tables.
78        ** The reason this is done is to split the processing phase from
79        ** the collection phase in order to minimize the chances of losing
80        ** data on account of monitoring and SQL Processing at the same time
81        **
82        ** monProcessSQLText provides the SQL Text that is executed at the
83        ** instant the stored procedure is run
84        ** 
85        ** monProcessActivity provides detailed statistics about the processes
86        ** at the instant the stored procedure is executed
87        **
88        ** the mdaconfig built-in helps freeze monitoring when the data is being
89        ** populated into the temp tables.
90        */
91        select @rtnstatus = mdaconfig('freeze', 'begin')
92    
93        /* 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
94        into #temp_P_SQLText
95        from master..monProcessSQLText
96    
97        update #temp_P_SQLText
98        set a.DisplayStats = 1
99        from #temp_P_SQLText a
100       where a.LineNumber = (select min(LineNumber) from #temp_P_SQLText b
101               where b.SPID = a.SPID)
102           and a.SequenceInLine = 1
103   
104   
105       /* 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.IOSize1Page, [master]..monProcessActivity.IOSize2Pages, [master]..monProcessActivity.IOSize4Pages, [master]..monProcessActivity.IOSize8Pages, [master]..monProcessActivity.Application, [master]..monProcessActivity.HostName, [master]..monProcessActivity.ClientName, [master]..monProcessActivity.ClientHostName, [master]..monProcessActivity.ClientApplName
106       into #temp_P_Activity
107       from master..monProcessActivity
108   
109       update #temp_P_Activity
110       set LocksHeld = 0 where LocksHeld < 0
111   
112       /* Unfreeze the MDA counters */
113       select @rtnstatus = mdaconfig('freeze', 'end')
114   
115       /* 
116       ** By default we report in the descending order of Elapsed time 
117       ** which is taken as sum of CPUTime and WaitTime 
118       ** 
119       ** The following is the core SQL that produces the o/p order 
120       ** by the passed in option 
121       **
122       ** This reports the following information
123       ** 1. The spid for which data is presented
124       ** 2. The LoginName for the spid
125       ** 3. The Elapsed time
126       ** 4. Number of locks held
127       ** 5. SQL being executed at that instant by the task.
128       */
129       if @option = "CPUTime+WaitTime"
130       begin
131   
132           select case when
133                       (st.DisplayStats = 1) then
134                   convert(varchar(10), ps.SPID) else '' end as spid,
135               case when (st.DisplayStats = 1)
136                   then
137                   convert(varchar(30), suser_name(sysp.suid))
138                   else '' end as LoginName,
139               case when (st.DisplayStats = 1)
140                   then convert(varchar(10), (ps.CPUTime + ps.WaitTime))
141                   else '' end as 'ElapsedTime',
142               case when (st.DisplayStats = 1)
143                   then convert(varchar(10), (ps.CPUTime))
144                   else '' end as 'CPU_Time',
145               case when (st.DisplayStats = 1) then
146                   convert(char(10), ps.PhysicalReads)
147                   else '' end as 'Physical_Reads',
148               case when (st.DisplayStats = 1)
149                   then convert(varchar(10), ps.LocksHeld)
150                   else '' end as 'LocksHeld',
151               st.SQLText as 'SQLText'
152           into #conn_elapse
153           from #temp_P_Activity ps,
154               #temp_P_SQLText st, master.dbo.sysprocesses sysp
155           where ps.SPID = st.SPID AND ps.SPID != @@spid AND
156               sysp.spid = ps.SPID
157               AND (ps.CPUTime + ps.WaitTime) != 0
158           order by (ps.CPUTime + ps.WaitTime) desc, ps.SPID,
159               st.LineNumber asc, st.SequenceInLine
160   
161           exec sp_autoformat @fulltabname = '#conn_elapse'
162   
163       end
164       else if (@option = "CPUTime")
165       begin
166           /* Core SQL that produces the o/p order by the passed in option */
167           select case when
168                       (st.DisplayStats = 1) then
169                   convert(varchar(10), ps.SPID) else '' end as spid,
170               case when (st.DisplayStats = 1)
171                   then
172                   convert(varchar(30), suser_name(sysp.suid))
173                   else '' end as LoginName,
174               case when (st.DisplayStats = 1)
175                   then convert(varchar(10), (ps.CPUTime + ps.WaitTime))
176                   else '' end as 'ElapsedTime',
177               case when (st.DisplayStats = 1)
178                   then convert(varchar(10), (ps.CPUTime))
179                   else '' end as 'CPU_Time',
180               case when (st.DisplayStats = 1) then
181                   convert(char(10), ps.PhysicalReads)
182                   else '' end as 'Physical_Reads',
183               case when (st.DisplayStats = 1) then
184                   convert(varchar(10), ps.LocksHeld)
185                   else '' end as 'LocksHeld',
186               st.SQLText as 'SQLText'
187           into #conn_cpu
188           from #temp_P_Activity ps,
189               #temp_P_SQLText st,
190               master.dbo.sysprocesses sysp
191           where ps.SPID != @@spid AND ps.SPID = st.SPID AND
192               sysp.spid = ps.SPID
193               AND ps.CPUTime != 0
194           order by ps.CPUTime desc, ps.SPID,
195               st.LineNumber asc, st.SequenceInLine
196   
197   
198           exec sp_autoformat @fulltabname = '#conn_cpu'
199       end
200       else if (@option = "PhysicalReads")
201       begin
202           /* Core SQL that produces the o/p order by the passed in option */
203           select case when
204                       (st.DisplayStats = 1) then
205                   convert(varchar(10), ps.SPID) else '' end as spid,
206               case when (st.DisplayStats = 1)
207                   then
208                   convert(varchar(30), suser_name(sysp.suid))
209                   else '' end as LoginName,
210               case when (st.DisplayStats = 1)
211                   then convert(varchar(10), (ps.CPUTime + ps.WaitTime))
212                   else '' end as 'ElapsedTime',
213               case when (st.DisplayStats = 1)
214                   then convert(varchar(10), (ps.CPUTime))
215                   else '' end as 'CPU_Time',
216               case when (st.DisplayStats = 1) then
217                   convert(char(10), ps.PhysicalReads)
218                   else '' end as 'Physical_Reads',
219               case when (st.DisplayStats = 1) then
220                   convert(varchar(10), ps.LocksHeld)
221                   else '' end as 'LocksHeld',
222               st.SQLText as 'SQLText'
223           into #conn_dsk
224           from #temp_P_Activity ps,
225               #temp_P_SQLText st,
226               master.dbo.sysprocesses sysp
227           where ps.SPID != @@spid AND ps.SPID = st.SPID AND
228               sysp.spid = ps.SPID
229               AND ps.PhysicalReads != 0
230           order by ps.PhysicalReads desc, ps.SPID,
231               st.LineNumber asc, st.SequenceInLine
232   
233           exec sp_autoformat @fulltabname = '#conn_dsk'
234       end
235       else
236       begin
237           raiserror 19260, "sp_monitor 'help', 'connection'"
238           return 1
239       end
240   
241   
242       return (0)
243   
244   error:
245       return (1) -- }
246   

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 134
 MEST 4 Empty String will be replaced by Single Space 138
 MEST 4 Empty String will be replaced by Single Space 141
 MEST 4 Empty String will be replaced by Single Space 144
 MEST 4 Empty String will be replaced by Single Space 147
 MEST 4 Empty String will be replaced by Single Space 150
 MEST 4 Empty String will be replaced by Single Space 169
 MEST 4 Empty String will be replaced by Single Space 173
 MEST 4 Empty String will be replaced by Single Space 176
 MEST 4 Empty String will be replaced by Single Space 179
 MEST 4 Empty String will be replaced by Single Space 182
 MEST 4 Empty String will be replaced by Single Space 185
 MEST 4 Empty String will be replaced by Single Space 205
 MEST 4 Empty String will be replaced by Single Space 209
 MEST 4 Empty String will be replaced by Single Space 212
 MEST 4 Empty String will be replaced by Single Space 215
 MEST 4 Empty String will be replaced by Single Space 218
 MEST 4 Empty String will be replaced by Single Space 221
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 156
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 192
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 228
 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  
 MNER 3 No Error Check should check @@error after select into 93
 MNER 3 No Error Check should check @@error after update 97
 MNER 3 No Error Check should check @@error after select into 105
 MNER 3 No Error Check should check @@error after update 109
 MNER 3 No Error Check should check @@error after select into 132
 MNER 3 No Error Check should check return value of exec 161
 MNER 3 No Error Check should check @@error after select into 167
 MNER 3 No Error Check should check return value of exec 198
 MNER 3 No Error Check should check @@error after select into 203
 MNER 3 No Error Check should check return value of exec 233
 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 133
 MUCO 3 Useless Code Useless Brackets 135
 MUCO 3 Useless Code Useless Brackets 139
 MUCO 3 Useless Code Useless Brackets 142
 MUCO 3 Useless Code Useless Brackets 143
 MUCO 3 Useless Code Useless Brackets 145
 MUCO 3 Useless Code Useless Brackets 148
 MUCO 3 Useless Code Useless Brackets 164
 MUCO 3 Useless Code Useless Brackets 168
 MUCO 3 Useless Code Useless Brackets 170
 MUCO 3 Useless Code Useless Brackets 174
 MUCO 3 Useless Code Useless Brackets 177
 MUCO 3 Useless Code Useless Brackets 178
 MUCO 3 Useless Code Useless Brackets 180
 MUCO 3 Useless Code Useless Brackets 183
 MUCO 3 Useless Code Useless Brackets 200
 MUCO 3 Useless Code Useless Brackets 204
 MUCO 3 Useless Code Useless Brackets 206
 MUCO 3 Useless Code Useless Brackets 210
 MUCO 3 Useless Code Useless Brackets 213
 MUCO 3 Useless Code Useless Brackets 214
 MUCO 3 Useless Code Useless Brackets 216
 MUCO 3 Useless Code Useless Brackets 219
 MUCO 3 Useless Code Useless Brackets 242
 MUCO 3 Useless Code Useless Brackets 245
 QAPT 3 Access to Proxy Table master..monProcessSQLText 95
 QAPT 3 Access to Proxy Table master..monProcessActivity 107
 QCTC 3 Conditional Table Creation 132
 QCTC 3 Conditional Table Creation 167
 QCTC 3 Conditional Table Creation 203
 QJWT 3 Join or Sarg Without Index on temp table 101
 QJWT 3 Join or Sarg Without Index on temp table 155
 QJWT 3 Join or Sarg Without Index on temp table 156
 QJWT 3 Join or Sarg Without Index on temp table 191
 QJWT 3 Join or Sarg Without Index on temp table 192
 QJWT 3 Join or Sarg Without Index on temp table 227
 QJWT 3 Join or Sarg Without Index on temp table 228
 QNAJ 3 Not using ANSI Inner Join 153
 QNAJ 3 Not using ANSI Inner Join 188
 QNAJ 3 Not using ANSI Inner Join 224
 VNRD 3 Variable is not read @whoami 44
 VNRD 3 Variable is not read @rtnstatus 113
 VUNU 3 Variable is not used @val 37
 VUNU 3 Variable is not used @this_spid 40
 MSUC 2 Correlated Subquery Marker 100
 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: 102 31

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