Database | Proc | Application | Created | Links |
sybsystemprocs | sp_sysmon_kernel | 31 Aug 14 | Defects Dependencies |
1 2 /* This stored procedure produces a report containing a summary of 3 ** SQL Server engine activity. 4 */ 5 create procedure sp_sysmon_kernel 6 @NumEngines tinyint, /* number of engines online */ 7 @NumElapsedMs int, /* for "per Elapsed second" calculations */ 8 @NumXacts int, /* for per transactions calculations */ 9 @Reco char(1), /* Flag for recommendations */ 10 @instid smallint = NULL /* optional SDC instance id */ 11 as 12 13 /* --------- declare local variables --------- */ 14 declare @i smallint /* loop index to iterate through multi-group 15 ** counters (engine, disk, & buffer) */ 16 declare @tmp_grp varchar(25) /* temp var to build group_names 17 ** ie. engine_N, disk_N */ 18 declare @tmp_int int /* temp var for integer storage */ 19 declare @tmp_total int /* temp var for summing 'total #s' data */ 20 declare @cpu_busy real /* var for cpu busy percentage */ 21 declare @io_busy real /* var for io busy percentage */ 22 declare @cpu_busy_sum real /* var for summing cpu busy percentage */ 23 declare @io_busy_sum real /* var for summing io busy percentage */ 24 declare @cpu_busy_avg real /* var for averaging cpu busy percentage */ 25 declare @io_busy_avg real /* var for averaging io busy percentage */ 26 declare @rpsc int /* holds cfgvalue for RPSC */ 27 declare @iopc int /* holds cfgvalue for I/O polling count */ 28 declare @eng_load_line char(67) /* string to delimit the engine load lines */ 29 declare @avg1line char(67) /* string to delimit avg lines on printout */ 30 declare @sum1line char(80) /* string to delimit total lines without 31 ** percent calc on printout */ 32 declare @sum2line char(67) /* string to delimit total lines with percent 33 ** calc on printout */ 34 declare @blankline char(1) /* to print blank line */ 35 declare @psign char(3) /* hold a percent sign (%) for print out */ 36 declare @na_str char(3) /* holds 'n/a' for 'not applicable' strings */ 37 declare @rptline char(80) /* formatted statistics line for print 38 ** statement */ 39 declare @section char(80) /* string to delimit sections on printout */ 40 41 /* ------------- Variables for Tuning Recommendations ------------*/ 42 declare @recotxt char(80) /* Header for tuning recommendation */ 43 declare @recoline char(80) /* to underline recotxt */ 44 declare @reco_hdr_prn bit /* to indicate if the recotxt is already printed */ 45 declare @reco_percent_diskio real /* percentage sucessful disk i/o */ 46 47 /* --------- Setup Environment --------- */ 48 set nocount on /* disable row counts being sent to client */ 49 50 select @avg1line = " ----------- --------------- ----------------" 51 select @sum1line = " ------------------------- ------------ ------------ ---------- ----------" 52 select @sum2line = " ------------------------- ------------ ------------ ----------" 53 select @blankline = " " 54 select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */ 55 select @na_str = "n/a" 56 select @section = "===============================================================================" 57 58 59 /* ======================= Kernel Utilization Section =================== */ 60 print @section 61 print @blankline 62 print "Kernel Utilization" 63 print "------------------" 64 print @blankline 65 66 select @rpsc = value from master..syscurconfigs where config = 177 67 68 69 select @rptline = " Your Runnable Process Search Count is set to " 70 + convert(varchar, @rpsc) 71 print @rptline 72 73 74 select @iopc = value from master..syscurconfigs where config = 178 75 76 77 select @rptline = " and I/O Polling Process Count is set to " 78 + convert(varchar, @iopc) 79 print @rptline 80 print @blankline 81 82 select @i = 0, @cpu_busy_sum = 0, @io_busy_sum = 0, @cpu_busy_avg = 0, 83 @io_busy_avg = 0, @reco_percent_diskio = 0 84 85 select @rptline = " Engine Busy Utilization " + space(7) 86 + "CPU Busy" + space(3) 87 + "I/O Busy" + space(7) 88 + "Idle" 89 print @rptline 90 select @eng_load_line = " ------------------------" + space(7) 91 + "--------" + space(3) 92 + "--------" + space(3) 93 + "--------" 94 print @eng_load_line 95 while @i < @NumEngines /* for each engine */ 96 begin 97 /* build group_name string */ 98 select @tmp_grp = "engine_" + convert(varchar(3), @i) 99 100 select @cpu_busy = isnull(100.0 * convert(real, c.value) / t.value, 0), 101 @io_busy = isnull(100.0 * convert(real, i.value) / t.value, 0) 102 from #tempmonitors t, #tempmonitors c, #tempmonitors i 103 where t.group_name = @tmp_grp 104 and t.group_name = c.group_name 105 and t.group_name = i.group_name 106 and t.field_name = "clock_ticks" 107 and i.field_name = "io_ticks" 108 and c.field_name = "cpu_ticks" 109 and t.value > 0 110 111 select @cpu_busy_sum = @cpu_busy_sum + @cpu_busy, 112 @io_busy_sum = @io_busy_sum + @io_busy 113 114 select @rptline = " Engine " + convert(char(3), @i) + space(20) 115 + str(@cpu_busy, 5, 1) + @psign + space(4) 116 + str(@io_busy, 5, 1) + @psign + space(4) 117 + str(100 - @cpu_busy - @io_busy, 5, 1) + @psign 118 print @rptline 119 select @i = @i + 1 120 end 121 122 if @NumEngines > 1 123 begin 124 print @eng_load_line 125 select @rptline = " Summary " + space(10) + "Total" + space(7) 126 + str(@cpu_busy_sum, 7, 1) + @psign + space(2) 127 + str(@io_busy_sum, 7, 1) + @psign + space(2) 128 + str(@NumEngines * 100 - @cpu_busy_sum 129 - @io_busy_sum, 7, 1) + @psign, 130 @cpu_busy_avg = @cpu_busy_sum / @NumEngines, 131 @io_busy_avg = @io_busy_sum / @NumEngines 132 print @rptline 133 select @rptline = space(18) + "Average" + space(9) 134 + str(@cpu_busy_avg, 5, 1) + @psign + space(4) 135 + str(@io_busy_avg, 5, 1) + @psign + space(4) 136 + str(100 - @cpu_busy_avg - @io_busy_avg, 5, 1) 137 + @psign 138 print @rptline 139 end 140 141 print @blankline 142 print " CPU Yields by Engine per sec per xact count %% of total" 143 print @sum1line 144 145 select @tmp_total = SUM(value) 146 from #tempmonitors 147 where group_name like "engine_%" and 148 field_name = "engine_sleeps" 149 150 if @tmp_total = 0 /* Avoid divide by zero errors - just print zero's */ 151 begin 152 select @rptline = " Total CPU Yields 0.0 0.0 0 n/a" 153 print @rptline 154 end 155 else 156 begin 157 select @i = 0 158 while @i < @NumEngines /* for each engine */ 159 begin 160 /* build group_name string */ 161 select @tmp_grp = "engine_" + convert(varchar(3), @i) 162 select @tmp_int = value 163 from #tempmonitors 164 where group_name = @tmp_grp and 165 field_name = "engine_sleeps" 166 167 select @rptline = " Engine " + convert(char(3), @i) + 168 space(15) + 169 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) 170 + space(2) + 171 str(@tmp_int / convert(real, @NumXacts), 12, 1) 172 + space(2) + 173 str(@tmp_int, 10) + space(5) + 174 str(100.0 * @tmp_int / @tmp_total, 5, 1) 175 + @psign 176 print @rptline 177 select @i = @i + 1 178 end 179 180 if @NumEngines > 1 181 begin 182 print @sum2line 183 select @rptline = " Total CPU Yields " + space(10) + 184 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) 185 + space(2) + 186 str(@tmp_total / convert(real, @NumXacts), 12, 1) 187 + space(2) + 188 str(@tmp_total, 10) 189 print @rptline 190 end 191 end 192 print @blankline 193 194 print " Network Checks" 195 196 select @tmp_total = SUM(value) 197 from #tempmonitors 198 where group_name = "kernel" and 199 field_name like "ncheck_%" 200 201 if @tmp_total = 0 /* Avoid divide by zero errors - print zero's */ 202 begin 203 select @rptline = " Total Network I/O Checks 0.0 0.0 0 n/a" 204 print @rptline 205 end 206 else 207 begin 208 select @tmp_int = value 209 from #tempmonitors 210 where group_name = "kernel" and 211 field_name = "ncheck_nonblocking" 212 213 select @rptline = " Non-Blocking" + space(13) + 214 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 215 space(2) + 216 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 217 space(2) + 218 str(@tmp_int, 10) + space(5) + 219 str(100.0 * @tmp_int / @tmp_total, 5, 1) 220 + @psign 221 print @rptline 222 223 select @tmp_int = value 224 from #tempmonitors 225 where group_name = "kernel" and 226 field_name = "ncheck_blocking" 227 228 select @rptline = " Blocking" + space(17) + 229 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 230 space(2) + 231 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 232 space(2) + 233 str(@tmp_int, 10) + space(5) + 234 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 235 print @rptline 236 237 print @sum2line 238 select @rptline = " Total Network I/O Checks " + space(2) + 239 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 240 space(2) + 241 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 242 space(2) + 243 str(@tmp_total, 10) 244 print @rptline 245 246 select @tmp_int = SUM(value) 247 from #tempmonitors 248 where group_name = "network" and 249 field_name like "total_packets_%" 250 251 select @rptline = " Avg Net I/Os per Check" + space(14) + 252 @na_str + space(11) + 253 @na_str + space(2) + 254 str(convert(real, @tmp_int) / @tmp_total, 10, 5) + 255 space(7) + 256 @na_str 257 print @rptline 258 end 259 print @blankline 260 261 print " Disk I/O Checks" 262 263 select @tmp_total = SUM(value) 264 from #tempmonitors 265 where group_name like "engine_%" and 266 field_name = "dcheck_calls" 267 268 if @tmp_total = 0 /* Avoid divide by zero errors - print zero's */ 269 begin 270 select @rptline = " Total Disk I/O Checks 0.0 0.0 0 n/a" 271 print @rptline 272 end 273 else 274 begin 275 select @rptline = " Total Disk I/O Checks" + space(4) + 276 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 277 space(2) + 278 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 279 space(2) + 280 str(@tmp_total, 10) + space(7) + 281 @na_str 282 print @rptline 283 284 select @tmp_int = SUM(value) 285 from #tempmonitors 286 where group_name like "engine_%" and 287 field_name = "dchecks_calling_dpoll" 288 289 select @rptline = " Checks Returning I/O" + space(5) + 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(5) + 295 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 296 if @tmp_total != 0 297 begin 298 select @reco_percent_diskio = 100.0 * (convert(real, @tmp_int) / @tmp_total) 299 end 300 301 print @rptline 302 303 /* save checks returning i/o value for average calc below */ 304 select @tmp_total = @tmp_int 305 306 if @tmp_total != 0 307 begin 308 select @tmp_int = SUM(value) 309 from #tempmonitors 310 where group_name like "engine_%" and 311 field_name = "total_dpoll_completed_aios" 312 313 select @rptline = " Avg Disk I/Os Returned" + space(12) + 314 @na_str + space(11) + 315 @na_str + space(2) + 316 str(convert(real, @tmp_int) / 317 @tmp_total, 10, 5) + 318 space(7) + 319 @na_str 320 print @rptline 321 end 322 end 323 324 print @blankline 325 if @Reco = 'Y' 326 begin 327 select @recotxt = " Tuning Recommendations for Kernel Utilization" 328 select @recoline = " ---------------------------------------------" 329 select @reco_hdr_prn = 0 330 331 if @NumEngines > 1 332 begin 333 /* 334 ** If the average percentage busy on the engines 335 ** is > 95% consider increasing the number of engines 336 */ 337 if (@cpu_busy_avg > 95) 338 begin 339 if (@reco_hdr_prn = 0) 340 begin 341 print @recotxt 342 print @recoline 343 select @reco_hdr_prn = 1 344 end 345 346 print " - Consider bringing more engines online" 347 print @blankline 348 select @reco_hdr_prn = 1 349 end 350 351 /* 352 ** If the average percentage busy on the engines is < 5% 353 ** consider decreasing the 'runnable process search count' 354 ** configuration parameter, if other applications are running 355 ** on the same machine. 356 ** If runnable process search count is already set to 100 or less 357 ** don't print this recommendation. 358 */ 359 if (@cpu_busy_avg < 5 and @rpsc > 100) 360 begin 361 if (@reco_hdr_prn = 0) 362 begin 363 print @recotxt 364 print @recoline 365 select @reco_hdr_prn = 1 366 end 367 368 print " - Consider decreasing the 'runnable process search count'" 369 print " configuration parameter if you require the CPU's on" 370 print " the machine to be used for other applications." 371 print @blankline 372 select @reco_hdr_prn = 1 373 end 374 /* 375 ** If the average cpu busy percentage on the engines 376 ** is > 70% and the average busy on the engines 377 ** is < 90% and the percentage diskio is < 5% consider 378 ** increasing the 'i/o polling process count' 379 ** configuration parameter. 380 ** Don't print this recommendation if the config param 381 ** is already set to 10000 or higher. 382 */ 383 if (@cpu_busy_avg > 70 AND @cpu_busy_avg < 90 384 AND @reco_percent_diskio < 5 385 AND @iopc < 10000) 386 begin 387 if (@reco_hdr_prn = 0) 388 begin 389 print @recotxt 390 print @recoline 391 select @reco_hdr_prn = 1 392 end 393 394 print " - Consider increasing the 'i/o polling process count'" 395 print " configuration parameter." 396 print @blankline 397 select @reco_hdr_prn = 1 398 end 399 end 400 end 401 print @blankline 402 403 return 0 404
exec sp_procxmode 'sp_sysmon_kernel', 'AnyMode' go Grant Execute on sp_sysmon_kernel to public go
DEPENDENCIES |
PROCS AND TABLES USED reads table master..syscurconfigs (1) reads table tempdb..#tempmonitors (1) CALLERS called by proc sybsystemprocs..sp_sysmon_analyze called by proc sybsystemprocs..sp_sysmon |