DatabaseProcApplicationCreatedLinks
sybsystemprocssp_monitor_statement  31 Aug 14Defects Dependencies

1     
2     create procedure sp_monitor_statement @OrderBy varchar(30) = NULL
3     as
4     
5         declare @upper_option varchar(100)
6         declare @option varchar(100)
7         declare @procname char(9)
8         declare @rtnstatus int
9             , @whoami varchar(30)
10    
11        select @procname = "statement"
12            , @whoami = object_name(@@procid, db_id('sybsystemprocs'))
13    
14        exec @rtnstatus = sp_monitor_check_permission @whoami
15        if (@rtnstatus != 0)
16            goto error
17    
18        exec @rtnstatus = sp_monitor_verify_setup @procname
19        if (@rtnstatus != 0)
20            goto error
21    
22        exec @rtnstatus = sp_monitor_verify_cfgval @procname
23        if (@rtnstatus != 0)
24            goto error
25    
26        /* Convert the parameters to upper case */
27        select @upper_option = upper(@OrderBy)
28    
29        /* If the option is elapsed time, order by the sum of CPUTime and Wait Time */
30        if @upper_option = "ELAPSED TIME"
31        begin
32            select @option = "CpuTime+WaitTime"
33        end
34    
35        /* If the option is cpu, order by CPUTime */
36        if @upper_option = "CPU"
37        begin
38            select @option = "CpuTime"
39        end
40    
41        /* If the option is diskio, order by PhysicalReads */
42        if @upper_option = "DISKIO"
43        begin
44            select @option = "PhysicalReads"
45        end
46    
47        /* 
48        ** Freeze the MDA counters to get a consistent version across monitoring
49        ** tables
50        */
51        select @rtnstatus = mdaconfig('freeze', 'begin')
52    
53        /*
54        ** Put all the values in the monProcessSQLText, monProcessActivity and
55        ** monProcessStatement tables into respective temptable
56        ** The reason this is done is to split the processing phase from
57        ** the collection phase in order to minimize the chances of losing
58        ** data on account of monitoring and SQL Processing at the same time
59        **
60        ** The logic behind the SQL is as follows :
61        **
62        ** Display the SPID, and its corresponding SQL text. The SQL text has
63        ** to be batched together for the given spid. The default sorting
64        ** for the output is the descending order of the elapsed time which
65        ** is CPUTime + Wait time
66        **
67        */
68        /* Adaptive Server has expanded all '*' elements in the following statement */ select master..monProcessSQLText.SPID, master..monProcessSQLText.InstanceID, master..monProcessSQLText.KPID, master..monProcessSQLText.ServerUserID, master..monProcessSQLText.BatchID, master..monProcessSQLText.LineNumber, master..monProcessSQLText.SequenceInLine, master..monProcessSQLText.SQLText, DisplayStats = 0
69        into #tmp_P_SQLText
70        from master..monProcessSQLText
71    
72        update #tmp_P_SQLText
73        set a.DisplayStats = 1
74        from #tmp_P_SQLText a
75        where a.LineNumber = (select min(LineNumber) from #tmp_P_SQLText b
76                where b.SPID = a.SPID)
77            and a.SequenceInLine = 1
78    
79    
80        /* Adaptive Server has expanded all '*' elements in the following statement */ select master..monProcessStatement.SPID, master..monProcessStatement.InstanceID, master..monProcessStatement.KPID, master..monProcessStatement.DBID, master..monProcessStatement.ProcedureID, master..monProcessStatement.PlanID, master..monProcessStatement.BatchID, master..monProcessStatement.ContextID, master..monProcessStatement.LineNumber, master..monProcessStatement.CpuTime, master..monProcessStatement.WaitTime, master..monProcessStatement.MemUsageKB, master..monProcessStatement.PhysicalReads, master..monProcessStatement.LogicalReads, master..monProcessStatement.PagesModified, master..monProcessStatement.PacketsSent, master..monProcessStatement.PacketsReceived, master..monProcessStatement.NetworkPacketSize, master..monProcessStatement.PlansAltered, master..monProcessStatement.RowsAffected, master..monProcessStatement.DBName, master..monProcessStatement.StartTime
81        into #tmp_P_Statement
82        from master..monProcessStatement
83    
84        /* Unfreeze the MDA counters */
85        select @rtnstatus = mdaconfig('freeze', 'end')
86    
87        if @option = "CpuTime+WaitTime"
88        begin
89            /* Core SQL that produces the o/p order by the passed in option */
90            select case when
91                        (st.DisplayStats = 1) then
92                    convert(char(5), ps.SPID) else '' end as spid,
93                case when (st.DisplayStats = 1)
94                    then
95                    convert(varchar(30), suser_name(sysp.suid))
96                    else '' end as LoginName,
97                case when (st.DisplayStats = 1)
98                    then
99                    convert(char(10), ps.CpuTime + ps.WaitTime)
100                   else '' end as 'ElapsedTime',
101               case when (st.DisplayStats = 1)
102                   then
103                   convert(varchar(10), ps.CpuTime)
104                   else '' end as CpuTime,
105               case when (st.DisplayStats = 1)
106                   then
107                   convert(varchar(10), ps.PhysicalReads)
108                   else '' end as 'PhysicalReads',
109               st.SQLText as 'SQLText'
110           into #stmt_elapse
111           from #tmp_P_Statement ps,
112               #tmp_P_SQLText st, master..sysprocesses sysp
113           where ps.SPID = st.SPID AND ps.SPID != @@spid AND
114               sysp.spid = ps.SPID
115               AND (ps.CpuTime + ps.WaitTime) != 0
116           order by (ps.CpuTime + ps.WaitTime) desc, ps.SPID,
117               st.LineNumber asc, st.SequenceInLine
118   
119           exec sp_autoformat @fulltabname = "#stmt_elapse"
120       end
121       else if (@option = "CpuTime")
122       begin
123           /* Core SQL that produces the o/p order by the passed in option */
124           select case when
125                       (st.DisplayStats = 1) then
126                   convert(varchar(10), ps.SPID) else '' end as spid,
127               case when (st.DisplayStats = 1)
128                   then
129                   convert(varchar(30), suser_name(sysp.suid))
130                   else '' end as LoginName,
131               case when (st.DisplayStats = 1)
132                   then
133                   convert(char(10), ps.CpuTime + ps.WaitTime)
134                   else '' end as 'ElapsedTime',
135               case when (st.DisplayStats = 1)
136                   then
137                   convert(varchar(10), ps.CpuTime)
138                   else '' end as CpuTime,
139               case when (st.DisplayStats = 1)
140                   then
141                   convert(varchar(10), ps.PhysicalReads)
142                   else '' end as 'PhysicalReads',
143               st.SQLText as 'SQLText'
144           into #stmt_cpu
145           from #tmp_P_Statement ps,
146               #tmp_P_SQLText st, master.dbo.sysprocesses sysp
147           where ps.SPID = st.SPID AND ps.SPID != @@spid AND
148               sysp.spid = ps.SPID
149   
150               AND ps.CpuTime != 0
151           order by ps.CpuTime desc, ps.SPID,
152               st.LineNumber asc, st.SequenceInLine
153   
154           exec sp_autoformat @fulltabname = "#stmt_cpu"
155   
156       end
157       else
158       begin
159           /* Core SQL that produces the o/p order by the passed in option */
160           select case when
161                       (st.DisplayStats = 1) then
162                   convert(varchar(10), ps.SPID) else '' end as spid,
163               case when (st.DisplayStats = 1)
164                   then
165                   convert(varchar(30), suser_name(sysp.suid))
166                   else '' end as LoginName,
167               case when (st.DisplayStats = 1)
168                   then
169                   convert(char(10), ps.CpuTime + ps.WaitTime)
170                   else '' end as 'ElapsedTime',
171               case when (st.DisplayStats = 1)
172                   then
173                   convert(varchar(10), ps.CpuTime)
174                   else '' end as CpuTime,
175               case when (st.DisplayStats = 1)
176                   then
177                   convert(varchar(10), ps.PhysicalReads)
178                   else '' end as 'PhysicalReads',
179               st.SQLText as 'SQLText'
180           into #stmt_dsk
181           from #tmp_P_Statement ps,
182               #tmp_P_SQLText st, master.dbo.sysprocesses sysp
183           where ps.SPID = st.SPID AND ps.SPID != @@spid AND
184               sysp.spid = ps.SPID
185   
186               AND ps.PhysicalReads != 0
187           order by ps.PhysicalReads desc, ps.SPID,
188               st.LineNumber asc, st.SequenceInLine
189   
190           exec sp_autoformat @fulltabname = "#stmt_dsk"
191       end
192   
193       return (0)
194   
195   error:
196       return (1)
197   
198   


exec sp_procxmode 'sp_monitor_statement', 'AnyMode'
go

Grant Execute on sp_monitor_statement to public
go
DEFECTS
 MEST 4 Empty String will be replaced by Single Space 92
 MEST 4 Empty String will be replaced by Single Space 96
 MEST 4 Empty String will be replaced by Single Space 100
 MEST 4 Empty String will be replaced by Single Space 104
 MEST 4 Empty String will be replaced by Single Space 108
 MEST 4 Empty String will be replaced by Single Space 126
 MEST 4 Empty String will be replaced by Single Space 130
 MEST 4 Empty String will be replaced by Single Space 134
 MEST 4 Empty String will be replaced by Single Space 138
 MEST 4 Empty String will be replaced by Single Space 142
 MEST 4 Empty String will be replaced by Single Space 162
 MEST 4 Empty String will be replaced by Single Space 166
 MEST 4 Empty String will be replaced by Single Space 170
 MEST 4 Empty String will be replaced by Single Space 174
 MEST 4 Empty String will be replaced by Single Space 178
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 114
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 148
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 184
 TNOI 4 Table with no index master..monProcessSQLText master..monProcessSQLText
 TNOI 4 Table with no index master..monProcessStatement master..monProcessStatement
 TNOI 4 Table with no index master..sysprocesses master..sysprocesses
 MGTP 3 Grant to public master..sysprocesses  
 MGTP 3 Grant to public sybsystemprocs..sp_monitor_statement  
 MNER 3 No Error Check should check @@error after select into 68
 MNER 3 No Error Check should check @@error after update 72
 MNER 3 No Error Check should check @@error after select into 80
 MNER 3 No Error Check should check @@error after select into 90
 MNER 3 No Error Check should check return value of exec 119
 MNER 3 No Error Check should check @@error after select into 124
 MNER 3 No Error Check should check return value of exec 154
 MNER 3 No Error Check should check @@error after select into 160
 MNER 3 No Error Check should check return value of exec 190
 MUCO 3 Useless Code Useless Brackets 15
 MUCO 3 Useless Code Useless Brackets 19
 MUCO 3 Useless Code Useless Brackets 23
 MUCO 3 Useless Code Useless Brackets 91
 MUCO 3 Useless Code Useless Brackets 93
 MUCO 3 Useless Code Useless Brackets 97
 MUCO 3 Useless Code Useless Brackets 101
 MUCO 3 Useless Code Useless Brackets 105
 MUCO 3 Useless Code Useless Brackets 121
 MUCO 3 Useless Code Useless Brackets 125
 MUCO 3 Useless Code Useless Brackets 127
 MUCO 3 Useless Code Useless Brackets 131
 MUCO 3 Useless Code Useless Brackets 135
 MUCO 3 Useless Code Useless Brackets 139
 MUCO 3 Useless Code Useless Brackets 161
 MUCO 3 Useless Code Useless Brackets 163
 MUCO 3 Useless Code Useless Brackets 167
 MUCO 3 Useless Code Useless Brackets 171
 MUCO 3 Useless Code Useless Brackets 175
 MUCO 3 Useless Code Useless Brackets 193
 MUCO 3 Useless Code Useless Brackets 196
 QAPT 3 Access to Proxy Table master..monProcessSQLText 70
 QAPT 3 Access to Proxy Table master..monProcessStatement 82
 QCTC 3 Conditional Table Creation 90
 QCTC 3 Conditional Table Creation 124
 QCTC 3 Conditional Table Creation 160
 QJWT 3 Join or Sarg Without Index on temp table 76
 QJWT 3 Join or Sarg Without Index on temp table 113
 QJWT 3 Join or Sarg Without Index on temp table 114
 QJWT 3 Join or Sarg Without Index on temp table 147
 QJWT 3 Join or Sarg Without Index on temp table 148
 QJWT 3 Join or Sarg Without Index on temp table 183
 QJWT 3 Join or Sarg Without Index on temp table 184
 QNAJ 3 Not using ANSI Inner Join 111
 QNAJ 3 Not using ANSI Inner Join 145
 QNAJ 3 Not using ANSI Inner Join 181
 VNRD 3 Variable is not read @rtnstatus 85
 MSUC 2 Correlated Subquery Marker 75
 MTR1 2 Metrics: Comments Ratio Comments: 18% 2
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 18 = 17dec - 1exi + 2 2
 MTR3 2 Metrics: Query Complexity Complexity: 95 2

DEPENDENCIES
PROCS AND TABLES USED
reads table master..monProcessStatement (1)  
writes table tempdb..#stmt_dsk (1) 
calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   read_writes table tempdb..#colinfo_af (1) 
   reads table tempdb..syscolumns (1)  
   reads table master..systypes (1)  
   calls proc sybsystemprocs..sp_autoformat  
   calls proc sybsystemprocs..sp_namecrack  
   reads table tempdb..systypes (1)  
reads table master..monProcessSQLText (1)  
read_writes table tempdb..#tmp_P_SQLText (1) 
calls proc sybsystemprocs..sp_monitor_check_permission  
read_writes table tempdb..#tmp_P_Statement (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  
      reads table master..sysconfigures (1)  
      reads table master..syscurconfigs (1)  
calls proc sybsystemprocs..sp_monitor_verify_cfgval  
   calls proc sybsystemprocs..sp_monitor_getcfgval  
writes table tempdb..#stmt_elapse (1) 
writes table tempdb..#stmt_cpu (1) 
reads table master..sysprocesses (1)