Home » Databases » Sybase » ASE » Eliminating Temp Table Usage in sp__diskdevice

Eliminating Temp Table Usage in sp__diskdevice

The sp__diskdevice code from Ed Barlow was written 11 years and there are a number of issues that need addressing:

  1. Unnecessary use of temporary tables (#tablename) that can lead to lock contention in the system tables tempdb..sysobjects, tempdb..sysindexes, and tempdb..syscolumns.
  2. It doesn’t handle the logical page size (2k, 4k, 8k, 16k) in an intuitive manner.

For those of you that don’t know, the system variable @@pagesize is the physical page size, which is always 2K.  The @@maxpagesize is the logical page size (2K, 4K, 8K, 16K).  The column size in the master..sysusages table is specified in logical pages while the low and high columns of the master..sysdevices table is specified in physical pages.

Original Code:

/ * Procedure copyright(c) 1995 by Edward M Barlow */
/************************************************************************\
|* Procedure Name:  diskdevice
|*
\************************************************************************/
:r database
go
:r dumpdb
go
if exists (select *
   from   sysobjects
   where  type = "P"
   and  name = "sp__diskdevice_old")
begin
drop proc sp__diskdevice_old
end
go
create procedure sp__diskdevice_old (@devname char(30)=NULL, @dont_format char(1)=null )
as
declare @msg varchar(255)
declare @numpgsmb float  /* Number of Pages per Megabytes */
declare @tapeblocksize int
set nocount on
select @numpgsmb = (1048576. / v.low)
from master.dbo.spt_values v
where v.number = 1 and v.type = "E"
create table #dev_tbl
(
  name  char(30),
  phyname    char(255),
  disk_size float  null,
  status   int  null,
  disk_used float  null,
  mirrored   char(1)  null
)
insert  #dev_tbl
select  name=d.name,
    phyname = d.phyname,
    disk_size=0,
    status=status,
    disk_used=0,
    mirrored=NULL
  from master.dbo.sysdevices d
  where name=isnull(@devname,name)
/* Parallel */
update #dev_tbl
  set mirrored="P"
  where status & 64 = 64
/* Serial */
update #dev_tbl
  set mirrored="S"
  where status & 32 = 32
/* Disabled */
update #dev_tbl
  set mirrored="?"
  where status & 256 = 256
/* Confused */
update #dev_tbl
  set mirrored="?"
  where status & 32 = 32
    and   status & 512 != 512
/*  Add in its size in MB.  */
update #dev_tbl
  set disk_size = (1. + (d.high - d.low)) / @numpgsmb
  from master.dbo.sysdevices d, #dev_tbl
  where d.status & 2 = 2
    and #dev_tbl.name = d.name
update #dev_tbl
  set disk_used = ( select sum(size)
  from master.dbo.sysusages u, master.dbo.sysdevices d
  where d.status & 2 = 2
    and vstart between low and high
    and #dev_tbl.name = d.name
  group by name ) / @numpgsmb
update #dev_tbl
  set name=rtrim(name)+" ("+mirrored+")"
  where mirrored is not null
/*
if @devname is null
begin
  if @dont_format is not null
  begin
    print ""
    print "****** PHYSICAL DISK DEVICES (Mirror info after device name) ******"
  end
end
*/
update #dev_tbl set disk_used=0 where disk_used is null
if @dont_format is not null
  select
      "Device Name"=name,
      "Physical Name"=phyname,
      size=str(disk_size,7,1)+"MB",
      alloc=str(disk_used,7,1)+"MB",
      free=str(disk_size-disk_used,7,1)+"MB"
  from  #dev_tbl
  where status & 2 = 2
else
  select
      "Device Name"=substring(name,1,18),
      "Physical Name"=substring(phyname,1,31),
      size=str(disk_size,6,0)+"MB",
      alloc=str(disk_used,6,0)+"MB",
      free=str(disk_size-disk_used,6,0)+"MB"
  from  #dev_tbl
  where status & 2 = 2
if @devname is not null
begin
  if exists (select  *
        from master.dbo.sysdatabases d, master.dbo.sysusages u,
          master.dbo.sysdevices dv
        where d.dbid = u.dbid
        and dv.low < = size + vstart
        and dv.high >= size + vstart - 1
        and dv.status & 2 = 2
        and dv.name=@devname
  )
  begin
       select  db_name=d.name,
            size = size / @numpgsmb,
            usage = convert(char(18),b.name)
       from master.dbo.sysdatabases d, master.dbo.sysusages u, master.dbo.sysdevices dv,
            master.dbo.spt_values b
       where d.dbid = u.dbid
            and dv.low < = size + vstart
            and dv.high >= size + vstart - 1
            and dv.status & 2 = 2
            and b.type = "S"
            and u.segmap & 7 = b.number
            and dv.name=@devname
       order by db_name,usage
  end
  else
  begin
      if @dont_format is not null
        print "****** Device Unused By Any Databases ******"
  end
end
return (0)
go
/* Give execute privilege to users. This can be removed if you only want
   the sa to have excute privilege on this stored proc */
grant exec on sp__diskdevice_old to public
go
exit

No more temp table code:

/* Procedure copyright(c) 1995 by Edward M Barlow */
/************************************************************************\
|* Procedure Name:  diskdevice
|*
\************************************************************************/
:r database
go
:r dumpdb
go
if exists (select *
     from   sysobjects
     where  type = "P"
     and  name = "sp__diskdevice")
begin
  drop proc sp__diskdevice
end
go
create procedure sp__diskdevice (@devname char(30)=NULL, @dont_format char(1)=null )
as
declare @msg varchar(255)
declare @numpgsmb float  /* Number of Pages per Megabytes */
declare @tapeblocksize int
set nocount on
if @devname is null
begin
  select d.name as "Device Name",
    d.phyname as "Physical Name"
  from master.dbo.sysdevices d
    where d.status & 2 != 2
end
select
  case
  when (@dont_format is null)
    then
    substring(d.name, 1, 18)
  else
    d.name
  end as "Device Name",
  case
  when (@dont_format is not null)
    then
    substring(d.phyname, 1, 31)
  else
    d.phyname
  end as "Physical Name",
  convert(varchar(7),
  convert(int, ((1. + (d.high - d.low))*@@pagesize) / 1048576.)
  ) + "MB" as "size",
  convert(varchar(7),
  (select convert(int, sum(su.size) / (1024 / (@@maxpagesize/1024)))
    from master.dbo.sysusages su, master.dbo.sysdevices sd
    where sd.status & 2 = 2
    and su.vstart between sd.low and sd.high
    and d.name = sd.name)
  ) + "MB" as "alloc",
  convert(varchar(7),
  convert(int,
    ( ((1. + (d.high - d.low))*@@pagesize) / 1048576.) -
    (select convert(int, sum(su.size) / (1024 / (@@maxpagesize/1024)))
    from master.dbo.sysusages su, master.dbo.sysdevices sd
    where sd.status & 2 = 2
    and su.vstart between sd.low and sd.high
    and d.name = sd.name)
  )
  ) + "MB" as "free"
from master.dbo.sysdevices d
where d.status & 2 = 2
  and name=isnull(@devname,name)
if @devname is not null
begin
  if exists (select  *
    from master.dbo.sysdatabases d, master.dbo.sysusages u,
      master.dbo.sysdevices dv
    where d.dbid = u.dbid
    and dv.low < = size + vstart
    and dv.high >= size + vstart - 1
    and dv.status & 2 = 2
    and dv.name=@devname
  )
  begin
     select  db_name=d.name,
      size =  convert(int, u.size / (1024 / (@@maxpagesize/1024))),
      usage = convert(char(18),b.name)
     from master.dbo.sysdatabases d, master.dbo.sysusages u, master.dbo.sysdevices dv,
      master.dbo.spt_values b
     where d.dbid = u.dbid
      and dv.low < = size + vstart
      and dv.high >= size + vstart - 1
      and dv.status & 2 = 2
      and b.type = "S"
      and u.segmap & 7 = b.number
      and dv.name=@devname
     order by db_name,usage
  end
  else
  begin
    if @dont_format is not null
    print "****** Device Unused By Any Databases ******"
  end
end
return (0)
go
/* Give execute privilege to users. This can be removed if you only want
   the sa to have excute privilege on this stored proc */
grant exec on sp__diskdevice to public
go
exit
Share Button

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

Facebook login by WP-FB-AutoConnect