DatabaseProcApplicationCreatedLinks
sybsystemprocssp_sysmon_parallel  14 déc. 14Defects Propagation Dependencies

1     
2     /* This stored procedure produces a report describing the parallel
3     ** features: activity.
4     **
5     */
6     create or replace procedure sp_sysmon_parallel
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 @NumTaskSwitch int /* Total Number of Task Context Switches 
14        ** across all engines */
15        declare @KnownTaskSwitch int /* Count of Number of Task Context Switches 
16        ** by Known Causes */
17        declare @i smallint /* loop index to iterate through multi-group 
18        ** counters (engine, disk, & buffer) */
19        declare @tmp_grp varchar(25) /* temp var for building group_names 
20        ** ie. engine_N, disk_N */
21        declare @tmp_int int /* temp var for integer storage */
22        declare @tmp_total int /* temp var for integer storage, totals */
23        declare @sum1line char(80) /* string to delimit total lines without 
24        ** percent calc on printout */
25        declare @sum2line char(67) /* string to delimit total lines with percent 
26        ** calc on printout */
27        declare @blankline char(1) /* to print blank line */
28        declare @psign char(3) /* hold a percent sign (%) for print out */
29        declare @na_str char(3) /* holds 'n/a' for 'not applicable' strings */
30        declare @rptline char(80) /* formatted stats line for print statement */
31        declare @section char(80) /* string to delimit sections on printout */
32    
33        /* ------------- Variables for Tuning Recommendations ------------*/
34        declare @reco_no_thread_adjusts int
35        declare @recotxt char(80)
36        declare @recoline char(80)
37        declare @reco_hdr_prn bit
38        declare @reco_total_waits int
39        declare @reco_runtime_adjust int
40        declare @reco_total_queries int
41    
42        /* --------- Setup Environment --------- */
43        set nocount on /* disable row counts being sent to client */
44    
45        select @sum1line = "  -------------------------  ------------  ------------  ----------  ----------"
46        select @sum2line = "  -------------------------  ------------  ------------  ----------"
47        select @blankline = " "
48        select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */
49        select @na_str = "n/a"
50        select @section = "==============================================================================="
51    
52        /* ===================== Parallel Query Management Section ============== */
53    
54        if not exists (select *
55                from #tempmonitors
56                where group_name = "parallel" and
57                    field_name = "pll_total_number_parallel_queries")
58        begin
59            print @blankline
60            return 0
61        end
62    
63        print @section
64        print @blankline
65        /*
66        ** ------ 
67        */
68        print "Parallel Query Management"
69        print "-------------------------"
70        print @blankline
71    
72    
73        select @tmp_total = value, @reco_total_queries = value
74        from #tempmonitors
75        where group_name = "parallel" and
76            field_name = "pll_total_number_parallel_queries"
77    
78        select @reco_no_thread_adjusts = 0
79        select @reco_runtime_adjust = 0
80        select @reco_total_waits = 0
81    
82        print "  Parallel Query Usage            per sec      per xact       count  %% of total"
83        print @sum1line
84        select @rptline = "  Total Parallel Queries" + space(5) +
85            str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
86            space(2) +
87            str(@tmp_total / convert(real, @NumXacts), 12, 1) +
88            space(2) +
89            str(@tmp_total, 10) + space(7) +
90            @na_str
91        print @rptline
92        if @tmp_total > 0
93        begin
94            select @tmp_int = value
95            from #tempmonitors
96            where group_name = "parallel" and
97                field_name = "pll_runtime_adjust_to_thread_limit"
98    
99            select @reco_runtime_adjust = convert(int,
100               100.0 * ((1.0 * @tmp_int) / @reco_total_queries))
101   
102           select @rptline = "  WP Adjustments Made"
103           print @rptline
104   
105           select @rptline = "    Due to WP Limit " + space(9) +
106               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
107               space(2) +
108               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
109               space(2) +
110               str(@tmp_int, 10) + space(5) +
111               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
112               @psign
113           print @rptline
114   
115   
116           select @tmp_int = value
117           from #tempmonitors
118           where group_name = "parallel" and
119               field_name = "pll_runtime_adjust_dueto_nothreads"
120   
121           select @reco_no_thread_adjusts = convert(int,
122               100.0 * ((1.0 * @tmp_int) / @reco_total_queries))
123   
124           select @rptline = "    Due to No WPs" + space(12) +
125               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
126               space(2) +
127               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
128               space(2) +
129               str(@tmp_int, 10) + space(5) +
130               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
131               @psign
132           print @rptline
133       end
134   
135   
136   
137   
138       print @blankline
139       select @tmp_total = sum(value)
140       from #tempmonitors
141       where group_name = "parallel"
142           and field_name like "pll_%lock_%"
143       print "  Merge Lock Requests             per sec      per xact       count  %% of total"
144       print @sum1line
145   
146       if @tmp_total = 0
147       begin
148           select @rptline = "  Total # of Requests                 0.0           0.0           0       n/a"
149           print @rptline
150       end
151       else
152       begin
153           select @rptline = "    Network Buffer Merge Locks"
154           print @rptline
155   
156           select @tmp_int = sum(value)
157           from #tempmonitors
158           where group_name = "parallel" and
159               field_name like "pll_tbm_lock_immed%"
160   
161           select @rptline = "      Granted with no wait" + space(3) +
162               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
163               space(2) +
164               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
165               space(2) +
166               str(@tmp_int, 10) + space(5) +
167               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
168               @psign
169           print @rptline
170           select @tmp_int = sum(value)
171           from #tempmonitors
172           where group_name = "parallel" and
173               field_name like "pll_tbm_lock_waited%"
174   
175           select @rptline = "      Granted after wait" + space(5) +
176               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
177               space(2) +
178               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
179               space(2) +
180               str(@tmp_int, 10) + space(5) +
181               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
182               @psign
183           print @rptline
184           print @blankline
185   
186           select @rptline = "    Result Buffer Merge Locks"
187           print @rptline
188   
189           select @tmp_int = sum(value)
190           from #tempmonitors
191           where group_name = "parallel" and
192               field_name like "pll_rbm_lock_immed%"
193   
194           select @rptline = "      Granted with no wait" + space(3) +
195               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
196               space(2) +
197               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
198               space(2) +
199               str(@tmp_int, 10) + space(5) +
200               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
201               @psign
202           print @rptline
203   
204           select @tmp_int = sum(value)
205           from #tempmonitors
206           where group_name = "parallel" and
207               field_name like "pll_rbm_lock_waited%"
208   
209           select @rptline = "      Granted after wait" + space(5) +
210               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
211               space(2) +
212               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
213               space(2) +
214               str(@tmp_int, 10) + space(5) +
215               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
216               @psign
217           print @rptline
218           print @blankline
219   
220           select @rptline = "    Work Table Merge Locks"
221           print @rptline
222   
223           select @tmp_int = sum(value)
224           from #tempmonitors
225           where group_name = "parallel" and
226               field_name like "pll_wkm_lock_immed%"
227   
228           select @rptline = "      Granted with no wait" + space(3) +
229               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
230               space(2) +
231               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
232               space(2) +
233               str(@tmp_int, 10) + space(5) +
234               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
235               @psign
236           print @rptline
237   
238           select @tmp_int = sum(value)
239           from #tempmonitors
240           where group_name = "parallel" and
241               field_name like "pll_wkm_lock_waited%"
242   
243           select @rptline = "      Granted after wait" + space(5) +
244               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
245               space(2) +
246               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
247               space(2) +
248               str(@tmp_int, 10) + space(5) +
249               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
250               @psign
251           print @rptline
252   
253           print @sum2line
254           select @rptline = "  Total # of Requests" + space(8) +
255               str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
256               space(2) +
257               str(@tmp_total / convert(real, @NumXacts), 12, 1) +
258               space(2) +
259               str(@tmp_total, 10)
260           print @rptline
261       end
262   
263       print @blankline
264       select @tmp_total = sum(value)
265       from #tempmonitors
266       where group_name = "parallel"
267           and field_name like "pll_pipe_%buf_waited"
268   
269       if (@reco_total_queries > 0)
270       begin
271           select @reco_total_waits = convert(int,
272               100.0 * ((1.0 * @tmp_total) / @reco_total_queries))
273       end
274   
275       print "  Sort Buffer Waits               per sec      per xact       count  %% of total"
276       print @sum1line
277   
278       if @tmp_total = 0
279       begin
280           select @rptline = "  Total # of Waits                    0.0           0.0           0       n/a"
281           print @rptline
282       end
283       else
284       begin
285           select @tmp_int = value
286           from #tempmonitors
287           where group_name = "parallel" and
288               field_name = "pll_pipe_allocbuf_waited"
289   
290           select @rptline = "    Producer Waits" + space(11) +
291               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
292               space(2) +
293               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
294               space(2) +
295               str(@tmp_int, 10) + space(5) +
296               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
297               @psign
298   
299           print @rptline
300           select @tmp_int = value
301           from #tempmonitors
302           where group_name = "parallel" and
303               field_name = "pll_pipe_recvbuf_waited"
304   
305           select @rptline = "    Consumer Waits" + space(11) +
306               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
307               space(2) +
308               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
309               space(2) +
310               str(@tmp_int, 10) + space(5) +
311               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
312               @psign
313   
314           print @rptline
315           print @sum2line
316           select @rptline = "  Total # of Waits" + space(11) +
317               str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
318               space(2) +
319               str(@tmp_total / convert(real, @NumXacts), 12, 1) +
320               space(2) +
321               str(@tmp_total, 10)
322           print @rptline
323       end
324       print @blankline
325       if @Reco = 'Y'
326       begin
327           select @recotxt = "  Tuning Recommendations for Parallel Query Management"
328           select @recoline = "  ----------------------------------------------------"
329           select @reco_hdr_prn = 0
330   
331           /*
332           ** If the % of number of thread adjusts due to no threads 
333           ** to the number of parallel queries run is > 10%, 
334           ** then consider increasing the 'number of worker processes' 
335           ** configuration parameter
336           */
337           if (@reco_no_thread_adjusts > 10)
338           begin
339               if (@reco_hdr_prn = 0)
340               begin
341                   print @recotxt
342                   print @recoline
343                   select @reco_hdr_prn = 1
344               end
345               print "  - Consider increasing the 'number of worker processes'"
346               print "    configuration parameter."
347               print @blankline
348           end
349   
350           /*
351           ** If the number of thread adjusts due to worker process limit
352           ** to the number of parallel queries run is > 10% 
353           ** then consider tuning your session level limits
354           */
355           if (@reco_runtime_adjust > 10)
356           begin
357               if (@reco_hdr_prn = 0)
358               begin
359                   print @recotxt
360                   print @recoline
361                   select @reco_hdr_prn = 1
362               end
363               print "  - Consider re-evaluating your session level limts set with"
364               print "   'set scan_parallel_degree' or 'set parallel_degree'."
365               print @blankline
366           end
367           /*
368           ** If the number of waits due to pipe buffers is greater 
369           ** to the number of parallel queries run in > 5%
370           ** consider increasing the 'number of sort buffers'
371           */
372           if (@reco_total_waits > 5)
373           begin
374               if (@reco_hdr_prn = 0)
375               begin
376                   print @recotxt
377                   print @recoline
378                   select @reco_hdr_prn = 1
379               end
380               print "  - Consider increasing the 'number of sort buffers'"
381               print "    configuration parameter."
382               print @blankline
383           end
384       end
385       return 0
386   

DEFECTS
 MLCH 3 Char type with length>30 char(80) 23
 MLCH 3 Char type with length>30 char(67) 25
 MLCH 3 Char type with length>30 char(80) 30
 MLCH 3 Char type with length>30 char(80) 31
 MLCH 3 Char type with length>30 char(80) 35
 MLCH 3 Char type with length>30 char(80) 36
 MUCO 3 Useless Code Useless Brackets 269
 MUCO 3 Useless Code Useless Brackets 337
 MUCO 3 Useless Code Useless Brackets 339
 MUCO 3 Useless Code Useless Brackets 355
 MUCO 3 Useless Code Useless Brackets 357
 MUCO 3 Useless Code Useless Brackets 372
 MUCO 3 Useless Code Useless Brackets 374
 QAFM 3 Var Assignment from potentially many rows 73
 QAFM 3 Var Assignment from potentially many rows 94
 QAFM 3 Var Assignment from potentially many rows 116
 QAFM 3 Var Assignment from potentially many rows 285
 QAFM 3 Var Assignment from potentially many rows 300
 VNRD 3 Variable is not read @reco_hdr_prn 378
 VUNU 3 Variable is not used @NumTaskSwitch 13
 VUNU 3 Variable is not used @KnownTaskSwitch 15
 VUNU 3 Variable is not used @i 17
 VUNU 3 Variable is not used @tmp_grp 19
 MSUB 2 Subquery Marker 54
 MTR1 2 Metrics: Comments Ratio Comments: 13% 6
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 27 = 27dec - 2exi + 2 6
 MTR3 2 Metrics: Query Complexity Complexity: 193 6

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

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