Database	Proc	Application	Created	Links
sybsystemprocs	sp_monitor_event		31 Aug 14	Defects Dependencies

1     
2     /*
3     ** This stored procedure is a subordinate stored procedure that is invoked from
4     ** the parent stored procedure sp_monitor when "event" is passed as the
5     ** argument. 
6     **
7     ** This stored procedure provides wait event metrics for all tasks in the system
8     ** based on a snapshot collected when the sp_monitor "event" stored procedure is
9     ** executed.
10    **
11    ** If a specific  is passed in it provides wait event information for
12    ** that particular task.
13    **
14    ** Wait events help analyze specific resource bottlenecks in the system.
15    **
16    ** Parameters:
17    **      @spid		- spid for which the events needs to be displayed.
18    **
19    ** Returns:
20    **      0       - upon sucessful completion
21    **      1       - If an error resulted.
22    */
23    
24    create procedure sp_monitor_event(@spid int = 0)
25    as
26    
27        /* Parameters needed to support monitoring of sprocs, connections and stmts */
28        declare @rtnstatus int
29        declare @procname char(5)
30        declare @whoami varchar(30)
31            , @tmptab varchar(10)
32    
33        select @procname = "event"
34            , @whoami = object_name(@@procid, db_id('sybsystemprocs'))
35    
36        exec @rtnstatus = sp_monitor_check_permission @whoami
37        if (@rtnstatus != 0)
38            goto error
39    
40        exec @rtnstatus = sp_monitor_verify_setup @procname
41        if (@rtnstatus != 0)
42            goto error
43    
44        select @procname = "event"
45    
46        exec @rtnstatus = sp_monitor_verify_cfgval @procname
47        if (@rtnstatus != 0)
48            goto error
49    
50        /* Freeze the MDA counters */
51        select @rtnstatus = mdaconfig('freeze', 'begin')
52    
53        /*
54        ** If the spid is 0 which is the default, display the wait event
55        ** information for all the user tasks in the descending order of wait times
56        ** else display it only for the passed in spid.
57        */
58        if (@spid < 0)
59        begin
60            -- Display information about both user and system tasks
61            select mpw.SPID, mpw.WaitTime, mwe.Description
62            into #evop0
63            from master..monProcessWaits mpw, master..monWaitEventInfo mwe
64            where mpw.WaitEventID = mwe.WaitEventID
65                AND mpw.WaitTime != 0
66    
67            select @tmptab = "#evop0"
68        end
69        else
70        if (@spid = 0)
71        begin
72            -- Display information only about all the user tasks
73            select mpw.SPID, mpw.WaitTime, mwe.Description
74            into #evop1
75            from master..monProcessWaits mpw,
76                master..monWaitEventInfo mwe,
77                master..monProcessLookup mpl
78            where mpw.WaitEventID = mwe.WaitEventID
79                AND mpw.WaitTime != 0
80                AND mpl.SPID = mpw.SPID
81    
82            select @tmptab = "#evop1"
83        end
84        else
85        if (@spid > 0)
86        begin
87            -- Display information for a particular SPID
88            select mpw.WaitTime, mwe.Description
89            into #evop2
90            from master..monProcessWaits mpw, master..monWaitEventInfo mwe
91            where mpw.SPID = @spid AND
92                mpw.WaitEventID = mwe.WaitEventID AND
93                mpw.WaitTime != 0
94    
95            select @tmptab = "#evop2"
96        end
97    
98        /* Unfreeze the MDA counters */
99        select @rtnstatus = mdaconfig('freeze', 'end')
100   
101       exec sp_autoformat @fulltabname = @tmptab
102           , @orderby = "order by WaitTime desc"
103       return (0)
104   
105   error:
106       return (1)
107   
108



exec sp_procxmode 'sp_monitor_event', 'AnyMode'
go

Grant Execute on sp_monitor_event to public
go

DEFECTS
QJWI 5 Join or Sarg Without Index	64
QJWI 5 Join or Sarg Without Index	78
QJWI 5 Join or Sarg Without Index	80
QJWI 5 Join or Sarg Without Index	92
TNOI 4 Table with no index master..monProcessLookup	master..monProcessLookup
TNOI 4 Table with no index master..monProcessWaits	master..monProcessWaits
TNOI 4 Table with no index master..monWaitEventInfo	master..monWaitEventInfo
MGTP 3 Grant to public sybsystemprocs..sp_monitor_event
MNER 3 No Error Check should check @@error after select into	61
MNER 3 No Error Check should check @@error after select into	73
MNER 3 No Error Check should check @@error after select into	88
MNER 3 No Error Check should check return value of exec	101
MUCO 3 Useless Code Useless Brackets in create proc	24
MUCO 3 Useless Code Useless Brackets	37
MUCO 3 Useless Code Useless Brackets	41
MUCO 3 Useless Code Useless Brackets	47
MUCO 3 Useless Code Useless Brackets	58
MUCO 3 Useless Code Useless Brackets	70
MUCO 3 Useless Code Useless Brackets	85
MUCO 3 Useless Code Useless Brackets	103
MUCO 3 Useless Code Useless Brackets	106
QAPT 3 Access to Proxy Table master..monProcessWaits	63
QAPT 3 Access to Proxy Table master..monWaitEventInfo	63
QAPT 3 Access to Proxy Table master..monProcessWaits	75
QAPT 3 Access to Proxy Table master..monWaitEventInfo	76
QAPT 3 Access to Proxy Table master..monProcessLookup	77
QAPT 3 Access to Proxy Table master..monProcessWaits	90
QAPT 3 Access to Proxy Table master..monWaitEventInfo	90
QCTC 3 Conditional Table Creation	61
QCTC 3 Conditional Table Creation	73
QCTC 3 Conditional Table Creation	88
QNAJ 3 Not using ANSI Inner Join	63
QNAJ 3 Not using ANSI Inner Join	75
QNAJ 3 Not using ANSI Inner Join	90
QTJ1 3 Table only appears in inner join clause	77
VNRD 3 Variable is not read @rtnstatus	99
MTR1 2 Metrics: Comments Ratio Comments: 38%	24
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 12 = 11dec - 1exi + 2	24
MTR3 2 Metrics: Query Complexity Complexity: 54	24
PRED_QUERY_COLLECTION 2 {mpw=master..monProcessWaits, mwei=master..monWaitEventInfo} 0	61
PRED_QUERY_COLLECTION 2 {mpl=master..monProcessLookup, mpw=master..monProcessWaits, mwei=master..monWaitEventInfo} 0	73
PRED_QUERY_COLLECTION 2 {mpw=master..monProcessWaits, mwei=master..monWaitEventInfo} 0	88