DatabaseProcApplicationCreatedLinks
sybsystemprocssp_sysmon_recovery  31 Aug 14Defects Dependencies

1     
2     /* This stored procedure produces a report containing a summary of
3     ** checkpoint and housekeeper activity.
4     */
5     create procedure sp_sysmon_recovery
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_int2 int /* temp var for integer storage */
13        declare @tmp_total int /* temp var for summing 'total #s' data */
14        declare @sum1line char(80) /* string to delimit total lines without 
15        ** percent calc on printout */
16        declare @sum2line char(80) /* string to delimit total lines without 
17        ** percent calc on printout */
18        declare @blankline char(1) /* to print blank line */
19        declare @psign char(3) /* hold a percent sign (%) for print out */
20        declare @na_str char(3) /* holds 'n/a' for 'not applicable' strings */
21        declare @rptline char(80) /* formatted stats line for print statement */
22        declare @section char(80) /* string to delimit sections on printout */
23    
24        /* --------- Setup Environment --------- */
25        set nocount on /* disable row counts being sent to client */
26    
27        select @sum1line = "  -------------------------  ------------  ------------  ----------  ----------"
28        select @sum2line = "  -------------------------  ------------  ------------  ----------"
29        select @blankline = " "
30        select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */
31        select @na_str = "n/a"
32        select @section = "==============================================================================="
33    
34        print @section
35        print @blankline
36    
37        print "Recovery Management"
38        print "-------------------"
39        print @blankline
40    
41        print "  Checkpoints                     per sec      per xact       count  %% of total"
42        print @sum1line
43    
44        select @tmp_int = value
45        from #tempmonitors
46        where group_name = "access" and
47            field_name = "normal_database_checkpoints"
48    
49        select @tmp_int2 = value
50        from #tempmonitors
51        where group_name = "housekeeper" and
52            field_name = "free_database_checkpoints"
53    
54        select @tmp_total = @tmp_int + @tmp_int2
55    
56        if @tmp_total = 0
57        begin
58            select @rptline = "  Total Checkpoints                   0.0           0.0           0       n/a"
59            print @rptline
60        end
61        else
62        begin
63            select @rptline = "    # of Normal Checkpoints" + space(2) +
64                str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
65                space(2) +
66                str(@tmp_int / convert(real, @NumXacts), 12, 1) +
67                space(2) +
68                str(@tmp_int, 10) + space(5) +
69                str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
70            print @rptline
71    
72            select @rptline = "    # of Free Checkpoints" + space(4) +
73                str(@tmp_int2 / (@NumElapsedMs / 1000.0), 12, 1) +
74                space(2) +
75                str(@tmp_int2 / convert(real, @NumXacts), 12, 1) +
76                space(2) +
77                str(@tmp_int2, 10) + space(5) +
78                str(100.0 * @tmp_int2 / @tmp_total, 5, 1) + @psign
79            print @rptline
80    
81            print @sum2line
82            select @rptline = "  Total Checkpoints" + space(10) +
83                str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
84                space(2) +
85                str(@tmp_total / convert(real, @NumXacts), 12, 1) +
86                space(2) +
87                str(@tmp_total, 10)
88            print @rptline
89            print @blankline
90    
91            select @tmp_total = value
92            from #tempmonitors
93            where group_name = "access" and
94                field_name = "time_todo_normal_checkpoints"
95    
96    
97            if @tmp_int != 0 /* any normal checkpoints ? */
98            begin
99                select @rptline = "  Avg Time per Normal Chkpt" + space(2) +
100                   str(convert(real, @tmp_total) / @tmp_int, 12, 5) +
101                   " seconds"
102               print @rptline
103           end
104   
105           select @tmp_total = value
106           from #tempmonitors
107           where group_name = "housekeeper" and
108               field_name = "time_todo_free_checkpoints"
109   
110   
111           if @tmp_int2 != 0 /* any housekeeper checkpoints ? */
112           begin
113               select @rptline = "  Avg Time per Free Chkpt" + space(4) +
114                   str(convert(real, @tmp_total) / @tmp_int2, 12, 5) +
115                   " seconds"
116               print @rptline
117           end
118       end
119   
120       print @blankline
121       return 0
122   


exec sp_procxmode 'sp_sysmon_recovery', 'AnyMode'
go

Grant Execute on sp_sysmon_recovery to public
go
DEFECTS
 MGTP 3 Grant to public sybsystemprocs..sp_sysmon_recovery  
 MLCH 3 Char type with length>30 char(80) 14
 MLCH 3 Char type with length>30 char(80) 16
 MLCH 3 Char type with length>30 char(80) 21
 MLCH 3 Char type with length>30 char(80) 22
 QAFM 3 Var Assignment from potentially many rows 44
 QAFM 3 Var Assignment from potentially many rows 49
 QAFM 3 Var Assignment from potentially many rows 91
 QAFM 3 Var Assignment from potentially many rows 105
 VNRD 3 Variable is not read @na_str 31
 MTR1 2 Metrics: Comments Ratio Comments: 21% 5
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 8 = 7dec - 1exi + 2 5
 MTR3 2 Metrics: Query Complexity Complexity: 64 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