| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_object_stats | | 31 Aug 14 | Defects Dependencies |
1 2 /* Messages for sp_object_stats 3 ** 4 ** 18617, "Usage: sp_object_stats 'hh:mm:ss', @top_n, @dbname, @objname, @rpt_option" 5 ** 18618, "sp_object_stats: Invalid interval string passed '%1!'. It should be in the form 'hh:mm:ss'." 6 ** 18619, "sp_object_stats: Failed to %1! temporary table %2!." 7 ** 18620, "sp_object_stats: If @objname is specified, a valid @dbname must also be specified." 8 ** 18127, "You must have System Administrator (SA) role to execute this 9 ** stored procedure." 10 ** 17260, "Can't run %1! from within a transaction." 11 */ 12 create procedure sp_object_stats 13 @interval char(12) = "", /* Time interval string */ 14 @top_n int = 10, /* Top 'n' number of hot objects to report on */ 15 @dbname char(255) = "", /* Database name */ 16 @objname char(255) = "", /* Object name */ 17 @rpt_option char(11) = "rpt_locks" /* Report option: 18 rpt_locks (default) - reports on objects having non_zero 19 lock contention with detailed 20 lock statistics 21 rpt_objlist - reports on most active objects 22 even if contention is zero with 23 no detailed lock statistics 24 */ 25 as 26 27 /* 28 ** Local variables. 29 */ 30 declare @msg varchar(255) /* message variable */ 31 declare @blankline char(80) 32 declare @rpt_line char(530) 33 declare @lock_scheme_str char(10) 34 declare @iter_count int 35 declare @ldspc char(2) 36 declare @tab char(1) 37 declare @tmp_dbid int 38 declare @tmp_objid int 39 declare @tmp_lkscheme smallint 40 declare @tmp_expg_cont double precision 41 declare @tmp_uppg_cont double precision 42 declare @tmp_shpg_cont double precision 43 declare @tmp_exad_cont double precision 44 declare @tmp_shad_cont double precision 45 declare @tmp_exrow_cont double precision 46 declare @tmp_uprow_cont double precision 47 declare @tmp_shrow_cont double precision 48 declare @AllPages int 49 declare @DataPages int 50 declare @DataRows int 51 declare @strlength int 52 53 /* 54 ** define some constants. 55 */ 56 select @AllPages = 1, @DataPages = 2, @DataRows = 3 57 58 select @blankline = " " 59 select @rpt_line = " " 60 61 select @tab = " " 62 select @ldspc = space(1) 63 64 /* BEGIN: Validate input parameters 65 ** ================================ */ 66 67 /* If no interval or option was passed, display usage */ 68 if (@interval = "") 69 begin 70 raiserror 18617, @msg 71 return 1 72 end 73 74 /* 75 ** Validate that it is a proper time interval string. 76 ** Eg of form "nn:mn:mn" with m between 0 - 5, n 0 - 9. 77 */ 78 79 if (patindex("[0-9][0-9]:[0-5][0-9]:[0-5][0-9]", @interval) = 0 or 80 patindex("00:00:00", @interval) = 1) 81 begin 82 raiserror 18618, @msg, @interval 83 return 1 84 end 85 86 /* END: Validate input parameters 87 ** ============================== */ 88 89 /* BEGIN: Validate permissions for proc 90 ** ==================================== */ 91 92 /* 93 ** Check if OK to Run 94 */ 95 if proc_role("sa_role") < 1 96 begin 97 /* 18127, "You must have System Administrator (SA) role to execute 98 this stored procedure."*/ 99 raiserror 18127 @msg, "sp_object_stats" 100 return 1 101 end 102 103 /* 104 ** In a transaction?, disallow since it might make recovery impossible 105 */ 106 if @@trancount > 0 107 begin 108 /* 17260, "Can't run %1! from within a transaction." */ 109 raiserror 17260 @msg, "sp_object_stats" 110 return 1 111 end 112 113 set chained off 114 set transaction isolation level 1 115 116 /* END: Validate permissions for proc 117 ** ================================== */ 118 119 /* BEGIN: Create repository for dbcc object_stats 120 ** ============================================== */ 121 122 /* 123 ** Drop and create tempdb..syslkstats. 124 ** 125 ** This table will hold the outputs from system monitoring 126 ** and will be analysed by this procedure. 127 */ 128 if (exists (select * from tempdb.dbo.sysobjects where id = 129 (select object_id("tempdb..syslkstats")))) 130 begin 131 drop table tempdb..syslkstats 132 end 133 134 /* 135 ** tempdb..syslkstats is the repository for information from 136 ** dbcc object_stats. So its format reflects the table 137 ** defined in object_stats. 138 */ 139 140 create table tempdb..syslkstats 141 ( 142 dbid smallint, 143 objid int, 144 lockscheme smallint, 145 page_type smallint, 146 stat_name char(30), 147 stat_value double precision 148 ) 149 150 /* bail out if we cannot create this table */ 151 if (@@error != 0) 152 begin 153 raiserror 18619, @msg, "create", "tempdb..syslkstats" 154 return 1 155 end 156 157 /* END: Create repository for dbcc object_stats 158 ** ============================================ */ 159 160 /* BEGIN: Enable object and user level lock statistics monitoring. 161 ** =============================================================== 162 ** NOTE: It is imperative that this be switched off before 163 ** returning from this procedure, else system performance may 164 ** be severely compromised. 165 */ 166 167 dbcc traceon(1213) 168 169 /* 170 ** Determine which mode we are called in. 171 ** If no database or object name is specified, default 172 ** is to sample lock usage. 173 */ 174 if (@dbname = "" and @objname = "") 175 begin 176 dbcc object_stats("init_locks") 177 end 178 else if (@dbname != "" and @objname = "") 179 begin 180 select @tmp_dbid = db_id(rtrim(@dbname)) 181 dbcc object_stats("init_locks", @tmp_dbid) 182 end 183 else if (@dbname != "" and @objname != "") 184 begin 185 select @tmp_dbid = db_id(rtrim(@dbname)) 186 select @tmp_objid = object_id(rtrim(@dbname) + ".." + rtrim(@objname)) 187 dbcc object_stats("init_locks", @tmp_dbid, @tmp_objid) 188 end 189 else 190 begin 191 raiserror 18620, @msg 192 dbcc traceoff(1213) 193 /* =================== */ 194 return 1 195 end 196 197 /* 198 ** Leave dbcc to collect stats for the specified interval 199 */ 200 waitfor delay @interval 201 202 if (@dbname = "" and @objname = "") 203 dbcc object_stats("insert_locks") 204 else if (@dbname != "" and @objname = "") 205 dbcc object_stats("insert_locks", @tmp_dbid) 206 else 207 dbcc object_stats("insert_locks", @tmp_dbid, @tmp_objid) 208 209 /* 210 ** Now we have collected the statistics, switch the traceflag off. 211 */ 212 dbcc traceoff(1213) 213 /*=================*/ 214 215 /* END: Enable object and user level lock statistics monitoring. 216 ** =============================================================*/ 217 218 /* BEGIN: Process data and look for contention. 219 ** ============================================ */ 220 221 /* 222 ** Now create a temporary table to store analysis of results. 223 */ 224 if object_id("#syslkstats_cont") is not null 225 begin 226 drop table #syslkstats_cont 227 end 228 create table #syslkstats_cont 229 ( 230 dbid smallint, 231 objid int, 232 lockscheme smallint, 233 expg_cont double precision, 234 uppg_cont double precision, 235 shpg_cont double precision, 236 exad_cont double precision, 237 shad_cont double precision, 238 exrow_cont double precision, 239 uprow_cont double precision, 240 shrow_cont double precision 241 ) 242 /* 243 ** Did the create table work? 244 */ 245 if (@@error != 0) 246 begin 247 raiserror 18619, @msg, "create", "#syslkstats_cont" 248 return 1 249 end 250 251 /* 252 ** Contention Algorithm 253 ** ==================== 254 ** 255 ** For each of the distinct database, object, lockscheme's 256 ** we look for the contention, which is measured as a 257 ** percentage of the 'waiting' time to the total time. 258 ** Total time is the sum of the waiting, granting and deadlock 259 ** time for an object in a database, and will depend on 260 ** the locking scheme used. 261 ** 262 ** The fields to be reported are 263 ** 264 ** dbid 265 ** objid 266 ** lockscheme Allpages, Datapages, Datarow = 1, 2, 3 267 ** expg_cont Exclusive page contention (%) 268 ** uppg_cont Update page contention (%) 269 ** shpg_cont Shared page contention (%) 270 ** exad_cont Exclusive address contention (%) 271 ** shad_cont Shared address contention (%) 272 ** exrow_cont Exclusive row contention (%) 273 ** uprow_cont Update row contention (%) 274 ** shrow_cont Shared row contention (%) 275 ** 276 ** These calculations are coded using a case statement since this 277 ** offers tremendous flexibility to define "functions" that can 278 ** be applied to each column. 279 ** 280 ** Since we wish to avoid "division by zero" exceptions we use 281 ** nullif function to return null if the denominator of the fraction is 0, 282 ** and hence the division results in NULL. As we do not want the tuple to 283 ** contain nulls, we use isnull to switch nulls to 0. 284 ** 285 ** We can classify the domain of the contention functions as follows: 286 ** 287 ** Locking Mode 288 ** attribute AP DP DR 289 ** 290 ** expg_cont f1 f1 0 291 ** uppg_cont f1 f1 0 292 ** shpg_cont f1 f1 0 293 ** exad_cont f2 0 0 294 ** shad_cont f2 0 0 295 ** exrow_cont f2 0 0 296 ** uprow_cont 0 0 f3 297 ** shrow_cont 0 0 f3 298 ** 299 ** where 300 ** 301 ** f1(mode) = isnull{ 302 ** 100 * Sum( stat_value if stat_name = "mode_pg_wait" 303 ** 0 otherwise) 304 ** ----------------------------------------------------------- 305 ** nullif ( Sum( stat_value if stat_name = ("mode_pg_wait" 306 ** | "mode_pg_grants" 307 ** | "mode_pg_deadlocks") 308 ** 0 otherwise 309 ** , 0) 310 ** , 0) 311 ** 312 ** and mode is {ex, up, sh} 313 ** 314 ** f2(mode) = isnull{ 315 ** 100 * Sum ( stat_value if stat_name = "mode_addr_wait" 316 ** and lockscheme = @AllPages 317 ** and page_type = 1 318 ** 0 otherwise) 319 ** ----------------------------------------------------------- 320 ** nullif ( sum( stat_value if stat_name = ("mode_addr_wait" 321 ** | "mode_addr_grants" 322 ** | "mode_addr_deadlocks") 323 ** and lockscheme = @AllPages 324 ** and page_type = 1 325 ** 0 otherwise) 326 ** , 0) 327 ** , 0) 328 ** 329 ** and mode is {ex, sh} 330 ** 331 ** f3(mode) = isnull{ 332 ** 100 * Sum ( stat_value if stat_name = "mode_row_wait" 333 ** and lockscheme = @DataRow 334 ** and page_type = 0 335 ** 0 otherwise) 336 ** ----------------------------------------------------------- 337 ** nullif ( sum( stat_value if stat_name = ("mode_row_wait" 338 ** | "mode_row_grants" 339 ** | "mode_row_deadlocks") 340 ** and lockscheme = @DataRow 341 ** and page_type = 0 342 ** 0 otherwise) 343 ** , 0) 344 ** , 0) 345 ** 346 ** and mode is {ex, up, sh} 347 ** 348 */ 349 350 insert into #syslkstats_cont(dbid, objid, lockscheme, expg_cont, 351 uppg_cont, shpg_cont, exad_cont, shad_cont, 352 exrow_cont, uprow_cont, shrow_cont) 353 select distinct dbid, objid, lockscheme, 354 355 -- f1 --> AllPage and Data Page lock statistics 356 357 isnull(100 * sum(case when stat_name = "ex_pg_waits" and lockscheme != @DataRows 358 then stat_value 359 else 0 360 end) 361 / nullif (sum(case when stat_name in ("ex_pg_grants", "ex_pg_waits", 362 "ex_pg_deadlocks") 363 then stat_value 364 else 0 365 end) 366 , 0) 367 , 0) as expg_cont, 368 369 isnull(100 * sum(case when stat_name = "up_pg_waits" and lockscheme != @DataRows 370 then stat_value 371 else 0 372 end) 373 / nullif (sum(case when stat_name in ("up_pg_grants", "up_pg_waits", 374 "up_pg_deadlocks") 375 then stat_value 376 else 0 377 end) 378 , 0) 379 , 0) as uppg_cont, 380 381 isnull(100 * sum(case when stat_name = "sh_pg_waits" and lockscheme != @DataRows 382 then stat_value 383 else 0 384 end) 385 / nullif (sum(case when stat_name in ("sh_pg_grants", "sh_pg_waits", 386 "sh_pg_deadlocks") 387 then stat_value 388 else 0 389 end) 390 , 0) 391 , 0) as shpg_cont, 392 393 -- f2 --> AllPage specific lock Statistics 394 395 isnull( 396 100 * sum(case when stat_name = "ex_addr_waits" and lockscheme = @AllPages 397 and page_type = 1 398 then stat_value 399 else 0 400 end) 401 / nullif (sum(case when stat_name in ("ex_addr_grants", 402 "ex_addr_waits", "ex_addr_deadlocks") 403 and lockscheme = @AllPages and page_type = 1 404 then stat_value 405 else 0 406 end) 407 , 0) 408 , 0) as exad_cont, 409 410 isnull( 411 100 * sum(case when stat_name = "sh_addr_waits" and lockscheme = @AllPages 412 and page_type = 1 413 then stat_value 414 else 0 415 end) 416 / nullif ( 417 sum(case when stat_name in ("sh_addr_grants", "sh_addr_waits", 418 "sh_addr_deadlocks") 419 and lockscheme = @AllPages and page_type = 1 420 then stat_value 421 else 0 422 end) 423 , 0) 424 , 0) as shad_cont, 425 426 427 -- DataRow locking statistics 428 isnull( 429 100 * (case when stat_name = "ex_row_waits" and lockscheme = @DataRows 430 and page_type = 0 431 then stat_value 432 else 0 433 end) 434 / nullif (sum(case when stat_name in ("ex_row_grants", 435 "ex_row_waits", "ex_row_deadlocks") 436 and lockscheme = @DataRows 437 and page_type = 0 438 then stat_value 439 else 0 440 end) 441 , 0) 442 , 0) as exrow_cont, 443 444 isnull( 445 100 * sum(case when stat_name = "up_row_waits" and lockscheme = @DataRows 446 and page_type = 0 447 then stat_value 448 else 0 449 end) 450 / nullif (sum(case when stat_name in ("up_row_grants", "up_row_waits", 451 "up_row_deadlocks") and lockscheme = @DataRows 452 and page_type = 0 453 then stat_value 454 else 0 455 end) 456 , 0) 457 , 0) as uprow_cont, 458 459 isnull( 460 100 * sum(case when stat_name = "sh_row_waits" and lockscheme = @DataRows 461 and page_type = 0 462 then stat_value 463 else 0 464 end) 465 / nullif (sum(case when stat_name in ("sh_row_grants", "sh_row_waits", 466 "sh_row_deadlocks") and lockscheme = @DataRows 467 and page_type = 0 468 then stat_value 469 else 0 470 end) 471 , 0) 472 , 0) as shrow_cont 473 474 from tempdb..syslkstats 475 group by dbid, objid 476 477 /* did this insert fail? */ 478 if (@@error != 0) 479 begin 480 raiserror 18619, @msg, "insert into", "#syslkstats_cont" 481 return 1 482 end 483 484 /* Reporting phase 485 ** =============== */ 486 487 select @iter_count = 0 488 489 if (@rpt_option = "rpt_objlist") 490 begin 491 declare obj_rpt_csr cursor for 492 select dbid, objid 493 from #syslkstats_cont 494 order by 495 (expg_cont + uppg_cont + shpg_cont + 496 exad_cont + shad_cont + 497 exrow_cont + uprow_cont + shrow_cont) desc 498 for read only 499 open obj_rpt_csr 500 fetch obj_rpt_csr into @tmp_dbid, @tmp_objid 501 while (@@sqlstatus = 0 and @iter_count < @top_n) 502 begin 503 if (@iter_count = 0) 504 begin 505 print @blankline 506 select @rpt_line = "List of the top " + 507 ltrim(str(@top_n)) + 508 " (or less) most frequently accessed objects:" 509 print @rpt_line 510 select @rpt_line = "----------------" + 511 replicate('-', datalength(ltrim(str(@top_n)))) + 512 "-------------------------------------------" 513 print @rpt_line 514 print @blankline 515 end 516 517 select @strlength = char_length(@ldspc + ltrim(str(@iter_count + 1)) + ")") 518 519 select @rpt_line = @ldspc + ltrim(str(@iter_count + 1)) + ")" + " " + 520 db_name(@tmp_dbid) + ".." + object_name(@tmp_objid, @tmp_dbid) 521 print @rpt_line 522 select @rpt_line = space(@strlength) + " " + 523 "(dbid=" + ltrim(str(@tmp_dbid)) + ", objid=" + ltrim(str(@tmp_objid)) + ")" 524 print @rpt_line 525 print @blankline 526 527 select @iter_count = @iter_count + 1 528 529 fetch obj_rpt_csr into @tmp_dbid, @tmp_objid 530 end 531 close obj_rpt_csr 532 deallocate cursor obj_rpt_csr 533 end 534 else 535 begin 536 declare stats_rpt_csr cursor for 537 select expg_cont, uppg_cont, shpg_cont, 538 exad_cont, shad_cont, 539 exrow_cont, uprow_cont, shrow_cont, 540 dbid, objid, lockscheme 541 from #syslkstats_cont 542 where (expg_cont + uppg_cont + shpg_cont + 543 exad_cont + shad_cont + 544 exrow_cont + uprow_cont + shrow_cont) > 0 545 order by 546 (expg_cont + uppg_cont + shpg_cont + 547 exad_cont + shad_cont + 548 exrow_cont + uprow_cont + shrow_cont) desc 549 for read only 550 551 open stats_rpt_csr 552 fetch stats_rpt_csr into @tmp_expg_cont, @tmp_uppg_cont, @tmp_shpg_cont, 553 @tmp_exad_cont, @tmp_shad_cont, 554 @tmp_exrow_cont, @tmp_uprow_cont, @tmp_shrow_cont, 555 @tmp_dbid, @tmp_objid, @tmp_lkscheme 556 557 while (@@sqlstatus = 0 and @iter_count < @top_n) 558 begin 559 560 if (@iter_count = 0) 561 begin 562 print @blankline 563 select @rpt_line = "Lock statistics for the top " + 564 ltrim(str(@top_n)) + 565 " (or less) most contended objects:" 566 print @rpt_line 567 select @rpt_line = "----------------------------" + 568 replicate('-', datalength(ltrim(str(@top_n)))) + 569 "---------------------------------" 570 print @rpt_line 571 print @blankline 572 end 573 574 if (@tmp_lkscheme = @AllPages) 575 select @lock_scheme_str = "Allpages" 576 else if (@tmp_lkscheme = @DataPages) 577 select @lock_scheme_str = "Datapages" 578 else if (@tmp_lkscheme = @DataRows) 579 select @lock_scheme_str = "Datarows" 580 581 print @blankline 582 select @strlength = char_length("Object Name:") 583 select @rpt_line = "Object Name:" + " " + db_name(@tmp_dbid) + ".." + 584 object_name(@tmp_objid, @tmp_dbid) 585 print @rpt_line 586 select @rpt_line = space(@strlength) + " " + 587 "(dbid=" + ltrim(str(@tmp_dbid)) + ", objid=" + 588 ltrim(str(@tmp_objid)) + ", lockscheme=" + 589 rtrim(@lock_scheme_str) + ")" 590 print @rpt_line 591 print @blankline 592 593 if (@tmp_lkscheme != @DataRows) 594 begin 595 596 if (exists (select * 597 from tempdb..syslkstats 598 where stat_name like "%_pg_%" 599 and dbid = @tmp_dbid 600 and objid = @tmp_objid)) 601 begin 602 603 print @blankline 604 605 select @rpt_line = rtrim(@ldspc + "Page Locks" + 606 @tab + " SH_PAGE " + @tab + @tab + 607 " UP_PAGE " + @tab + @tab + " EX_PAGE ") 608 print @rpt_line 609 select @rpt_line = rtrim(@ldspc + "----------" + 610 @tab + "----------" + @tab + @tab + "----------" + 611 @tab + @tab + "----------") 612 print @rpt_line 613 select @rpt_line = rtrim(@ldspc + "Grants:" + @tab + 614 /* the field is unsigned int32 that means we can only have 615 ** 10 characters in it 616 */ 617 str(sum(case when stat_name = "sh_pg_grants" 618 then stat_value 619 else 0 620 end), 10) + @tab + @tab + 621 str(sum(case when stat_name = "up_pg_grants" 622 then stat_value 623 else 0 624 end), 10) + @tab + @tab + 625 str(sum(case when stat_name = "ex_pg_grants" 626 then stat_value 627 else 0 628 end), 10)) 629 from tempdb..syslkstats 630 where dbid = @tmp_dbid 631 and objid = @tmp_objid 632 633 print @rpt_line 634 635 select @rpt_line = rtrim(@ldspc + "Waits:" + @tab + 636 str(sum(case when stat_name = "sh_pg_waits" 637 then stat_value 638 else 0 639 end), 10) + @tab + @tab + 640 str(sum(case when stat_name = "up_pg_waits" 641 then stat_value 642 else 0 643 end), 10) + @tab + @tab + 644 str(sum(case when stat_name = "ex_pg_waits" 645 then stat_value 646 else 0 647 end), 10)) 648 from tempdb..syslkstats 649 where dbid = @tmp_dbid 650 and objid = @tmp_objid 651 652 print @rpt_line 653 654 select @rpt_line = rtrim(@ldspc + "Deadlocks:" + @tab + 655 str(sum(case when stat_name = "sh_pg_deadlocks" 656 then stat_value 657 else 0 658 end), 10) + @tab + @tab + 659 str(sum(case when stat_name = "up_pg_deadlocks" 660 then stat_value 661 else 0 662 end), 10) + @tab + @tab + 663 str(sum(case when stat_name = "ex_pg_deadlocks" 664 then stat_value 665 else 0 666 end), 10)) 667 from tempdb..syslkstats 668 where dbid = @tmp_dbid 669 and objid = @tmp_objid 670 671 print @rpt_line 672 673 select @rpt_line = rtrim(@ldspc + "Wait-time:" + @tab + 674 str(sum(case when stat_name = "sh_pg_waittime" 675 then stat_value 676 else 0 677 end), 10) + " ms" + @tab + @tab + 678 str(sum(case when stat_name = "up_pg_waittime" 679 then stat_value 680 else 0 681 end), 10) + " ms" + @tab + @tab + 682 str(sum(case when stat_name = "ex_pg_waittime" 683 then stat_value 684 else 0 685 end), 10) + " ms") 686 from tempdb..syslkstats 687 where dbid = @tmp_dbid 688 and objid = @tmp_objid 689 690 print @rpt_line 691 692 select @rpt_line = rtrim(@ldspc + "Contention:" + 693 + @tab + str(@tmp_shpg_cont, 10, 2) + "%%" + 694 + @tab + @tab + str(@tmp_uppg_cont, 10, 2) + "%%" + @tab + @tab + 695 str(@tmp_expg_cont, 10, 2)) + "%%" 696 697 print @rpt_line 698 699 print @blankline 700 end 701 702 if ((@tmp_shpg_cont + @tmp_uppg_cont + @tmp_expg_cont) > 15.0) 703 begin 704 if (@tmp_lkscheme = @AllPages) 705 begin 706 select @rpt_line = " *** Consider altering " + 707 db_name(@tmp_dbid) + ".." + 708 object_name(@tmp_objid, @tmp_dbid) + 709 " to Datapages locking." 710 print @rpt_line 711 end 712 else 713 begin 714 select @rpt_line = " *** Consider altering " + 715 db_name(@tmp_dbid) + ".." + 716 object_name(@tmp_objid, @tmp_dbid) + 717 " to Datarows locking." 718 print @rpt_line 719 end 720 end 721 end 722 else 723 begin 724 print @blankline 725 726 select @rpt_line = rtrim(@ldspc + "Row Locks" + @tab + " 727 SH_ROW " + @tab + @tab + 728 " UP_ROW " + @tab + @tab + " EX_ROW ") 729 print @rpt_line 730 select @rpt_line = rtrim(@ldspc + "----------" + @tab + "----------" + 731 @tab + @tab + "----------" + @tab + @tab + "----------") 732 print @rpt_line 733 select @rpt_line = rtrim(@ldspc + "Grants:" + 734 @tab + str(s.stat_value, 10) + @tab + @tab + 735 str(u.stat_value, 10) + @tab + @tab + str(e.stat_value, 10)) 736 from tempdb..syslkstats s, tempdb..syslkstats u, tempdb..syslkstats e 737 where s.stat_name = "sh_row_grants" 738 and u.stat_name = "up_row_grants" 739 and e.stat_name = "ex_row_grants" 740 and s.dbid = @tmp_dbid 741 and s.dbid = u.dbid 742 and u.dbid = e.dbid 743 and s.objid = @tmp_objid 744 and s.objid = u.objid 745 and u.objid = e.objid 746 and s.page_type = 0 747 and s.page_type = u.page_type 748 and u.page_type = e.page_type 749 750 print @rpt_line 751 752 select @rpt_line = rtrim(@ldspc + "Waits:" + @tab + 753 str(s.stat_value, 10) + @tab + @tab + 754 str(u.stat_value, 10) + @tab + @tab + str(e.stat_value, 10)) 755 from tempdb..syslkstats s, tempdb..syslkstats u, tempdb..syslkstats e 756 where s.stat_name = "sh_row_waits" 757 and u.stat_name = "up_row_waits" 758 and e.stat_name = "ex_row_waits" 759 and s.dbid = @tmp_dbid 760 and s.dbid = u.dbid 761 and u.dbid = e.dbid 762 and s.objid = @tmp_objid 763 and s.objid = u.objid 764 and u.objid = e.objid 765 and s.page_type = 0 766 and s.page_type = u.page_type 767 and u.page_type = e.page_type 768 769 print @rpt_line 770 771 select @rpt_line = rtrim(@ldspc + "Deadlocks:" + 772 @tab + str(s.stat_value, 10) + @tab + @tab + 773 str(u.stat_value, 10) + @tab + @tab + str(e.stat_value, 10)) 774 from tempdb..syslkstats s, tempdb..syslkstats u, tempdb..syslkstats e 775 where s.stat_name = "sh_row_deadlocks" 776 and u.stat_name = "up_row_deadlocks" 777 and e.stat_name = "ex_row_deadlocks" 778 and s.dbid = @tmp_dbid 779 and s.dbid = u.dbid 780 and u.dbid = e.dbid 781 and s.objid = @tmp_objid 782 and s.objid = u.objid 783 and u.objid = e.objid 784 and s.page_type = 0 785 and s.page_type = u.page_type 786 and u.page_type = e.page_type 787 788 print @rpt_line 789 790 select @rpt_line = rtrim(@ldspc + "Wait-time:" + 791 @tab + str(s.stat_value, 10) + " ms" + 792 @tab + @tab + str(u.stat_value, 10) + " ms" + 793 @tab + @tab + str(e.stat_value, 10) + " ms") 794 from tempdb..syslkstats s, tempdb..syslkstats u, tempdb..syslkstats e 795 where s.stat_name = "sh_row_waittime" 796 and u.stat_name = "up_row_waittime" 797 and e.stat_name = "ex_row_waittime" 798 and s.dbid = @tmp_dbid 799 and s.dbid = u.dbid 800 and u.dbid = e.dbid 801 and s.objid = @tmp_objid 802 and s.objid = u.objid 803 and u.objid = e.objid 804 and s.page_type = 0 805 and s.page_type = u.page_type 806 and u.page_type = e.page_type 807 808 print @rpt_line 809 810 select @rpt_line = rtrim(@ldspc + "Contention:" + 811 @tab + str(@tmp_shrow_cont, 10, 2) + 812 "%%" + @tab + @tab + 813 str(@tmp_uprow_cont, 10, 2) + "%%" + 814 @tab + @tab + str(@tmp_exrow_cont, 10, 2)) + "%%" 815 print @rpt_line 816 817 print @blankline 818 end 819 820 fetch stats_rpt_csr into @tmp_expg_cont, @tmp_uppg_cont, @tmp_shpg_cont, 821 @tmp_exad_cont, @tmp_shad_cont, 822 @tmp_exrow_cont, @tmp_uprow_cont, @tmp_shrow_cont, 823 @tmp_dbid, @tmp_objid, @tmp_lkscheme 824 select @iter_count = @iter_count + 1 825 826 end 827 close stats_rpt_csr 828 deallocate cursor stats_rpt_csr 829 830 if (@iter_count = 0) 831 begin 832 print "----------------------------" 833 print "No contention on any tables!" 834 print "----------------------------" 835 end 836 end 837 return 0 838
exec sp_procxmode 'sp_object_stats', 'AnyMode' go Grant Execute on sp_object_stats to public go
| DEPENDENCIES |
| PROCS AND TABLES USED reads table tempdb..sysobjects (1)  read_writes table tempdb..#syslkstats_cont (1) |
