DatabaseProcApplicationCreatedLinks
sybsystemprocssp_monitor_event  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 "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 or replace 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_verify_setup @procname
37        if (@rtnstatus != 0)
38            goto error
39    
40        select @procname = "event"
41    
42        exec @rtnstatus = sp_monitor_verify_cfgval @procname
43        if (@rtnstatus != 0)
44            goto error
45    
46        /* Freeze the MDA counters */
47        select @rtnstatus = mdaconfig('freeze', 'begin')
48    
49        /*
50        ** If the spid is 0 which is the default, display the wait event
51        ** information for all the user tasks in the descending order of wait times
52        ** else display it only for the passed in spid.
53        */
54        if (@spid < 0)
55        begin
56            -- Display information about both user and system tasks
57            select mpw.SPID, mpw.WaitTime, mwe.Description
58            into #evop0
59            from master..monProcessWaits mpw, master..monWaitEventInfo mwe
60            where mpw.WaitEventID = mwe.WaitEventID
61                AND mpw.WaitTime != 0
62    
63            select @tmptab = "#evop0"
64        end
65        else
66        if (@spid = 0)
67        begin
68            -- Display information only about all the user tasks
69            select mpw.SPID, mpw.WaitTime, mwe.Description
70            into #evop1
71            from master..monProcessWaits mpw,
72                master..monWaitEventInfo mwe,
73                master..monProcessLookup mpl
74            where mpw.WaitEventID = mwe.WaitEventID
75                AND mpw.WaitTime != 0
76                AND mpl.SPID = mpw.SPID
77    
78            select @tmptab = "#evop1"
79        end
80        else
81        if (@spid > 0)
82        begin
83            -- Display information for a particular SPID
84            select mpw.WaitTime, mwe.Description
85            into #evop2
86            from master..monProcessWaits mpw, master..monWaitEventInfo mwe
87            where mpw.SPID = @spid AND
88                mpw.WaitEventID = mwe.WaitEventID AND
89                mpw.WaitTime != 0
90    
91            select @tmptab = "#evop2"
92        end
93    
94        /* Unfreeze the MDA counters */
95        select @rtnstatus = mdaconfig('freeze', 'end')
96    
97        exec sp_autoformat @fulltabname = @tmptab
98            , @orderby = "order by WaitTime desc"
99        return (0)
100   
101   error:
102       return (1)
103   
104   

DEFECTS
 QJWI 5 Join or Sarg Without Index 60
 QJWI 5 Join or Sarg Without Index 74
 QJWI 5 Join or Sarg Without Index 76
 QJWI 5 Join or Sarg Without Index 88
 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
 MNER 3 No Error Check should check @@error after select into 57
 MNER 3 No Error Check should check @@error after select into 69
 MNER 3 No Error Check should check @@error after select into 84
 MNER 3 No Error Check should check return value of exec 97
 MUCO 3 Useless Code Useless Brackets in create proc 24
 MUCO 3 Useless Code Useless Brackets 37
 MUCO 3 Useless Code Useless Brackets 43
 MUCO 3 Useless Code Useless Brackets 54
 MUCO 3 Useless Code Useless Brackets 66
 MUCO 3 Useless Code Useless Brackets 81
 MUCO 3 Useless Code Useless Brackets 99
 MUCO 3 Useless Code Useless Brackets 102
 QAPT 3 Access to Proxy Table master..monProcessWaits 59
 QAPT 3 Access to Proxy Table master..monWaitEventInfo 59
 QAPT 3 Access to Proxy Table master..monProcessWaits 71
 QAPT 3 Access to Proxy Table master..monWaitEventInfo 72
 QAPT 3 Access to Proxy Table master..monProcessLookup 73
 QAPT 3 Access to Proxy Table master..monProcessWaits 86
 QAPT 3 Access to Proxy Table master..monWaitEventInfo 86
 QCTC 3 Conditional Table Creation 57
 QCTC 3 Conditional Table Creation 69
 QCTC 3 Conditional Table Creation 84
 QNAJ 3 Not using ANSI Inner Join 59
 QNAJ 3 Not using ANSI Inner Join 71
 QNAJ 3 Not using ANSI Inner Join 86
 QTJ1 3 Table only appears in inner join clause 73
 VNRD 3 Variable is not read @whoami 34
 VNRD 3 Variable is not read @rtnstatus 95
 MTR1 2 Metrics: Comments Ratio Comments: 42% 24
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 12 = 11dec - 1exi + 2 24
 MTR3 2 Metrics: Query Complexity Complexity: 50 24
 PRED_QUERY_COLLECTION 2 {mpw=master..monProcessWaits, mwei=master..monWaitEventInfo} 0 57
 PRED_QUERY_COLLECTION 2 {mpl=master..monProcessLookup, mpw=master..monProcessWaits, mwei=master..monWaitEventInfo} 0 69
 PRED_QUERY_COLLECTION 2 {mpw=master..monProcessWaits, mwei=master..monWaitEventInfo} 0 84

DEPENDENCIES
PROCS AND TABLES USED
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  
      reads table master..sysconfigures (1)  
      reads table master..syscurconfigs (1)  
reads table master..monProcessLookup (1)  
reads table master..monWaitEventInfo (1)  
writes table tempdb..#evop1 (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_001 
   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_003 
   calls proc sybsystemprocs..sp_namecrack  
   writes table sybsystemprocs..sp_autoformat_rset_005 
   reads table master..systypes (1)  
writes table tempdb..#evop2 (1) 
reads table master..monProcessWaits (1)  
writes table tempdb..#evop0 (1) 
calls proc sybsystemprocs..sp_monitor_verify_cfgval  
   calls proc sybsystemprocs..sp_monitor_getcfgval