DatabaseProcApplicationCreatedLinks
sybsystemprocssp_sysmon_kernel  14 déc. 14Defects Propagation Dependencies

1     
2     /* This stored procedure produces a report containing a summary of
3     ** SQL Server engine activity.
4     */
5     create or replace procedure sp_sysmon_kernel
6         @NumEngines tinyint, /* max number of engines online */
7         @NumElapsedMs int, /* for "per Elapsed second" calculations */
8         @NumXacts int, /* for per transactions calculations */
9         @Reco char(1), /* Flag for recommendations */
10        @instid smallint = NULL /* optional SDC instance id */
11    as
12    
13        /* --------- declare local variables --------- */
14        declare @NumActiveEngines tinyint /* number of engines active */
15        declare @i smallint /* loop index to iterate through  multi-group
16        ** counters (engine, disk, & buffer) */
17        declare @tmp_grp varchar(25) /* temp var to build group_names
18        ** ie. engine_N, disk_N */
19        declare @tmp_int int /* temp var for integer storage */
20        declare @tmp_total int /* temp var for summing 'total #s' data */
21        declare @cpu_busy real /* var for cpu busy percentage */
22        declare @io_busy real /* var for io busy percentage */
23        declare @cpu_busy_sum real /* var for summing cpu busy percentage */
24        declare @io_busy_sum real /* var for summing io busy percentage */
25        declare @cpu_busy_avg real /* var for averaging cpu busy percentage */
26        declare @io_busy_avg real /* var for averaging io busy percentage */
27        declare @rpsc int /* holds cfgvalue for RPSC */
28        declare @iopc int /* holds cfgvalue for I/O polling count */
29        declare @eng_load_line char(67) /* string to delimit the engine load lines */
30        declare @avg1line char(67) /* string to delimit avg lines on printout */
31        declare @sum1line char(80) /* string to delimit total lines without 
32        ** percent calc on printout */
33        declare @sum2line char(67) /* string to delimit total lines with percent 
34        ** calc on printout */
35        declare @blankline char(1) /* to print blank line */
36        declare @psign char(3) /* hold a percent sign (%) for print out */
37        declare @na_str char(3) /* holds 'n/a' for 'not applicable' strings */
38        declare @rptline char(80) /* formatted statistics line for print 
39        ** statement */
40        declare @section char(80) /* string to delimit sections on printout */
41    
42        /* ------------- Variables for Tuning Recommendations ------------*/
43        declare @recotxt char(80) /* Header for tuning recommendation */
44        declare @recoline char(80) /* to underline recotxt */
45        declare @reco_hdr_prn bit /* to indicate if the recotxt is already printed */
46        declare @reco_percent_diskio real /* percentage sucessful disk i/o */
47    
48        /* --------- Setup Environment --------- */
49        set nocount on /* disable row counts being sent to client */
50    
51        select @avg1line = "  -----------             ---------------          ----------------"
52        select @sum1line = "  -------------------------  ------------  ------------  ----------  ----------"
53        select @sum2line = "  -------------------------  ------------  ------------  ----------"
54        select @blankline = " "
55        select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */
56        select @na_str = "n/a"
57        select @section = "==============================================================================="
58    
59    
60        /* ======================= Kernel Utilization Section =================== */
61        print @section
62        print @blankline
63        print "Kernel Utilization"
64        print "------------------"
65        print @blankline
66    
67        select @rpsc = value from master..syscurconfigs where config = 177
68    
69    
70        select @rptline = "  Your Runnable Process Search Count is set to "
71            + convert(varchar, @rpsc)
72        print @rptline
73    
74    
75        select @iopc = value from master..syscurconfigs where config = 178
76    
77    
78        select @rptline = "  and I/O Polling Process Count is set to "
79            + convert(varchar, @iopc)
80        print @rptline
81        print @blankline
82    
83        select @i = 0, @cpu_busy_sum = 0, @io_busy_sum = 0, @cpu_busy_avg = 0,
84            @io_busy_avg = 0, @reco_percent_diskio = 0
85    
86        select @rptline = "  Engine Busy Utilization " + space(7)
87            + "CPU Busy" + space(3)
88            + "I/O Busy" + space(7)
89            + "Idle"
90        print @rptline
91        select @eng_load_line = "  ------------------------" + space(7)
92            + "--------" + space(3)
93            + "--------" + space(3)
94            + "--------"
95        print @eng_load_line
96    
97        select @NumActiveEngines = value
98        from #tempmonitors
99        where field_name = "active_online_engines" and
100           group_name = "resource_stats"
101   
102       while @i < @NumEngines /* for each engine */
103       begin
104           /* build group_name string */
105           select @tmp_grp = "engine_" + convert(varchar(4), @i)
106   
107           /* If an engine's clock_ticks counter is 0, this means this engine
108           ** is in offline status. We should skip this engine when priting
109           ** the statistic information.
110           */
111           if (select value
112                   from #tempmonitors where field_name = "clock_ticks"
113                       and group_name = @tmp_grp) > 0
114           begin
115               select @cpu_busy = isnull(100.0 * convert(real, c.value) / t.value, 0),
116                   @io_busy = isnull(100.0 * convert(real, i.value) / t.value, 0)
117               from #tempmonitors t, #tempmonitors c, #tempmonitors i
118               where t.group_name = @tmp_grp
119                   and t.group_name = c.group_name
120                   and t.group_name = i.group_name
121                   and t.field_name = "clock_ticks"
122                   and i.field_name = "io_ticks"
123                   and c.field_name = "cpu_ticks"
124                   and t.value > 0
125   
126               select @cpu_busy_sum = @cpu_busy_sum + @cpu_busy,
127                   @io_busy_sum = @io_busy_sum + @io_busy
128   
129               select @rptline = "    Engine " + convert(char(4), @i) + space(19)
130                   + str(@cpu_busy, 5, 1) + @psign + space(4)
131                   + str(@io_busy, 5, 1) + @psign + space(4)
132                   + str(100 - @cpu_busy - @io_busy, 5, 1) + @psign
133               print @rptline
134           end
135           select @i = @i + 1
136       end
137   
138       if @NumActiveEngines > 1
139       begin
140           print @eng_load_line
141           select @rptline = "  Summary " + space(10) + "Total" + space(7)
142               + str(@cpu_busy_sum, 7, 1) + @psign + space(2)
143               + str(@io_busy_sum, 7, 1) + @psign + space(2)
144               + str(@NumActiveEngines * 100 - @cpu_busy_sum
145                   - @io_busy_sum, 7, 1) + @psign,
146               @cpu_busy_avg = @cpu_busy_sum / @NumActiveEngines,
147               @io_busy_avg = @io_busy_sum / @NumActiveEngines
148           print @rptline
149           select @rptline = space(18) + "Average" + space(9)
150               + str(@cpu_busy_avg, 5, 1) + @psign + space(4)
151               + str(@io_busy_avg, 5, 1) + @psign + space(4)
152               + str(100 - @cpu_busy_avg - @io_busy_avg, 5, 1)
153               + @psign
154           print @rptline
155       end
156   
157       print @blankline
158       print "  CPU Yields by Engine            per sec      per xact       count  %% of total"
159       print @sum1line
160   
161       select @tmp_total = SUM(value)
162       from #tempmonitors
163       where group_name like "engine_%" and
164           field_name = "engine_sleeps"
165   
166       if @tmp_total = 0 /* Avoid divide by zero errors - just print zero's */
167       begin
168           select @rptline = "  Total CPU Yields                    0.0           0.0           0       n/a"
169           print @rptline
170       end
171       else
172       begin
173           select @i = 0
174           while @i < @NumEngines /* for each engine */
175           begin
176               /* build group_name string */
177               select @tmp_grp = "engine_" + convert(varchar(4), @i)
178   
179               /* If an engine's clock_ticks counter is 0, this means this engine
180               ** is in offline status. We should skip this engine when priting
181               ** the statistic information.
182               */
183               if (select value
184                       from #tempmonitors where field_name = "clock_ticks"
185                           and group_name = @tmp_grp) > 0
186               begin
187                   select @tmp_int = value
188                   from #tempmonitors
189                   where group_name = @tmp_grp and
190                       field_name = "engine_sleeps"
191   
192                   select @rptline = "    Engine " + convert(char(4), @i) +
193                       space(14) +
194                       str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1)
195                       + space(2) +
196                       str(@tmp_int / convert(real, @NumXacts), 12, 1)
197                       + space(2) +
198                       str(@tmp_int, 10) + space(5) +
199                       str(100.0 * @tmp_int / @tmp_total, 5, 1)
200                       + @psign
201                   print @rptline
202               end
203               select @i = @i + 1
204           end
205   
206           if @NumActiveEngines > 1
207           begin
208               print @sum2line
209               select @rptline = "  Total CPU Yields " + space(10) +
210                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1)
211                   + space(2) +
212                   str(@tmp_total / convert(real, @NumXacts), 12, 1)
213                   + space(2) +
214                   str(@tmp_total, 10)
215               print @rptline
216           end
217       end
218       print @blankline
219   
220       print "  Network Checks"
221   
222       select @tmp_total = SUM(value)
223       from #tempmonitors
224       where group_name = "kernel" and
225           field_name like "ncheck_%"
226   
227       if @tmp_total = 0 /* Avoid divide by zero errors - print zero's */
228       begin
229           select @rptline = "    Total Network I/O Checks          0.0           0.0           0       n/a"
230           print @rptline
231       end
232       else
233       begin
234           select @tmp_int = value
235           from #tempmonitors
236           where group_name = "kernel" and
237               field_name = "ncheck_nonblocking"
238   
239           select @rptline = "    Non-Blocking" + space(13) +
240               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
241               space(2) +
242               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
243               space(2) +
244               str(@tmp_int, 10) + space(5) +
245               str(100.0 * @tmp_int / @tmp_total, 5, 1)
246               + @psign
247           print @rptline
248   
249           select @tmp_int = value
250           from #tempmonitors
251           where group_name = "kernel" and
252               field_name = "ncheck_blocking"
253   
254           select @rptline = "    Blocking" + space(17) +
255               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
256               space(2) +
257               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
258               space(2) +
259               str(@tmp_int, 10) + space(5) +
260               str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
261           print @rptline
262   
263           print @sum2line
264           select @rptline = "  Total Network I/O Checks " + space(2) +
265               str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
266               space(2) +
267               str(@tmp_total / convert(real, @NumXacts), 12, 1) +
268               space(2) +
269               str(@tmp_total, 10)
270           print @rptline
271   
272           select @tmp_int = SUM(value)
273           from #tempmonitors
274           where group_name = "network" and
275               field_name like "total_packets_%"
276   
277           select @rptline = "  Avg Net I/Os per Check" + space(14) +
278               @na_str + space(11) +
279               @na_str + space(2) +
280               str(convert(real, @tmp_int) / @tmp_total, 10, 5) +
281               space(7) +
282               @na_str
283           print @rptline
284       end
285       print @blankline
286   
287       print "  Disk I/O Checks"
288   
289       select @tmp_total = SUM(value)
290       from #tempmonitors
291       where group_name like "engine_%" and
292           field_name = "dcheck_calls"
293   
294       if @tmp_total = 0 /* Avoid divide by zero errors - print zero's */
295       begin
296           select @rptline = "      Total Disk I/O Checks           0.0           0.0           0       n/a"
297           print @rptline
298       end
299       else
300       begin
301           select @rptline = "    Total Disk I/O Checks" + space(4) +
302               str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
303               space(2) +
304               str(@tmp_total / convert(real, @NumXacts), 12, 1) +
305               space(2) +
306               str(@tmp_total, 10) + space(7) +
307               @na_str
308           print @rptline
309   
310           select @tmp_int = SUM(value)
311           from #tempmonitors
312           where group_name like "engine_%" and
313               field_name = "dchecks_calling_dpoll"
314   
315           select @rptline = "    Checks Returning I/O" + space(5) +
316               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
317               space(2) +
318               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
319               space(2) +
320               str(@tmp_int, 10) + space(5) +
321               str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
322           if @tmp_total != 0
323           begin
324               select @reco_percent_diskio = 100.0 * (convert(real, @tmp_int) / @tmp_total)
325           end
326   
327           print @rptline
328   
329           /* save checks returning i/o value for average calc below */
330           select @tmp_total = @tmp_int
331   
332           if @tmp_total != 0
333           begin
334               select @tmp_int = SUM(value)
335               from #tempmonitors
336               where group_name like "engine_%" and
337                   field_name = "total_dpoll_completed_aios"
338   
339               select @rptline = "    Avg Disk I/Os Returned" + space(12) +
340                   @na_str + space(11) +
341                   @na_str + space(2) +
342                   str(convert(real, @tmp_int) /
343                       @tmp_total, 10, 5) +
344                   space(7) +
345                   @na_str
346               print @rptline
347           end
348       end
349   
350       print @blankline
351       if @Reco = 'Y'
352       begin
353           select @recotxt = "  Tuning Recommendations for Kernel Utilization"
354           select @recoline = "  ---------------------------------------------"
355           select @reco_hdr_prn = 0
356   
357           if @NumEngines > 1
358           begin
359               /*
360               ** If the average percentage busy on the engines
361               ** is > 95% consider increasing the number of engines
362               */
363               if (@cpu_busy_avg > 95)
364               begin
365                   if (@reco_hdr_prn = 0)
366                   begin
367                       print @recotxt
368                       print @recoline
369                       select @reco_hdr_prn = 1
370                   end
371   
372                   print "  - Consider bringing more engines online"
373                   print @blankline
374                   select @reco_hdr_prn = 1
375               end
376   
377               /*
378               ** If the average percentage busy on the engines is < 5% 
379               ** consider decreasing the 'runnable process search count' 
380               ** configuration parameter, if other applications are running
381               ** on the same machine. 
382               ** If runnable process search count is already set to 100 or less
383               ** don't print this recommendation.
384               */
385               if (@cpu_busy_avg < 5 and @rpsc > 100)
386               begin
387                   if (@reco_hdr_prn = 0)
388                   begin
389                       print @recotxt
390                       print @recoline
391                       select @reco_hdr_prn = 1
392                   end
393   
394                   print "  - Consider decreasing the 'runnable process search count'"
395                   print "    configuration parameter if you require the CPU's on"
396                   print "    the machine to be used for other applications."
397                   print @blankline
398                   select @reco_hdr_prn = 1
399               end
400               /*
401               ** If the average cpu busy percentage on the engines
402               ** is > 70% and the average busy on the engines
403               ** is < 90% and the percentage diskio is < 5% consider 
404               ** increasing the 'i/o polling process count' 
405               ** configuration parameter.
406               ** Don't print this recommendation if the config param 
407               ** is already set to 10000 or higher.
408               */
409               if (@cpu_busy_avg > 70 AND @cpu_busy_avg < 90
410                       AND @reco_percent_diskio < 5
411                       AND @iopc < 10000)
412               begin
413                   if (@reco_hdr_prn = 0)
414                   begin
415                       print @recotxt
416                       print @recoline
417                       select @reco_hdr_prn = 1
418                   end
419   
420                   print "  - Consider increasing the 'i/o polling process count'"
421                   print "    configuration parameter."
422                   print @blankline
423                   select @reco_hdr_prn = 1
424               end
425           end
426       end
427       print @blankline
428   
429       return 0
430   

DEFECTS
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 67
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 75
 TNOI 4 Table with no index master..syscurconfigs master..syscurconfigs
 MGTP 3 Grant to public master..syscurconfigs  
 MLCH 3 Char type with length>30 char(67) 29
 MLCH 3 Char type with length>30 char(67) 30
 MLCH 3 Char type with length>30 char(80) 31
 MLCH 3 Char type with length>30 char(67) 33
 MLCH 3 Char type with length>30 char(80) 38
 MLCH 3 Char type with length>30 char(80) 40
 MLCH 3 Char type with length>30 char(80) 43
 MLCH 3 Char type with length>30 char(80) 44
 MUCO 3 Useless Code Useless Brackets 363
 MUCO 3 Useless Code Useless Brackets 365
 MUCO 3 Useless Code Useless Brackets 385
 MUCO 3 Useless Code Useless Brackets 387
 MUCO 3 Useless Code Useless Brackets 409
 MUCO 3 Useless Code Useless Brackets 413
 QAFM 3 Var Assignment from potentially many rows 67
 QAFM 3 Var Assignment from potentially many rows 75
 QAFM 3 Var Assignment from potentially many rows 97
 QAFM 3 Var Assignment from potentially many rows 115
 QAFM 3 Var Assignment from potentially many rows 187
 QAFM 3 Var Assignment from potentially many rows 234
 QAFM 3 Var Assignment from potentially many rows 249
 QJWT 3 Join or Sarg Without Index on temp table 119
 QJWT 3 Join or Sarg Without Index on temp table 120
 QNAJ 3 Not using ANSI Inner Join 117
 VNRD 3 Variable is not read @avg1line 51
 VNRD 3 Variable is not read @reco_hdr_prn 423
 MSUB 2 Subquery Marker 111
 MSUB 2 Subquery Marker 183
 MTR1 2 Metrics: Comments Ratio Comments: 23% 5
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 43 = 42dec - 1exi + 2 5
 MTR3 2 Metrics: Query Complexity Complexity: 230 5

DEPENDENCIES
PROCS AND TABLES USED
reads table tempdb..#tempmonitors (1) 
reads table master..syscurconfigs (1)  

CALLERS
called by proc sybsystemprocs..sp_sysmon_analyze  
   called by proc sybsystemprocs..sp_sysmon