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