DatabaseProcApplicationCreatedLinks
sybsystemprocssp_sysmon_wpm  31 Aug 14Defects Dependencies

1     
2     /* This stored procedure produces a report containing a summary of
3     ** SQL Server worker process activity.
4     */
5     create procedure sp_sysmon_wpm
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 @tmp_int int /* temp var for integer storage */
13        declare @tmp_int2 int /* temp var for integer storage */
14        declare @tmp_int3 int /* temp var for integer storage */
15        declare @tmp_total int /* temp var for summing 'total #s' data */
16        declare @sum1line char(80) /* string to delimit total lines without 
17        ** percent calc on printout */
18        declare @sum2line char(80)
19        declare @blankline char(1) /* to print blank line */
20        declare @psign char(3) /* hold a percent sign (%) for print out */
21        declare @na_str char(3) /* holds 'n/a' for 'not applicable' strings */
22        declare @rptline char(80) /* formatted statistics line for print 
23        ** statement */
24        declare @section char(80) /* string to delimit sections on printout */
25        /* ------------- Variables for Tuning Recommendations ------------*/
26        declare @recotxt char(80)
27        declare @recoline char(80)
28        declare @reco_hdr_prn bit
29    
30        /* --------- Setup Environment --------- */
31        set nocount on /* disable row counts being sent to client */
32    
33        select @blankline = " "
34        select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */
35        select @na_str = "n/a"
36        select @sum1line = " --------------------------  ------------  ------------  ----------"
37        select @sum2line = "                             ------------  ------------  ----------  ----------"
38        select @section = "==============================================================================="
39    
40        /* ======================= Worker Process Management Section =================== */
41    
42        /* This subordinate should not appear for pre 11.1 servers */
43    
44        if not exists (select *
45                from #tempmonitors
46                where group_name = "kernel" and
47                    field_name = "wtm_connect_requests")
48        begin
49            print @blankline
50            return 0
51        end
52    
53        print @section
54        print @blankline
55        print "Worker Process Management"
56        print "-------------------------"
57        print "                                  per sec      per xact       count  %% of total"
58        print @sum2line
59        print " Worker Process Requests"
60    
61    
62        select @tmp_total = value
63        from #tempmonitors
64        where group_name = "kernel" and
65            field_name = "wtm_connect_requests"
66    
67    
68    
69        if @tmp_total = 0 /* Avoid divide by zero errors - just print zero's */
70        begin
71            select @rptline = "   Total Requests                     0.0           0.0           0       n/a"
72            print @rptline
73        end
74        else
75        begin
76            select @tmp_int = value
77            from #tempmonitors
78            where group_name = "kernel" and
79                field_name = "wtm_denied_requests"
80    
81            select @tmp_int2 = @tmp_total - @tmp_int
82    
83            select @rptline = "  Requests Granted " + space(10) +
84                str(@tmp_int2 / (@NumElapsedMs / 1000.0), 12, 1)
85                + space(2) +
86                str(@tmp_int2 / convert(real, @NumXacts), 12, 1)
87                + space(2) +
88                str(@tmp_int2, 10) + space(5) +
89                str(100.0 * @tmp_int2 / @tmp_total, 5, 1)
90                + @psign
91            print @rptline
92    
93            select @rptline = "  Requests Denied  " + space(10) +
94                str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1)
95                + space(2) +
96                str(@tmp_int / convert(real, @NumXacts), 12, 1)
97                + space(2) +
98                str(@tmp_int, 10) + space(5) +
99                str(100.0 * @tmp_int / @tmp_total, 5, 1)
100               + @psign
101           print @rptline
102   
103           print @sum1line
104   
105           select @rptline = "  Total Requests " + space(12) +
106               str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1)
107               + space(2) +
108               str(@tmp_total / convert(real, @NumXacts), 12, 1)
109               + space(2) +
110               str(@tmp_total, 10)
111           print @rptline
112   
113           print @blankline
114   
115           select @tmp_int = value
116           from #tempmonitors
117           where group_name = "kernel" and
118               field_name = "wtm_terminations"
119   
120           select @rptline = "  Requests Terminated " + space(7) +
121               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1)
122               + space(2) +
123               str(@tmp_int / convert(real, @NumXacts), 12, 1)
124               + space(2) +
125               str(@tmp_int, 10) + space(5) +
126               str(100.0 * @tmp_int / @tmp_total, 5, 1)
127               + @psign
128           print @rptline
129   
130       end
131   
132       print @blankline
133       print " Worker Process Usage"
134   
135       select @tmp_int = value
136       from #tempmonitors
137       where group_name = "kernel" and
138           field_name = "wtm_thread_requests"
139   
140       select @rptline = "   Total Used" + space(17) +
141           str(@tmp_int / (@NumElapsedMs / 1000.0), 11, 1)
142           + space(3) +
143           str(@tmp_int / convert(real, @NumXacts), 11, 1)
144           + space(2) +
145           str(@tmp_int, 10) + space(7) +
146           @na_str
147   
148       print @rptline
149   
150       select @tmp_int3 = value
151       from #tempmonitors
152       where group_name = "kernel" and
153           field_name = "wtm_high_water_mark"
154   
155       select @rptline = "   Max Ever Used During Sample" +
156           str(@tmp_int3 / (@NumElapsedMs / 1000.0), 11, 1)
157           + space(3) +
158           str(@tmp_int3 / convert(real, @NumXacts), 11, 1)
159           + space(2) +
160           str(@tmp_int3, 10) + space(7) +
161           @na_str
162       print @rptline
163   
164       print @blankline
165       print " Memory Requests for Worker Processes"
166   
167       select @tmp_total = value
168       from #tempmonitors
169       where group_name = "kernel" and
170           field_name = "wtm_memory_allocs"
171   
172       if @tmp_total = 0 /* Avoid divide by zero errors - just print zero's */
173       begin
174           select @rptline = "   Total Requests                     0.0           0.0           0       n/a"
175           print @rptline
176       end
177       else
178       begin
179           select @tmp_int = value
180           from #tempmonitors
181           where group_name = "kernel" and
182               field_name = "wtm_memory_allocs_failed"
183   
184           select @tmp_int2 = @tmp_total - @tmp_int
185   
186           select @rptline = "   Succeeded     " + space(12) +
187               str(@tmp_int2 / (@NumElapsedMs / 1000.0), 12, 1)
188               + space(2) +
189               str(@tmp_int2 / convert(real, @NumXacts), 12, 1)
190               + space(2) +
191               str(@tmp_int2, 10) + space(5) +
192               str(100.0 * @tmp_int2 / @tmp_total, 5, 1)
193               + @psign
194           print @rptline
195   
196           select @rptline = "   Failed        " + space(12) +
197               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1)
198               + space(2) +
199               str(@tmp_int / convert(real, @NumXacts), 12, 1)
200               + space(2) +
201               str(@tmp_int, 10) + space(5) +
202               str(100.0 * @tmp_int / @tmp_total, 5, 1)
203               + @psign
204           print @rptline
205   
206           print @sum1line
207   
208           select @rptline = "   Total Requests" + space(12) +
209               str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1)
210               + space(2) +
211               str(@tmp_total / convert(real, @NumXacts), 12, 1)
212               + space(2) +
213               str(@tmp_total, 10)
214           print @rptline
215   
216   
217           print @blankline
218   
219           if (@tmp_int3 > 0)
220           begin
221   
222               select @tmp_int = value
223               from #tempmonitors
224               where group_name = "kernel" and
225                   field_name = "wtm_mem_alloc_hwm"
226   
227               select @rptline = " Avg Mem Ever Used by a WP (in bytes)" + space(1) +
228                   @na_str +
229                   + space(11) +
230                   @na_str +
231                   + space(2) +
232                   str(@tmp_int / convert(real, @tmp_int3), 10, 1) + space(7) +
233                   @na_str
234               print @rptline
235           end
236       end
237   
238   
239       /* If requested, print worker process recommendations (if any) */
240   
241       if (@Reco = 'Y')
242       begin
243           print @blankline
244           select @recotxt = " Tuning Recommendations for Worker Processes"
245           select @recoline = " -------------------------------------------"
246           select @reco_hdr_prn = 0
247   
248           select @tmp_total = 0
249           select @tmp_int = 0
250           select @tmp_int2 = 0
251   
252           select @tmp_int = convert(integer, value) from #tempconfigures
253           where name like "%number of worker processes%"
254   
255           if (@tmp_int > 0)
256           begin
257               select @tmp_int2 = value from #tempmonitors
258               where group_name = "kernel" and
259                   field_name = "wtm_high_water_mark"
260   
261               select @tmp_total = convert(int, (100.0 * a.value / b.value))
262               from #tempmonitors a, #tempmonitors b
263               where a.group_name = "kernel"
264                   and a.field_name = "wtm_denied_requests"
265                   and b.group_name = "kernel"
266                   and b.field_name = "wtm_connect_requests"
267                   and b.value != 0
268   
269               /*
270               ** If the number of worker processes configured is less
271               ** than the high water mark, consider increasing
272               ** the 'number of worker processes'
273               */
274               if (@tmp_int <= @tmp_int2 and @tmp_total > 80)
275               begin
276                   if (@reco_hdr_prn = 0)
277                   begin
278                       print @recotxt
279                       print @recoline
280                       select @reco_hdr_prn = 1
281                   end
282                   print "  - Consider increasing the 'number of worker processes'"
283                   print "    configuration parameter."
284                   print @blankline
285               end
286               else
287               if ((100.0 * @tmp_int2 / @tmp_int) <= 25 and @tmp_total = 0)
288               begin
289                   if (@reco_hdr_prn = 0)
290                   begin
291                       print @recotxt
292                       print @recoline
293                       select @reco_hdr_prn = 1
294                   end
295                   print "  - Consider decreasing the 'number of worker processes'"
296                   print "    configuration parameter."
297                   print @blankline
298               end
299   
300           end
301   
302           select @tmp_total = 0
303           select @tmp_int2 = 0
304   
305           select @tmp_int = value from #tempmonitors
306           where group_name = "kernel" and
307               field_name = "wtm_high_water_mark"
308   
309           if (@tmp_int > 0)
310           begin
311               select @tmp_int2 = convert(integer, value) from #tempconfigures
312               where name like "%memory per worker process%"
313   
314               if (@tmp_int2 > 0)
315               begin
316                   select @tmp_int3 = convert(integer, value / @tmp_int) from #tempmonitors
317                   where group_name = "kernel" and
318                       field_name = "wtm_mem_alloc_hwm"
319   
320                   select @tmp_total = convert(int, (100.0 * a.value / b.value))
321                   from #tempmonitors a, #tempmonitors b
322                   where a.group_name = "kernel"
323                       and a.field_name = "wtm_memory_allocs_failed"
324                       and b.group_name = "kernel"
325                       and b.field_name = "wtm_memory_allocs"
326                       and b.value != 0
327   
328                   /*
329                   ** If the % of times worker thread memory
330                   ** allocations failed to the number of allocations
331                   ** is > 80% then consider increasing the 
332                   ** 'memory per worker process' configuration parameter
333                   ** else consider decreasing the 'memory per worker process'
334                   ** configuration parameter.
335                   */
336                   if (@tmp_int2 <= @tmp_int3 and @tmp_total > 80)
337                   begin
338                       if (@reco_hdr_prn = 0)
339                       begin
340                           print @recotxt
341                           print @recoline
342                           select @reco_hdr_prn = 1
343                       end
344                       print "  - Consider increasing the 'memory per worker process'"
345                       print "    configuration parameter."
346                       print @blankline
347                   end
348                   else
349                   if ((@tmp_int2 - @tmp_int3) > 512 and @tmp_total = 0)
350                   begin
351                       if (@reco_hdr_prn = 0)
352                       begin
353                           print @recotxt
354                           print @recoline
355                           select @reco_hdr_prn = 1
356                       end
357                       print "  - Consider decreasing the 'memory per worker process'"
358                       print "    configuration parameter."
359                       print @blankline
360                   end
361               end
362           end
363       end
364   
365       print @blankline
366       return 0
367   


exec sp_procxmode 'sp_sysmon_wpm', 'AnyMode'
go

Grant Execute on sp_sysmon_wpm to public
go
DEFECTS
 QCAR 6 Cartesian product between tables #tempmonitors a and [#tempmonitors b] 262
 QCAR 6 Cartesian product between tables #tempmonitors a and [#tempmonitors b] 321
 MGTP 3 Grant to public sybsystemprocs..sp_sysmon_wpm  
 MLCH 3 Char type with length>30 char(80) 16
 MLCH 3 Char type with length>30 char(80) 18
 MLCH 3 Char type with length>30 char(80) 22
 MLCH 3 Char type with length>30 char(80) 24
 MLCH 3 Char type with length>30 char(80) 26
 MLCH 3 Char type with length>30 char(80) 27
 MUCO 3 Useless Code Useless Brackets 219
 MUCO 3 Useless Code Useless Brackets 241
 MUCO 3 Useless Code Useless Brackets 255
 MUCO 3 Useless Code Useless Brackets 261
 MUCO 3 Useless Code Useless Brackets 274
 MUCO 3 Useless Code Useless Brackets 276
 MUCO 3 Useless Code Useless Brackets 287
 MUCO 3 Useless Code Useless Brackets 289
 MUCO 3 Useless Code Useless Brackets 309
 MUCO 3 Useless Code Useless Brackets 314
 MUCO 3 Useless Code Useless Brackets 320
 MUCO 3 Useless Code Useless Brackets 336
 MUCO 3 Useless Code Useless Brackets 338
 MUCO 3 Useless Code Useless Brackets 349
 MUCO 3 Useless Code Useless Brackets 351
 QAFM 3 Var Assignment from potentially many rows 62
 QAFM 3 Var Assignment from potentially many rows 76
 QAFM 3 Var Assignment from potentially many rows 115
 QAFM 3 Var Assignment from potentially many rows 135
 QAFM 3 Var Assignment from potentially many rows 150
 QAFM 3 Var Assignment from potentially many rows 167
 QAFM 3 Var Assignment from potentially many rows 179
 QAFM 3 Var Assignment from potentially many rows 222
 QAFM 3 Var Assignment from potentially many rows 252
 QAFM 3 Var Assignment from potentially many rows 257
 QAFM 3 Var Assignment from potentially many rows 261
 QAFM 3 Var Assignment from potentially many rows 305
 QAFM 3 Var Assignment from potentially many rows 311
 QAFM 3 Var Assignment from potentially many rows 316
 QAFM 3 Var Assignment from potentially many rows 320
 QNAJ 3 Not using ANSI Inner Join 262
 QNAJ 3 Not using ANSI Inner Join 321
 VNRD 3 Variable is not read @reco_hdr_prn 355
 MSUB 2 Subquery Marker 44
 MTR1 2 Metrics: Comments Ratio Comments: 14% 5
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 40 = 40dec - 2exi + 2 5
 MTR3 2 Metrics: Query Complexity Complexity: 205 5

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

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