DatabaseProcApplicationCreatedLinks
sybsystemprocssp_monitor_procedure  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** Stored Procedure: sp_monitor "procedure"
4     **
5     ** This stored procedure provides a report on performance metrics for 
6     ** stored procedures that have been run on the server.
7     ** It provides data for each execution of a stored procedure
8     ** or average statistics for a stored procedure for
9     ** all of the times it has recently been executed. Either or
10    ** both reports can be generated depending on the input parameters.
11    ** You can also specify a single procedure to report on.
12    **
13    ** This procedure uses the monSysStatement Monitoring Table
14    ** to collect statistics on the execution of stored procedures.
15    **
16    ** Usage:
17    **	sp_monitor "procedure", [ [,  [, ]]]
18    **
19    ** Examples:
20    **	sp_monitor "procedure" -- Display summary for all stored procedures
21    **	sp_monitor "procedure", "detail" -- Display detail for all stored procs
22    **	sp_monitor "procedure", "db1" -- Display average data for database db1
23    **	sp_monitor "procedure", "db1", "detail" -- Detail for database db1
24    **	sp_monitor "procedure", "db1", "sproca" -- Display avg data for sproca
25    **	sp_monitor "procedure", "db1", "sproca", "detail" -- Detail for sproca
26    **
27    ** If no procedure and database name is given then this procedure
28    ** provides a report on all stored procedures run on the system
29    ** since the sp_monitor "procedure" stored procedure was previously run.
30    **
31    ** If a procedure and database name are provided, then a report
32    ** on activity by that stored prcedure since the last time the
33    ** sp_monitor "procedure" stored procedure was run is reported.
34    **
35    ** All of the data is sorted in descending order of Elapsed Times.
36    **
37    ** Parameters:
38    ** dbname:   Optional.  Name of database for stored procedure. 
39    **		- Reports Average of stored procedure
40    **		  executions for the database specified.
41    **		- If not supplied or if 'summary' was supplied, it 
42    **		  reports Average stored procedure statistics for all databases.
43    **		- If 'detail' is supplied, then report detailed
44    **		  stored procedure execution statistics for the entire server.
45    **		REQUIRED if a stored procedure name is supplied.
46    ** procname: Optional. Name of stored procedure to report on.
47    **		- If not supplied, statistics for all stored
48    **		  procedures is displayed for the given database.
49    **		- If value is 'summary', then only average execution
50    **		  statistics are reported for each procedure that
51    **		  has been executed for the given database.
52    **		- If value is 'detail', then details of procedure
53    **		  procedure execution and average execution
54    **		  statistics will be reported for each stored 
55    **		  procedure that executed for the given database.
56    ** option:   Optional.  Additional information
57    **		- If stored procedure and database name are
58    **		  given and the value of option is 'summary' then
59    **		  only average execution statistics for the
60    **		  specified stored procedure are reported.
61    **		- If stored procedure and database name are
62    **		  given and the value of option is 'detail' then
63    **		  detailed statistics are provided
64    **		  for the passed in stored procedure.
65    **
66    {
67    */
68    
69    create or replace procedure sp_monitor_procedure
70        @dbname varchar(30) = NULL,
71        @procname varchar(30) = "%",
72        @option varchar(30) = NULL
73    as
74    
75        /* Parameters needed to support monitoring of sprocs, connections and stmts */
76        declare @output varchar(1024)
77        declare @dbid int
78        declare @procedureID int
79        declare @query_text varchar(1024)
80        declare @rptline char(255)
81        declare @averages int
82        declare @detail int
83        declare @sqlmsg char(255)
84        declare @cursprocname varchar(30)
85        declare @myobjid int
86        declare @sybprocs_dbid int
87        declare @rtnstatus int
88            , @mdacfg_retval int
89            , @whoami varchar(30)
90    
91        select @sybprocs_dbid = db_id('sybsystemprocs')
92            , @whoami = object_name(@@procid, db_id('sybsystemprocs'))
93    
94        select @myobjid = id
95        from sybsystemprocs..sysobjects
96        where name = 'sp_monitor_procedure'
97            and type = 'P'
98    
99        select @cursprocname = "procedure"
100   
101       exec @rtnstatus = sp_monitor_verify_setup @cursprocname
102   
103       exec @mdacfg_retval = sp_monitor_verify_cfgval @cursprocname
104   
105       if ((@rtnstatus != 0) or (@mdacfg_retval != 0))
106           goto error
107   
108       if (@procname is NULL)
109       begin
110           select @procname = "%"
111       end
112   
113       /*
114       ** Process the input parameters.  Determine whether the
115       ** all or average option was specified.
116       */
117       if (@dbname = 'detail' and @procname = "%")
118       begin
119           select @dbname = NULL
120           select @averages = 0
121           select @detail = 1
122       end
123       else if (@dbname = 'summary' and @procname = "%")
124       begin
125           select @dbname = NULL
126           select @averages = 1
127           select @detail = 0
128       end
129       else if (@dbname is not NULL and @procname = 'detail')
130       begin
131           select @procname = "%"
132           select @averages = 1
133           select @detail = 1
134       end
135       else if (@dbname is not NULL and @procname = 'summary')
136       begin
137           select @procname = "%"
138           select @averages = 1
139           select @detail = 0
140       end
141       else if (@dbname is not NULL and @procname != "%" and @option = 'detail')
142       begin
143           select @averages = 1
144           select @detail = 1
145       end
146       else if (@dbname is not NULL and @procname != "%" and @option = 'summary')
147       begin
148           select @averages = 1
149           select @detail = 0
150       end
151       else
152       begin
153           select @averages = 1
154           select @detail = 0
155       end
156   
157       /*
158       ** Create temporary table here so either one of the insert
159       ** statements below can populate it.  The ASE parser will not
160       ** allow two select-into statements to the same table in the
161       ** same procedure.
162       */
163       select ProcName = convert(varchar(30), ""),
164           DBName = convert(varchar(30), ""),
165           SPID, DBID, ProcedureID, BatchID, CpuTime = convert(float, ""), WaitTime = convert(float, ""),
166           PhysicalReads = convert(float, ""), LogicalReads = convert(float, ""),
167           PacketsSent = convert(float, ""),
168           StartTime = convert(datetime, ""),
169           EndTime = convert(datetime, ""),
170           ElapsedTime = convert(int, "")
171       into #procstats
172       from master..monSysStatement
173       where 1 = 2
174   
175   
176       /*
177       ** Populate table with one row per stored procedure.
178       ** These  queries aggregates data for all of the rows in the 
179       ** monSysStatement table for a single instance of a stored procedure
180       ** execution into a single row.  This yields a summary of the 
181       ** statistics for the execution instance.
182       */
183       if (@dbname is NULL)
184       begin
185           insert #procstats
186           select ProcName = isnull(object_name(ProcedureID, DBID), "UNKNOWN"),
187               DBName = isnull(db_name(DBID), "UNKNOWN"),
188               SPID,
189               DBID,
190               ProcedureID,
191               BatchID,
192               CpuTime = sum(1.0 * CpuTime),
193               WaitTime = sum(1.0 * WaitTime),
194               PhysicalReads = sum(1.0 * PhysicalReads),
195               LogicalReads = sum(1.0 * LogicalReads),
196               PacketsSent = sum(1.0 * PacketsSent),
197               StartTime = min(StartTime),
198               EndTime = max(EndTime),
199               ElapsedTime = datediff(ms, min(StartTime), max(EndTime))
200           from master..monSysStatement
201           group by SPID, DBID, ProcedureID, BatchID
202           having ProcedureID != 0
203               and not (DBID = @sybprocs_dbid and ProcedureID = @myobjid)
204       end
205       else
206       begin
207           insert #procstats
208           select ProcName = isnull(object_name(ProcedureID, DBID),
209                   "UNKNOWN"),
210               DBName = isnull(db_name(DBID), "UNKNOWN"),
211               SPID,
212               DBID,
213               ProcedureID,
214               BatchID,
215               CpuTime = sum(1.0 * CpuTime),
216               WaitTime = sum(1.0 * WaitTime),
217               PhysicalReads = sum(1.0 * PhysicalReads),
218               LogicalReads = sum(1.0 * LogicalReads),
219               PacketsSent = sum(1.0 * PacketsSent),
220               StartTime = min(StartTime),
221               EndTime = max(EndTime),
222               ElapsedTime = datediff(ms, min(StartTime), max(EndTime))
223           from master..monSysStatement
224           group by SPID, DBID, ProcedureID, BatchID
225           having object_name(ProcedureID, DBID) like @procname
226               and @dbname = db_name(DBID)
227   
228       end
229   
230   
231       /*
232       ** Display details of each execution of a stored procedure
233       */
234       if (@detail = 1)
235       begin
236           print "Detailed Procedure Statistics"
237           print "============================="
238           print ""
239           select ProcName,
240               DBName,
241               SPID,
242               ElapsedTime =
243               convert(varchar(2), datediff(hh, StartTime,
244                   EndTime) % 24)
245               + ':'
246               + convert(varchar(2), datediff(mi, StartTime,
247                   EndTime) % 60)
248               + ':'
249               + convert(char(5),
250               convert(numeric(4, 2),
251               convert(float,
252               (datediff(ms, StartTime,
253                   EndTime) % 60000)) / 1000)),
254               CpuTime = convert(int, CpuTime),
255               WaitTime = convert(int, WaitTime),
256               PhysicalReads = convert(int, PhysicalReads),
257               LogicalReads = convert(int, LogicalReads),
258               PacketsSent = convert(int, PacketsSent),
259               StartTime = convert(char(8), StartTime, 108),
260               EndTime = convert(char(8), EndTime, 108)
261           into #procstats_detail
262           from #procstats
263   
264           exec sp_autoformat @fulltabname = #procstats_detail
265               , @orderby = "order by ElapsedTime desc"
266       end
267   
268       /*
269       ** Display average statistics for each stored procedure
270       */
271       if (@averages = 1)
272       begin
273           print ""
274           print "Average Procedure Statistics"
275           print "============================"
276           print ""
277           select ProcName,
278               DBName,
279               AvgElapsedTime = avg(ElapsedTime),
280               AvgCPUTime = avg(CpuTime),
281               AvgWaitTime = avg(WaitTime),
282               AvgPhysicalReads = avg(PhysicalReads),
283               AvgLogicalReads = avg(LogicalReads),
284               AvgPacketsSent = avg(PacketsSent),
285               NumExecs = count(ProcName)
286           into #procstats_avg
287           from #procstats
288           group by ProcName, DBName
289   
290           exec sp_autoformat @fulltabname = #procstats_avg
291               , @orderby = "order by AvgElapsedTime desc"
292       end
293   
294       return (0)
295   
296   error:
297       return (1) -- }
298   

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 163
 MEST 4 Empty String will be replaced by Single Space 164
 MEST 4 Empty String will be replaced by Single Space 165
 MEST 4 Empty String will be replaced by Single Space 166
 MEST 4 Empty String will be replaced by Single Space 167
 MEST 4 Empty String will be replaced by Single Space 168
 MEST 4 Empty String will be replaced by Single Space 169
 MEST 4 Empty String will be replaced by Single Space 170
 MEST 4 Empty String will be replaced by Single Space 238
 MEST 4 Empty String will be replaced by Single Space 273
 MEST 4 Empty String will be replaced by Single Space 276
 MTYP 4 Assignment type mismatch @montype: varchar(10) = varchar(30) 103
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 264
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 290
 TNOI 4 Table with no index master..monSysStatement master..monSysStatement
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 94
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MLCH 3 Char type with length>30 char(255) 80
 MLCH 3 Char type with length>30 char(255) 83
 MNER 3 No Error Check should check return value of exec 101
 MNER 3 No Error Check should check @@error after select into 163
 MNER 3 No Error Check should check @@error after insert 185
 MNER 3 No Error Check should check @@error after insert 207
 MNER 3 No Error Check should check @@error after select into 239
 MNER 3 No Error Check should check return value of exec 264
 MNER 3 No Error Check should check @@error after select into 277
 MNER 3 No Error Check should check return value of exec 290
 MUCO 3 Useless Code Useless Brackets 105
 MUCO 3 Useless Code Useless Brackets 108
 MUCO 3 Useless Code Useless Brackets 117
 MUCO 3 Useless Code Useless Brackets 123
 MUCO 3 Useless Code Useless Brackets 129
 MUCO 3 Useless Code Useless Brackets 135
 MUCO 3 Useless Code Useless Brackets 141
 MUCO 3 Useless Code Useless Brackets 146
 MUCO 3 Useless Code Useless Brackets 183
 MUCO 3 Useless Code Useless Brackets 234
 MUCO 3 Useless Code Useless Brackets 252
 MUCO 3 Useless Code Useless Brackets 271
 MUCO 3 Useless Code Useless Brackets 294
 MUCO 3 Useless Code Useless Brackets 297
 QAFM 3 Var Assignment from potentially many rows 94
 QAPT 3 Access to Proxy Table master..monSysStatement 172
 QAPT 3 Access to Proxy Table master..monSysStatement 200
 QAPT 3 Access to Proxy Table master..monSysStatement 223
 QCTC 3 Conditional Table Creation 239
 QCTC 3 Conditional Table Creation 277
 QPNC 3 No column in condition 173
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
96
 VNRD 3 Variable is not read @whoami 92
 VUNU 3 Variable is not used @output 76
 VUNU 3 Variable is not used @dbid 77
 VUNU 3 Variable is not used @procedureID 78
 VUNU 3 Variable is not used @query_text 79
 VUNU 3 Variable is not used @rptline 80
 VUNU 3 Variable is not used @sqlmsg 83
 MTR1 2 Metrics: Comments Ratio Comments: 41% 69
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 23 = 22dec - 1exi + 2 69
 MTR3 2 Metrics: Query Complexity Complexity: 105 69

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_monitor_verify_cfgval  
   calls proc sybsystemprocs..sp_monitor_getcfgval  
      reads table master..sysconfigures (1)  
      reads table master..syscurconfigs (1)  
reads table master..monSysStatement (1)  
read_writes table tempdb..#procstats (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  
writes table tempdb..#procstats_avg (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_005 
   writes table sybsystemprocs..sp_autoformat_rset_004 
   calls proc sybsystemprocs..sp_autoformat  
   read_writes table tempdb..#colinfo_af (1) 
   reads table tempdb..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_001 
   writes table sybsystemprocs..sp_autoformat_rset_003 
   calls proc sybsystemprocs..sp_namecrack  
   reads table master..systypes (1)  
reads table sybsystemprocs..sysobjects  
writes table tempdb..#procstats_detail (1)