DatabaseProcApplicationCreatedLinks
sybsystemprocssp_sysmon_kernel  31 Aug 14Defects Dependencies

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


exec sp_procxmode 'sp_sysmon_kernel', 'AnyMode'
go

Grant Execute on sp_sysmon_kernel to public
go
DEFECTS
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 66
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 74
 TNOI 4 Table with no index master..syscurconfigs master..syscurconfigs
 MGTP 3 Grant to public master..syscurconfigs  
 MGTP 3 Grant to public sybsystemprocs..sp_sysmon_kernel  
 MLCH 3 Char type with length>30 char(67) 28
 MLCH 3 Char type with length>30 char(67) 29
 MLCH 3 Char type with length>30 char(80) 30
 MLCH 3 Char type with length>30 char(67) 32
 MLCH 3 Char type with length>30 char(80) 37
 MLCH 3 Char type with length>30 char(80) 39
 MLCH 3 Char type with length>30 char(80) 42
 MLCH 3 Char type with length>30 char(80) 43
 MUCO 3 Useless Code Useless Brackets 337
 MUCO 3 Useless Code Useless Brackets 339
 MUCO 3 Useless Code Useless Brackets 359
 MUCO 3 Useless Code Useless Brackets 361
 MUCO 3 Useless Code Useless Brackets 383
 MUCO 3 Useless Code Useless Brackets 387
 QAFM 3 Var Assignment from potentially many rows 66
 QAFM 3 Var Assignment from potentially many rows 74
 QAFM 3 Var Assignment from potentially many rows 100
 QAFM 3 Var Assignment from potentially many rows 162
 QAFM 3 Var Assignment from potentially many rows 208
 QAFM 3 Var Assignment from potentially many rows 223
 QJWT 3 Join or Sarg Without Index on temp table 104
 QJWT 3 Join or Sarg Without Index on temp table 105
 QNAJ 3 Not using ANSI Inner Join 102
 VNRD 3 Variable is not read @avg1line 50
 VNRD 3 Variable is not read @reco_hdr_prn 397
 VUNU 3 Variable is not used @instid 10
 MTR1 2 Metrics: Comments Ratio Comments: 21% 5
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 37 = 36dec - 1exi + 2 5
 MTR3 2 Metrics: Query Complexity Complexity: 215 5

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

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