DatabaseProcApplicationCreatedLinks
sybsystemprocssp_sysmon_pcache  14 déc. 14Defects Propagation Dependencies

1     
2     /* This stored procedure produces a report containing a summary of
3     ** of how effectively the procedure cache is being utilized.
4     */
5     create or replace procedure sp_sysmon_pcache
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_total int /* temp var for summing 'total #s' data */
14        declare @tmp_reco_total int /* temp var for summing 'total #s' data */
15        declare @sum1line char(80) /* string to delimit total lines without 
16        ** percent calc on printout */
17        declare @blankline char(1) /* to print blank line */
18        declare @psign char(3) /* hold a percent sign (%) for print out */
19        declare @na_str char(3) /* holds 'n/a' for 'not applicable' strings */
20        declare @rptline char(80) /* formatted stats line for print statement */
21        declare @section char(80) /* string to delimit sections on printout */
22    
23        /* ------------- Variables for Tuning Recommendations ------------*/
24        declare @recotxt char(80) /* Header for tuning recommendation */
25        declare @recoline char(80) /* to underline recotxt */
26        declare @reco_hdr_prn bit /* to indicate if the recotxt is already printed */
27        declare @reco_phdr_read_from_disk real
28    
29        /* --------- Setup Environment --------- */
30        set nocount on /* disable row counts being sent to client */
31    
32        select @sum1line = "---------------------------  ------------  ------------  ----------  ----------"
33        select @blankline = " "
34        select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */
35        select @na_str = "n/a"
36        select @section = "==============================================================================="
37    
38        print @section
39        print @blankline
40    
41        print "Procedure Cache Management        per sec      per xact       count  %% of total"
42        print @sum1line
43    
44        select @tmp_total = value
45        from #tempmonitors
46        where group_name = "procmgr" and
47            field_name = "procedure_requests"
48    
49        select @rptline = "  Procedure Requests" + space(9) +
50            str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
51            space(2) +
52            str(@tmp_total / convert(real, @NumXacts), 12, 1) +
53            space(2) +
54            str(@tmp_total, 10) + space(7) +
55            @na_str
56        print @rptline
57    
58        if @tmp_total != 0
59        begin
60            select @tmp_int = value
61            from #tempmonitors
62            where group_name = "procmgr" and
63                field_name = "procedure_reads"
64    
65            select @rptline = "  Procedure Reads from Disk" + space(2) +
66                str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
67                space(2) +
68                str(@tmp_int / convert(real, @NumXacts), 12, 1) +
69                space(2) +
70                str(@tmp_int, 10) + space(5) +
71                str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
72            print @rptline
73    
74            select @reco_phdr_read_from_disk =
75                convert(int, 100.0 * ((1.0 * @tmp_int) / @tmp_total))
76    
77            select @tmp_int = value
78            from #tempmonitors
79            where group_name = "procmgr" and
80                field_name = "procedure_writes"
81    
82            select @rptline = "  Procedure Writes to Disk" + space(3) +
83                str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
84                space(2) +
85                str(@tmp_int / convert(real, @NumXacts), 12, 1) +
86                space(2) +
87                str(@tmp_int, 10) + space(5) +
88                str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
89            print @rptline
90    
91            select @tmp_int = value
92            from #tempmonitors
93            where group_name = "procmgr" and
94                field_name = "procedure_removes"
95    
96            select @rptline = "  Procedure Removals" + space(9) +
97                str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
98                space(2) +
99                str(@tmp_int / convert(real, @NumXacts), 12, 1) +
100               space(2) +
101               str(@tmp_int, 10) + space(7) +
102               @na_str
103   
104           print @rptline
105   
106           select @tmp_reco_total = sum(value)
107           from #tempmonitors
108           where group_name = "procmgr" and
109               field_name in ("proc_recompilation_exec",
110                   "proc_recompilation_comp",
111                   "proc_recompilation_exec_curs",
112                   "proc_recompilation_redef")
113   
114           select @rptline = "  Procedure Recompilations" + space(3) +
115               str(@tmp_reco_total / (@NumElapsedMs / 1000.0), 12, 1) +
116               space(2) +
117               str(@tmp_reco_total / convert(real, @NumXacts), 12, 1) +
118               space(2) +
119               str(@tmp_reco_total, 10) + space(7) +
120               @na_str
121   
122           print @rptline
123   
124           if (@tmp_reco_total != 0)
125           begin
126               print @blankline
127   
128               print "  Recompilations Requests:"
129   
130               select @tmp_int = value
131               from #tempmonitors
132               where group_name = "procmgr" and
133                   field_name = "proc_recompilation_exec"
134   
135               select @rptline = "    Execution Phase" + space(10) +
136                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
137                   space(2) +
138                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
139                   space(2) +
140                   str(@tmp_int, 10) + space(5) +
141                   str(100.0 * @tmp_int / @tmp_reco_total, 5, 1) + @psign
142   
143               print @rptline
144   
145               select @tmp_int = value
146               from #tempmonitors
147               where group_name = "procmgr" and
148                   field_name = "proc_recompilation_comp"
149   
150               select @rptline = "    Compilation Phase" + space(8) +
151                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
152                   space(2) +
153                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
154                   space(2) +
155                   str(@tmp_int, 10) + space(5) +
156                   str(100.0 * @tmp_int / @tmp_reco_total, 5, 1) + @psign
157   
158               print @rptline
159   
160               select @tmp_int = value
161               from #tempmonitors
162               where group_name = "procmgr" and
163                   field_name = "proc_recompilation_exec_curs"
164   
165               select @rptline = "    Execute Cursor Execution" + space(1) +
166                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
167                   space(2) +
168                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
169                   space(2) +
170                   str(@tmp_int, 10) + space(5) +
171                   str(100.0 * @tmp_int / @tmp_reco_total, 5, 1) + @psign
172   
173               print @rptline
174   
175               select @tmp_int = value
176               from #tempmonitors
177               where group_name = "procmgr" and
178                   field_name = "proc_recompilation_redef"
179   
180               select @rptline = "    Redefinition Phase" + space(7) +
181                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
182                   space(2) +
183                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
184                   space(2) +
185                   str(@tmp_int, 10) + space(5) +
186                   str(100.0 * @tmp_int / @tmp_reco_total, 5, 1) + @psign
187   
188               print @rptline
189   
190               print @blankline
191   
192               print "  Recompilation Reasons:"
193   
194               select @tmp_int = value
195               from #tempmonitors
196               where group_name = "procmgr" and
197                   field_name = "proc_recomp_tabmissing"
198   
199               select @rptline = "    Table Missing" + space(12) +
200                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
201                   space(2) +
202                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
203                   space(2) +
204                   str(@tmp_int, 10) + space(7) +
205                   @na_str
206   
207               print @rptline
208   
209               select @tmp_int = value
210               from #tempmonitors
211               where group_name = "procmgr" and
212                   field_name = "proc_recomp_tempmissing"
213   
214               select @rptline = "    Temporary Table Missing" + space(2) +
215                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
216                   space(2) +
217                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
218                   space(2) +
219                   str(@tmp_int, 10) + space(7) +
220                   @na_str
221   
222               print @rptline
223   
224               select @tmp_int = value
225               from #tempmonitors
226               where group_name = "procmgr" and
227                   field_name = "proc_recomp_schemacount"
228   
229               select @rptline = "    Schema Change" + space(12) +
230                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
231                   space(2) +
232                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
233                   space(2) +
234                   str(@tmp_int, 10) + space(7) +
235                   @na_str
236   
237               print @rptline
238   
239               select @tmp_int = value
240               from #tempmonitors
241               where group_name = "procmgr" and
242                   field_name = "proc_recomp_idxchange"
243   
244               select @rptline = "    Index Change" + space(13) +
245                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
246                   space(2) +
247                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
248                   space(2) +
249                   str(@tmp_int, 10) + space(7) +
250                   @na_str
251   
252               print @rptline
253   
254               select @tmp_int = value
255               from #tempmonitors
256               where group_name = "procmgr" and
257                   field_name = "proc_recomp_isolevel"
258   
259               select @rptline = "    Isolation Level Change" + space(3) +
260                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
261                   space(2) +
262                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
263                   space(2) +
264                   str(@tmp_int, 10) + space(7) +
265                   @na_str
266   
267               print @rptline
268   
269               select @tmp_int = value
270               from #tempmonitors
271               where group_name = "procmgr" and
272                   field_name = "proc_recomp_permissions"
273   
274               select @rptline = "    Permissions Change" + space(7) +
275                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
276                   space(2) +
277                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
278                   space(2) +
279                   str(@tmp_int, 10) + space(7) +
280                   @na_str
281   
282               print @rptline
283   
284               select @tmp_int = value
285               from #tempmonitors
286               where group_name = "procmgr" and
287                   field_name = "proc_recomp_cursor_perm"
288   
289               select @rptline = "    Cursor Permissions Change" +
290                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
291                   space(2) +
292                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
293                   space(2) +
294                   str(@tmp_int, 10) + space(7) +
295                   @na_str
296   
297               print @rptline
298   
299               select @tmp_int = value
300               from #tempmonitors
301               where group_name = "procmgr" and
302                   field_name = "proc_recomp_mutable_rowcount"
303   
304               if (@tmp_int > 0)
305               begin
306                   select @rptline = "    Mutable Row Count" + space(8) +
307                       str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
308                       space(2) +
309                       str(@tmp_int / convert(real, @NumXacts), 12, 1) +
310                       space(2) +
311                       str(@tmp_int, 10) + space(7) +
312                       @na_str
313                   print @rptline
314               end
315           end
316       end
317   
318       print @blankline
319   
320       print "  SQL Statement Cache:"
321   
322       select @tmp_total = value
323       from #tempmonitors
324       where group_name = "procmgr" and
325           field_name = "proc_ssql_procs_available"
326   
327       select @rptline = "    Statements Cached" + space(8) +
328           str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
329           space(2) +
330           str(@tmp_total / convert(real, @NumXacts), 12, 1) +
331           space(2) +
332           str(@tmp_total, 10) + space(7) +
333           @na_str
334       print @rptline
335   
336       select @tmp_total = value
337       from #tempmonitors
338       where group_name = "procmgr" and
339           field_name = "proc_ssql_found"
340   
341       select @rptline = "    Statements Found in Cache" +
342           str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
343           space(2) +
344           str(@tmp_total / convert(real, @NumXacts), 12, 1) +
345           space(2) +
346           str(@tmp_total, 10) + space(7) +
347           @na_str
348       print @rptline
349   
350       select @tmp_total = value
351       from #tempmonitors
352       where group_name = "procmgr" and
353           field_name = "proc_ssql_not_found"
354   
355       select @rptline = "    Statements Not Found" + space(5) +
356           str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
357           space(2) +
358           str(@tmp_total / convert(real, @NumXacts), 12, 1) +
359           space(2) +
360           str(@tmp_total, 10) + space(7) +
361           @na_str
362       print @rptline
363   
364       select @tmp_total = value
365       from #tempmonitors
366       where group_name = "procmgr" and
367           field_name = "proc_ssql_dropped"
368   
369       select @rptline = "    Statements Dropped" + space(7) +
370           str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
371           space(2) +
372           str(@tmp_total / convert(real, @NumXacts), 12, 1) +
373           space(2) +
374           str(@tmp_total, 10) + space(7) +
375           @na_str
376       print @rptline
377   
378       select @tmp_total = value
379       from #tempmonitors
380       where group_name = "procmgr" and
381           field_name = "proc_ssql_restored"
382   
383       select @rptline = "    Statements Restored" + space(6) +
384           str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
385           space(2) +
386           str(@tmp_total / convert(real, @NumXacts), 12, 1) +
387           space(2) +
388           str(@tmp_total, 10) + space(7) +
389           @na_str
390       print @rptline
391   
392       select @tmp_total = value
393       from #tempmonitors
394       where group_name = "procmgr" and
395           field_name = "proc_ssql_notcached"
396   
397       select @rptline = "    Statements Not Cached" + space(4) +
398           str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
399           space(2) +
400           str(@tmp_total / convert(real, @NumXacts), 12, 1) +
401           space(2) +
402           str(@tmp_total, 10) + space(7) +
403           @na_str
404       print @rptline
405   
406       print @blankline
407       if @Reco = 'Y'
408       begin
409           select @recotxt = "  Tuning Recommendations for Procedure cache management"
410           select @recoline = "  -----------------------------------------------------"
411           select @reco_hdr_prn = 0
412   
413           /*
414           ** If % of the number of times a procedure was read from disk is
415           ** is > 10% consider increasing the procedure cache size
416           */
417           if @reco_phdr_read_from_disk > 10
418           begin
419               if (@reco_hdr_prn = 0)
420               begin
421                   print @recotxt
422                   print @recoline
423                   select @reco_hdr_prn = 1
424               end
425   
426               print "  - Consider increasing the 'procedure cache size'"
427               print "    configuration parameter."
428               print @blankline
429           end
430       end
431   
432   
433       print @blankline
434       return
435   

DEFECTS
 MLCH 3 Char type with length>30 char(80) 15
 MLCH 3 Char type with length>30 char(80) 20
 MLCH 3 Char type with length>30 char(80) 21
 MLCH 3 Char type with length>30 char(80) 24
 MLCH 3 Char type with length>30 char(80) 25
 MUCO 3 Useless Code Useless Brackets 124
 MUCO 3 Useless Code Useless Brackets 304
 MUCO 3 Useless Code Useless Brackets 419
 QAFM 3 Var Assignment from potentially many rows 44
 QAFM 3 Var Assignment from potentially many rows 60
 QAFM 3 Var Assignment from potentially many rows 77
 QAFM 3 Var Assignment from potentially many rows 91
 QAFM 3 Var Assignment from potentially many rows 130
 QAFM 3 Var Assignment from potentially many rows 145
 QAFM 3 Var Assignment from potentially many rows 160
 QAFM 3 Var Assignment from potentially many rows 175
 QAFM 3 Var Assignment from potentially many rows 194
 QAFM 3 Var Assignment from potentially many rows 209
 QAFM 3 Var Assignment from potentially many rows 224
 QAFM 3 Var Assignment from potentially many rows 239
 QAFM 3 Var Assignment from potentially many rows 254
 QAFM 3 Var Assignment from potentially many rows 269
 QAFM 3 Var Assignment from potentially many rows 284
 QAFM 3 Var Assignment from potentially many rows 299
 QAFM 3 Var Assignment from potentially many rows 322
 QAFM 3 Var Assignment from potentially many rows 336
 QAFM 3 Var Assignment from potentially many rows 350
 QAFM 3 Var Assignment from potentially many rows 364
 QAFM 3 Var Assignment from potentially many rows 378
 QAFM 3 Var Assignment from potentially many rows 392
 VNRD 3 Variable is not read @reco_hdr_prn 423
 MTR1 2 Metrics: Comments Ratio Comments: 9% 5
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 31 = 30dec - 1exi + 2 5
 MTR3 2 Metrics: Query Complexity Complexity: 193 5

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

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