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


exec sp_procxmode 'sp_object_stats', 'AnyMode'
go

Grant Execute on sp_object_stats to public
go
DEFECTS
 PERR 6 Parsing Error Could not find definition for table tempdb..syslkstats 498
 PERR 6 Parsing Error Could not find definition for table tempdb..syslkstats 621
 PERR 6 Parsing Error Could not find definition for table tempdb..syslkstats 653
 PERR 6 Parsing Error Could not find definition for table tempdb..syslkstats 672
 PERR 6 Parsing Error Could not find definition for table tempdb..syslkstats 691
 PERR 6 Parsing Error Could not find definition for table tempdb..syslkstats 710
 PERR 6 Parsing Error Could not find definition for table tempdb..syslkstats 760
 PERR 6 Parsing Error Could not find definition for table tempdb..syslkstats 779
 PERR 6 Parsing Error Could not find definition for table tempdb..syslkstats 798
 PERR 6 Parsing Error Could not find definition for table tempdb..syslkstats 818
 MEST 4 Empty String will be replaced by Single Space 13
 MEST 4 Empty String will be replaced by Single Space 15
 MEST 4 Empty String will be replaced by Single Space 16
 MEST 4 Empty String will be replaced by Single Space 75
 MEST 4 Empty String will be replaced by Single Space 198
 MEST 4 Empty String will be replaced by Single Space 202
 MEST 4 Empty String will be replaced by Single Space 207
 MEST 4 Empty String will be replaced by Single Space 226
 MEST 4 Empty String will be replaced by Single Space 228
 MULT 4 Using literal database 'tempdb' tempdb..sysobjects 152
 MULT 4 Using literal database 'tempdb' tempdb..syslkstats 164
 MULT 4 Using literal database 'tempdb' tempdb..syslkstats 498
 MULT 4 Using literal database 'tempdb' tempdb..syslkstats 621
 MULT 4 Using literal database 'tempdb' tempdb..syslkstats 653
 MULT 4 Using literal database 'tempdb' tempdb..syslkstats 672
 MULT 4 Using literal database 'tempdb' tempdb..syslkstats 691
 MULT 4 Using literal database 'tempdb' tempdb..syslkstats 710
 MULT 4 Using literal database 'tempdb' tempdb..syslkstats 760
 MULT 4 Using literal database 'tempdb' tempdb..syslkstats 779
 MULT 4 Using literal database 'tempdb' tempdb..syslkstats 798
 MULT 4 Using literal database 'tempdb' tempdb..syslkstats 818
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 381
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(11) 381
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 393
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(11) 393
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 405
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(11) 405
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 420
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(13) 420
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 421
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 427
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 435
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(13) 435
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 436
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 443
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 453
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(12) 453
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 454
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 460
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 461
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 469
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(12) 469
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 470
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 475
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 476
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 484
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(12) 484
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 485
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 490
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 491
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 623
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 624
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(12) 641
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(12) 645
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(12) 649
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 654
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 655
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(11) 660
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(11) 664
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(11) 668
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 673
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 674
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(15) 679
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(15) 683
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(15) 687
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 692
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 693
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(14) 698
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(14) 702
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(14) 706
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 711
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 712
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(13) 761
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(13) 762
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(13) 763
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 764
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 767
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 770
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(12) 780
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(12) 781
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(12) 782
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 783
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 786
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 789
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(16) 799
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(16) 800
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(16) 801
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 802
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 805
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 808
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(15) 819
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(15) 820
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(15) 821
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 822
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 825
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 828
 VRUN 4 Variable is read and not initialized @msg 77
 MAW1 3 Warning message on %name% tempdb..sysobjects.id: Warning message on sysobjects 152
 MGTP 3 Grant to public sybsystemprocs..sp_object_stats  
 MGTP 3 Grant to public tempdb..sysobjects  
 MLCH 3 Char type with length>30 char(255) 15
 MLCH 3 Char type with length>30 char(255) 16
 MLCH 3 Char type with length>30 char(80) 31
 MLCH 3 Char type with length>30 char(530) 32
 MUCO 3 Useless Code Useless Brackets 75
 MUCO 3 Useless Code Useless Brackets 86
 MUCO 3 Useless Code Useless Brackets 113
 MUCO 3 Useless Code Useless Brackets 115
 MUCO 3 Useless Code Useless Brackets 123
 MUCO 3 Useless Code Useless Brackets 152
 MUCO 3 Useless Code Useless Brackets 175
 MUCO 3 Useless Code Useless Brackets 198
 MUCO 3 Useless Code Useless Brackets 202
 MUCO 3 Useless Code Useless Brackets 207
 MUCO 3 Useless Code Useless Brackets 226
 MUCO 3 Useless Code Useless Brackets 228
 MUCO 3 Useless Code Useless Brackets 269
 MUCO 3 Useless Code Useless Brackets 502
 MUCO 3 Useless Code Useless Brackets 513
 MUCO 3 Useless Code Useless Brackets 525
 MUCO 3 Useless Code Useless Brackets 527
 MUCO 3 Useless Code Useless Brackets 581
 MUCO 3 Useless Code Useless Brackets 584
 MUCO 3 Useless Code Useless Brackets 598
 MUCO 3 Useless Code Useless Brackets 600
 MUCO 3 Useless Code Useless Brackets 602
 MUCO 3 Useless Code Useless Brackets 617
 MUCO 3 Useless Code Useless Brackets 620
 MUCO 3 Useless Code Useless Brackets 726
 MUCO 3 Useless Code Useless Brackets 728
 MUCO 3 Useless Code Useless Brackets 854
 MUIN 3 Column created using implicit nullability 252
 MZMB 3 Zombie: use of non-existent object tempdb..syslkstats 164
 MZMB 3 Zombie: use of non-existent object tempdb..syslkstats 498
 MZMB 3 Zombie: use of non-existent object tempdb..syslkstats 621
 MZMB 3 Zombie: use of non-existent object tempdb..syslkstats 653
 MZMB 3 Zombie: use of non-existent object tempdb..syslkstats 672
 MZMB 3 Zombie: use of non-existent object tempdb..syslkstats 691
 MZMB 3 Zombie: use of non-existent object tempdb..syslkstats 710
 MZMB 3 Zombie: use of non-existent object tempdb..syslkstats 760
 MZMB 3 Zombie: use of non-existent object tempdb..syslkstats 779
 MZMB 3 Zombie: use of non-existent object tempdb..syslkstats 798
 MZMB 3 Zombie: use of non-existent object tempdb..syslkstats 818
 QISO 3 Set isolation level 138
 QNAJ 3 Not using ANSI Inner Join 760
 QNAJ 3 Not using ANSI Inner Join 779
 QNAJ 3 Not using ANSI Inner Join 798
 QNAJ 3 Not using ANSI Inner Join 818
 VNRD 3 Variable is not read @dummy 120
 CRDO 2 Read Only Cursor Marker (has for read only clause) 516
 CRDO 2 Read Only Cursor Marker (has for read only clause) 561
 MSUB 2 Subquery Marker 152
 MTR1 2 Metrics: Comments Ratio Comments: 27% 12
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 81 = 88dec - 9exi + 2 12
 MTR3 2 Metrics: Query Complexity Complexity: 356 12

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
read_writes table tempdb..#syslkstats_cont (1) 
reads table tempdb..sysobjects (1)