print 'sp_hotobjects'
SETUSER 'dbo'
go
use sybsystemprocs
go
drop proc sp_hotobjects
go
create procedure sp_hotobjects
@interval char(12) = "", /* time interval string */
@interval_sample char(12) = "00:05:00" /* sample interval every 5 minutes by default */
as
declare @TmpTime datetime /* temporary datetime variable */
declare @Seconds int /* Interval converted to seconds */
declare @Endtime datetime
create table #hotobjects_totals
(dbname char(30) not null,
objname char(30) not null,
lockcount int null,
locktable int null,
lockshared int null,
lockexclusive int null,
lockrow int null,
lockpage int null,
lockblk int null
)
create table #hotobjects
(dbname char(30) not null,
objname char(30) not null,
lockcount int null,
locktype int not null,
primary key(dbname, objname, locktype))
/* loop for the interval specified */
select @TmpTime = convert(datetime, @interval)
select @Seconds = datepart(hour,@TmpTime)*3600+datepart(minute,@TmpTime)*60+datepart(second,@TmpTime)
select @Endtime = dateadd(second, @Seconds, getdate())
/* create a holding table */
select dbname, objname into #hotobjects_holding
from #hotobjects where 1=2
while (getdate() < @Endtime)
begin
/* populate the initial records */
delete from #hotobjects
insert into #hotobjects(dbname, objname, lockcount, locktype)
select distinct db_name(dbid), object_name(id,dbid), count(type), type from master..syslocks
where object_name(id,dbid) not like "#%" -- and object_name(id,dbid) not like "sys%"
and object_name(id,dbid) not like "hot%" group by type
waitfor delay @interval_sample
/* add a record into #hotobjects_totals if it does not exist */
if not exists(select 1 from #hotobjects_totals TOT, #hotobjects HOT
where TOT.dbname = HOT.dbname and TOT.objname = HOT.objname)
/* add this new lock record */
begin
/* populate a holding table */
delete from #hotobjects_holding
insert into #hotobjects_holding
select distinct dbname, objname from #hotobjects
/* now delete from the holding table all records we have done before */
delete from #hotobjects_holding
from #hotobjects_holding HOLD, #hotobjects_totals TOT
where HOLD.dbname = TOT.dbname and HOLD.objname = TOT.objname
/* what is left is the new records.....add these into the totals table */
insert into #hotobjects_totals(dbname, objname, lockcount, locktable, lockshared,
lockexclusive, lockrow, lockpage, lockblk)
select distinct HOLD.dbname, HOLD.objname, 0, 0,0,0,0,0,0
from #hotobjects_holding HOLD
end
/* from here on we will update this record */
update #hotobjects_totals
set lockcount = TOT.lockcount + HOT.lockcount
from #hotobjects_totals TOT, #hotobjects HOT
where TOT.dbname = HOT.dbname and TOT.objname = HOT.objname
update #hotobjects_totals
set locktable = locktable + HOT.lockcount
from #hotobjects_totals TOT, #hotobjects HOT
where TOT.dbname = HOT.dbname and TOT.objname = HOT.objname
and HOT.locktype in (1,2)
update #hotobjects_totals
set lockshared = lockshared + HOT.lockcount
from #hotobjects_totals TOT, #hotobjects HOT
where TOT.dbname = HOT.dbname and TOT.objname = HOT.objname
and HOT.locktype in (2,6,9)
update #hotobjects_totals
set lockexclusive = lockexclusive + HOT.lockcount
from #hotobjects_totals TOT, #hotobjects HOT
where TOT.dbname = HOT.dbname and TOT.objname = HOT.objname
and HOT.locktype in (1,5,8)
update #hotobjects_totals
set lockrow = lockrow + HOT.lockcount
from #hotobjects_totals TOT, #hotobjects HOT
where TOT.dbname = HOT.dbname and TOT.objname = HOT.objname
and HOT.locktype in (8,9,10)
update #hotobjects_totals
set lockpage = lockpage + HOT.lockcount
from #hotobjects_totals TOT, #hotobjects HOT
where TOT.dbname = HOT.dbname and TOT.objname = HOT.objname
and HOT.locktype in (5,6,7)
update #hotobjects_totals
set lockblk = lockblk + HOT.lockcount
from #hotobjects_totals TOT, #hotobjects HOT
where TOT.dbname = HOT.dbname and TOT.objname = HOT.objname
and HOT.locktype > 255 and HOT.locktype < 269
end
select "In " + rtrim(dbname) + "the table " + rtrim(objname) + " had " +
case
when locktable > 1 then "table level, "
when lockshared > 1 then "shared, "
when lockexclusive > 1 then "exclusive, "
when lockrow > 1 then "row, "
when lockpage > 1 then "page, "
when lockblk > 1 then "blocking "
end
+ " locks"
from #hotobjects_totals
return (0)
go
grant exec on sp_hotobjects to public
go