| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_sysmon_diskio | | 31 Aug 14 | Defects Dependencies |
1 2 /* This stored procedure produces a report containing a summary of 3 ** of disk activity. 4 */ 5 create procedure sp_sysmon_diskio 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 as 11 12 /* --------- declare local variables --------- */ 13 declare @SybDiskName varchar(265) /* cursor var for logical disk name - 14 ** sysdevices.name */ 15 declare @PhyDiskName varchar(127) /* cursor var for physical disk name - 16 ** sysdevices.phyname */ 17 18 declare @i smallint /* loop index to iterate through multi-group 19 ** counters (engine, disk, & buffer) */ 20 declare @tmp_grp varchar(25) /* temp var for build group_name's - ie., 21 ** engine_N, disk_N */ 22 declare @tmp_int int /* temp var for integer storage */ 23 declare @tmp_int2 int /* temp var for integer storage */ 24 declare @tmp_int3 int /* temp var for integer storage */ 25 declare @tmp_int4 int /* temp var for integer storage */ 26 declare @tmp_total int /* temp var for summing 'total #s' data */ 27 declare @tmp_total_async int /* temp var for summing total #s of 28 ** asynchronous IOs completed */ 29 declare @tmp_total_sync int /* temp var for summing total #s of synchronous 30 ** IOs completed */ 31 declare @tmp_total_ios int /* temp var for summing total #s of IOs 32 ** completed. 33 ** @tmp_total_ios = @tmp_total_async + 34 ** @tmp_total_sync 35 */ 36 declare @sum1line char(80) /* string to delimit total lines without 37 ** percent calc on printout */ 38 declare @sum2line char(80) /* string to delimit total lines without 39 ** percent calc on printout */ 40 declare @subsection char(80) /* delimit disk sections */ 41 declare @blankline char(1) /* to print blank line */ 42 declare @psign char(3) /* hold a percent sign (%) for print out */ 43 declare @na_str char(3) /* holds 'n/a' for 'not applicable' strings */ 44 declare @rptline char(80) /* formatted stats line for print statement */ 45 declare @section char(80) /* string to delimit sections on printout */ 46 47 /* ------------- Variables for Tuning Recommendations ------------*/ 48 declare @recotxt char(80) /* Header for tuning recommendation */ 49 declare @recoline char(80) /* to underline recotxt */ 50 declare @reco_hdr_prn bit /* to indicate if the recotxt is already printed */ 51 declare @reco_total_diskio int 52 declare @reco_diskio_struct_delay int 53 declare @reco_maxaio_server int 54 declare @reco_maxaio_engine int 55 declare @reco_aio_os_limit int 56 57 /* --------- Setup Environment --------- */ 58 set nocount on /* disable row counts being sent to client */ 59 60 select @sum1line = " ------------------------- ------------ ------------ ---------- ----------" 61 select @sum2line = " ------------------------- ------------ ------------ ----------" 62 select @subsection = " -----------------------------------------------------------------------------" 63 select @blankline = " " 64 select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */ 65 select @na_str = "n/a" 66 select @section = "===============================================================================" 67 68 print @section 69 print @blankline 70 print "Disk I/O Management" 71 print "-------------------" 72 print @blankline 73 print " Max Outstanding I/Os per sec per xact count %% of total" 74 print @sum1line 75 76 select @tmp_int = value 77 from #tempmonitors 78 where group_name = "kernel" and 79 field_name = "max_outstanding_AIOs_server" 80 81 select @rptline = " Server" + space(28) + 82 @na_str + space(11) + 83 @na_str + space(2) + 84 str(@tmp_int, 10) + space(7) + 85 @na_str 86 print @rptline 87 88 select @i = 0 89 while @i < @NumEngines /* for each engine */ 90 begin 91 /* build group_name string */ 92 select @tmp_grp = "engine_" + convert(varchar(3), @i) 93 94 select @tmp_int = value 95 from #tempmonitors 96 where group_name = @tmp_grp and 97 field_name = "max_outstanding_AIOs_engine" 98 99 select @rptline = " Engine " + convert(char(3), @i) + space(24) + 100 @na_str + space(11) + 101 @na_str + space(2) + 102 str(@tmp_int, 10) + space(7) + 103 @na_str 104 print @rptline 105 106 select @i = @i + 1 107 end 108 109 print @blankline 110 print @blankline 111 print " I/Os Delayed by" 112 113 select @tmp_int = value, @reco_diskio_struct_delay = value 114 from #tempmonitors 115 where group_name = "kernel" and 116 field_name = "udalloc_sleeps" 117 118 select @rptline = " Disk I/O Structures" + space(15) + 119 @na_str + space(11) + 120 @na_str + space(2) + 121 str(@tmp_int, 10) + space(7) + 122 @na_str 123 print @rptline 124 125 select @tmp_int = SUM(value), @reco_maxaio_server = SUM(value) 126 from #tempmonitors 127 where group_name like "engine_%" and 128 field_name = "AIOs_delayed_due_to_server_limit" 129 130 select @rptline = " Server Config Limit" + space(15) + 131 @na_str + space(11) + 132 @na_str + space(2) + 133 str(@tmp_int, 10) + space(7) + 134 @na_str 135 print @rptline 136 137 select @tmp_int = SUM(value), @reco_maxaio_engine = SUM(value) 138 from #tempmonitors 139 where group_name like "engine_%" and 140 field_name = "AIOs_delayed_due_to_engine_limit" 141 142 select @rptline = " Engine Config Limit" + space(15) + 143 @na_str + space(11) + 144 @na_str + space(2) + 145 str(@tmp_int, 10) + space(7) + 146 @na_str 147 print @rptline 148 149 select @tmp_int = SUM(value), @reco_aio_os_limit = SUM(value) 150 from #tempmonitors 151 where group_name like "engine_%" and 152 field_name = "AIOs_delayed_due_to_os_limit" 153 154 select @rptline = " Operating System Limit" + space(12) + 155 @na_str + space(11) + 156 @na_str + space(2) + 157 str(@tmp_int, 10) + space(7) + 158 @na_str 159 print @rptline 160 161 print @blankline 162 print @blankline 163 164 select @tmp_int = value, @reco_total_diskio = value 165 from #tempmonitors 166 where group_name = "kernel" and 167 field_name = "udalloc_calls" 168 169 select @rptline = " Total Requested Disk I/Os" + space(2) + 170 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 171 space(2) + 172 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 173 space(2) + 174 str(@tmp_int, 10) 175 print @rptline 176 print @blankline 177 print " Completed Disk I/O's" 178 179 select @tmp_total_async = SUM(value) 180 from #tempmonitors 181 where group_name like "engine_%" and 182 field_name = "total_dpoll_completed_aios" 183 184 select @tmp_total_sync = value 185 from #tempmonitors 186 where group_name like "kernel" and 187 field_name = "total_sync_completed_ios" 188 189 select @tmp_total_ios = @tmp_total_async + @tmp_total_sync 190 191 print " Asynchronous I/O's" 192 if @tmp_total_async = 0 193 begin 194 select @rptline = " Total Completed I/Os 0.0 0.0 0 n/a" 195 print @rptline 196 end 197 else 198 begin 199 if @@kernelmode = "process" 200 begin 201 select @i = 0 202 while @i < @NumEngines /* for each engine */ 203 begin 204 /* build group_name string */ 205 select @tmp_grp = "engine_" + convert(varchar(3), @i) 206 207 select @tmp_int = value 208 from #tempmonitors 209 where group_name = @tmp_grp and 210 field_name = "total_dpoll_completed_aios" 211 212 select @rptline = " Engine " + convert(char(3), @i) + 213 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_async, 5, 1) + @psign 220 print @rptline 221 222 select @i = @i + 1 223 end /* while loop */ 224 end /* else */ 225 else 226 begin /* threaded kernel */ 227 select @rptline = " Total Completed I/Os" + 228 space(3) + 229 str(@tmp_total_async / (@NumElapsedMs / 1000.0), 12, 1) + 230 space(2) + 231 str(@tmp_total_async / convert(real, @NumXacts), 12, 1) + 232 space(2) + 233 str(@tmp_total_async, 10) + space(5) + 234 str(100.0 * @tmp_total_async / @tmp_total_ios, 5, 1) + @psign 235 print @rptline 236 end 237 end 238 239 print " Synchronous I/O's" 240 if @tmp_total_sync = 0 241 begin 242 select @rptline = " Total Completed I/Os 0.0 0.0 0 n/a" 243 print @rptline 244 end 245 else 246 begin 247 select @rptline = " Total Completed I/Os " + 248 str(@tmp_total_sync / (@NumElapsedMs / 1000.0), 12, 1) + 249 space(2) + 250 str(@tmp_total_sync / convert(real, @NumXacts), 12, 1) + 251 space(2) + 252 str(@tmp_total_sync, 10) + space(5) + 253 str(100.0, 5, 1) + @psign 254 print @rptline 255 end /* else */ 256 257 print @sum2line 258 259 select @rptline = " Total Completed I/Os" + space(7) + 260 str(@tmp_total_ios / (@NumElapsedMs / 1000.0), 12, 1) + 261 space(2) + 262 str(@tmp_total_ios / convert(real, @NumXacts), 12, 1) + 263 space(2) + 264 str(@tmp_total_ios, 10) 265 print @rptline 266 print @blankline 267 print @blankline 268 print " Device Activity Detail" 269 print " ----------------------" 270 print @blankline 271 272 /* get total number of I/Os to all devices to calc each device's percentage */ 273 select @tmp_total = SUM(value) 274 from #tempmonitors 275 where group_name like "disk_%" and 276 (field_name = "total_reads" or field_name = "total_writes") 277 278 if @tmp_total = 0 279 begin 280 print " No Disk I/O in Given Sample Period" 281 print @blankline 282 end 283 else 284 begin 285 declare disk_info cursor for 286 select name, phyname, group_name 287 from #devicemap 288 order by phyname 289 for read only 290 291 open disk_info 292 fetch disk_info into @SybDiskName, @PhyDiskName, @tmp_grp 293 294 while (@@sqlstatus = 0) 295 begin 296 297 select @rptline = " Device:" 298 print @rptline 299 select @rptline = space(4) + substring(@PhyDiskName, 1, 76) 300 print @rptline 301 select @rptline = space(4) + convert(char(25), 302 substring(@SybDiskName, 1, 25)) + 303 " per sec per xact count %% of total" 304 print @rptline 305 306 print @sum1line 307 308 select @tmp_int2 = SUM(value) 309 from #tempmonitors 310 where group_name = @tmp_grp and 311 (field_name = "total_reads" or 312 field_name = "total_writes") 313 314 if @tmp_int2 = 0 315 begin 316 select @rptline = " Total I/Os 0.0 0.0 0 n/a" 317 print @rptline 318 end 319 else 320 begin 321 select @tmp_int = value 322 from #tempmonitors 323 where group_name = @tmp_grp and 324 field_name = "total_reads" 325 if not exists (select * 326 from #tempmonitors 327 where group_name = @tmp_grp and 328 field_name = "apf_physical_reads") 329 330 begin /*{ begin to check existence of apf counters*/ 331 select @rptline = " Reads" + space(20) + 332 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 333 space(2) + 334 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 335 space(2) + 336 str(@tmp_int, 10) + space(5) + 337 str(100.0 * @tmp_int / @tmp_int2, 5, 1) + @psign 338 print @rptline 339 end /*} end for apf counter check*/ 340 341 else 342 begin /*{begin in case apf counters exist*/ 343 select @tmp_int3 = value 344 from #tempmonitors 345 where group_name = @tmp_grp and 346 field_name = "apf_physical_reads" 347 348 select @tmp_int4 = @tmp_int - @tmp_int3 349 350 select @rptline = " Reads" 351 print @rptline 352 353 select @rptline = " APF" + space(20) + 354 str(@tmp_int3 / (@NumElapsedMs / 1000.0), 12, 1) + 355 space(2) + 356 str(@tmp_int3 / convert(real, @NumXacts), 12, 1) + 357 space(2) + 358 str(@tmp_int3, 10) + space(5) + 359 str(100.0 * @tmp_int3 / @tmp_int2, 5, 1) + @psign 360 print @rptline 361 362 select @rptline = " Non-APF" + space(16) + 363 str(@tmp_int4 / (@NumElapsedMs / 1000.0), 12, 1) + 364 space(2) + 365 str(@tmp_int4 / convert(real, @NumXacts), 12, 1) + 366 space(2) + 367 str(@tmp_int4, 10) + space(5) + 368 str(100.0 * @tmp_int4 / @tmp_int2, 5, 1) + @psign 369 print @rptline 370 end /*} end in case where apf counters exist*/ 371 372 select @tmp_int = value 373 from #tempmonitors 374 where group_name = @tmp_grp and 375 field_name = "total_writes" 376 377 select @rptline = " Writes" + space(19) + 378 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 379 space(2) + 380 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 381 space(2) + 382 str(@tmp_int, 10) + space(5) + 383 str(100.0 * @tmp_int / @tmp_int2, 5, 1) + @psign 384 print @rptline 385 386 end /* else @tmp_int2 != 0 */ 387 388 print @sum1line 389 390 select @rptline = " Total I/Os" + space(17) + 391 str(@tmp_int2 / (@NumElapsedMs / 1000.0), 12, 1) + 392 space(2) + 393 str(@tmp_int2 / convert(real, @NumXacts), 12, 1) + 394 space(2) + 395 str(@tmp_int2, 10) + space(5) + 396 str(100.0 * @tmp_int2 / @tmp_total, 5, 1) + @psign 397 print @rptline 398 print @blankline 399 400 select @tmp_int2 = SUM(value) 401 from #tempmonitors 402 where group_name = @tmp_grp and 403 (field_name = "p_hits" or field_name = "p_misses") 404 405 if @tmp_int2 != 0 406 begin 407 408 select @tmp_int = value 409 from #tempmonitors 410 where group_name = @tmp_grp and 411 field_name = "p_hits" 412 413 select @rptline = " Mirror Semaphore Granted" + 414 space(3) + 415 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 416 space(2) + 417 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 418 space(2) + 419 str(@tmp_int, 10) + space(5) + 420 str(100.0 * @tmp_int / @tmp_int2, 5, 1) + @psign 421 print @rptline 422 423 select @tmp_int = value 424 from #tempmonitors 425 where group_name = @tmp_grp and 426 field_name = "p_misses" 427 428 select @rptline = " Mirror Semaphore Waited" + 429 space(4) + 430 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 431 space(2) + 432 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 433 space(2) + 434 str(@tmp_int, 10) + space(5) + 435 str(100.0 * @tmp_int / @tmp_int2, 5, 1) + @psign 436 print @rptline 437 end /* else @tmp_int2 != 0 */ 438 439 print @blankline 440 print @subsection 441 print @blankline 442 443 fetch disk_info into @SybDiskName, @PhyDiskName, @tmp_grp 444 445 end /* while @@sqlstatus */ 446 447 close disk_info 448 deallocate cursor disk_info 449 450 end /* else @tmp_total != 0 */ 451 452 print @blankline 453 454 if (@Reco = 'Y' and @reco_total_diskio != 0) 455 begin 456 select @recotxt = " Tuning Recommendations for Disk I/O Management" 457 select @recoline = " ----------------------------------------------" 458 select @reco_hdr_prn = 0 459 460 select @reco_diskio_struct_delay = convert(int, 461 (100.0 * ((1.0 * @reco_diskio_struct_delay) / @reco_total_diskio))) 462 select @reco_maxaio_server = convert(int, 463 (100.0 * ((1.0 * @reco_maxaio_server) / @reco_total_diskio))) 464 select @reco_maxaio_engine = convert(int, 465 (100.0 * ((1.0 * @reco_maxaio_engine) / @reco_total_diskio))) 466 select @reco_aio_os_limit = convert(int, 467 (100.0 * ((1.0 * @reco_aio_os_limit) / @reco_total_diskio))) 468 469 /* 470 ** If the % of I/O's delayed on account of 471 ** number of disk I/O structures is > 5% 472 ** consider increasing the number of disk i/o 473 ** structures 474 */ 475 if @reco_diskio_struct_delay > 5 476 begin 477 if (@reco_hdr_prn = 0) 478 begin 479 print @recotxt 480 print @recoline 481 select @reco_hdr_prn = 1 482 end 483 484 print " - Consider increasing the 'disk i/o structures'" 485 print " configuration parameter." 486 print @blankline 487 select @reco_hdr_prn = 1 488 end 489 490 /* 491 ** If the % of the number of I/O's delayed on account of 492 ** the server limit is > 5 493 ** consider increasing the 'max async I/Os per server' 494 ** configuration parameter 495 */ 496 if @reco_maxaio_server > 5 497 begin 498 if (@reco_hdr_prn = 0) 499 begin 500 print @recotxt 501 print @recoline 502 select @reco_hdr_prn = 1 503 end 504 505 print " - Consider increasing the 'max async I/Os per server'" 506 print " configuration parameter." 507 print @blankline 508 select @reco_hdr_prn = 1 509 end 510 511 /* 512 ** If the % of the number of I/O's delayed on account of 513 ** the engine limit is > 5 514 ** consider increasing 'max async I/Os per engine' 515 */ 516 if @reco_maxaio_engine > 5 517 begin 518 if (@reco_hdr_prn = 0) 519 begin 520 print @recotxt 521 print @recoline 522 select @reco_hdr_prn = 1 523 end 524 525 print " - Consider increasing the 'max async I/Os per engine'" 526 print " configuration parameter." 527 print @blankline 528 select @reco_hdr_prn = 1 529 end 530 531 /* 532 ** If the % of the number of I/O's delayed on account of 533 ** operating system parameters governing async I/O 534 ** is greater than 5, then consider increasing that 535 ** parameter. 536 */ 537 if @reco_aio_os_limit > 5 538 begin 539 if (@reco_hdr_prn = 0) 540 begin 541 print @recotxt 542 print @recoline 543 select @reco_hdr_prn = 1 544 end 545 546 print " - Consider increasing the operating system parameter" 547 print " governing the number of asynchronous I/O's." 548 print @blankline 549 select @reco_hdr_prn = 1 550 end 551 552 end 553 554 print @blankline 555 556 return 0 557
exec sp_procxmode 'sp_sysmon_diskio', 'AnyMode' go Grant Execute on sp_sysmon_diskio to public go
| DEPENDENCIES |
| PROCS AND TABLES USED reads table tempdb..#devicemap (1) reads table tempdb..#tempmonitors (1) CALLERS called by proc sybsystemprocs..sp_sysmon_analyze  called by proc sybsystemprocs..sp_sysmon  |
