DatabaseProcApplicationCreatedLinks
sybsystemprocssp_sysmon_pcache  31 Aug 14Defects 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 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           end
299       end
300   
301       print @blankline
302   
303       print "  SQL Statement Cache:"
304   
305       select @tmp_total = value
306       from #tempmonitors
307       where group_name = "procmgr" and
308           field_name = "proc_ssql_procs_available"
309   
310       select @rptline = "    Statements Cached" + space(8) +
311           str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
312           space(2) +
313           str(@tmp_total / convert(real, @NumXacts), 12, 1) +
314           space(2) +
315           str(@tmp_total, 10) + space(7) +
316           @na_str
317       print @rptline
318   
319       select @tmp_total = value
320       from #tempmonitors
321       where group_name = "procmgr" and
322           field_name = "proc_ssql_found"
323   
324       select @rptline = "    Statements Found in Cache" +
325           str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
326           space(2) +
327           str(@tmp_total / convert(real, @NumXacts), 12, 1) +
328           space(2) +
329           str(@tmp_total, 10) + space(7) +
330           @na_str
331       print @rptline
332   
333       select @tmp_total = value
334       from #tempmonitors
335       where group_name = "procmgr" and
336           field_name = "proc_ssql_not_found"
337   
338       select @rptline = "    Statements Not Found" + space(5) +
339           str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
340           space(2) +
341           str(@tmp_total / convert(real, @NumXacts), 12, 1) +
342           space(2) +
343           str(@tmp_total, 10) + space(7) +
344           @na_str
345       print @rptline
346   
347       select @tmp_total = value
348       from #tempmonitors
349       where group_name = "procmgr" and
350           field_name = "proc_ssql_dropped"
351   
352       select @rptline = "    Statements Dropped" + space(7) +
353           str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
354           space(2) +
355           str(@tmp_total / convert(real, @NumXacts), 12, 1) +
356           space(2) +
357           str(@tmp_total, 10) + space(7) +
358           @na_str
359       print @rptline
360   
361       select @tmp_total = value
362       from #tempmonitors
363       where group_name = "procmgr" and
364           field_name = "proc_ssql_restored"
365   
366       select @rptline = "    Statements Restored" + space(6) +
367           str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
368           space(2) +
369           str(@tmp_total / convert(real, @NumXacts), 12, 1) +
370           space(2) +
371           str(@tmp_total, 10) + space(7) +
372           @na_str
373       print @rptline
374   
375       select @tmp_total = value
376       from #tempmonitors
377       where group_name = "procmgr" and
378           field_name = "proc_ssql_notcached"
379   
380       select @rptline = "    Statements Not Cached" + space(4) +
381           str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
382           space(2) +
383           str(@tmp_total / convert(real, @NumXacts), 12, 1) +
384           space(2) +
385           str(@tmp_total, 10) + space(7) +
386           @na_str
387       print @rptline
388   
389       print @blankline
390       if @Reco = 'Y'
391       begin
392           select @recotxt = "  Tuning Recommendations for Procedure cache management"
393           select @recoline = "  -----------------------------------------------------"
394           select @reco_hdr_prn = 0
395   
396           /*
397           ** If % of the number of times a procedure was read from disk is
398           ** is > 10% consider increasing the procedure cache size
399           */
400           if @reco_phdr_read_from_disk > 10
401           begin
402               if (@reco_hdr_prn = 0)
403               begin
404                   print @recotxt
405                   print @recoline
406                   select @reco_hdr_prn = 1
407               end
408   
409               print "  - Consider increasing the 'procedure cache size'"
410               print "    configuration parameter."
411               print @blankline
412           end
413       end
414   
415   
416       print @blankline
417       return
418   


exec sp_procxmode 'sp_sysmon_pcache', 'AnyMode'
go

Grant Execute on sp_sysmon_pcache to public
go
DEFECTS
 MGTP 3 Grant to public sybsystemprocs..sp_sysmon_pcache  
 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 402
 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 305
 QAFM 3 Var Assignment from potentially many rows 319
 QAFM 3 Var Assignment from potentially many rows 333
 QAFM 3 Var Assignment from potentially many rows 347
 QAFM 3 Var Assignment from potentially many rows 361
 QAFM 3 Var Assignment from potentially many rows 375
 VNRD 3 Variable is not read @reco_hdr_prn 406
 MTR1 2 Metrics: Comments Ratio Comments: 9% 5
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 28 = 27dec - 1exi + 2 5
 MTR3 2 Metrics: Query Complexity Complexity: 185 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