DatabaseProcApplicationCreatedLinks
sybsystemprocssp_sysmon_diskio  14 déc. 14Defects Propagation Dependencies

1     
2     /* This stored procedure produces a report containing a summary of
3     ** of disk activity.
4     */
5     create or replace procedure sp_sysmon_diskio
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    as
11    
12        /* --------- declare local variables --------- */
13        declare @SybDiskName varchar(265) /* cursor var for logical disk name - 
14        ** sysdevices.name */
15        declare @PhyDiskName varchar(127) /* cursor var for physical disk name - 
16        ** sysdevices.phyname */
17    
18        declare @i smallint /* loop index to iterate through multi-group
19        **  counters (engine, disk, & buffer) */
20        declare @tmp_grp varchar(25) /* temp var for build group_name's - ie., 
21        ** engine_N, disk_N */
22        declare @tmp_int int /* temp var for integer storage */
23        declare @tmp_int2 int /* temp var for integer storage */
24        declare @tmp_int3 int /* temp var for integer storage */
25        declare @tmp_int4 int /* temp var for integer storage */
26        declare @tmp_total int /* temp var for summing 'total #s' data */
27        declare @tmp_total_async int /* temp var for summing total #s of
28        ** asynchronous IOs completed */
29        declare @tmp_total_sync int /* temp var for summing total #s of synchronous
30        ** IOs completed */
31        declare @tmp_total_ios int /* temp var for summing total #s of IOs
32        ** completed.
33        ** @tmp_total_ios = @tmp_total_async + 
34        ** @tmp_total_sync
35        */
36        declare @sum1line char(80) /* string to delimit total lines without 
37        ** percent calc on printout */
38        declare @sum2line char(80) /* string to delimit total lines without 
39        ** percent calc on printout */
40        declare @subsection char(80) /* delimit disk sections */
41        declare @blankline char(1) /* to print blank line */
42        declare @psign char(3) /* hold a percent sign (%) for print out */
43        declare @na_str char(3) /* holds 'n/a' for 'not applicable' strings */
44        declare @rptline char(80) /* formatted stats line for print statement */
45        declare @section char(80) /* string to delimit sections on printout */
46    
47        /* ------------- Variables for Tuning Recommendations ------------*/
48        declare @recotxt char(80) /* Header for tuning recommendation */
49        declare @recoline char(80) /* to underline recotxt */
50        declare @reco_hdr_prn bit /* to indicate if the recotxt is already printed */
51        declare @reco_total_diskio int
52        declare @reco_diskio_struct_delay int
53        declare @reco_maxaio_server int
54        declare @reco_maxaio_engine int
55        declare @reco_aio_os_limit int
56    
57        /* --------- Setup Environment --------- */
58        set nocount on /* disable row counts being sent to client */
59    
60        select @sum1line = "  -------------------------  ------------  ------------  ----------  ----------"
61        select @sum2line = "  -------------------------  ------------  ------------  ----------"
62        select @subsection = "  -----------------------------------------------------------------------------"
63        select @blankline = " "
64        select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */
65        select @na_str = "n/a"
66        select @section = "==============================================================================="
67    
68        print @section
69        print @blankline
70        print "Disk I/O Management"
71        print "-------------------"
72        print @blankline
73        print "  Max Outstanding I/Os            per sec      per xact       count  %% of total"
74        print @sum1line
75    
76        select @tmp_int = value
77        from #tempmonitors
78        where group_name = "kernel" and
79            field_name = "max_outstanding_AIOs_server"
80    
81        select @rptline = "    Server" + space(28) +
82            @na_str + space(11) +
83            @na_str + space(2) +
84            str(@tmp_int, 10) + space(7) +
85            @na_str
86        print @rptline
87    
88        select @i = 0
89        while @i < @NumEngines /* for each engine */
90        begin
91            /* build group_name string */
92            select @tmp_grp = "engine_" + convert(varchar(4), @i)
93    
94            /* If an engine's clock_ticks counter is 0, this means this engine
95            ** is in offline status. We should skip this engine when priting
96            ** the statistic information.
97            */
98            if (select value
99                    from #tempmonitors where field_name = "clock_ticks"
100                       and group_name = @tmp_grp) > 0
101           begin
102               select @tmp_int = value
103               from #tempmonitors
104               where group_name = @tmp_grp and
105                   field_name = "max_outstanding_AIOs_engine"
106   
107               select @rptline = "    Engine " + convert(char(4), @i) + space(23) +
108                   @na_str + space(11) +
109                   @na_str + space(2) +
110                   str(@tmp_int, 10) + space(7) +
111                   @na_str
112               print @rptline
113           end
114   
115           select @i = @i + 1
116       end
117   
118       print @blankline
119       print @blankline
120       print "  I/Os Delayed by"
121   
122       select @tmp_int = value, @reco_diskio_struct_delay = value
123       from #tempmonitors
124       where group_name = "kernel" and
125           field_name = "udalloc_sleeps"
126   
127       select @rptline = "    Disk I/O Structures" + space(15) +
128           @na_str + space(11) +
129           @na_str + space(2) +
130           str(@tmp_int, 10) + space(7) +
131           @na_str
132       print @rptline
133   
134       select @tmp_int = SUM(value), @reco_maxaio_server = SUM(value)
135       from #tempmonitors
136       where group_name like "engine_%" and
137           field_name = "AIOs_delayed_due_to_server_limit"
138   
139       select @rptline = "    Server Config Limit" + space(15) +
140           @na_str + space(11) +
141           @na_str + space(2) +
142           str(@tmp_int, 10) + space(7) +
143           @na_str
144       print @rptline
145   
146       select @tmp_int = SUM(value), @reco_maxaio_engine = SUM(value)
147       from #tempmonitors
148       where group_name like "engine_%" and
149           field_name = "AIOs_delayed_due_to_engine_limit"
150   
151       select @rptline = "    Engine Config Limit" + space(15) +
152           @na_str + space(11) +
153           @na_str + space(2) +
154           str(@tmp_int, 10) + space(7) +
155           @na_str
156       print @rptline
157   
158       select @tmp_int = SUM(value), @reco_aio_os_limit = SUM(value)
159       from #tempmonitors
160       where group_name like "engine_%" and
161           field_name = "AIOs_delayed_due_to_os_limit"
162   
163       select @rptline = "    Operating System Limit" + space(12) +
164           @na_str + space(11) +
165           @na_str + space(2) +
166           str(@tmp_int, 10) + space(7) +
167           @na_str
168       print @rptline
169   
170       print @blankline
171       print @blankline
172   
173       select @tmp_int = value, @reco_total_diskio = value
174       from #tempmonitors
175       where group_name = "kernel" and
176           field_name = "udalloc_calls"
177   
178       select @rptline = "  Total Requested Disk I/Os" + space(2) +
179           str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
180           space(2) +
181           str(@tmp_int / convert(real, @NumXacts), 12, 1) +
182           space(2) +
183           str(@tmp_int, 10)
184       print @rptline
185       print @blankline
186       print "  Completed Disk I/O's"
187   
188       select @tmp_total_async = isnull(SUM(value), 0)
189       from #tempmonitors
190       where group_name like "engine_%" and
191           field_name = "total_dpoll_completed_aios"
192   
193       select @tmp_total_sync = value
194       from #tempmonitors
195       where group_name like "kernel" and
196           field_name = "total_sync_completed_ios"
197   
198       select @tmp_total_ios = @tmp_total_async + @tmp_total_sync
199   
200       print "    Asynchronous I/O's"
201       if @tmp_total_async = 0
202       begin
203           select @rptline = "      Total Completed I/Os            0.0           0.0           0       n/a"
204           print @rptline
205       end
206       else
207       begin
208           if @@kernelmode = "process"
209           begin
210               select @i = 0
211               while @i < @NumEngines /* for each engine */
212               begin
213                   /* build group_name string */
214                   select @tmp_grp = "engine_" + convert(varchar(4), @i)
215   
216                   /* If an engine's clock_ticks counter is 0, this means this engine
217                   ** is in offline status. We should skip this engine when priting
218                   ** the statistic information.
219                   */
220                   if (select value
221                           from #tempmonitors where field_name = "clock_ticks"
222                               and group_name = @tmp_grp) > 0
223                   begin
224                       select @tmp_int = value
225                       from #tempmonitors
226                       where group_name = @tmp_grp and
227                           field_name = "total_dpoll_completed_aios"
228   
229                       select @rptline = "      Engine " + convert(char(4), @i) +
230                           space(12) +
231                           str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
232                           space(2) +
233                           str(@tmp_int / convert(real, @NumXacts), 12, 1) +
234                           space(2) +
235                           str(@tmp_int, 10) + space(5) +
236                           str(100.0 * @tmp_int / @tmp_total_async, 5, 1) + @psign
237                       print @rptline
238                   end
239   
240                   select @i = @i + 1
241               end /* while loop */
242           end /* else */
243           else
244           begin /* threaded kernel */
245               select @rptline = "      Total Completed I/Os" +
246                   space(3) +
247                   str(@tmp_total_async / (@NumElapsedMs / 1000.0), 12, 1) +
248                   space(2) +
249                   str(@tmp_total_async / convert(real, @NumXacts), 12, 1) +
250                   space(2) +
251                   str(@tmp_total_async, 10) + space(5) +
252                   str(100.0 * @tmp_total_async / @tmp_total_ios, 5, 1) + @psign
253               print @rptline
254           end
255       end
256   
257       print "    Synchronous I/O's"
258       if @tmp_total_sync = 0
259       begin
260           select @rptline = "      Total Completed I/Os            0.0           0.0           0       n/a"
261           print @rptline
262       end
263       else
264       begin
265           select @rptline = "      Total Completed I/Os   " +
266               str(@tmp_total_sync / (@NumElapsedMs / 1000.0), 12, 1) +
267               space(2) +
268               str(@tmp_total_sync / convert(real, @NumXacts), 12, 1) +
269               space(2) +
270               str(@tmp_total_sync, 10) + space(5) +
271               str(100.0, 5, 1) + @psign
272           print @rptline
273       end /* else */
274   
275       print @sum2line
276   
277       select @rptline = "  Total Completed I/Os" + space(7) +
278           str(@tmp_total_ios / (@NumElapsedMs / 1000.0), 12, 1) +
279           space(2) +
280           str(@tmp_total_ios / convert(real, @NumXacts), 12, 1) +
281           space(2) +
282           str(@tmp_total_ios, 10)
283       print @rptline
284       print @blankline
285       print @blankline
286       print "  Device Activity Detail"
287       print "  ----------------------"
288       print @blankline
289   
290       /* get total number of I/Os to all devices to calc each device's percentage */
291       select @tmp_total = isnull(SUM(value), 0)
292       from #tempmonitors
293       where group_name like "disk_%" and
294           (field_name = "total_reads" or field_name = "total_writes")
295   
296       if @tmp_total = 0
297       begin
298           print "    No Disk I/O in Given Sample Period"
299           print @blankline
300       end
301       else
302       begin
303           declare disk_info cursor for
304           select name, phyname, group_name
305           from #devicemap
306           order by phyname
307           for read only
308   
309           open disk_info
310           fetch disk_info into @SybDiskName, @PhyDiskName, @tmp_grp
311   
312           while (@@sqlstatus = 0)
313           begin
314   
315               select @rptline = "  Device:"
316               print @rptline
317               select @rptline = space(4) + substring(@PhyDiskName, 1, 76)
318               print @rptline
319               select @rptline = space(4) + convert(char(25),
320                   substring(@SybDiskName, 1, 25)) +
321                   "     per sec      per xact       count  %% of total"
322               print @rptline
323   
324               print @sum1line
325   
326               select @tmp_int2 = isnull(SUM(value), 0)
327               from #tempmonitors
328               where group_name = @tmp_grp and
329                   (field_name = "total_reads" or
330                       field_name = "total_writes")
331   
332               if @tmp_int2 = 0
333               begin
334                   select @rptline = "  Total I/Os                          0.0           0.0           0       n/a"
335                   print @rptline
336               end
337               else
338               begin
339                   select @tmp_int = value
340                   from #tempmonitors
341                   where group_name = @tmp_grp and
342                       field_name = "total_reads"
343                   if not exists (select *
344                           from #tempmonitors
345                           where group_name = @tmp_grp and
346                               field_name = "apf_physical_reads")
347   
348                   begin /*{ begin to check existence of apf counters*/
349                       select @rptline = "    Reads" + space(20) +
350                           str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
351                           space(2) +
352                           str(@tmp_int / convert(real, @NumXacts), 12, 1) +
353                           space(2) +
354                           str(@tmp_int, 10) + space(5) +
355                           str(100.0 * @tmp_int / @tmp_int2, 5, 1) + @psign
356                       print @rptline
357                   end /*} end for apf counter check*/
358   
359                   else
360                   begin /*{begin in case apf counters exist*/
361                       select @tmp_int3 = value
362                       from #tempmonitors
363                       where group_name = @tmp_grp and
364                           field_name = "apf_physical_reads"
365   
366                       select @tmp_int4 = @tmp_int - @tmp_int3
367   
368                       select @rptline = "    Reads"
369                       print @rptline
370   
371                       select @rptline = "      APF" + space(20) +
372                           str(@tmp_int3 / (@NumElapsedMs / 1000.0), 12, 1) +
373                           space(2) +
374                           str(@tmp_int3 / convert(real, @NumXacts), 12, 1) +
375                           space(2) +
376                           str(@tmp_int3, 10) + space(5) +
377                           str(100.0 * @tmp_int3 / @tmp_int2, 5, 1) + @psign
378                       print @rptline
379   
380                       select @rptline = "      Non-APF" + space(16) +
381                           str(@tmp_int4 / (@NumElapsedMs / 1000.0), 12, 1) +
382                           space(2) +
383                           str(@tmp_int4 / convert(real, @NumXacts), 12, 1) +
384                           space(2) +
385                           str(@tmp_int4, 10) + space(5) +
386                           str(100.0 * @tmp_int4 / @tmp_int2, 5, 1) + @psign
387                       print @rptline
388                   end /*} end in case where apf counters exist*/
389   
390                   select @tmp_int = value
391                   from #tempmonitors
392                   where group_name = @tmp_grp and
393                       field_name = "total_writes"
394   
395                   select @rptline = "    Writes" + space(19) +
396                       str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
397                       space(2) +
398                       str(@tmp_int / convert(real, @NumXacts), 12, 1) +
399                       space(2) +
400                       str(@tmp_int, 10) + space(5) +
401                       str(100.0 * @tmp_int / @tmp_int2, 5, 1) + @psign
402                   print @rptline
403   
404               end /* else @tmp_int2 != 0 */
405   
406               print @sum1line
407   
408               select @rptline = "  Total I/Os" + space(17) +
409                   str(@tmp_int2 / (@NumElapsedMs / 1000.0), 12, 1) +
410                   space(2) +
411                   str(@tmp_int2 / convert(real, @NumXacts), 12, 1) +
412                   space(2) +
413                   str(@tmp_int2, 10) + space(5) +
414                   str(100.0 * @tmp_int2 / @tmp_total, 5, 1) + @psign
415               print @rptline
416               print @blankline
417   
418               select @tmp_int2 = isnull(SUM(value), 0)
419               from #tempmonitors
420               where group_name = @tmp_grp and
421                   (field_name = "p_hits" or field_name = "p_misses")
422   
423               if @tmp_int2 != 0
424               begin
425   
426                   select @tmp_int = value
427                   from #tempmonitors
428                   where group_name = @tmp_grp and
429                       field_name = "p_hits"
430   
431                   select @rptline = "  Mirror Semaphore Granted" +
432                       space(3) +
433                       str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
434                       space(2) +
435                       str(@tmp_int / convert(real, @NumXacts), 12, 1) +
436                       space(2) +
437                       str(@tmp_int, 10) + space(5) +
438                       str(100.0 * @tmp_int / @tmp_int2, 5, 1) + @psign
439                   print @rptline
440   
441                   select @tmp_int = value
442                   from #tempmonitors
443                   where group_name = @tmp_grp and
444                       field_name = "p_misses"
445   
446                   select @rptline = "  Mirror Semaphore Waited" +
447                       space(4) +
448                       str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
449                       space(2) +
450                       str(@tmp_int / convert(real, @NumXacts), 12, 1) +
451                       space(2) +
452                       str(@tmp_int, 10) + space(5) +
453                       str(100.0 * @tmp_int / @tmp_int2, 5, 1) + @psign
454                   print @rptline
455               end /* else @tmp_int2 != 0 */
456   
457               print @blankline
458               print @subsection
459               print @blankline
460   
461               fetch disk_info into @SybDiskName, @PhyDiskName, @tmp_grp
462   
463           end /* while @@sqlstatus */
464   
465           close disk_info
466           deallocate cursor disk_info
467   
468       end /* else @tmp_total != 0 */
469   
470       print @blankline
471   
472       if (@Reco = 'Y' and @reco_total_diskio != 0)
473       begin
474           select @recotxt = "  Tuning Recommendations for Disk I/O Management"
475           select @recoline = "  ----------------------------------------------"
476           select @reco_hdr_prn = 0
477   
478           select @reco_diskio_struct_delay = convert(int,
479               (100.0 * ((1.0 * @reco_diskio_struct_delay) / @reco_total_diskio)))
480           select @reco_maxaio_server = convert(int,
481               (100.0 * ((1.0 * @reco_maxaio_server) / @reco_total_diskio)))
482           select @reco_maxaio_engine = convert(int,
483               (100.0 * ((1.0 * @reco_maxaio_engine) / @reco_total_diskio)))
484           select @reco_aio_os_limit = convert(int,
485               (100.0 * ((1.0 * @reco_aio_os_limit) / @reco_total_diskio)))
486   
487           /*
488           ** If the % of I/O's delayed on account of
489           ** number of disk I/O structures is > 5%
490           ** consider increasing the number of disk i/o
491           ** structures
492           */
493           if @reco_diskio_struct_delay > 5
494           begin
495               if (@reco_hdr_prn = 0)
496               begin
497                   print @recotxt
498                   print @recoline
499                   select @reco_hdr_prn = 1
500               end
501   
502               print "  - Consider increasing the 'disk i/o structures'"
503               print "    configuration parameter."
504               print @blankline
505               select @reco_hdr_prn = 1
506           end
507   
508           /*
509           ** If the % of the number of I/O's delayed on account of
510           ** the server limit is > 5
511           ** consider increasing the 'max async I/Os per server'
512           ** configuration parameter 
513           */
514           if @reco_maxaio_server > 5
515           begin
516               if (@reco_hdr_prn = 0)
517               begin
518                   print @recotxt
519                   print @recoline
520                   select @reco_hdr_prn = 1
521               end
522   
523               print "  - Consider increasing the 'max async I/Os per server'"
524               print "    configuration parameter."
525               print @blankline
526               select @reco_hdr_prn = 1
527           end
528   
529           /*
530           ** If the % of the number of I/O's delayed on account of
531           ** the engine limit is > 5
532           ** consider increasing 'max async I/Os per engine'
533           */
534           if @reco_maxaio_engine > 5
535           begin
536               if (@reco_hdr_prn = 0)
537               begin
538                   print @recotxt
539                   print @recoline
540                   select @reco_hdr_prn = 1
541               end
542   
543               print "  - Consider increasing the 'max async I/Os per engine'"
544               print "    configuration parameter."
545               print @blankline
546               select @reco_hdr_prn = 1
547           end
548   
549           /*
550           ** If the % of the number of I/O's delayed on account of
551           ** operating system parameters governing async I/O
552           ** is greater than 5, then consider increasing that
553           ** parameter.
554           */
555           if @reco_aio_os_limit > 5
556           begin
557               if (@reco_hdr_prn = 0)
558               begin
559                   print @recotxt
560                   print @recoline
561                   select @reco_hdr_prn = 1
562               end
563   
564               print "  - Consider increasing the operating system parameter"
565               print "    governing the number of asynchronous I/O's."
566               print @blankline
567               select @reco_hdr_prn = 1
568           end
569   
570       end
571   
572       print @blankline
573   
574       return 0
575   

DEFECTS
 MLCH 3 Char type with length>30 char(80) 36
 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) 44
 MLCH 3 Char type with length>30 char(80) 45
 MLCH 3 Char type with length>30 char(80) 48
 MLCH 3 Char type with length>30 char(80) 49
 MUCO 3 Useless Code Useless Brackets 312
 MUCO 3 Useless Code Useless Brackets 472
 MUCO 3 Useless Code Useless Brackets 479
 MUCO 3 Useless Code Useless Brackets 481
 MUCO 3 Useless Code Useless Brackets 483
 MUCO 3 Useless Code Useless Brackets 485
 MUCO 3 Useless Code Useless Brackets 495
 MUCO 3 Useless Code Useless Brackets 516
 MUCO 3 Useless Code Useless Brackets 536
 MUCO 3 Useless Code Useless Brackets 557
 QAFM 3 Var Assignment from potentially many rows 76
 QAFM 3 Var Assignment from potentially many rows 102
 QAFM 3 Var Assignment from potentially many rows 122
 QAFM 3 Var Assignment from potentially many rows 173
 QAFM 3 Var Assignment from potentially many rows 193
 QAFM 3 Var Assignment from potentially many rows 224
 QAFM 3 Var Assignment from potentially many rows 339
 QAFM 3 Var Assignment from potentially many rows 361
 QAFM 3 Var Assignment from potentially many rows 390
 QAFM 3 Var Assignment from potentially many rows 426
 QAFM 3 Var Assignment from potentially many rows 441
 VNRD 3 Variable is not read @reco_hdr_prn 567
 CRDO 2 Read Only Cursor Marker (has for read only clause) 304
 MSUB 2 Subquery Marker 98
 MSUB 2 Subquery Marker 220
 MSUB 2 Subquery Marker 343
 MTR1 2 Metrics: Comments Ratio Comments: 18% 5
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 48 = 47dec - 1exi + 2 5
 MTR3 2 Metrics: Query Complexity Complexity: 297 5

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

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