DatabaseProcApplicationCreatedLinks
sybsystemprocssp_monitor_statement  14 déc. 14Defects Propagation Dependencies

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

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 88
 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 122
 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 158
 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
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 110
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 144
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 180
 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  
 MNER 3 No Error Check should check @@error after select into 64
 MNER 3 No Error Check should check @@error after update 68
 MNER 3 No Error Check should check @@error after select into 76
 MNER 3 No Error Check should check @@error after select into 86
 MNER 3 No Error Check should check return value of exec 115
 MNER 3 No Error Check should check @@error after select into 120
 MNER 3 No Error Check should check return value of exec 150
 MNER 3 No Error Check should check @@error after select into 156
 MNER 3 No Error Check should check return value of exec 186
 MUCO 3 Useless Code Useless Brackets 15
 MUCO 3 Useless Code Useless Brackets 19
 MUCO 3 Useless Code Useless Brackets 87
 MUCO 3 Useless Code Useless Brackets 89
 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 117
 MUCO 3 Useless Code Useless Brackets 121
 MUCO 3 Useless Code Useless Brackets 123
 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 157
 MUCO 3 Useless Code Useless Brackets 159
 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 189
 MUCO 3 Useless Code Useless Brackets 192
 QAPT 3 Access to Proxy Table master..monProcessSQLText 66
 QAPT 3 Access to Proxy Table master..monProcessStatement 78
 QCTC 3 Conditional Table Creation 86
 QCTC 3 Conditional Table Creation 120
 QCTC 3 Conditional Table Creation 156
 QJWT 3 Join or Sarg Without Index on temp table 72
 QJWT 3 Join or Sarg Without Index on temp table 109
 QJWT 3 Join or Sarg Without Index on temp table 110
 QJWT 3 Join or Sarg Without Index on temp table 143
 QJWT 3 Join or Sarg Without Index on temp table 144
 QJWT 3 Join or Sarg Without Index on temp table 179
 QJWT 3 Join or Sarg Without Index on temp table 180
 QNAJ 3 Not using ANSI Inner Join 107
 QNAJ 3 Not using ANSI Inner Join 141
 QNAJ 3 Not using ANSI Inner Join 177
 VNRD 3 Variable is not read @whoami 12
 VNRD 3 Variable is not read @rtnstatus 81
 MSUC 2 Correlated Subquery Marker 71
 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: 91 2

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