DatabaseProcApplicationCreatedLinks
sybsystemprocssp_sysmon_kernel_threaded  14 déc. 14Defects Propagation Dependencies

1     
2     create or replace procedure sp_sysmon_kernel_threaded
3         @NumMuxThreads tinyint, /* number of engine threads online */
4         @NumElapsedMs int, /* for "per Elapsed second" calculations */
5         @NumXacts int, /* for per transactions calculations */
6         @Reco char(1), /* Flag for recommendations */
7         @instid smallint = NULL /* optional SDC instance id */
8     as
9     
10        /* --------- declare local variables --------- */
11        declare @EngineId int, /* Engine Id corresponding to thread */
12            @ThreadId int, /* Thread ID*/
13            @TpId int, /* Thread Pool ID */
14            @TpName varchar(80), /* ThreadPool Name */
15            @tmp_grp varchar(25), /* temp var to build group_names
16            ** ie. engine_N, disk_N */
17    
18            @tmp_int int, /* temp var for integer storage */
19            @tmp_int2 int, /* temp var for integer storage */
20            @tmp_tot int, /* temp var for summing 'total #s' data */
21            @tmp_total bigint, /* temp var for summing 'total #s' data */
22            @tmp_server int, /* temp var for storing server summary */
23            @cpu_busy real, /* var for cpu busy percentage */
24            @cpu_busy_sum real, /* var for summing cpu busy percentage */
25            @cpu_busy_avg real, /* var for averaging cpu busy percentage */
26            @cpu_busy_server real, /* Total cpu busy of server */
27            @cpu_server_avg real, /* var for averaging server cpu percentage */
28            @idle real, /* var for tracking idle value */
29            @thr_count int, /* number of threads within in a pool */
30    
31            @user_busy real, /* user time percentage */
32            @user_busy_sum real, /* user time percentage, summed */
33            @user_busy_avg real, /* user time percentage, average */
34            @user_busy_server real, /* user time for all threads */
35            @system_busy real, /* system time percentage */
36            @system_busy_sum real, /* system time percentage, summed */
37            @system_busy_avg real, /* system time percentage, average */
38            @system_busy_server real, /* system time for all threads */
39    
40            @tmp_bigint1 bigint,
41            @tmp_bigint2 bigint,
42            @tmp_real1 real,
43            @tmp_real2 real,
44            @tmp_real3 real,
45            @real_tot real,
46    
47            @TaskName varchar(30), /* Task thread is running */
48    
49            /* useful variables for printing */
50            @sum1line char(80), /* string to delimit total lines without 
51            ** percent calc on printout */
52            @sum2line char(80), /* string to delimit total lines with percent 
53            ** calc on printout */
54            @blankline char(1), /* to print blank line */
55            @psign char(3), /* hold a percent sign (%) for print out */
56            @na_str char(3), /* holds 'n/a' for 'not applicable' strings */
57            @rptline char(80), /* formatted statistics line for print 
58            ** statement */
59            @wideline char(84), /* special print line when lots of % are used.
60            ** make sure you don't line wrap */
61            @section char(80), /* string to delimit sections on printout */
62            @summary_line char(80)
63    
64        /* --------- Setup Environment --------- */
65        set nocount on /* disable row counts being sent to client */
66    
67        select @sum1line = "  -------------------------  ------------  ------------  ----------  ----------"
68        select @sum2line = "  -------------------------  ------------  ------------  ----------"
69        select @blankline = " "
70        select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */
71        select @na_str = "n/a"
72        select @section = "==============================================================================="
73    
74    
75        /* ======================= Kernel Utilization Section =================== */
76        print @section
77        print @blankline
78        print "Kernel Utilization"
79        print "------------------"
80        print @blankline
81    
82        select @cpu_busy_sum = 0, @cpu_busy_avg = 0,
83            @cpu_busy_server = 0, @cpu_server_avg = 0,
84            @user_busy_sum = 0, @user_busy_avg = 0,
85            @system_busy_sum = 0, @system_busy_avg = 0,
86            @user_busy_server = 0, @system_busy_server = 0,
87            @thr_count = 0, @tmp_tot = 0,
88            @tmp_server = 0
89    
90        select ThreadPoolID, ThreadPoolName, Size, Type
91        into #tmpThreadPool
92        from master.dbo.monThreadPool
93        where InstanceID = @instid
94        order by ThreadPoolName
95    
96        select StatisticID, l.EngineNumber, Avg_1min, Avg_5min,
97            Avg_15min, ThreadPoolID
98        into #tmpLoad
99        from master.dbo.monSysLoad l,
100           master.dbo.monEngine e,
101           master.dbo.monThread t
102       where l.StatisticID in (4, 5)
103           and l.EngineNumber = e.EngineNumber
104           and e.ThreadID = t.ThreadID
105           and l.InstanceID = @instid
106           and e.InstanceID = @instid
107           and t.InstanceID = @instid
108   
109       /* Common Cursors */
110       declare tpcursor cursor for
111       select ThreadPoolID, ThreadPoolName, Size
112       from #tmpThreadPool
113       order by ThreadPoolName
114   
115       declare epcursor cursor for
116       select ThreadPoolID, ThreadPoolName, Size
117       from #tmpThreadPool
118       where Type = "Engine (Multiplexed)"
119       order by ThreadPoolName
120   
121       declare engcursor cursor for
122       select engineid, enginename
123       from #muxthreadsinfo
124       where tpname = @TpName
125           and InstanceID = @instid
126       order by engineid
127   
128       /*************************************************
129       **		Engine Utilization		**
130       *************************************************/
131   
132       print @blankline
133       print "  Engine Utilization (Tick %%)   User Busy   System Busy    I/O Busy        Idle"
134       print @sum1line
135   
136       /* build a temp table that has the usage info for each engine */
137       select isnull(100.0 * convert(real, u.value) / t.value, 0) "UserBusy",
138           isnull(100.0 * convert(real, s.value) / t.value, 0) "SystemBusy",
139           isnull(100.0 * convert(real, io.value) / t.value, 0) "IOBusy",
140           isnull(100.0 * convert(real, i.value) / t.value, 0) "Idle",
141           t.group_name
142       into #tmpEngUtilization
143       from #tempmonitors u, #tempmonitors s,
144           #tempmonitors io, #tempmonitors i,
145           #tempmonitors t
146       where u.group_name = t.group_name
147           and s.group_name = t.group_name
148           and io.group_name = t.group_name
149           and i.group_name = t.group_name
150           and u.field_name = "user_ticks"
151           and s.field_name = "system_ticks"
152           and io.field_name = "io_ticks"
153           and i.field_name = "idle_ticks"
154           and t.field_name = "clock_ticks"
155           and t.value > 0
156   
157       open epcursor
158       fetch epcursor into @TpId, @TpName, @thr_count
159       while (@@sqlstatus = 0)
160       begin
161           select @rptline = "  ThreadPool : " + @TpName
162           print @rptline
163   
164           open engcursor
165           fetch engcursor into @EngineId, @tmp_grp
166           while (@@sqlstatus = 0)
167           begin
168               select @wideline = "   Engine " + convert(char(4), @EngineId)
169                   + space(20)
170                   + str(UserBusy, 5, 1) + @psign + space(7)
171                   + str(SystemBusy, 5, 1) + @psign + space(5)
172                   + str(IOBusy, 5, 1) + @psign + space(5)
173                   + str(Idle, 5, 1) + @psign
174               from #tmpEngUtilization
175               where group_name = @tmp_grp
176               print @wideline
177   
178               fetch engcursor into @EngineId, @tmp_grp
179           end
180           close engcursor
181   
182           /* Print the Average and Summary of each threadpool */
183           if @thr_count > 1
184           begin
185               print @sum1line
186               select @wideline = "  Pool Summary " + space(7) + "Total"
187                   + space(5)
188                   + str(sum(UserBusy), 7, 1) + @psign + space(5)
189                   + str(sum(SystemBusy), 7, 1) + @psign + space(3)
190                   + str(sum(IOBusy), 7, 1) + @psign + space(3)
191                   + str(sum(Idle), 7, 1) + @psign
192               from #tmpEngUtilization
193               where group_name in
194                       (select enginename from #muxthreadsinfo
195                       where tpname = @TpName)
196               print @wideline
197               select @wideline = space(20) + "Average"
198                   + space(7)
199                   + str(avg(UserBusy), 5, 1) + @psign + space(7)
200                   + str(avg(SystemBusy), 5, 1) + @psign + space(5)
201                   + str(avg(IOBusy), 5, 1) + @psign + space(5)
202                   + str(avg(Idle), 5, 1) + @psign
203               from #tmpEngUtilization
204               where group_name in
205                       (select enginename from #muxthreadsinfo
206                       where tpname = @TpName)
207               print @wideline
208           end
209           print @blankline
210   
211           fetch epcursor into @TpId, @TpName, @thr_count
212       end /* loop of pools */
213       close epcursor
214   
215   
216       /* Print the Server Summary */
217       if @NumMuxThreads > 1
218       begin
219           print @sum1line
220           select @wideline = "  Server Summary " + space(5)
221               + "Total" + space(5)
222               + str(sum(UserBusy), 7, 1) + @psign + space(5)
223               + str(sum(SystemBusy), 7, 1) + @psign + space(3)
224               + str(sum(IOBusy), 7, 1) + @psign + space(3)
225               + str(sum(Idle), 7, 1) + @psign
226           from #tmpEngUtilization
227           print @wideline
228           select @wideline = space(20) + "Average" + space(7)
229               + str(avg(UserBusy), 5, 1) + @psign + space(7)
230               + str(avg(SystemBusy), 5, 1) + @psign + space(5)
231               + str(avg(IOBusy), 5, 1) + @psign + space(5)
232               + str(avg(Idle), 5, 1) + @psign
233           from #tmpEngUtilization
234           print @wideline
235       end /* Server Summary */
236       print @blankline
237   
238   
239       /*************************************************
240       **		Run Queue Length		**
241       *************************************************/
242   
243       print @blankline
244       print "  Average Runnable Tasks            1 min         5 min      15 min  %% of total"
245       print @sum1line
246   
247       declare loadcursor cursor for
248       select EngineNumber, StatisticID, Avg_1min, Avg_5min, Avg_15min
249       from #tmpLoad
250       where ThreadPoolID = @TpId
251       order by StatisticID desc,
252           EngineNumber
253   
254       open epcursor
255       fetch epcursor into @TpId, @TpName, @thr_count
256       while (@@sqlstatus = 0)
257       begin
258           select @real_tot = sum(Avg_1min) from
259               #tmpLoad where ThreadPoolID = @TpId
260   
261           select @rptline = "  ThreadPool : " + @TpName
262           print @rptline
263   
264           open loadcursor
265           fetch loadcursor into @EngineId, @tmp_int, @tmp_real1,
266               @tmp_real2, @tmp_real3
267           while (@@sqlstatus = 0)
268           begin
269               select @rptline = (case when @tmp_int = 4
270                       then "   Engine "
271                       + convert(char(4), @EngineId)
272                       + space(22)
273                       else "   Global Queue" + space(21)
274                   end)
275                   + str(@tmp_real1, 5, 1) + space(9)
276                   + str(@tmp_real2, 5, 1) + space(7)
277                   + str(@tmp_real3, 5, 1) + space(5)
278                   + (case when @real_tot = 0
279                       then str(0, 5, 1)
280                       else str(100.0 * @tmp_real1 / @real_tot, 5, 1)
281                   end)
282                   + @psign
283               print @rptline
284   
285               fetch loadcursor into @EngineId, @tmp_int, @tmp_real1,
286                   @tmp_real2, @tmp_real3
287           end
288           close loadcursor
289   
290           if (@thr_count > 1)
291           begin
292               select @summary_line = "  Pool Summary" + space(8)
293                   + "Total" + space(7)
294                   + str(sum(Avg_1min), 7, 1) + space(7)
295                   + str(sum(Avg_5min), 7, 1) + space(5)
296                   + str(sum(Avg_15min), 7, 1)
297               from #tmpLoad where ThreadPoolID = @TpId
298               print @sum2line
299               print @summary_line
300               select @summary_line = space(20)
301                   + "Average" + space(7)
302                   + str(avg(Avg_1min), 7, 1) + space(7)
303                   + str(avg(Avg_5min), 7, 1) + space(5)
304                   + str(avg(Avg_15min), 7, 1)
305               from #tmpLoad where ThreadPoolID = @TpId
306               print @summary_line
307           end
308   
309           print @blankline
310   
311           fetch epcursor into @TpId, @TpName, @thr_count
312       end /* loop of pools */
313   
314       print @sum2line
315       select @summary_line = "  Server Summary" + space(6)
316           + "Total" + space(7)
317           + str(sum(Avg_1min), 7, 1) + space(7)
318           + str(sum(Avg_5min), 7, 1) + space(5)
319           + str(sum(Avg_15min), 7, 1)
320       from #tmpLoad
321       print @summary_line
322       select @summary_line = space(20)
323           + "Average" + space(7)
324           + str(avg(Avg_1min), 7, 1) + space(7)
325           + str(avg(Avg_5min), 7, 1) + space(5)
326           + str(avg(Avg_15min), 7, 1)
327       from #tmpLoad
328       print @summary_line
329       print @blankline
330   
331       close epcursor
332       deallocate loadcursor
333   
334   
335       /*************************************************
336       **		Engine Sleeps			**
337       *************************************************/
338   
339       print @blankline
340       print "  CPU Yields by Engine            per sec      per xact       count  %% of total"
341       print @sum1line
342   
343       select @tmp_total = SUM(value)
344       from #tempmonitors
345       where group_name like "engine_%" and
346           field_name = "engine_sleeps"
347   
348       if @tmp_total = 0 /* Avoid divide by zero errors - just print zero's */
349       begin
350           select @rptline = "  Total CPU Yields                    0.0           0.0           0       n/a"
351           print @rptline
352       end
353       else
354       begin
355           open epcursor
356           fetch epcursor into @TpId, @TpName, @thr_count
357           while (@@sqlstatus = 0)
358           begin
359               select @rptline = "  ThreadPool : " + @TpName
360               print @rptline
361   
362               select @tmp_tot = SUM(value) from #tempmonitors where group_name in
363                       (select enginename from #muxthreadsinfo where
364                           tpname = @TpName) and field_name = "engine_sleeps"
365   
366               open engcursor
367               fetch engcursor into @EngineId, @tmp_grp
368               while (@@sqlstatus = 0)
369               begin
370                   select @tmp_int = value from #tempmonitors
371                   where group_name = @tmp_grp and
372                       field_name = "engine_sleeps"
373   
374   
375                   select @tmp_int2 = value from #tempmonitors
376                   where group_name = @tmp_grp and
377                       field_name = "engine_sleep_interrupted"
378   
379   
380                   /* 
381                   ** Make tmp_int the number of full sleeps.  Due to 
382                   ** timing issues collecting the monitor counters we may
383                   ** end up with more interrupted sleeps than total 
384                   ** sleeps.  If this is the case we just consider full
385                   ** sleeps to be zero.
386                   */
387                   select @tmp_int =
388                       case when @tmp_int > @tmp_int2
389                           then @tmp_int - @tmp_int2
390                           else 0
391                       end
392   
393                   if @tmp_tot != 0
394                   begin
395                       select @rptline = "   Engine " + convert(char(4), @EngineId)
396                       print @rptline
397   
398                       select @rptline = "      Full Sleeps"
399                           + space(12)
400                           + str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1)
401                           + space(2) +
402                           str(@tmp_int / convert(real, @NumXacts), 12, 1)
403                           + space(2) +
404                           str(@tmp_int, 10) + space(5) +
405                           str(100.0 * @tmp_int / @tmp_tot, 5, 1)
406                           + @psign
407                       print @rptline
408   
409                       select @rptline = "      Interrupted Sleeps"
410                           + space(5)
411                           + str(@tmp_int2 / (@NumElapsedMs / 1000.0), 12, 1)
412                           + space(2) +
413                           str(@tmp_int2 / convert(real, @NumXacts), 12, 1)
414                           + space(2) +
415                           str(@tmp_int2, 10) + space(5) +
416                           str(100.0 * @tmp_int2 / @tmp_tot, 5, 1)
417                           + @psign
418                       print @rptline
419                   end
420                   else
421                   begin
422                       select @rptline = "   Engine " + convert(char(4), @EngineId) +
423                           space(24) +
424                           "0.0           0.0           0       n/a"
425                       print @rptline
426                   end
427   
428                   fetch engcursor into @EngineId, @tmp_grp
429               end
430               close engcursor
431   
432               /* Print the Pool Average */
433               if @thr_count > 1
434               begin
435                   print @sum2line
436                   select @rptline = "  Pool Summary " + space(14) +
437                       str(@tmp_tot / (@NumElapsedMs / 1000.0), 12, 1)
438                       + space(2) +
439                       str(@tmp_tot / convert(real, @NumXacts), 12, 1)
440                       + space(2) +
441                       str(@tmp_tot, 10)
442                   print @rptline
443               end
444   
445               print @blankline
446   
447               fetch epcursor into @TpId, @TpName, @thr_count
448           end /* loop of pools */
449   
450           close epcursor
451   
452           /* Print the Server Summary */
453           if @NumMuxThreads > 1
454           begin
455               print @sum2line
456               select @rptline = "  Total CPU Yields " + space(10) +
457                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1)
458                   + space(2) +
459                   str(@tmp_total / convert(real, @NumXacts), 12, 1)
460                   + space(2) +
461                   str(@tmp_total, 10)
462               print @rptline
463           end
464   
465       end
466       print @blankline
467   
468   
469       /*************************************************
470       **		Thread Utilization		**
471       *************************************************/
472       set @tmp_tot = 0
473   
474       print @blankline
475       print "  Thread Utilization (OS %%)     User Busy   System Busy        Idle"
476       print @sum2line
477   
478   
479       declare threadcursor cursor for
480       select ThreadID, UserTime, SystemTime, TaskName
481       from #tempThreadStats
482       where ThreadPoolID = @TpId
483           and InstanceID = @instid
484       order by ThreadID
485   
486       open tpcursor
487       fetch tpcursor into @TpId, @TpName, @thr_count
488       while (@@sqlstatus = 0)
489       begin
490           /* use @tmp_int to track the number of thread rows printed in a pool */
491           select @tmp_int = 0
492   
493           open threadcursor
494           fetch threadcursor into @ThreadId, @tmp_bigint1, @tmp_bigint2, @TaskName
495           while (@@sqlstatus = 0)
496           begin
497               select @user_busy = 100.0 * @tmp_bigint1 / @NumElapsedMs
498               select @user_busy_sum = @user_busy_sum + @user_busy
499               select @system_busy = 100.0 * @tmp_bigint2 / @NumElapsedMs
500               select @system_busy_sum = @system_busy_sum + @system_busy
501               select @idle = 100 - (@user_busy + @system_busy)
502   
503               if (@TaskName like "Engine%" or
504                       @tmp_bigint1 > 0 or
505                       @tmp_bigint2 > 0)
506               begin
507   
508                   if (@tmp_int = 0)
509                   begin
510                       /* print the hadter the first time through */
511                       select @rptline = "  ThreadPool : " + @TpName
512                       print @rptline
513                   end
514   
515                   select @rptline = "   Thread " + convert(char(4), @ThreadId)
516                       + convert(char(18), (" (" + @TaskName + ")"))
517                       + space(2)
518                       + str(@user_busy, 5, 1) + @psign + space(7)
519                       + str(@system_busy, 5, 1) + @psign + space(5)
520                       + (case when @idle < 0
521                           then str(0, 5, 1)
522                           else str(@idle, 5, 1)
523                       end)
524                       + @psign
525                   print @rptline
526                   select @tmp_int = @tmp_int + 1
527               end
528   
529               fetch threadcursor into @ThreadId, @tmp_bigint1,
530                   @tmp_bigint2, @TaskName
531           end /* loop of threads */
532           close threadcursor
533   
534           set @user_busy_server = @user_busy_server + @user_busy_sum
535           set @system_busy_server = @system_busy_server + @system_busy_sum
536           set @tmp_tot = @tmp_tot + @thr_count
537   
538           if @tmp_int = 0
539           begin
540               select @rptline = "  ThreadPool : " + @TpName + " : no activity during sample"
541               print @rptline
542           end
543           else
544           if @thr_count > 1
545           begin
546               select @idle = @thr_count * 100 - (@user_busy_sum + @system_busy_sum)
547               select @summary_line = "  Pool Summary" + space(6)
548                   + "Total" + space(7)
549                   + str(@user_busy_sum, 7, 1) + @psign + space(5)
550                   + str(@system_busy_sum, 7, 1) + @psign + space(3)
551                   + (case when @idle < 0
552                       then str(0, 7, 1)
553                       else str(@idle, 7, 1)
554                   end)
555                   + @psign,
556                   @user_busy_avg = @user_busy_sum / @thr_count,
557                   @system_busy_avg = @system_busy_sum / @thr_count
558               select @idle = 100 - (@user_busy_avg + @system_busy_avg)
559               select @rptline = space(18) + "Average" + space(9)
560                   + str(@user_busy_avg, 5, 1) + @psign + space(7)
561                   + str(@system_busy_avg, 5, 1) + @psign + space(5)
562                   + (case when @idle < 0
563                       then str(0, 5, 1)
564                       else str(@idle, 5, 1)
565                   end)
566                   + @psign
567               print @sum2line
568               print @summary_line
569               print @rptline
570           end
571           print @blankline
572   
573           /* reset the counters */
574           select @user_busy_sum = 0, @user_busy_avg = 0,
575               @system_busy_sum = 0, @system_busy_avg = 0
576   
577           fetch tpcursor into @TpId, @TpName, @thr_count
578       end /* loop of pools */
579   
580       select @idle = @tmp_tot * 100 - (@user_busy_server + @system_busy_server)
581       select @summary_line = "  Server Summary " + space(3) + "Total" + space(7)
582           + str(@user_busy_server, 7, 1) + @psign + space(5)
583           + str(@system_busy_server, 7, 1) + @psign + space(3)
584           + (case when @idle < 0
585               then str(0, 7, 1)
586               else str(@idle, 7, 1)
587           end)
588           + @psign,
589           @user_busy_avg = @user_busy_server / @tmp_tot,
590           @system_busy_avg = @system_busy_server / @tmp_tot
591       select @idle = 100 - (@user_busy_avg + @system_busy_avg)
592       select @rptline = space(18) + "Average" + space(9)
593           + str(@user_busy_avg, 5, 1) + @psign + space(7)
594           + str(@system_busy_avg, 5, 1) + @psign + space(5)
595           + (case when @idle < 0
596               then str(0, 5, 1)
597               else str(@idle, 5, 1)
598           end)
599           + @psign
600       print @sum2line
601       print @summary_line
602       print @rptline
603   
604       print @blankline
605       select @real_tot = (sum(UserTime) + sum(SystemTime)) / (1.0 * @NumElapsedMs)
606       from #tempThreadStats
607       select @rptline = "  Adaptive Server threads are consuming "
608           + ltrim(str(@real_tot, 5, 1))
609           + " CPU units."
610       print @rptline
611       select @tmp_real1 = @NumXacts * 1000.0 / @NumElapsedMs
612   
613       if (@real_tot != 0)
614       begin
615           select @rptline = "  Throughput (committed xacts per CPU unit) : "
616               + ltrim(str(@tmp_real1 / @real_tot, 12, 1))
617       end
618       else
619       begin
620           select @rptline = "  Throughput (committed xacts per CPU unit) : n/a "
621       end
622       print @rptline
623       print @blankline
624   
625       close tpcursor
626       deallocate threadcursor
627   
628       /*************************************************
629       **	Page Faults and Context Switches	**
630       *************************************************/
631   
632       select @tmp_bigint1 = sum(MinorFaults),
633           @tmp_bigint2 = sum(MajorFaults)
634       from #tempThreadStats
635       set @tmp_total = @tmp_bigint1 + @tmp_bigint2
636   
637       if (@tmp_total > 0)
638       begin
639           print @blankline
640           print "  Page Faults at OS               per sec      per xact       count  %% of total"
641           print @sum1line
642   
643           select @rptline = "   Minor Faults"
644               + space(14)
645               + str(@tmp_bigint1 / (@NumElapsedMs / 1000.0), 12, 1)
646               + space(2)
647               + str(@tmp_bigint1 / convert(real, @NumXacts), 12, 1)
648               + space(2)
649               + str(@tmp_bigint1, 10) + space(5)
650               + str(100.0 * @tmp_bigint1 / @tmp_total, 5, 1) + @psign
651           print @rptline
652   
653           select @rptline = "   Major Faults"
654               + space(14)
655               + str(@tmp_bigint2 / (@NumElapsedMs / 1000.0), 12, 1)
656               + space(2)
657               + str(@tmp_bigint2 / convert(real, @NumXacts), 12, 1)
658               + space(2)
659               + str(@tmp_bigint2, 10) + space(5)
660               + str(100.0 * @tmp_bigint2 / @tmp_total, 5, 1) + @psign
661           print @rptline
662   
663           print @sum1line
664           select @rptline = "   Total Page Faults"
665               + space(9)
666               + str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1)
667               + space(2)
668               + str(@tmp_total / convert(real, @NumXacts), 12, 1)
669               + space(2)
670               + str(@tmp_total, 10)
671               + space(5) + "100.0" + @psign
672           print @rptline
673   
674           print @blankline
675       end
676   
677       select @tmp_total = sum(VoluntaryCtxtSwitches) + sum(NonVoluntaryCtxtSwitches)
678       from #tempThreadStats
679   
680       if (@tmp_total > 0)
681       begin
682           print @blankline
683           print "  Context Switches at OS          per sec      per xact       count  %% of total"
684           print @sum1line
685   
686           open tpcursor
687           fetch tpcursor into @TpId, @TpName, @thr_count
688           while (@@sqlstatus = 0)
689           begin
690               select @tmp_bigint1 = sum(VoluntaryCtxtSwitches),
691                   @tmp_bigint2 = sum(NonVoluntaryCtxtSwitches)
692               from #tempThreadStats
693               where ThreadPoolID = @TpId
694   
695               select @rptline = "  ThreadPool : " + @TpName
696               print @rptline
697   
698               select @rptline = "   Voluntary"
699                   + space(17)
700                   + str(@tmp_bigint1 / (@NumElapsedMs / 1000.0), 12, 1)
701                   + space(2)
702                   + str(@tmp_bigint1 / convert(real, @NumXacts), 12, 1)
703                   + space(2)
704                   + str(@tmp_bigint1, 10) + space(5)
705                   + str(100.0 * @tmp_bigint1 / @tmp_total, 5, 1) + @psign
706               print @rptline
707   
708               select @rptline = "   Non-Voluntary"
709                   + space(13)
710                   + str(@tmp_bigint2 / (@NumElapsedMs / 1000.0), 12, 1)
711                   + space(2)
712                   + str(@tmp_bigint2 / convert(real, @NumXacts), 12, 1)
713                   + space(2)
714                   + str(@tmp_bigint2, 10) + space(5)
715                   + str(100.0 * @tmp_bigint2 / @tmp_total, 5, 1) + @psign
716               print @rptline
717   
718               fetch tpcursor into @TpId, @TpName, @thr_count
719           end
720           close tpcursor
721   
722           print @sum1line
723           select @rptline = "   Total Context Switches"
724               + space(4)
725               + str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1)
726               + space(2)
727               + str(@tmp_total / convert(real, @NumXacts), 12, 1)
728               + space(2)
729               + str(@tmp_total, 10)
730               + space(5) + "100.0" + @psign
731           print @rptline
732   
733           print @blankline
734       end
735   
736   
737       /*************************************************
738       **		IO Controllers			**
739       *************************************************/
740       declare iocursor cursor for
741       select distinct Type from #tempIOCStats
742       where InstanceID = @instid
743       order by Type
744   
745       open iocursor
746       fetch iocursor into @TaskName
747       while (@@sqlstatus = 0)
748       begin
749           select @rptline = space(2) + convert(char(32), @TaskName + " Activity")
750               + "per sec      per xact       count  %% of total"
751           print @blankline
752           print @rptline
753           print @sum1line
754   
755           select @tmp_total = sum(BlockingPolls) + sum(NonBlockingPolls),
756               @tmp_bigint1 = sum(EventPolls),
757               @tmp_bigint2 = sum(FullPolls)
758           from #tempIOCStats
759           where Type = @TaskName
760               and InstanceID = @instid
761   
762           if @tmp_total = 0
763           begin
764               select @rptline = "   Polls"
765                   + space(29) + "0.0"
766                   + space(11) + "0.0"
767                   + space(11) + "0"
768                   + space(7) + "0.0" + @psign
769               print @rptline
770   
771               fetch iocursor into @TaskName
772               continue
773           end
774   
775           select @rptline = "   Polls"
776               + space(21)
777               + str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1)
778               + space(2)
779               + str(@tmp_total / convert(real, @NumXacts), 12, 1)
780               + space(2)
781               + str(@tmp_total, 10) + space(7) + @na_str
782           print @rptline
783   
784           if @tmp_bigint1 = 0
785           begin
786               select @rptline = "   Polls Returning Events"
787                   + space(13) + "0.0"
788                   + space(11) + "0.0"
789                   + space(11) + "0"
790                   + space(7) + "0.0" + @psign
791               print @rptline
792   
793               fetch iocursor into @TaskName
794               continue
795           end
796   
797           select @rptline = "   Polls Returning Events"
798               + space(4)
799               + str(@tmp_bigint1 / (@NumElapsedMs / 1000.0), 12, 1)
800               + space(2)
801               + str(@tmp_bigint1 / convert(real, @NumXacts), 12, 1)
802               + space(2)
803               + str(@tmp_bigint1, 10) + space(5)
804               + str(100.0 * @tmp_bigint1 / @tmp_total, 5, 1) + @psign
805           print @rptline
806   
807           select @rptline = "   Polls Returning Max Events"
808               + str(@tmp_bigint2 / (@NumElapsedMs / 1000.0), 12, 1)
809               + space(2)
810               + str(@tmp_bigint2 / convert(real, @NumXacts), 12, 1)
811               + space(2)
812               + str(@tmp_bigint2, 10) + space(5)
813               + str(100.0 * @tmp_bigint2 / @tmp_total, 5, 1) + @psign
814           print @rptline
815   
816   
817           select @tmp_bigint1 = sum(Events)
818           from #tempIOCStats
819           where Type = @TaskName
820               and InstanceID = @instid
821   
822           select @rptline = "   Total Events"
823               + space(14)
824               + str(@tmp_bigint1 / (@NumElapsedMs / 1000.0), 12, 1)
825               + space(2)
826               + str(@tmp_bigint1 / convert(real, @NumXacts), 12, 1)
827               + space(2)
828               + str(@tmp_bigint1, 10)
829               + space(7) + @na_str
830           print @rptline
831   
832           select @rptline = "   Events Per Poll"
833               + space(20)
834               + @na_str + space(11)
835               + @na_str + space(5)
836               + str((convert(real, sum(Events)) / @tmp_total), 7, 3)
837               + space(7) + @na_str
838           from #tempIOCStats
839           where Type = @TaskName
840               and InstanceID = @instid
841           print @rptline
842   
843           fetch iocursor into @TaskName
844       end
845       print @blankline
846       close iocursor
847       deallocate iocursor
848   
849       /*************************************************
850       **		Blocking Calls			**
851       *************************************************/
852       print @blankline
853       print "  Blocking Call Activity          per sec      per xact       count  %% of total"
854       print @sum1line
855   
856       select @tmp_tot = TotalRequests,
857           @tmp_int = QueuedRequests,
858           @tmp_int2 = WaitTime
859       from #tempWorkQueue
860       where Name = "syb_blocking_pool"
861   
862       if @tmp_tot = 0
863       begin
864           select @rptline = "  Total Requests                      0.0           0.0           0       n/a"
865           print @rptline
866       end
867       else
868       begin
869           select @rptline = "   Serviced Requests"
870               + space(9)
871               + str((@tmp_tot - @tmp_int) / (@NumElapsedMs / 1000.0), 12, 1)
872               + space(2) +
873               str((@tmp_tot - @tmp_int) / convert(real, @NumXacts), 12, 1)
874               + space(2) +
875               str((@tmp_tot - @tmp_int), 10) + space(5) +
876               str(100.0 * (@tmp_tot - @tmp_int) / @tmp_tot, 5, 1)
877               + @psign
878           print @rptline
879   
880           select @rptline = "   Queued Requests"
881               + space(11)
882               + str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1)
883               + space(2) +
884               str(@tmp_int / convert(real, @NumXacts), 12, 1)
885               + space(2) +
886               str(@tmp_int, 10) + space(5) +
887               str(100.0 * @tmp_int / @tmp_tot, 5, 1)
888               + @psign
889           print @rptline
890   
891           print @sum2line
892   
893           select @rptline = "  Total Requests"
894               + space(13) +
895               str(@tmp_tot / (@NumElapsedMs / 1000.0), 12, 1)
896               + space(2) +
897               str(@tmp_tot / convert(real, @NumXacts), 12, 1)
898               + space(2) +
899               str(@tmp_tot, 10)
900           print @rptline
901   
902           select @rptline = "  Total Wait Time (ms)"
903               + space(16)
904               + @na_str + space(11)
905               + @na_str + space(2)
906               + str(@tmp_int2, 10)
907           print @rptline
908       end
909       print @blankline
910   
911       /* cleanup common cursors */
912       deallocate tpcursor
913       deallocate engcursor
914       deallocate epcursor
915   
916       return 0
917   
918   

DEFECTS
 QJWI 5 Join or Sarg Without Index 103
 QJWI 5 Join or Sarg Without Index 104
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs smallint 93
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs smallint 105
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs smallint 106
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs smallint 107
 TNOI 4 Table with no index master..monEngine master..monEngine
 TNOI 4 Table with no index master..monSysLoad master..monSysLoad
 TNOI 4 Table with no index master..monThread master..monThread
 TNOI 4 Table with no index master..monThreadPool master..monThreadPool
 VRUN 4 Variable is read and not initialized @TpName 124
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause epcursor 116
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause engcursor 122
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause loadcursor 248
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause threadcursor 480
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause iocursor 741
 MGTP 3 Grant to public master..monEngine  
 MGTP 3 Grant to public master..monSysLoad  
 MGTP 3 Grant to public master..monThread  
 MGTP 3 Grant to public master..monThreadPool  
 MLCH 3 Char type with length>30 char(80) 50
 MLCH 3 Char type with length>30 char(80) 52
 MLCH 3 Char type with length>30 char(80) 57
 MLCH 3 Char type with length>30 char(84) 59
 MLCH 3 Char type with length>30 char(80) 61
 MLCH 3 Char type with length>30 char(80) 62
 MLCH 3 Char type with length>30 char(32) 749
 MNER 3 No Error Check should check @@error after select into 90
 MNER 3 No Error Check should check @@error after select into 96
 MNER 3 No Error Check should check @@error after select into 137
 MUCO 3 Useless Code Useless Brackets 159
 MUCO 3 Useless Code Useless Brackets 166
 MUCO 3 Useless Code Useless Brackets 256
 MUCO 3 Useless Code Useless Brackets 267
 MUCO 3 Useless Code Useless Brackets 269
 MUCO 3 Useless Code Useless Brackets 278
 MUCO 3 Useless Code Useless Brackets 290
 MUCO 3 Useless Code Useless Brackets 357
 MUCO 3 Useless Code Useless Brackets 368
 MUCO 3 Useless Code Useless Brackets 488
 MUCO 3 Useless Code Useless Brackets 495
 MUCO 3 Useless Code Useless Brackets 503
 MUCO 3 Useless Code Useless Brackets 508
 MUCO 3 Useless Code Useless Brackets 520
 MUCO 3 Useless Code Useless Brackets 551
 MUCO 3 Useless Code Useless Brackets 562
 MUCO 3 Useless Code Useless Brackets 584
 MUCO 3 Useless Code Useless Brackets 595
 MUCO 3 Useless Code Useless Brackets 613
 MUCO 3 Useless Code Useless Brackets 637
 MUCO 3 Useless Code Useless Brackets 680
 MUCO 3 Useless Code Useless Brackets 688
 MUCO 3 Useless Code Useless Brackets 747
 MUCO 3 Useless Code Useless Brackets 836
 QAFM 3 Var Assignment from potentially many rows 168
 QAFM 3 Var Assignment from potentially many rows 370
 QAFM 3 Var Assignment from potentially many rows 375
 QAFM 3 Var Assignment from potentially many rows 856
 QAPT 3 Access to Proxy Table master..monThreadPool 92
 QAPT 3 Access to Proxy Table master..monSysLoad 99
 QAPT 3 Access to Proxy Table master..monEngine 100
 QAPT 3 Access to Proxy Table master..monThread 101
 QJWT 3 Join or Sarg Without Index on temp table 146
 QJWT 3 Join or Sarg Without Index on temp table 147
 QJWT 3 Join or Sarg Without Index on temp table 148
 QJWT 3 Join or Sarg Without Index on temp table 149
 QJWT 3 Join or Sarg Without Index on temp table 193
 QJWT 3 Join or Sarg Without Index on temp table 204
 QJWT 3 Join or Sarg Without Index on temp table 362
 QNAJ 3 Not using ANSI Inner Join 99
 QNAJ 3 Not using ANSI Inner Join 143
 QNUA 3 Should use Alias: Column Avg_1min should use alias l 96
 QNUA 3 Should use Alias: Column Avg_5min should use alias l 96
 QNUA 3 Should use Alias: Column StatisticID should use alias l 96
 QNUA 3 Should use Alias: Column Avg_15min should use alias l 97
 QNUA 3 Should use Alias: Column ThreadPoolID should use alias t 97
 VNRD 3 Variable is not read @cpu_busy_avg 82
 VNRD 3 Variable is not read @cpu_busy_sum 82
 VNRD 3 Variable is not read @cpu_busy_server 83
 VNRD 3 Variable is not read @cpu_server_avg 83
 VNRD 3 Variable is not read @tmp_server 88
 VUNU 3 Variable is not used @cpu_busy 23
 CRDO 2 Read Only Cursor Marker (has an 'order by' clause) 111
 CRDO 2 Read Only Cursor Marker (has an 'order by' clause) 116
 CRDO 2 Read Only Cursor Marker (has an 'order by' clause) 122
 CRDO 2 Read Only Cursor Marker (has an 'order by' clause) 248
 CRDO 2 Read Only Cursor Marker (has an 'order by' clause) 480
 CRDO 2 Read Only Cursor Marker (has a 'distinct' option) 741
 MSUB 2 Subquery Marker 194
 MSUB 2 Subquery Marker 205
 MSUB 2 Subquery Marker 363
 MTR1 2 Metrics: Comments Ratio Comments: 13% 2
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 54 = 53dec - 1exi + 2 2
 MTR3 2 Metrics: Query Complexity Complexity: 408 2
 PRED_QUERY_COLLECTION 2 {me=master..monEngine, msl=master..monSysLoad, mt=master..monThread} 0 96

DEPENDENCIES
PROCS AND TABLES USED
read_writes table tempdb..#tmpLoad (1) 
reads table tempdb..#tempIOCStats (1) 
reads table tempdb..#tempWorkQueue (1) 
reads table master..monThread (1)  
reads table master..monSysLoad (1)  
read_writes table tempdb..#tmpThreadPool (1) 
reads table tempdb..#muxthreadsinfo (1) 
reads table tempdb..#tempThreadStats (1) 
reads table tempdb..#tempmonitors (1) 
read_writes table tempdb..#tmpEngUtilization (1) 
reads table master..monEngine (1)  
reads table master..monThreadPool (1)  

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