DatabaseProcApplicationCreatedLinks
sybsystemprocssp_sysmon_index  14 déc. 14Defects Propagation Dependencies

1     
2     /* This stored procedure produces a report containing a summary of
3     ** index behavior including deadlocks and page splits.
4     */
5     create or replace procedure sp_sysmon_index
6         @NumElapsedMs int, /* for "per Elapsed second" calculations */
7         @NumXacts int /* for per transactions calculations */
8     as
9     
10        /* --------- declare local variables --------- */
11        declare @tmp_int int /* temp var for integer storage */
12        declare @tmp_total int /* temp var for summing 'total #s' data */
13        declare @sum1line char(80) /* string to delimit total lines without 
14        ** percent calc on printout */
15        declare @sum2line char(80)
16        declare @blankline char(1) /* to print blank line */
17        declare @psign char(3) /* hold a percent sign (%) for print out */
18        declare @na_str char(3) /* holds 'n/a' for 'not applicable' strings */
19        declare @rptline char(80) /* formatted stats line for print statement */
20        declare @section char(80) /* string to delimit sections on printout */
21        /* --------- Setup Environment --------- */
22        set nocount on /* disable row counts being sent to client */
23    
24        select @sum1line = "  -------------------------  ------------  ------------  ----------  ----------"
25        select @sum2line = "                             ------------  ------------  ----------"
26    
27        select @blankline = " "
28        select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */
29        select @na_str = "n/a"
30        select @section = "==============================================================================="
31    
32        print @section
33        print @blankline
34    
35        print "Index Management"
36        print "----------------"
37        print @blankline
38    
39        print "  Nonclustered Maintenance        per sec      per xact       count  %% of total"
40        print @sum1line
41    
42        select @tmp_total = value
43        from #tempmonitors
44        where group_name = 'access' and
45            field_name = "ncupdate"
46    
47        select @rptline = "    Ins/Upd Requiring Maint" + space(2) +
48            str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
49            space(2) +
50            str(@tmp_total / convert(real, @NumXacts), 12, 1) +
51            space(2) +
52            str(@tmp_total, 10) + space(7) +
53            @na_str
54        print @rptline
55    
56        select @tmp_int = value
57        from #tempmonitors
58        where group_name = 'access' and
59            field_name = "ncupdate_indexes"
60    
61        select @rptline = "      # of NC Ndx Maint" + space(6) +
62            str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
63            space(2) +
64            str(@tmp_int / convert(real, @NumXacts), 12, 1) +
65            space(2) +
66            str(@tmp_int, 10) + space(7) +
67            @na_str
68        print @rptline
69    
70        if @tmp_total != 0
71        begin
72            select @rptline = "      Avg NC Ndx Maint / Op" + space(11) +
73                @na_str + space(11) +
74                @na_str + space(2) +
75                str(convert(real, @tmp_int) / @tmp_total, 10, 5) +
76                space(7) +
77                @na_str
78            print @rptline
79        end
80    
81        print @blankline
82    
83        select @tmp_total = value
84        from #tempmonitors where
85            group_name = 'access' and
86            field_name = "ncdelete"
87    
88        select @rptline = "    Deletes Requiring Maint" + space(2) +
89            str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
90            space(2) +
91            str(@tmp_total / convert(real, @NumXacts), 12, 1) +
92            space(2) +
93            str(@tmp_total, 10) + space(7) +
94            @na_str
95        print @rptline
96    
97        select @tmp_int = value
98        from #tempmonitors
99        where group_name = 'access' and
100           field_name = "ncdelete_indexes"
101   
102       select @rptline = "      # of NC Ndx Maint" + space(6) +
103           str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
104           space(2) +
105           str(@tmp_int / convert(real, @NumXacts), 12, 1) +
106           space(2) +
107           str(@tmp_int, 10) + space(7) +
108           @na_str
109       print @rptline
110   
111       if @tmp_total != 0
112       begin
113           select @rptline = "      Avg NC Ndx Maint / Op" + space(11) +
114               @na_str + space(11) +
115               @na_str + space(2) +
116               str(convert(real, @tmp_int) / @tmp_total, 10, 5) +
117               space(7) +
118               @na_str
119           print @rptline
120       end
121   
122       print @blankline
123   
124       select @tmp_total = value
125       from #tempmonitors
126       where group_name = 'access' and
127           field_name = "ncrid_update"
128   
129       select @rptline = "    RID Upd from Clust Split " +
130           str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
131           space(2) +
132           str(@tmp_total / convert(real, @NumXacts), 12, 1) +
133           space(2) +
134           str(@tmp_total, 10) + space(7) +
135           @na_str
136       print @rptline
137   
138       select @tmp_int = value
139       from #tempmonitors
140       where group_name = 'access' and
141           field_name = "ncrid_update_indexes"
142   
143       select @rptline = "      # of NC Ndx Maint" + space(6) +
144           str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
145           space(2) +
146           str(@tmp_int / convert(real, @NumXacts), 12, 1) +
147           space(2) +
148           str(@tmp_int, 10) + space(7) +
149           @na_str
150       print @rptline
151   
152       if @tmp_total != 0
153       begin
154           select @rptline = "      Avg NC Ndx Maint / Op" + space(11) +
155               @na_str + space(11) +
156               @na_str + space(2) +
157               str(convert(real, @tmp_int) / @tmp_total, 10, 5) +
158               space(7) +
159               @na_str
160           print @rptline
161       end
162   
163       print @blankline
164   
165   
166       select @tmp_total = value
167       from #tempmonitors
168       where group_name = 'dolaccess' and
169           field_name = "dolncdelete"
170   
171       select @rptline = "    Upd/Del DOL Req Maint" + space(4) +
172           str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
173           space(2) +
174           str(@tmp_total / convert(real, @NumXacts), 12, 1) +
175           space(2) +
176           str(@tmp_total, 10) + space(7) +
177           @na_str
178       print @rptline
179   
180       select @tmp_int = value
181       from #tempmonitors
182       where group_name = 'dolaccess' and
183           field_name = "dolncdelete_indexes"
184   
185       select @rptline = "      # of DOL Ndx Maint" + space(5) +
186           str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
187           space(2) +
188           str(@tmp_int / convert(real, @NumXacts), 12, 1) +
189           space(2) +
190           str(@tmp_int, 10) + space(7) +
191           @na_str
192       print @rptline
193   
194       if @tmp_total != 0
195       begin
196           select @rptline = "      Avg DOL Ndx Maint / Op" + space(10) +
197               @na_str + space(11) +
198               @na_str + space(2) +
199               str(convert(real, @tmp_int) / @tmp_total, 10, 5) +
200               space(7) +
201               @na_str
202           print @rptline
203       end
204   
205       print @blankline
206   
207       select @tmp_total = sum(value)
208       from #tempmonitors
209       where (group_name = "access" and
210               field_name IN ("split_index", "split_root"))
211           OR (group_name = "btree" and
212               field_name IN ("bt_leafsplit_count", "bt_noleafsplit_count"))
213   
214       select @rptline = "  Page Splits" + space(16) +
215           str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
216           space(2) +
217           str(@tmp_total / convert(real, @NumXacts), 12, 1) +
218           space(2) +
219           str(@tmp_total, 10) + space(7) +
220           @na_str
221       print @rptline
222   
223       if @tmp_total != 0 /* Avoid Divide by Zero Errors */
224       begin
225           select @tmp_int = value
226           from #tempmonitors
227           where group_name = "access" and
228               field_name = "split_index_retry"
229   
230           select @rptline = "    Retries" + space(18) +
231               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
232               space(2) +
233               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
234               space(2) +
235               str(@tmp_int, 10) + space(5) +
236               str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
237           print @rptline
238   
239           select @tmp_int = value
240           from #tempmonitors
241           where group_name = "access" and
242               field_name = "split_index_deadlock"
243   
244           select @rptline = "    Deadlocks" + space(16) +
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(5) +
250               str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
251           print @rptline
252   
253           select @tmp_int = value
254           from #tempmonitors
255           where (group_name = "access" and
256                   field_name = "add_ind_level")
257               OR (group_name = "btree" and
258                   field_name = "bt_add_ind_level")
259   
260           select @rptline = "    Add Index Level" + space(10) +
261               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
262               space(2) +
263               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
264               space(2) +
265               str(@tmp_int, 10) + space(5) +
266               str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
267           print @rptline
268   
269       end
270   
271       print @blankline
272   
273       select @tmp_total = SUM(value)
274       from #tempmonitors
275       where (group_name = "access" and
276               field_name like "shrink%")
277           OR (group_name = "btree" and
278               field_name IN ("bt_shrink_bylastdel", "bt_shrink_byscan",
279                   "bt_shrink_nonleaf"))
280   
281       select @rptline = "  Page Shrinks" + space(15) +
282           str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
283           space(2) +
284           str(@tmp_total / convert(real, @NumXacts), 12, 1) +
285           space(2) +
286           str(@tmp_total, 10) + space(7) +
287           @na_str
288       print @rptline
289   
290   
291       if @tmp_total != 0 /* Avoid Divide by Zero Errors */
292       begin
293           select @tmp_int = SUM(value)
294           from #tempmonitors
295           where group_name = "access" and
296               field_name IN ("am_split_shrink_LOSTP", "am_split_shrink_WDP",
297                   "am_split_shrink_NWFP", "am_split_shrink_WDC",
298                   "am_split_shrink_NWFC", "am_split_shrink_WDNXT",
299                   "am_split_shrink_NWFNX", "am_split_shrink_WDPRV",
300                   "am_split_shrink_NWFPRV")
301   
302   
303           select @rptline = "    Deadlocks" + space(16) +
304               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
305               space(2) +
306               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
307               space(2) +
308               str(@tmp_int, 10) + space(5) +
309               str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
310           print @rptline
311   
312           select @tmp_int = value
313           from #tempmonitors
314           where group_name = "access" and
315               field_name = "split_shrink_retries_exceeded"
316   
317           select @rptline = "    Deadlock Retries Exceeded" +
318               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
319               space(2) +
320               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
321               space(2) +
322               str(@tmp_int, 10) + space(5) +
323               str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
324           print @rptline
325       end
326       if exists (select *
327               from #tempmonitors
328               where group_name = "access" and
329                   field_name = "forward_scans")
330       begin
331           print @blankline
332           print "  Index Scans                     per sec      per xact       count  %% of total"
333           print @sum1line
334   
335           select @tmp_total = sum(value)
336           from #tempmonitors
337           where (group_name = 'access' and
338                   field_name in ("forward_scans", "backward_scans"))
339               OR (group_name = 'btree' and
340                   field_name in ("bt_forward_scans", "bt_backward_scans"))
341   
342           if @tmp_total = 0
343           begin
344               select @rptline = "    Total Scans                       0.0           0.0           0       n/a"
345               print @rptline
346           end
347           else
348           begin
349               select @tmp_int = value
350               from #tempmonitors
351               where group_name = "access" and
352                   field_name = "forward_scans"
353   
354               select @rptline = "    Ascending Scans        " + space(2) +
355                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
356                   space(2) +
357                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
358                   space(2) +
359                   str(@tmp_int, 10) + space(5) +
360                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
361               print @rptline
362   
363               select @tmp_int = value
364               from #tempmonitors
365               where group_name = "btree" and
366                   field_name = "bt_forward_scans"
367   
368               select @rptline = "    DOL Ascending Scans    " + space(2) +
369                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
370                   space(2) +
371                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
372                   space(2) +
373                   str(@tmp_int, 10) + space(5) +
374                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
375               print @rptline
376   
377               select @tmp_int = value
378               from #tempmonitors
379               where group_name = "access" and
380                   field_name = "backward_scans"
381   
382               select @rptline = "    Descending Scans       " + space(2) +
383                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
384                   space(2) +
385                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
386                   space(2) +
387                   str(@tmp_int, 10) + space(5) +
388                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
389               print @rptline
390   
391               select @tmp_int = value
392               from #tempmonitors
393               where group_name = "btree" and
394                   field_name = "bt_backward_scans"
395   
396               select @rptline = "    DOL Descending Scans   " + space(2) +
397                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
398                   space(2) +
399                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
400                   space(2) +
401                   str(@tmp_int, 10) + space(5) +
402                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
403               print @rptline
404   
405               print @sum2line
406   
407               select @rptline = "    Total Scans            " + space(2) +
408                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
409                   space(2) +
410                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
411                   space(2) +
412                   str(@tmp_total, 10)
413               print @rptline
414   
415           end
416       end
417       print @blankline
418       return 0
419   

DEFECTS
 MLCH 3 Char type with length>30 char(80) 13
 MLCH 3 Char type with length>30 char(80) 15
 MLCH 3 Char type with length>30 char(80) 19
 MLCH 3 Char type with length>30 char(80) 20
 QAFM 3 Var Assignment from potentially many rows 42
 QAFM 3 Var Assignment from potentially many rows 56
 QAFM 3 Var Assignment from potentially many rows 83
 QAFM 3 Var Assignment from potentially many rows 97
 QAFM 3 Var Assignment from potentially many rows 124
 QAFM 3 Var Assignment from potentially many rows 138
 QAFM 3 Var Assignment from potentially many rows 166
 QAFM 3 Var Assignment from potentially many rows 180
 QAFM 3 Var Assignment from potentially many rows 225
 QAFM 3 Var Assignment from potentially many rows 239
 QAFM 3 Var Assignment from potentially many rows 253
 QAFM 3 Var Assignment from potentially many rows 312
 QAFM 3 Var Assignment from potentially many rows 349
 QAFM 3 Var Assignment from potentially many rows 363
 QAFM 3 Var Assignment from potentially many rows 377
 QAFM 3 Var Assignment from potentially many rows 391
 QTLO 3 Top-Level OR 209
 QTLO 3 Top-Level OR 255
 QTLO 3 Top-Level OR 275
 QTLO 3 Top-Level OR 337
 MSUB 2 Subquery Marker 326
 MTR1 2 Metrics: Comments Ratio Comments: 6% 5
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 39 = 38dec - 1exi + 2 5
 MTR3 2 Metrics: Query Complexity Complexity: 192 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