DatabaseProcApplicationCreatedLinks
sybsystemprocssp_sysmon_taskmgmt  14 déc. 14Defects Propagation Dependencies

1     
2     
3     create or replace procedure sp_sysmon_taskmgmt
4     
5         @NumEngines tinyint, /* number of engines online */
6         @NumElapsedMs int, /* for "per Elapsed second" calculations */
7         @NumXacts int, /* for per transactions calculations */
8         @Reco char(1) /* Flag for recommendations */
9     as
10    
11        /* --------- declare local variables --------- */
12        declare @NumTaskSwitch int, /* Total Number of Task Context Switches 
13            ** across all engines */
14            @IgnoreTaskSwitch int, /* Total Number of Task Context Switches 
15            ** which can be ignored */
16            @KnownTaskSwitch int, /* Count of Number of Task Context Switches 
17            ** by Known Causes */
18            @IgnoreTaskYields int, /* Total Number of Task Yields which can be
19            ** ignored */
20            @i smallint, /* loop index to iterate through multi-group 
21            ** counters (engine, disk, & buffer) */
22            @tmp_grp varchar(25), /* temp var for building group_names 
23            ** ie. engine_N, disk_N */
24            @tmp_int int, /* temp var for integer storage */
25            @tmp_tot int, /* temp var for integer storage */
26            @tmp_iopacing int, /* temp var for storing the value of I/O pacing */
27            @sum1line char(80), /* string to delimit total lines without 
28            ** percent calc on printout */
29            @sum2line char(67), /* string to delimit total lines with percent 
30            ** calc on printout */
31            @blankline char(1), /* to print blank line */
32            @psign char(3), /* hold a percent sign (%) for print out */
33            @na_str char(3), /* holds 'n/a' for 'not applicable' strings */
34            @rptline char(80), /* formatted stats line for print statement */
35            @section char(80), /* string to delimit sections on printout */
36    
37            /* ------------- threaded mode variables -------------*/
38            @EngineId int, /* Engine Id corresponding to thread */
39            @ThreadId int, /* Thread ID*/
40            @TpId int, /* Thread Pool ID */
41            @thr_count int, /* number of threads */
42            @TpName varchar(80), /* ThreadPool Name */
43    
44            /* ------------- Variables for Tuning Recommendations ------------*/
45            @recotxt char(80),
46            @recoline char(80),
47            @reco_hdr_prn bit,
48            @reco_cache_search_miss real,
49            @reco_io_pacing real,
50            @reco_lock_contention real,
51            @reco_group_commit_sleeps real,
52            @reco_device_contention real,
53            @reco_network_sent real,
54            @reco_network_received real
55    
56    
57        /* --------- Setup Environment --------- */
58        set nocount on /* disable row counts being sent to client */
59    
60        select @sum1line = "---------------------------  ------------  ------------  ----------  ----------"
61        select @sum2line = "  -------------------------  ------------  ------------  ----------"
62        select @blankline = " "
63        select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */
64        select @na_str = "n/a"
65        select @section = "==============================================================================="
66    
67        /* =========================  Task Management Section ==================== */
68        print @section
69        print @blankline
70        print "Task Management                   per sec      per xact       count  %% of total"
71        print @sum1line
72        print @blankline
73        /*
74        ** ------ Connections Opened (Closed Not Available in counters) ------
75        */
76        select @tmp_int = value
77        from #tempmonitors
78        where group_name = "kernel" and
79            field_name = "processes_created"
80    
81        select @rptline = "  Connections Opened" + space(9)
82            + str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1)
83            + space(2)
84            + str(@tmp_int / convert(real, @NumXacts), 12, 1)
85            + space(2)
86            + str(@tmp_int, 10) + space(7)
87            + @na_str
88        print @rptline
89        print @blankline
90    
91        select @NumTaskSwitch = SUM(value)
92        from #tempmonitors
93        where group_name like "engine_%" and
94            field_name = "context_switches"
95    
96    
97        /*
98        ** Count the number of task switches which can be ignored. For example,
99        ** BCM/CLM daemon may be woken up when ever a request is queued. Once
100       ** the request is serviced, daemons goes back to sleep. Similarly sleeps
101       ** due to garbage collector can also be ignored.
102       */
103       select @IgnoreTaskSwitch = 0
104   
105       select @tmp_int = value
106       from #tempmonitors
107       where group_name = "lock" and
108           field_name = "lock_gc_yields"
109   
110       select @IgnoreTaskSwitch = @IgnoreTaskSwitch + @tmp_int
111   
112   
113       select @tmp_int = value
114       from #tempmonitors
115       where group_name = "lock" and
116           field_name = "daemon_context_switches"
117   
118       select @IgnoreTaskSwitch = @IgnoreTaskSwitch + @tmp_int
119   
120       select @tmp_int = SUM(value)
121       from #tempmonitors
122       where group_name = "bcmt" and
123           field_name in ("bcmt_pri_sleeps", "bcmt_sec_sleeps")
124   
125       select @IgnoreTaskSwitch = @IgnoreTaskSwitch + @tmp_int
126   
127       select @IgnoreTaskYields = value
128       from #tempmonitors
129       where group_name = "bcmt" and
130           field_name = "bcmt_sec_empty_scans"
131   
132       print "  Task Context Switches by Engine"
133   
134       if @NumTaskSwitch <= 0 /* Avoid divide by zero errors - print zero's */
135       begin
136           select @rptline = "      Total Task Switches             0.0           0.0           0       n/a"
137           print @rptline
138       end
139       else
140       begin
141           if (@@kernelmode = 'process')
142           begin
143               select @i = 0
144               while @i < @NumEngines /* for each engine */
145               begin
146                   /* build group_name string */
147                   select @tmp_grp = "engine_" + convert(varchar(4), @i)
148   
149                   /* If an engine's clock_ticks counter is 0, this means this engine
150                   ** is in offline status. We should skip this engine when priting
151                   ** the statistic information.
152                   */
153                   if (select value
154                           from #tempmonitors where field_name = "clock_ticks"
155                               and group_name = @tmp_grp) > 0
156                   begin
157                       select @tmp_int = value
158                       from #tempmonitors
159                       where group_name = @tmp_grp and
160                           field_name = "context_switches"
161   
162                       select @rptline = "    Engine " + convert(char(4), @i) + space(14) +
163                           str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
164                           space(2) +
165                           str(@tmp_int / convert(real, @NumXacts), 12, 1) +
166                           space(2) +
167                           str(@tmp_int, 10) + space(5) +
168                           str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) +
169                           @psign
170                       print @rptline
171                   end
172                   select @i = @i + 1
173               end /* while */
174           end /* process mode */
175           else
176           begin
177               print @blankline
178   
179               /* threaded mode */
180               select ThreadPoolID, ThreadPoolName, Size, Type
181               into #tmpThreadPool
182               from master.dbo.monThreadPool
183               order by ThreadPoolName
184   
185               declare epcursor cursor for
186               select ThreadPoolID, ThreadPoolName, Size
187               from #tmpThreadPool
188               where Type = "Engine (Multiplexed)"
189               order by ThreadPoolName
190   
191               declare engcursor cursor for
192               select engineid, enginename
193               from #muxthreadsinfo
194               where tpname = @TpName
195               order by engineid
196   
197               open epcursor
198               fetch epcursor into @TpId, @TpName, @thr_count
199               while (@@sqlstatus = 0)
200               begin
201                   select @rptline = "  ThreadPool : " + @TpName
202                   print @rptline
203   
204                   select @tmp_tot = 0
205   
206                   open engcursor
207                   fetch engcursor into @EngineId, @tmp_grp
208                   while (@@sqlstatus = 0)
209                   begin
210                       select @tmp_int = value
211                       from #tempmonitors
212                       where group_name = @tmp_grp and
213                           field_name = "context_switches"
214                       select @tmp_tot = @tmp_tot + @tmp_int
215   
216                       select @rptline = "    Engine " + convert(char(4), @EngineId) +
217                           space(14) +
218                           str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
219                           space(2) +
220                           str(@tmp_int / convert(real, @NumXacts), 12, 1) +
221                           space(2) +
222                           str(@tmp_int, 10) + space(5) +
223                           str(100.0 * @tmp_int / (@NumTaskSwitch +
224                               @IgnoreTaskSwitch + @IgnoreTaskYields), 5, 1) +
225                           @psign
226                       print @rptline
227   
228                       fetch engcursor into @EngineId, @tmp_grp
229                   end
230                   close engcursor
231   
232                   /* Print the Pool Average */
233                   if @thr_count > 1
234                   begin
235                       print @sum2line
236                       select @rptline = "  Pool Summary" + space(8)
237                           + "Total" + space(2) +
238                           str(@tmp_tot / (@NumElapsedMs / 1000.0), 12, 1)
239                           + space(2) +
240                           str(@tmp_tot / convert(real, @NumXacts), 12, 1)
241                           + space(2) +
242                           str(@tmp_tot, 10) + space(5)
243                       print @rptline
244   
245                       select @rptline = space(20) + "Average" + space(2) +
246                           str((@tmp_tot / @thr_count) / (@NumElapsedMs / 1000.0), 12, 1)
247                           + space(2) +
248                           str((@tmp_tot / @thr_count) / convert(real, @NumXacts), 12, 1)
249                           + space(2) +
250                           str((@tmp_tot / @thr_count), 10) + space(5)
251                       print @rptline
252                   end
253   
254                   print @blankline
255   
256                   fetch epcursor into @TpId, @TpName, @thr_count
257               end /* loop of pools */
258   
259               close epcursor
260               deallocate cursor epcursor
261   
262           end /* threaded mode */
263   
264           if @NumEngines > 1
265           begin
266               print @sum2line
267               select @rptline = "    Total Task Switches:" + space(5) +
268                   str(@NumTaskSwitch / (@NumElapsedMs / 1000.0), 12, 1) +
269                   space(2) +
270                   str(@NumTaskSwitch / convert(real, @NumXacts), 12, 1) +
271                   space(2) +
272                   str(@NumTaskSwitch, 10)
273               print @rptline
274           end /* if @NumEngines > 1*/
275   
276           print @blankline
277   
278           select @NumTaskSwitch = @NumTaskSwitch - @IgnoreTaskSwitch - @IgnoreTaskYields
279   
280           /* 
281           ** Break Down All Task Context Switches by Cause 
282           */
283           print "  Task Context Switches Due To:"
284   
285           /* init count of known context switches */
286           select @KnownTaskSwitch = 0
287   
288           /* 
289           ** Context Switch Due to Task Yields (Voluntary)
290           */
291           select @tmp_int = value
292           from #tempmonitors
293           where group_name = "kernel" and
294               field_name = "yields"
295   
296           select @tmp_int = @tmp_int - @IgnoreTaskYields
297   
298           select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int
299   
300           select @rptline = "    Voluntary Yields" + space(9) +
301               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
302               space(2) +
303               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
304               space(2) +
305               str(@tmp_int, 10) + space(5) +
306               str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) +
307               @psign
308           print @rptline
309   
310           /*
311           **  Context Switch Due to Cache Search Misses resulting in a read
312           */
313           select @tmp_int = SUM(value)
314           from #tempmonitors
315           where group_name like "buffer_%" and
316               field_name = "bufread_read_waits"
317   
318           select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int
319   
320           select @rptline = "    Cache Search Misses" + space(6) +
321               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
322               space(2) +
323               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
324               space(2) +
325               str(@tmp_int, 10) + space(5) +
326               str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) +
327               @psign
328   
329           select @reco_cache_search_miss = convert(int,
330               100.0 * ((1.0 * @tmp_int) / @NumTaskSwitch))
331   
332           print @rptline
333   
334           /*  
335           **  Context Switch due to exceeding the 'i/o batch size' config limit.
336           **
337           **  In other words, we started I/O batch and now we are waiting
338           **  for them to complete before starting the next batch.  The server 
339           **  works in batches to avoid flooding the I/O subsystem.  The size 
340           **  of the batch is tuneable via config parameter 'io batch size'.
341           */
342           select @tmp_int = SUM(value)
343           from #tempmonitors
344           where group_name like "buffer_%" and
345               field_name in ("my_start_waits_periobatch")
346   
347           select @tmp_iopacing = @tmp_int
348   
349           select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int
350   
351           select @rptline = "    Exceeding I/O batch size" + space(1) +
352               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
353               space(2) +
354               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
355               space(2) +
356               str(@tmp_int, 10) + space(5) +
357               str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) +
358               @psign
359   
360           select @reco_io_pacing = convert(int,
361               100.0 * ((1.0 * @tmp_int) / @NumTaskSwitch))
362   
363           print @rptline
364   
365           /* 
366           ** Context Switch Due to Disk Writes 
367           */
368           select @tmp_int = SUM(value)
369           from #tempmonitors
370           where group_name like "buffer_%" and
371               field_name in ("write_waits", "hk_write_waits",
372                   "restart_io_waits",
373                   "my_start_waits_log",
374                   "my_start_waits_non-log",
375                   "my_other_waits_non-log")
376   
377           /* 
378           ** the I/O pacing times will also be record in 
379           ** restart_io_waits || my_start_waits_log || my_start_waits_non-log.
380           ** So we should subtract out my_start_waits_periobatch
381           */
382           select @tmp_int = @tmp_int - @tmp_iopacing
383   
384           select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int
385   
386           select @rptline = "    System Disk Writes" + space(7) +
387               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
388               space(2) +
389               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
390               space(2) +
391               str(@tmp_int, 10) + space(5) +
392               str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) +
393               @psign
394           print @rptline
395   
396           /*
397           ** Context Switch Due to DB Lock Contention
398           */
399           select @tmp_int = SUM(value)
400           from #tempmonitors
401           where group_name = "lock" and
402               field_name like "waited_%" and
403               field_name not like "waited_%_ADDR"
404   
405           select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int
406   
407           select @rptline = "    Logical Lock Contention  " +
408               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
409               space(2) +
410               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
411               space(2) +
412               str(@tmp_int, 10) + space(5) +
413               str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) +
414               @psign
415   
416           select @reco_lock_contention = convert(int,
417               100.0 * ((1.0 * @tmp_int) / @NumTaskSwitch))
418   
419           print @rptline
420   
421           /* 
422           ** Context Switch Due to Address Lock Contention 
423           */
424           select @tmp_int = SUM(value)
425           from #tempmonitors
426           where group_name = "lock" and
427               field_name like "waited_%_ADDR"
428   
429           select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int
430   
431           select @rptline = "    Address Lock Contention  " +
432               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
433               space(2) +
434               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
435               space(2) +
436               str(@tmp_int, 10) + space(5) +
437               str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) +
438               @psign
439           print @rptline
440   
441           /* 
442           ** Context Switch Due to Latch Contention 
443           */
444           select @tmp_int = SUM(value)
445           from #tempmonitors
446           where group_name = "latch" and
447               field_name like "waited_%_LATCH"
448   
449           select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int
450   
451           select @rptline = "    Latch Contention  " + space(7) +
452               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
453               space(2) +
454               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
455               space(2) +
456               str(@tmp_int, 10) + space(5) +
457               str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) +
458               @psign
459   
460           print @rptline
461   
462           /* Context Switch Due to Physical lock transition. */
463           select @tmp_int = SUM(value)
464           from #tempmonitors
465           where group_name = "lock" and
466               field_name like "physical_lock_context_switches"
467   
468           select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int
469   
470           select @rptline = "    Physical Lock Transition" + space(1) +
471               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
472               space(2) +
473               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
474               space(2) +
475               str(@tmp_int, 10) + space(5) +
476               str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) +
477               @psign
478   
479           print @rptline
480   
481           /* Context Switch Due to Logical Lock Transition. */
482           select @tmp_int = SUM(value)
483           from #tempmonitors
484           where group_name = "lock" and
485               field_name like "logical_lock_context_switches"
486   
487           select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int
488   
489           select @rptline = "    Logical Lock Transition" + space(2) +
490               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
491               space(2) +
492               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
493               space(2) +
494               str(@tmp_int, 10) + space(5) +
495               str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) +
496               @psign
497   
498           print @rptline
499   
500           /* Context Switch Due to Object Lock Transition. */
501           select @tmp_int = SUM(value)
502           from #tempmonitors
503           where group_name = "lock" and
504               field_name like "ocm_context_switches"
505   
506           select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int
507   
508           select @rptline = "    Object Lock Transition " + space(2) +
509               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
510               space(2) +
511               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
512               space(2) +
513               str(@tmp_int, 10) + space(5) +
514               str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) +
515               @psign
516   
517           print @rptline
518   
519           /* 
520           ** Context Switch Due to Blocking on Log Semaphore 
521           */
522           select @tmp_int = value
523           from #tempmonitors
524           where group_name = "xls" and
525               field_name = "log_lock_waited"
526   
527           select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int
528   
529           select @rptline = "    Log Semaphore Contention " +
530               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
531               space(2) +
532               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
533               space(2) +
534               str(@tmp_int, 10) + space(5) +
535               str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) +
536               @psign
537           print @rptline
538   
539           /* 
540           ** Context Switch Due to Blocking on PLC lock
541           */
542           select @tmp_int = value
543           from #tempmonitors
544           where group_name = "xls" and
545               field_name = "plc_lock_waits"
546   
547           select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int
548   
549           select @rptline = "    PLC Lock Contention " + space(5) +
550               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
551               space(2) +
552               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
553               space(2) +
554               str(@tmp_int, 10) + space(5) +
555               str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) +
556               @psign
557           print @rptline
558   
559           /* 
560           ** Context Switch Due to Group Commit Sleeps 
561           */
562           select @tmp_int = SUM(value)
563           from #tempmonitors
564           where group_name like "buffer_%" and
565               field_name IN ("my_other_waits_log",
566                   "log_lastpage_pending_io_sleeps")
567   
568           select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int
569   
570           select @rptline = "    Group Commit Sleeps" + space(6) +
571               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
572               space(2) +
573               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
574               space(2) +
575               str(@tmp_int, 10) + space(5) +
576               str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) +
577               @psign
578   
579           select @reco_group_commit_sleeps = convert(int,
580               100.0 * ((1.0 * @tmp_int) / @NumTaskSwitch))
581   
582           print @rptline
583   
584           /* 
585           ** Context Switch Due to Last Log Page Writes 
586           */
587           select @tmp_int = SUM(value)
588           from #tempmonitors
589           where group_name like "buffer_%" and
590               field_name = "last_log_page_writes"
591   
592           select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int
593   
594           select @rptline = "    Last Log Page Writes" + space(5) +
595               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
596               space(2) +
597               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
598               space(2) +
599               str(@tmp_int, 10) + space(5) +
600               str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) +
601               @psign
602           print @rptline
603   
604           /* Context Switch Due to Modify Conflicts 
605           **
606           **  In other words, a task wants to perform an operation on a page 
607           **  (i.e. write it), but can't because another task is in the middle 
608           **  of modifying it.
609           */
610           select @tmp_int = SUM(value)
611           from #tempmonitors
612           where group_name like "buffer_%" and field_name in
613               ("changing_state_waits", "bufwrite_changing_waits",
614                   "bufpredirty_write_waits", "bufpredirty_changing_waits",
615                   "bufnewpage_changing_waits", "ind_bufguess_changing_waits",
616                   "ind_bufguess_writing_waits")
617   
618           select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int
619   
620           select @rptline = "    Modify Conflicts" + space(9) +
621               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
622               space(2) +
623               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
624               space(2) +
625               str(@tmp_int, 10) + space(5) +
626               str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) +
627               @psign
628           print @rptline
629   
630           /* 
631           ** Context Switch Due to Disk Device Contention 
632           */
633           select @tmp_int = SUM(value)
634           from #tempmonitors
635           where group_name like "disk_%" and
636               field_name = "p_sleeps"
637   
638           select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int
639   
640           select @rptline = "    I/O Device Contention" + space(4) +
641               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
642               space(2) +
643               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
644               space(2) +
645               str(@tmp_int, 10) + space(5) +
646               str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) +
647               @psign
648   
649           select @reco_device_contention = convert(int,
650               100.0 * ((1.0 * @tmp_int) / @NumTaskSwitch))
651   
652           print @rptline
653   
654           /* 
655           ** Context Switch Due to Network Packets Received
656           */
657           select @tmp_int = value
658           from #tempmonitors
659           where group_name = "network" and
660               field_name = "network_read_sleeps"
661   
662           select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int
663   
664           select @rptline = "    Network Packet Received" + space(2) +
665               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
666               space(2) +
667               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
668               space(2) +
669               str(@tmp_int, 10) + space(5) +
670               str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) +
671               @psign
672   
673           select @reco_network_received = convert(int,
674               100.0 * ((1.0 * @tmp_int) / @NumTaskSwitch))
675   
676           print @rptline
677   
678           /* 
679           ** Context Switch Due to Network Packets Sent
680           */
681           select @tmp_int = SUM(value)
682           from #tempmonitors
683           where group_name = "network" and
684               field_name = "network_send_sleeps"
685   
686           select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int
687   
688           select @rptline = "    Network Packet Sent" + space(6) +
689               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
690               space(2) +
691               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
692               space(2) +
693               str(@tmp_int, 10) + space(5) +
694               str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) +
695               @psign
696   
697           select @reco_network_sent = convert(int,
698               100.0 * ((1.0 * @tmp_int) / @NumTaskSwitch))
699   
700           print @rptline
701   
702           /* Context Switch Due to CIPC Thread Sleeps. */
703           select @tmp_int = SUM(value)
704           from #tempmonitors
705           where group_name = "kernel" and
706               field_name like "cipc_context_switches"
707   
708           select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int
709   
710           select @rptline = "    Interconnect Message Sleeps" + space(1) +
711               str(@tmp_int / (@NumElapsedMs / 1000.0), 9, 1) +
712               space(2) +
713               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
714               space(2) +
715               str(@tmp_int, 10) + space(5) +
716               str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) +
717               @psign
718   
719           print @rptline
720   
721           /*  
722           **  Context Switch Due to Network services
723           */
724           select @tmp_int = SUM(value)
725           from #tempmonitors
726           where group_name like "network%" and
727               field_name in ("nserver_sleeps")
728   
729           select @KnownTaskSwitch = @KnownTaskSwitch + @tmp_int
730   
731           select @rptline = "    Network services" + space(9) +
732               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
733               space(2) +
734               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
735               space(2) +
736               str(@tmp_int, 10) + space(5) +
737               str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) +
738               @psign
739   
740           print @rptline
741   
742           /* 
743           ** Context Switch Due to Other Causes
744           */
745           select @tmp_int = @NumTaskSwitch - @KnownTaskSwitch
746           select @rptline = "    Other Causes" + space(13) +
747               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
748               space(2) +
749               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
750               space(2) +
751               str(@tmp_int, 10) + space(5) +
752               str(100.0 * @tmp_int / @NumTaskSwitch, 5, 1) +
753               @psign
754           print @rptline
755   
756       end /* else @NumTaskSwitch != 0 */
757   
758       print @blankline
759   
760       if @Reco = 'Y'
761       begin
762           select @recotxt = "  Tuning Recommendations for Task Management"
763           select @recoline = "  ------------------------------------------"
764           select @reco_hdr_prn = 0
765   
766           /*
767           ** If the context switches due to cache misses is > 50% 
768           ** consider tuning your Data Caches
769           */
770           if (@reco_cache_search_miss > 50)
771           begin
772               if (@reco_hdr_prn = 0)
773               begin
774                   print @recotxt
775                   print @recoline
776                   select @reco_hdr_prn = 1
777               end
778   
779               print "  - Consider tuning your Data Caches."
780               print "    Look into the Data Cache management section for"
781               print "    more details on this."
782               print @blankline
783               select @reco_hdr_prn = 1
784           end
785   
786           /*
787           ** If the context switches due to IO Pacing is > 50% consider tuning 
788           ** the 'i/o batch size' configuration parameter.
789           */
790           if (@reco_io_pacing > 50)
791           begin
792               if (@reco_hdr_prn = 0)
793               begin
794                   print @recotxt
795                   print @recoline
796                   select @reco_hdr_prn = 1
797               end
798   
799               print "  - Consider tuning the 'i/o batch size' configuration parameter parameter"
800               print "	   A value of 100 is generally optimal. Also verify the I/O response times if the 'i/o batch size' tuning does not help"
801               print @blankline
802               select @reco_hdr_prn = 1
803           end
804   
805           /*
806           ** If context switches due to lock contention is > 50% consider using 
807           ** different lock management strategies such as row level locking
808           ** for certain objects.
809           */
810           if (@reco_lock_contention > 50)
811           begin
812               if (@reco_hdr_prn = 0)
813               begin
814                   print @recotxt
815                   print @recoline
816                   select @reco_hdr_prn = 1
817               end
818   
819               print "  - Consider identifying objects with high"
820               print "    lock contention using the sp_object_stats stored procedure"
821               print "    Review the Lock management section for more details."
822               print @blankline
823               select @reco_hdr_prn = 1
824           end
825           /*
826           ** If the context switches due to group commit sleeps is > 50% 
827           ** consider tuning the log io size
828           */
829           if (@reco_group_commit_sleeps > 50)
830           begin
831               if (@reco_hdr_prn = 0)
832               begin
833                   print @recotxt
834                   print @recoline
835                   select @reco_hdr_prn = 1
836               end
837               print "  - Consider lowering the logio size using sp_logiosize"
838               print "    for better transaction commit performance."
839               print "    This recommendation is not valid if the logio size is 2K."
840               print @blankline
841               select @reco_hdr_prn = 1
842           end
843           /*
844           ** If the context switches due to device contention is > 50% 
845           ** consider tuning the disk i/o sub system
846           */
847           if (@reco_device_contention > 50)
848           begin
849               if (@reco_hdr_prn = 0)
850               begin
851                   print @recotxt
852                   print @recoline
853                   select @reco_hdr_prn = 1
854               end
855   
856               print "  - Consider tuning your Disk I/O sub-system."
857               print @blankline
858               select @reco_hdr_prn = 1
859           end
860           /*
861           ** If the context switches due to the network is > 50% 
862           ** consider tuning the network I/O sub-system
863           */
864           if (@reco_network_sent > 50 OR @reco_network_received > 50)
865           begin
866               if (@reco_hdr_prn = 0)
867               begin
868                   print @recotxt
869                   print @recoline
870                   select @reco_hdr_prn = 1
871               end
872   
873               print "  - Consider tuning your Network I/O sub-system."
874               print @blankline
875               select @reco_hdr_prn = 1
876           end
877       end
878       print @blankline
879   
880       return 0
881   

DEFECTS
 TNOI 4 Table with no index master..monThreadPool master..monThreadPool
 VRUN 4 Variable is read and not initialized @TpName 194
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause epcursor 186
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause engcursor 192
 MGTP 3 Grant to public master..monThreadPool  
 MLCH 3 Char type with length>30 char(80) 27
 MLCH 3 Char type with length>30 char(67) 29
 MLCH 3 Char type with length>30 char(80) 34
 MLCH 3 Char type with length>30 char(80) 35
 MLCH 3 Char type with length>30 char(80) 45
 MLCH 3 Char type with length>30 char(80) 46
 MNER 3 No Error Check should check @@error after select into 180
 MUCO 3 Useless Code Useless Brackets 141
 MUCO 3 Useless Code Useless Brackets 199
 MUCO 3 Useless Code Useless Brackets 208
 MUCO 3 Useless Code Useless Brackets 250
 MUCO 3 Useless Code Useless Brackets 770
 MUCO 3 Useless Code Useless Brackets 772
 MUCO 3 Useless Code Useless Brackets 790
 MUCO 3 Useless Code Useless Brackets 792
 MUCO 3 Useless Code Useless Brackets 810
 MUCO 3 Useless Code Useless Brackets 812
 MUCO 3 Useless Code Useless Brackets 829
 MUCO 3 Useless Code Useless Brackets 831
 MUCO 3 Useless Code Useless Brackets 847
 MUCO 3 Useless Code Useless Brackets 849
 MUCO 3 Useless Code Useless Brackets 864
 MUCO 3 Useless Code Useless Brackets 866
 QAFM 3 Var Assignment from potentially many rows 76
 QAFM 3 Var Assignment from potentially many rows 105
 QAFM 3 Var Assignment from potentially many rows 113
 QAFM 3 Var Assignment from potentially many rows 127
 QAFM 3 Var Assignment from potentially many rows 157
 QAFM 3 Var Assignment from potentially many rows 210
 QAFM 3 Var Assignment from potentially many rows 291
 QAFM 3 Var Assignment from potentially many rows 522
 QAFM 3 Var Assignment from potentially many rows 542
 QAFM 3 Var Assignment from potentially many rows 657
 QAPT 3 Access to Proxy Table master..monThreadPool 182
 QCTC 3 Conditional Table Creation 180
 VNRD 3 Variable is not read @reco_hdr_prn 875
 VUNU 3 Variable is not used @ThreadId 39
 CRDO 2 Read Only Cursor Marker (has an 'order by' clause) 186
 CRDO 2 Read Only Cursor Marker (has an 'order by' clause) 192
 MSUB 2 Subquery Marker 153
 MTR1 2 Metrics: Comments Ratio Comments: 18% 3
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 54 = 53dec - 1exi + 2 3
 MTR3 2 Metrics: Query Complexity Complexity: 348 3

DEPENDENCIES
PROCS AND TABLES USED
reads table master..monThreadPool (1)  
reads table tempdb..#muxthreadsinfo (1) 
read_writes table tempdb..#tmpThreadPool (1) 
reads table tempdb..#tempmonitors (1) 

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