| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_monitor_procedure | | 31 Aug 14 | Defects 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", [[, 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[, ]]] 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 */
exec sp_procxmode 'sp_monitor_procedure', 'AnyMode' go Grant Execute on sp_monitor_procedure to public go
| 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) |
