| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_sysmon_index | | 31 Aug 14 | Defects Dependencies |
1 2 /* This stored procedure produces a report containing a summary of 3 ** index behavior including deadlocks and page splits. 4 */ 5 create procedure sp_sysmon_index 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_total int /* temp var for summing 'total #s' data */ 13 declare @sum1line char(80) /* string to delimit total lines without 14 ** percent calc on printout */ 15 declare @sum2line char(80) 16 declare @blankline char(1) /* to print blank line */ 17 declare @psign char(3) /* hold a percent sign (%) for print out */ 18 declare @na_str char(3) /* holds 'n/a' for 'not applicable' strings */ 19 declare @rptline char(80) /* formatted stats line for print statement */ 20 declare @section char(80) /* string to delimit sections on printout */ 21 /* --------- Setup Environment --------- */ 22 set nocount on /* disable row counts being sent to client */ 23 24 select @sum1line = " ------------------------- ------------ ------------ ---------- ----------" 25 select @sum2line = " ------------ ------------ ----------" 26 27 select @blankline = " " 28 select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */ 29 select @na_str = "n/a" 30 select @section = "===============================================================================" 31 32 print @section 33 print @blankline 34 35 print "Index Management" 36 print "----------------" 37 print @blankline 38 39 print " Nonclustered Maintenance per sec per xact count %% of total" 40 print @sum1line 41 42 select @tmp_total = value 43 from #tempmonitors 44 where group_name = 'access' and 45 field_name = "ncupdate" 46 47 select @rptline = " Ins/Upd Requiring Maint" + space(2) + 48 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 49 space(2) + 50 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 51 space(2) + 52 str(@tmp_total, 10) + space(7) + 53 @na_str 54 print @rptline 55 56 select @tmp_int = value 57 from #tempmonitors 58 where group_name = 'access' and 59 field_name = "ncupdate_indexes" 60 61 select @rptline = " # of NC Ndx Maint" + space(6) + 62 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 63 space(2) + 64 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 65 space(2) + 66 str(@tmp_int, 10) + space(7) + 67 @na_str 68 print @rptline 69 70 if @tmp_total != 0 71 begin 72 select @rptline = " Avg NC Ndx Maint / Op" + space(11) + 73 @na_str + space(11) + 74 @na_str + space(2) + 75 str(convert(real, @tmp_int) / @tmp_total, 10, 5) + 76 space(7) + 77 @na_str 78 print @rptline 79 end 80 81 print @blankline 82 83 select @tmp_total = value 84 from #tempmonitors where 85 group_name = 'access' and 86 field_name = "ncdelete" 87 88 select @rptline = " Deletes Requiring Maint" + space(2) + 89 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 90 space(2) + 91 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 92 space(2) + 93 str(@tmp_total, 10) + space(7) + 94 @na_str 95 print @rptline 96 97 select @tmp_int = value 98 from #tempmonitors 99 where group_name = 'access' and 100 field_name = "ncdelete_indexes" 101 102 select @rptline = " # of NC Ndx Maint" + space(6) + 103 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 104 space(2) + 105 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 106 space(2) + 107 str(@tmp_int, 10) + space(7) + 108 @na_str 109 print @rptline 110 111 if @tmp_total != 0 112 begin 113 select @rptline = " Avg NC Ndx Maint / Op" + space(11) + 114 @na_str + space(11) + 115 @na_str + space(2) + 116 str(convert(real, @tmp_int) / @tmp_total, 10, 5) + 117 space(7) + 118 @na_str 119 print @rptline 120 end 121 122 print @blankline 123 124 select @tmp_total = value 125 from #tempmonitors 126 where group_name = 'access' and 127 field_name = "ncrid_update" 128 129 select @rptline = " RID Upd from Clust Split " + 130 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 131 space(2) + 132 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 133 space(2) + 134 str(@tmp_total, 10) + space(7) + 135 @na_str 136 print @rptline 137 138 select @tmp_int = value 139 from #tempmonitors 140 where group_name = 'access' and 141 field_name = "ncrid_update_indexes" 142 143 select @rptline = " # of NC Ndx Maint" + space(6) + 144 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 145 space(2) + 146 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 147 space(2) + 148 str(@tmp_int, 10) + space(7) + 149 @na_str 150 print @rptline 151 152 if @tmp_total != 0 153 begin 154 select @rptline = " Avg NC Ndx Maint / Op" + space(11) + 155 @na_str + space(11) + 156 @na_str + space(2) + 157 str(convert(real, @tmp_int) / @tmp_total, 10, 5) + 158 space(7) + 159 @na_str 160 print @rptline 161 end 162 163 print @blankline 164 165 166 select @tmp_total = value 167 from #tempmonitors 168 where group_name = 'dolaccess' and 169 field_name = "dolncdelete" 170 171 select @rptline = " Upd/Del DOL Req Maint" + space(4) + 172 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 173 space(2) + 174 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 175 space(2) + 176 str(@tmp_total, 10) + space(7) + 177 @na_str 178 print @rptline 179 180 select @tmp_int = value 181 from #tempmonitors 182 where group_name = 'dolaccess' and 183 field_name = "dolncdelete_indexes" 184 185 select @rptline = " # of DOL Ndx Maint" + space(5) + 186 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 187 space(2) + 188 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 189 space(2) + 190 str(@tmp_int, 10) + space(7) + 191 @na_str 192 print @rptline 193 194 if @tmp_total != 0 195 begin 196 select @rptline = " Avg DOL Ndx Maint / Op" + space(10) + 197 @na_str + space(11) + 198 @na_str + space(2) + 199 str(convert(real, @tmp_int) / @tmp_total, 10, 5) + 200 space(7) + 201 @na_str 202 print @rptline 203 end 204 205 print @blankline 206 207 select @tmp_total = sum(value) 208 from #tempmonitors 209 where (group_name = "access" and 210 field_name IN ("split_index", "split_root")) 211 OR (group_name = "btree" and 212 field_name IN ("bt_leafsplit_count", "bt_noleafsplit_count")) 213 214 select @rptline = " Page Splits" + space(16) + 215 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 216 space(2) + 217 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 218 space(2) + 219 str(@tmp_total, 10) + space(7) + 220 @na_str 221 print @rptline 222 223 if @tmp_total != 0 /* Avoid Divide by Zero Errors */ 224 begin 225 select @tmp_int = value 226 from #tempmonitors 227 where group_name = "access" and 228 field_name = "split_index_retry" 229 230 select @rptline = " Retries" + space(18) + 231 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 232 space(2) + 233 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 234 space(2) + 235 str(@tmp_int, 10) + space(5) + 236 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 237 print @rptline 238 239 select @tmp_int = value 240 from #tempmonitors 241 where group_name = "access" and 242 field_name = "split_index_deadlock" 243 244 select @rptline = " Deadlocks" + space(16) + 245 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 246 space(2) + 247 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 248 space(2) + 249 str(@tmp_int, 10) + space(5) + 250 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 251 print @rptline 252 253 select @tmp_int = value 254 from #tempmonitors 255 where (group_name = "access" and 256 field_name = "add_ind_level") 257 OR (group_name = "btree" and 258 field_name = "bt_add_ind_level") 259 260 select @rptline = " Add Index Level" + space(10) + 261 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 262 space(2) + 263 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 264 space(2) + 265 str(@tmp_int, 10) + space(5) + 266 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 267 print @rptline 268 269 end 270 271 print @blankline 272 273 select @tmp_total = SUM(value) 274 from #tempmonitors 275 where (group_name = "access" and 276 field_name like "shrink%") 277 OR (group_name = "btree" and 278 field_name IN ("bt_shrink_bylastdel", "bt_shrink_byscan", 279 "bt_shrink_nonleaf")) 280 281 select @rptline = " Page Shrinks" + space(15) + 282 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 283 space(2) + 284 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 285 space(2) + 286 str(@tmp_total, 10) + space(7) + 287 @na_str 288 print @rptline 289 290 291 if @tmp_total != 0 /* Avoid Divide by Zero Errors */ 292 begin 293 select @tmp_int = SUM(value) 294 from #tempmonitors 295 where group_name = "access" and 296 field_name IN ("am_split_shrink_LOSTP", "am_split_shrink_WDP", 297 "am_split_shrink_NWFP", "am_split_shrink_WDC", 298 "am_split_shrink_NWFC", "am_split_shrink_WDNXT", 299 "am_split_shrink_NWFNX", "am_split_shrink_WDPRV", 300 "am_split_shrink_NWFPRV") 301 302 303 select @rptline = " Deadlocks" + space(16) + 304 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 305 space(2) + 306 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 307 space(2) + 308 str(@tmp_int, 10) + space(5) + 309 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 310 print @rptline 311 312 select @tmp_int = value 313 from #tempmonitors 314 where group_name = "access" and 315 field_name = "split_shrink_retries_exceeded" 316 317 select @rptline = " Deadlock Retries Exceeded" + 318 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 319 space(2) + 320 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 321 space(2) + 322 str(@tmp_int, 10) + space(5) + 323 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 324 print @rptline 325 end 326 if exists (select * 327 from #tempmonitors 328 where group_name = "access" and 329 field_name = "forward_scans") 330 begin 331 print @blankline 332 print " Index Scans per sec per xact count %% of total" 333 print @sum1line 334 335 select @tmp_total = sum(value) 336 from #tempmonitors 337 where (group_name = 'access' and 338 field_name in ("forward_scans", "backward_scans")) 339 OR (group_name = 'btree' and 340 field_name in ("bt_forward_scans", "bt_backward_scans")) 341 342 if @tmp_total = 0 343 begin 344 select @rptline = " Total Scans 0.0 0.0 0 n/a" 345 print @rptline 346 end 347 else 348 begin 349 select @tmp_int = value 350 from #tempmonitors 351 where group_name = "access" and 352 field_name = "forward_scans" 353 354 select @rptline = " Ascending Scans " + space(2) + 355 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 356 space(2) + 357 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 358 space(2) + 359 str(@tmp_int, 10) + space(5) + 360 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 361 print @rptline 362 363 select @tmp_int = value 364 from #tempmonitors 365 where group_name = "btree" and 366 field_name = "bt_forward_scans" 367 368 select @rptline = " DOL Ascending Scans " + space(2) + 369 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 370 space(2) + 371 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 372 space(2) + 373 str(@tmp_int, 10) + space(5) + 374 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 375 print @rptline 376 377 select @tmp_int = value 378 from #tempmonitors 379 where group_name = "access" and 380 field_name = "backward_scans" 381 382 select @rptline = " Descending Scans " + space(2) + 383 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 384 space(2) + 385 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 386 space(2) + 387 str(@tmp_int, 10) + space(5) + 388 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 389 print @rptline 390 391 select @tmp_int = value 392 from #tempmonitors 393 where group_name = "btree" and 394 field_name = "bt_backward_scans" 395 396 select @rptline = " DOL Descending Scans " + space(2) + 397 str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) + 398 space(2) + 399 str(@tmp_int / convert(real, @NumXacts), 12, 1) + 400 space(2) + 401 str(@tmp_int, 10) + space(5) + 402 str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign 403 print @rptline 404 405 print @sum2line 406 407 select @rptline = " Total Scans " + space(2) + 408 str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) + 409 space(2) + 410 str(@tmp_total / convert(real, @NumXacts), 12, 1) + 411 space(2) + 412 str(@tmp_total, 10) 413 print @rptline 414 415 end 416 end 417 print @blankline 418 return 0 419
exec sp_procxmode 'sp_sysmon_index', 'AnyMode' go Grant Execute on sp_sysmon_index to public go
| DEPENDENCIES |
| PROCS AND TABLES USED reads table tempdb..#tempmonitors (1) CALLERS called by proc sybsystemprocs..sp_sysmon_analyze  called by proc sybsystemprocs..sp_sysmon  |
