| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_sysmon_hk | | 31 Aug 14 | Defects Dependencies |
1 2 /* This stored procedure produces a report containing a summary of 3 ** SQL Server housekeeper activities. 4 */ 5 create procedure sp_sysmon_hk 6 @NumElapsedMs int, /* for "per Elapsed second" calculations */ 7 @NumXacts int, /* for per transactions calculations */ 8 @Reco char(1), /* Flag for recommendations */ 9 @instid smallint = NULL /* optional SDC instance id */ 10 as 11 12 /* --------- declare local variables --------- */ 13 declare @tmp_int int /* temp var for integer storage */ 14 declare @tmp_int2 int /* temp var for integer storage */ 15 declare @tmp_total int /* temp var for summing 'total #s' data */ 16 declare @sum2line char(80) 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 statistics line for print 21 ** statement */ 22 declare @section char(80) /* string to delimit sections on printout */ 23 /* ------------- Variables for Tuning Recommendations ------------*/ 24 declare @recotxt char(80) 25 declare @recoline char(80) 26 declare @reco_hdr_prn bit 27 declare @char_str varchar(30) 28 declare @char_trimmed varchar(30) 29 declare @engines tinyint 30 declare @eng_count int 31 declare @tmp_grp_name varchar(80) 32 declare @reco_avg_busy real /* Avg cpu busy utilization */ 33 declare @reco_percent real /* temp var for percentage storage */ 34 declare @reco_idle_ticks real /* temp var for percentage storage */ 35 declare @reco_clock_ticks real /* temp var for percentage storage */ 36 declare @reco_dirty real 37 declare @reco_clean real 38 39 declare @i int 40 41 /* --------- Setup Environment --------- */ 42 set nocount on /* disable row counts being sent to client */ 43 44 select @blankline = " " 45 select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */ 46 select @na_str = "n/a" 47 select @sum2line = " ------------ ------------ ----------" 48 select @section = "===============================================================================" 49 50 /* ======================= Housekeeper Section =================== */ 51 print @section 52 print @blankline 53 print "Housekeeper Task Activity" 54 print "-------------------------" 55 print " per sec per xact count %% of total" 56 print @sum2line 57 58 select @tmp_total = sum(value) 59 from #tempmonitors 60 where group_name like "buffer_%" and 61 field_name = "hk_wash" 62 63 if (@tmp_total > 0) 64 begin 65 select @rptline = "Buffer Cache Washes" 66 67 print @rptline 68 69 select @tmp_int2 = sum(value) 70 from #tempmonitors 71 where group_name like "buffer_%" and 72 field_name = "hk_washclean" 73 74 select @rptline = " Clean" + space(22) + 75 str(@tmp_int2 / (@NumElapsedMs / 1000.0), 11, 1) 76 + space(2) + 77 str(@tmp_int2 / convert(real, @NumXacts), 12, 1) 78 + space(2) + 79 str(@tmp_int2, 10) + space(5) + 80 str(100.0 * @tmp_int2 / @tmp_total, 5, 1) + @psign 81 82 select @reco_clean = convert(int, 100.0 * ((1.0 * @tmp_int2) / @tmp_total)) 83 84 print @rptline 85 86 select @tmp_int = @tmp_total - @tmp_int2 87 88 select @rptline = " Dirty" + space(22) + 89 str(@tmp_int / (@NumElapsedMs / 1000.0), 11, 1) 90 + space(2) + 91 str(@tmp_int / convert(real, @NumXacts), 12, 1) 92 + space(2) + 93 str(@tmp_int, 10) + space(5) + 94 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 95 96 select @reco_dirty = convert(int, 100.0 * ((1.0 * @tmp_int2) / @tmp_total)) 97 98 print @rptline 99 100 print @sum2line 101 102 select @rptline = "Total Washes" + space(17) + 103 str(@tmp_total / (@NumElapsedMs / 1000.0), 11, 1) 104 + space(2) + 105 str(@tmp_total / convert(real, @NumXacts), 12, 1) 106 + space(2) + 107 str(@tmp_total, 10) 108 print @rptline 109 110 print @blankline 111 end 112 113 select @tmp_int = value 114 from #tempmonitors 115 where group_name = "housekeeper" and 116 field_name = "hk_gc_wakes" 117 118 select @rptline = "Garbage Collections" + space(9) + 119 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) 120 + space(2) + 121 str(@tmp_int / convert(real, @NumXacts), 12, 1) 122 + space(2) + 123 str(@tmp_int, 10) + space(7) + 124 @na_str 125 print @rptline 126 127 select @tmp_int = value 128 from #tempmonitors 129 where group_name = "housekeeper" and 130 field_name = "hk_gc_numgoodpages" 131 132 select @rptline = "Pages Processed in GC" + space(8) + 133 str(@tmp_int / (@NumElapsedMs / 1000.0), 11, 1) 134 + space(2) + 135 str(@tmp_int / convert(real, @NumXacts), 12, 1) 136 + space(2) + 137 str(@tmp_int, 10) + space(7) + 138 @na_str 139 print @rptline 140 141 print @blankline 142 143 select @tmp_int = value 144 from #tempmonitors 145 where group_name = "housekeeper" and 146 field_name = "hk_stats_wakes" 147 148 select @rptline = "Statistics Updates" + space(11) + 149 str(@tmp_int / (@NumElapsedMs / 1000.0), 11, 1) 150 + space(2) + 151 str(@tmp_int / convert(real, @NumXacts), 12, 1) 152 + space(2) + 153 str(@tmp_int, 10) + space(7) + 154 @na_str 155 print @rptline 156 157 print @blankline 158 159 if @Reco = 'Y' 160 begin 161 select @recotxt = " Tuning Recommendations for Housekeeper" 162 select @recoline = " --------------------------------------" 163 select @reco_hdr_prn = 0 164 165 select @i = 0, @eng_count = 0 166 167 168 select @engines = count(engine) 169 from master.dbo.sysengines 170 where status != "offline" 171 172 173 select @reco_clock_ticks = SUM(value) 174 from #tempmonitors 175 where group_name like "engine_%" and 176 field_name = "clock_ticks" and 177 value > 0 178 179 select @reco_idle_ticks = SUM(value) 180 from #tempmonitors 181 where group_name like "engine_%" and 182 field_name = "idle_ticks" and 183 value > 0 184 185 select @reco_percent = 186 100.0 * ((@reco_clock_ticks - @reco_idle_ticks) / @reco_clock_ticks) 187 188 if @engines > 1 189 begin 190 select @reco_avg_busy = @reco_percent / @engines 191 end 192 193 /* 194 ** If the average CPU busy is > 95% and if the 195 ** percentage of masses washed clean by HK is 196 ** greater than 95%, we have an overactive 197 ** housekeeper which could be intruding with 198 ** user tasks, so reduce the 'housekeeper free write 199 ** percent' configuration parameter. 200 */ 201 if @reco_avg_busy > 95 AND @reco_clean > 95 202 begin 203 if (@reco_hdr_prn = 0) 204 begin 205 print @recotxt 206 print @recoline 207 select @reco_hdr_prn = 1 208 end 209 210 print " - Consider decreasing the 'housekeeper free write percent'" 211 print " configuration parameter." 212 select @reco_hdr_prn = 1 213 print @blankline 214 end 215 216 /* 217 ** If the average CPU busy is < 5% and if the 218 ** percentage of masses washed dirty by HK is 219 ** greater than 50%, we have a lot more washing to do 220 ** and since we have the cycles to do it, do it. 221 */ 222 if @reco_avg_busy < 5 AND @reco_dirty > 50 223 begin 224 if (@reco_hdr_prn = 0) 225 begin 226 print @recotxt 227 print @recoline 228 select @reco_hdr_prn = 1 229 end 230 print " - Consider increasing the 'housekeeper free write percent'" 231 print " configuration parameter." 232 select @reco_hdr_prn = 1 233 print @blankline 234 end 235 end 236 237 238 return 0 239
exec sp_procxmode 'sp_sysmon_hk', 'AnyMode' go Grant Execute on sp_sysmon_hk to public go
| DEPENDENCIES |
| PROCS AND TABLES USED reads table tempdb..#tempmonitors (1) reads table master..sysengines (1)  CALLERS called by proc sybsystemprocs..sp_sysmon_analyze  called by proc sybsystemprocs..sp_sysmon  |
