DatabaseProcApplicationCreatedLinks
sybsystemprocssp_monitor_procedure  31 Aug 14Defects 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 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_check_permission @whoami
102       if (@rtnstatus != 0)
103           goto error
104   
105       exec @rtnstatus = sp_monitor_verify_setup @cursprocname
106   
107       exec @mdacfg_retval = sp_monitor_verify_cfgval @cursprocname
108   
109       if ((@rtnstatus != 0) or (@mdacfg_retval != 0))
110           goto error
111   
112       if (@procname is NULL)
113       begin
114           select @procname = "%"
115       end
116   
117       /*
118       ** Process the input parameters.  Determine whether the
119       ** all or average option was specified.
120       */
121       if (@dbname = 'detail' and @procname = "%")
122       begin
123           select @dbname = NULL
124           select @averages = 0
125           select @detail = 1
126       end
127       else if (@dbname = 'summary' and @procname = "%")
128       begin
129           select @dbname = NULL
130           select @averages = 1
131           select @detail = 0
132       end
133       else if (@dbname is not NULL and @procname = 'detail')
134       begin
135           select @procname = "%"
136           select @averages = 1
137           select @detail = 1
138       end
139       else if (@dbname is not NULL and @procname = 'summary')
140       begin
141           select @procname = "%"
142           select @averages = 1
143           select @detail = 0
144       end
145       else if (@dbname is not NULL and @procname != "%" and @option = 'detail')
146       begin
147           select @averages = 1
148           select @detail = 1
149       end
150       else if (@dbname is not NULL and @procname != "%" and @option = 'summary')
151       begin
152           select @averages = 1
153           select @detail = 0
154       end
155       else
156       begin
157           select @averages = 1
158           select @detail = 0
159       end
160   
161       /*
162       ** Create temporary table here so either one of the insert
163       ** statements below can populate it.  The ASE parser will not
164       ** allow two select-into statements to the same table in the
165       ** same procedure.
166       */
167       select ProcName = convert(varchar(30), ""),
168           DBName = convert(varchar(30), ""),
169           SPID, DBID, ProcedureID, BatchID, CpuTime = convert(float, ""), WaitTime = convert(float, ""),
170           PhysicalReads = convert(float, ""), LogicalReads = convert(float, ""),
171           PacketsSent = convert(float, ""),
172           StartTime = convert(datetime, ""),
173           EndTime = convert(datetime, ""),
174           ElapsedTime = convert(int, "")
175       into #procstats
176       from master..monSysStatement
177       where 1 = 2
178   
179   
180       /*
181       ** Populate table with one row per stored procedure.
182       ** These  queries aggregates data for all of the rows in the 
183       ** monSysStatement table for a single instance of a stored procedure
184       ** execution into a single row.  This yields a summary of the 
185       ** statistics for the execution instance.
186       */
187       if (@dbname is NULL)
188       begin
189           insert #procstats
190           select ProcName = isnull(object_name(ProcedureID, DBID), "UNKNOWN"),
191               DBName = isnull(db_name(DBID), "UNKNOWN"),
192               SPID,
193               DBID,
194               ProcedureID,
195               BatchID,
196               CpuTime = sum(1.0 * CpuTime),
197               WaitTime = sum(1.0 * WaitTime),
198               PhysicalReads = sum(1.0 * PhysicalReads),
199               LogicalReads = sum(1.0 * LogicalReads),
200               PacketsSent = sum(1.0 * PacketsSent),
201               StartTime = min(StartTime),
202               EndTime = max(EndTime),
203               ElapsedTime = datediff(ms, min(StartTime), max(EndTime))
204           from master..monSysStatement
205           group by SPID, DBID, ProcedureID, BatchID
206           having ProcedureID != 0
207               and not (DBID = @sybprocs_dbid and ProcedureID = @myobjid)
208       end
209       else
210       begin
211           insert #procstats
212           select ProcName = isnull(object_name(ProcedureID, DBID),
213                   "UNKNOWN"),
214               DBName = isnull(db_name(DBID), "UNKNOWN"),
215               SPID,
216               DBID,
217               ProcedureID,
218               BatchID,
219               CpuTime = sum(1.0 * CpuTime),
220               WaitTime = sum(1.0 * WaitTime),
221               PhysicalReads = sum(1.0 * PhysicalReads),
222               LogicalReads = sum(1.0 * LogicalReads),
223               PacketsSent = sum(1.0 * PacketsSent),
224               StartTime = min(StartTime),
225               EndTime = max(EndTime),
226               ElapsedTime = datediff(ms, min(StartTime), max(EndTime))
227           from master..monSysStatement
228           group by SPID, DBID, ProcedureID, BatchID
229           having object_name(ProcedureID, DBID) like @procname
230               and @dbname = db_name(DBID)
231   
232       end
233   
234   
235       /*
236       ** Display details of each execution of a stored procedure
237       */
238       if (@detail = 1)
239       begin
240           print "Detailed Procedure Statistics"
241           print "============================="
242           print ""
243           select ProcName,
244               DBName,
245               SPID,
246               ElapsedTime =
247               convert(varchar(2), datediff(hh, StartTime,
248                   EndTime) % 24)
249               + ':'
250               + convert(varchar(2), datediff(mi, StartTime,
251                   EndTime) % 60)
252               + ':'
253               + convert(char(5),
254               convert(numeric(4, 2),
255               convert(float,
256               (datediff(ms, StartTime,
257                   EndTime) % 60000)) / 1000)),
258               CpuTime = convert(int, CpuTime),
259               WaitTime = convert(int, WaitTime),
260               PhysicalReads = convert(int, PhysicalReads),
261               LogicalReads = convert(int, LogicalReads),
262               PacketsSent = convert(int, PacketsSent),
263               StartTime = convert(char(8), StartTime, 108),
264               EndTime = convert(char(8), EndTime, 108)
265           into #procstats_detail
266           from #procstats
267   
268           exec sp_autoformat @fulltabname = #procstats_detail
269               , @orderby = "order by ElapsedTime desc"
270       end
271   
272       /*
273       ** Display average statistics for each stored procedure
274       */
275       if (@averages = 1)
276       begin
277           print ""
278           print "Average Procedure Statistics"
279           print "============================"
280           print ""
281           select ProcName,
282               DBName,
283               AvgElapsedTime = avg(ElapsedTime),
284               AvgCPUTime = avg(CpuTime),
285               AvgWaitTime = avg(WaitTime),
286               AvgPhysicalReads = avg(PhysicalReads),
287               AvgLogicalReads = avg(LogicalReads),
288               AvgPacketsSent = avg(PacketsSent),
289               NumExecs = count(ProcName)
290           into #procstats_avg
291           from #procstats
292           group by ProcName, DBName
293   
294           exec sp_autoformat @fulltabname = #procstats_avg
295               , @orderby = "order by AvgElapsedTime desc"
296       end
297   
298       return (0)
299   
300   error:
301       return (1) -- }
302   


exec sp_procxmode 'sp_monitor_procedure', 'AnyMode'
go

Grant Execute on sp_monitor_procedure to public
go
DEFECTS
 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 171
 MEST 4 Empty String will be replaced by Single Space 172
 MEST 4 Empty String will be replaced by Single Space 173
 MEST 4 Empty String will be replaced by Single Space 174
 MEST 4 Empty String will be replaced by Single Space 242
 MEST 4 Empty String will be replaced by Single Space 277
 MEST 4 Empty String will be replaced by Single Space 280
 MTYP 4 Assignment type mismatch @montype: varchar(10) = varchar(30) 107
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 268
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 294
 TNOI 4 Table with no index master..monSysStatement master..monSysStatement
 MGTP 3 Grant to public sybsystemprocs..sp_monitor_procedure  
 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 105
 MNER 3 No Error Check should check @@error after select into 167
 MNER 3 No Error Check should check @@error after insert 189
 MNER 3 No Error Check should check @@error after insert 211
 MNER 3 No Error Check should check @@error after select into 243
 MNER 3 No Error Check should check return value of exec 268
 MNER 3 No Error Check should check @@error after select into 281
 MNER 3 No Error Check should check return value of exec 294
 MUCO 3 Useless Code Useless Brackets 102
 MUCO 3 Useless Code Useless Brackets 109
 MUCO 3 Useless Code Useless Brackets 112
 MUCO 3 Useless Code Useless Brackets 121
 MUCO 3 Useless Code Useless Brackets 127
 MUCO 3 Useless Code Useless Brackets 133
 MUCO 3 Useless Code Useless Brackets 139
 MUCO 3 Useless Code Useless Brackets 145
 MUCO 3 Useless Code Useless Brackets 150
 MUCO 3 Useless Code Useless Brackets 187
 MUCO 3 Useless Code Useless Brackets 238
 MUCO 3 Useless Code Useless Brackets 256
 MUCO 3 Useless Code Useless Brackets 275
 MUCO 3 Useless Code Useless Brackets 298
 MUCO 3 Useless Code Useless Brackets 301
 QAFM 3 Var Assignment from potentially many rows 94
 QAPT 3 Access to Proxy Table master..monSysStatement 176
 QAPT 3 Access to Proxy Table master..monSysStatement 204
 QAPT 3 Access to Proxy Table master..monSysStatement 227
 QCTC 3 Conditional Table Creation 243
 QCTC 3 Conditional Table Creation 281
 QPNC 3 No column in condition 177
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
96
 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: 109 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)  
calls proc sybsystemprocs..sp_monitor_check_permission  
writes table tempdb..#procstats_detail (1) 
writes table tempdb..#procstats_avg (1) 
read_writes table tempdb..#procstats (1) 
calls proc sybsystemprocs..sp_monitor_verify_setup  
   calls proc sybsystemprocs..sp_monitor_getcfgval  
   calls proc sybsystemprocs..sp_monitor_getcfgnum  
      reads table master..sysconfigures (1)  
   reads table master..sysobjects (1)  
reads table master..monSysStatement (1)  
reads table sybsystemprocs..sysobjects  
calls proc sybsystemprocs..sp_autoformat  
   read_writes table tempdb..#colinfo_af (1) 
   calls proc sybsystemprocs..sp_namecrack  
   calls proc sybsystemprocs..sp_autoformat  
   reads table master..systypes (1)  
   reads table tempdb..syscolumns (1)  
   reads table master..syscolumns (1)  
   reads table tempdb..systypes (1)