Database | Proc | Application | Created | Links |
sybsystemprocs | sp_sysmon_recovery | 31 Aug 14 | Defects 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 |