Drew Montgomery’s sp__dbsubspace for SAP Sybase ASE space usage

A few years back we were both working at a company that needed a method to obtain Sybase ASE database space usage every few minutes. The output of Ed Barlow’s sp__dbspace was fine but it was an expensive call. Drew Montgomery devised a set of stored procedures that would partially cache the results. Kudos to Drew!

With large databases, several of the calculations would overflow. I’m made only minor changes, mainly convert(numeric(19,5), … ) inside the sum()s.

sp__dbsubspace output:

1> sp__dbsubspace
2> go
 Name         Data MB       Used MB        Percent Log MB    Log Used  Log Pct Total MB
 ------------ ------------- -------------- ------- --------- --------- ------- ---------------
 mydb               4836323      4134753.0   85.49     87079  48793.15   56.03         4923402
(return status = 0)

sp_dbsubspace code:

/************************************************************************************************************************************************
 *  This process will use a persistant table called sysdbspaceinfo.  It is not a previously defined sybase system table, but  
 *  is used by the sp__dbsubspace procedure.  The database that this table resides in is defined by the "sybsystemdb" 
 *  marker.  Please replace the "sybsystemdb" marker with an actual database prior to installing this procedure.  In some    
 *  cases you may want to use something like sybsystemprocs or sybsystemdb.  You may also use tempdb, but if you do
 *  you may want to define the table in the model database as well, so it will exist when the server is restarted.  Good luck
 *  and enjoy.                                                                        Drew Montgomery
 ************************************************************************************************************************************************/

use master
go

/*  This is to set the Truncate Log on Checkpoint option for the database with the space check table on, so it doesn't fill up.
 *     This was done in case the database being used for this doesn't have its logs regularly cleaned (some sybase system
 *     databases are like that).  If the database you are going to use has a regular log cleanup procedure, you may comment 
 *     out this section.                         DM                                                                                                                       */
sp_dboption sybsystemdb, "trunc. log", true
go

use sybsystemdb
go

checkpoint
go

/* Drops the table if it already exists, as a precaution for the create */
if object_id('sysdbspaceinfo') is not null  
 drop table sysdbspaceinfo
go

create table sysdbspaceinfo 
   (dbid int, 
    DataPages numeric(19,5), 
    DataPagesUsed numeric(19,5), 
    LogPages numeric(19,5),
    LogPagesUsed numeric(19,5), 
    LogFirstPage int,
    NumberOfObjects int    )
go

/* Unique index is required for the isolation level, and makes the access faster! */
create unique index UCsysdbspaceinfo on sysdbspaceinfo(dbid)
go

/* Go to the sybsystemprocs to install the procedure */
use sybsystemprocs
go

/* Drop it first, as a precaution */
if object_id("sp__dbsubspace") is not null
  drop procedure sp__dbsubspace
go

create procedure sp__dbsubspace 
          @dont_format char(1) = NULL,          /* Flag for indicating the need to have more or less formatted information */
          @LogThreshold float = 0.01,              /* Change threshold for the amount of log "movement" before a recalculation of the data space is performed.
                                                                   The default value of 0.01 represents a 1.0% change in the amont of log space or the first page pointer of the log.
                                                                   Please note: it is theoretically possible that the log pointer could move just less than this amount and have 
                                                                   an amount of log space used that is just less than this amount, therefore it is POSSIBLE that change could be 
                                                                   two times this amount before a recalculation of the data space is performend.  Possible but not very likely.        */
           @Force varchar(10) = NULL
                                                                    
as
/* ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 *  This procedure conceived and written by Drew Montgomery - please forward any and all substantial updates to 
 *        drew_montgomery@ameritech.net (so I can make this procedure better).  Thank you.                               
 * ------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

set nocount on
set lock nowait
set transaction isolation level 0

/* Declaration Section */
declare     @MixedFlag char,                                 /* Flag to signify if log and data are on the same device */
                @DataPages numeric(19,5),                  /* Number of data pages defined from sysusages */
                @LogPages numeric(19,5),                    /* Number of log pages defined from sysusages */
                @TotalPages numeric(19,5),                    /* Number of pages defined from sysusages */
                @DataPagesUsed numeric(19,5),           /* Number of data pages currently in use (reserved) */
                @LogPagesUsed numeric(19,5),             /* Number of log pages currently in use */
                @LogFirstPage bigint,                                /* First defined page of the transaction log */
                @NumberOfObjects int,                       /* for determining if a table has been dropped or added */
                @OldDataPages numeric(19,5),              /* Previous number of data pages defined from sysusages */
                @OldLogPages numeric(19,5),                /* Previous number of log pages defined from sysusages */
                @OldLogPagesUsed numeric(19,5),        /* Previous number of data pages being used */
                @OldDataPagesUsed numeric(19,5),       /* Previous number of log pages being used */
                @OldLogFirstPage int,                            /* Previous transaction log first page pointer */
                @OldNumberOfObjects int,                  /* Previous number of user tables in this database */
--                @LogDelta numeric(19,5),                       /* Change in the number of log pages being used */
--                @LFPDelta int,                                       /* Change in the location of the first log page */
                @scale numeric(19,5),                            /*  Multiplier used for displaying amount of space from number of pages -
                                                                                 this value is derived from the @@maxpagesize */
                @ReSize char,                                       /* Flag indicating if we need to recalculate the amount of data space being used */
                @pct_used numeric(10,2),                       /* Calculated percentage of data space used */
                @log_pct_used numeric(10,2)                  /* Calculated percentage of log space used */

set @ReSize = 'F'            /* Predefined as "No, thanks, we don't need to get a new reading of the data space being used -
                                          the old value will do just fine.   */

/* Determine the current data and log space allocated to this dabase as defined from sysusages */
select @DataPages = sum(case when segmap=4 then 0 else convert(numeric(19,5), size) end),
          @LogPages = sum(case when segmap & 4=4 then convert(numeric(19,5), size) else 0 end),
          @TotalPages = sum(convert(numeric(19,5), size))
  from master..sysusages where dbid = db_id()

/* If there are no log pages (or the value is null), then the system is defined as having Mixed data and log space -
 *   which also means that the number of potential log pages is the same as the number of data pages.              */
if isnull(@LogPages,0) = 0
begin
  set @MixedFlag = 'M'  -- Mode is Mixed
  set @LogPages=@DataPages
end
else if exists (select 1 from master..sysusages where dbid = db_id() and segmap &5 =5)
  set @MixedFlag = "C"  -- Stands for Confused
else
  set @MixedFlag = 'P'  -- Indicates Pristine

/* We are getting the number of pages being used by the syslogs table (table id = 8).
 * The first select line is "Prior to system 15 version", the second is the "System 15 or above" version-
 *   Please set the appropriate comment as to which kind of system is being used */
/*  select @LogPagesUsed = reserved_pgs(id, doampg), */
select @LogPagesUsed = reserved_pages(db_id(), id), 
          @LogFirstPage = first
  from sysindexes
 where id = 8

/* Extract the information from previous executions of sp__dbsubspace */
select @OldDataPages = DataPages, 
          @OldDataPagesUsed = DataPagesUsed,
          @OldLogPages = LogPages,
          @OldLogPagesUsed = LogPagesUsed,
          @OldLogFirstPage = LogFirstPage,
          @OldNumberOfObjects = NumberOfObjects
  from sybsystemdb..sysdbspaceinfo
where dbid = db_id()

/* Get an object count of the USER TABLES */
select @NumberOfObjects = count(*) from sysobjects where type = 'U'

/* If there are no records retrieved (first run) we need to Recalculate the size */
if @@rowcount = 0
begin
  set @ReSize = 'T'
end
else        
  if @OldDataPages != @DataPages           /* If the number of data pages changed from sysusages - Recalculate size */
  begin
    set @ReSize = 'T'
  end
  else
    if @OldLogPages != @LogPages            /* If the nubmer of log pages changed from sysusages - Recalculate size */
    begin
      set @ReSize = 'T'
    end
    else
    begin                                                   /* if the number of log pages used is greather than a percentage of the total number of log pages available - Recalc Size */
      if (@LogPagesUsed - @OldLogPagesUsed) > @LogThreshold * @LogPages
        set @ReSize = 'T'
      else
        begin                                               /* if the log's first page moved more than the threshold of the number of log pages available - Recalc Size */
        if (abs(@LogFirstPage - @OldLogFirstPage)) > @LogThreshold * @LogPages
          set @ReSize = 'T'
        else
          begin
            if (@OldNumberOfObjects != @NumberOfObjects)
              set @ReSize = 'T'
          end
        end
    end

if @Force is not null  -- This is a force option to make it update the value
  set @ReSize = 'T'

if @ReSize = 'T'                                        /* We are recalculating size, and getting back the new value of the Data Pages Used */
  exec sp__dbsubspace;2 @DataPages, @DataPagesUsed out, @LogPages, @LogPagesUsed, @LogFirstPage, @NumberOfObjects
else
begin                                                       /* or if we don't need to do that, we just use the previous value of the Data Pages Used */
  set @DataPagesUsed = @OldDataPagesUsed
end

/* Calculated scale from the maximum page size (size of the data pages, usually 2k, 4k, 8k, or 16k) */
set @scale = @@maxpagesize / 1024

/* "Borrowed" this calculation from sp__dbspace of the percentages used*/
if @MixedFlag = 'M' 
begin  /* Please note, if the mode is mixed, we will apparently have no Log Percentage Used as it is part of the data space */
  set @pct_used = convert(numeric(10,2), ((@DataPagesUsed + @LogPagesUsed) * 100) / @DataPages)
  set @log_pct_used = convert(numeric(10,2), 0)
end
else
begin
  set @pct_used = convert(numeric(10,2), (@DataPagesUsed * 100) / @DataPages) 
  set @log_pct_used = convert(numeric(10,2), (@LogPagesUsed * 100 )/(@LogPages) ) 
end

/* The @dont_format is from the sp__dbspace command - and provides the option for an abbreviated display of information */
if @dont_format is not null
begin            /* Provide the results based on the local variables - formatted first */
select  Name             = db_name(),
        "Data MB"  = str((@DataPages*@scale)/1024, 16, 0),
        "Used MB"  = str(((@DataPagesUsed + case @MixedFlag when 'M' then @LogPagesUsed else 0 end)*@scale)/1024, 16, 1),
        Percent    = str(@pct_used, 7, 2),
        "Log MB" = str((@LogPages*@scale)/1024, 12, 0),
        "Log Used"   = str(((case @MixedFlag when 'M' then 0 else @LogPagesUsed end)*@scale)/1024, 12, 2),
        "Log Pct"  = str(@log_pct_used, 7, 2),
        "Total MB" = str((@TotalPages*@scale)/1024, 18, 0)
end
else
begin          /* And unformatted */
select  Name             = convert(char(12),db_name()),
        "Data MB"  = str((@DataPages*@scale)/1024, 13, 0),
        "Used MB"  = str(((@DataPagesUsed + case when @MixedFlag = 'M' then @LogPagesUsed else 0 end)*@scale)/1024, 14, 1),
        Percent    = str(@pct_used, 7, 2),
        "Log MB"   = str((@LogPages*@scale)/1024, 9, 0),
       "Log Used" = str(((case when @MixedFlag='M' then 0 else @LogPagesUsed end)*@scale)/1024, 9, 2),
        "Log Pct"  = str(@log_pct_used, 7, 2),
        "Total MB" = str((@TotalPages*@scale)/1024, 15, 0)
end

/*  And Vola' we are done!  */
go

/* This is a subordinate procedure that gets the information from the sysindexes (or sysobjects for system 15+) and stores the information into the sysdbspaceinfo table */
create procedure sp__dbsubspace;2
  @DataPages numeric(19,5),                           /* See comments above about variables */
  @DataPagesUsed numeric(19,5) output,         /* NOTE: this value is returned to the calling procedure */
  @LogPages numeric(19,5),
  @LogPagesUsed numeric(19,5),
  @LogFirstPage bigint,
  @NumberOfObjects int

with recompile
as

  /* Prior to system 15 version */
  /* select @DataPagesUsed = sum(reserved_pgs(id, doampg) + reserved_pgs(id, ioampg))
     from sysindexes
    where id != 8
  */
/* System 15 and above version */    
   select @DataPagesUsed = sum(convert(numeric(19,5), reserved_pages(db_id(), id)))
     from sysobjects
    where id != 8
    
    
 /* Update the information in the table */
      update sybsystemdb..sysdbspaceinfo 
           set  DataPages = @DataPages,
                 DataPagesUsed = @DataPagesUsed,
                 LogPages = @LogPages,
                 LogPagesUsed = @LogPagesUsed,
                 LogFirstPage = @LogFirstPage,
                 NumberOfObjects = @NumberOfObjects
         where dbid = db_id()                 

/* if the update is not for any rows, then add a row */
        if @@rowcount = 0
          insert into sybsystemdb..sysdbspaceinfo values (db_id(), @DataPages, @DataPagesUsed, @LogPages, @LogPagesUsed, @LogFirstPage, @NumberOfObjects)
          
return  /* and we are done with this one */
go

/* You may want to grant some permissions (execute, perhaps) if the users are not sa's */
Share Button

Ed Barlow Stored Procedures: sp__monobj Updated with formatting for SAP Sybase ASE 15 and higher

Ed Barlow createdSAP Sybase the sp__monobj uses a heuristic algorithm to find which objects are most busy as part of the excellent Extended Stored Procedures by Ed Barlow (AKA the Ed Barlow Stored Procedures).

I’ve cleaned up the format output

Differences:
diff sp__monobj.old sp__monobj.15
17a18,26
> ————————————————————————————————–
> — Vers| Date | Who | DA | Description
> ——-+———-+——————–+—-+—————————————————–
> — 1.1 |11/20/2013| Jason Froebe | | Fix formatting of outputs
> — 1.0 | 2006 | Edward Barlow | | Shows Highest Usage Objects Based On Mda tables.
> — | | | | This uses a heuristic algorithm to find what objects
> — | | | | are most busy.
> ——-+———-+——————–+—-+—————————————————–
>
21a31,34
> declare @max_objectname_size varchar(3)
> declare @exec_str varchar(2000)
>
>
34a48,51
> print “”
> print ” score = LogicalReads/100 + PhysicalReads/10 + PhysicalWrites + RowsInserted + RowsDeleted + RowsUpdated + LockRequests + Lockwaits”
> print “”
>
36,37c53,55
< select ObjName=convert(varchar(40),db_name(DBID)+"."+object_name(ObjectID,DBID)), < score= LogicalReads/100 + --- >
> select ObjName = db_name(DBID) + “..” + object_name(ObjectID, DBID),
> score = LogicalReads/100 +
45,46c63,65
< from master..monOpenObjectActivity < where IndexID=0 --- > into #busy_report
> from master..monOpenObjectActivity
> where IndexID=0
55c74
< and db_name(DBID)!="tempdb" --- > and DBName !=”tempdb”
57a77
>
58a79,87
>
> select @max_objectname_size = convert(varchar(3), isnull( max(char_length(ObjName)), 1)) from #busy_report
>
> select @exec_str = ‘select “Object Name” = convert(varchar(‘ + @max_objectname_size + ‘), ObjName), score
> from #busy_report
> order by score desc’
>
> exec (@exec_str)
>
72a102,121
> create table #busy_report_iter_obj (
> ObjName varchar(255) not null,
> Op bigint not null,
> LogReads int not null,
> PhysReads int not null,
> PageReads int not null,
> Writes int not null,
> Ins int not null,
> Del int not null,
> Upd int not null,
> Locks int not null,
> LockWt int not null)
>
> create table #busy_report_iter (
> ObjName varchar(255) not null,
> Op int not null,
> Reads int not null,
> Writes int not null,
> NumRows bigint not null)
>
81,86c130,131
< -- select * < -- from master..monOpenObjectActivity < -- where IndexID=0 and db_name(DBID)!='tempdb' < -- and ( @object_name is null or @object_name=object_name(ObjectID, DBID)) < < select distinct ObjName=convert(varchar(39),db_name(o.DBID)+"."+object_name(o.ObjectID,o.DBID)), --- > insert into #busy_report_iter_obj
> select distinct ObjName = db_name(o.DBID) + “..” + object_name(o.ObjectID,o.DBID),
88c133
< LogReads=o.LogicalReads - i.LogicalReads, --- > LogReads=o.LogicalReads – i.LogicalReads,
92,103c137,167
< "Ins"=o.RowsInserted -i.RowsInserted, < "Del"= o.RowsDeleted - i.RowsDeleted, < "Upd"= o.RowsUpdated - i.RowsUpdated , < "Locks"= o.LockRequests - i.LockRequests, < "LockWt"= o.LockWaits - i.LockWaits < from master..monOpenObjectActivity o,#tmp i < where o.IndexID=i.IndexID < and o.ObjectID=i.ObjectID < and o.DBID=i.DBID < and o.IndexID=i.IndexID < and o.IndexID=0 < and i.IndexID=0 --- > Ins=o.RowsInserted -i.RowsInserted,
> Del= o.RowsDeleted – i.RowsDeleted,
> Upd= o.RowsUpdated – i.RowsUpdated ,
> Locks= o.LockRequests – i.LockRequests,
> LockWt= o.LockWaits – i.LockWaits
> from master..monOpenObjectActivity o,#tmp i
> where o.IndexID=i.IndexID
> and o.ObjectID=i.ObjectID
> and o.DBID=i.DBID
> and o.IndexID=i.IndexID
> and o.IndexID=0
> and i.IndexID=0
>
> select @max_objectname_size = convert(varchar(3), isnull( max(char_length(ObjName)), 1)) from #busy_report_iter_obj
>
> select @exec_str = ‘select “Object Name” = convert(varchar(‘ + @max_objectname_size + ‘), ObjName),
> Op,
> LogReads,
> PhysReads,
> PageReads,
> Writes,
> Ins,
> Del,
> Upd,
> Locks,
> LockWt
> from #busy_report_iter_obj’
>
> exec (@exec_str)
>
> delete #busy_report_iter_obj
107c171
< set rowcount 10 --- > set rowcount 10
109,138c173,202
< select distinct ObjName=convert(varchar(39),db_name(o.DBID)+"."+object_name(o.ObjectID,o.DBID)), < Op=o.Operations-i.Operations, < Reads=o.LogicalReads+ o.PhysicalReads- i.LogicalReads- i.PhysicalReads, < Writes=o.PhysicalWrites-i.PhysicalWrites, < "Rows"=o.RowsInserted + o.RowsDeleted + o.RowsUpdated-i.RowsInserted - i.RowsDeleted - i.RowsUpdated < from master..monOpenObjectActivity o,#tmp i < where o.IndexID=i.IndexID < and o.ObjectID=i.ObjectID < and o.DBID=i.DBID < and o.IndexID=i.IndexID < and o.IndexID=0 < and i.IndexID=0 < -- and ( o.LogicalReads>0 or o.LockRequests>0 )
< order by < o.LogicalReads/100 + < o.PhysicalReads/10 + < o.PhysicalWrites + < o.RowsInserted + < o.RowsDeleted + < o.RowsUpdated + < o.LockRequests/100 + < o.LockWaits - < i.LogicalReads/100 - < i.PhysicalReads/10 - < i.PhysicalWrites - < i.RowsInserted - < i.RowsDeleted - < i.RowsUpdated - < i.LockRequests/100 - < i.LockWaits desc --- > insert into #busy_report_iter
> select distinct ObjName = db_name(o.DBID) + “..” + object_name(o.ObjectID,o.DBID),
> Op = o.Operations-i.Operations,
> Reads = o.LogicalReads+ o.PhysicalReads- i.LogicalReads- i.PhysicalReads,
> Writes = o.PhysicalWrites-i.PhysicalWrites,
> NumRows = o.RowsInserted + o.RowsDeleted + o.RowsUpdated-i.RowsInserted – i.RowsDeleted – i.RowsUpdated
> from master..monOpenObjectActivity o,#tmp i
> where o.IndexID=i.IndexID
> and o.ObjectID=i.ObjectID
> and o.DBID=i.DBID
> and o.IndexID=i.IndexID
> and o.IndexID=0
> and i.IndexID=0
> order by
> o.LogicalReads/100 +
> o.PhysicalReads/10 +
> o.PhysicalWrites +
> o.RowsInserted +
> o.RowsDeleted +
> o.RowsUpdated +
> o.LockRequests/100 +
> o.LockWaits –
> i.LogicalReads/100 –
> i.PhysicalReads/10 –
> i.PhysicalWrites –
> i.RowsInserted –
> i.RowsDeleted –
> i.RowsUpdated –
> i.LockRequests/100 –
> i.LockWaits desc
139a204,215
> select @max_objectname_size = convert(varchar(3), isnull( max(char_length(ObjName)), 1)) from #busy_report_iter
>
> select @exec_str = ‘select “Object Name” = convert(varchar(‘ + @max_objectname_size + ‘), ObjName),
> Op,
> Reads,
> Writes,
> NumRows
> from #busy_report_iter’
>
> exec (@exec_str)
>
> delete #busy_report_iter
152d227
< -- select 1,* from #tmp where object_id('sp_aux_getsize')=ObjectID and DBID=db_id('master')[/diff] SQL of sp__monobj: [sql highlight_lines="18,19,20,21,22,23,24,25,31,32,33,34,54,55,63,64,65,74,79,80,81,82,83,84,85,86,102,103,104,105,106,107,108,109,110,111,112,113,115,116,117,118,119,120,130,131,133,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,204,205,206,207,208,209,210,211,212,213,214,215,227"]use sybsystemprocs go /* Procedure library copyright(c) 2004-2006 by Edward M Barlow */ IF EXISTS (SELECT * FROM sysobjects WHERE name = "sp__monobj" AND type = "P") DROP PROC sp__monobj go CREATE PROC sp__monobj( @num_sec_delay int=NULL, @num_iter int=NULL, @dont_format char(1)=NULL, @object_name varchar(30)=NULL) AS -------------------------------------------------------------------------------------------------- -- Vers| Date | Who | DA | Description -------+----------+--------------------+----+----------------------------------------------------- -- 1.1 |11/20/2013| Jason Froebe | | Fix formatting of outputs -- 1.0 | 2006 | Edward Barlow | | Shows Highest Usage Objects Based On Mda tables. -- | | | | This uses a heuristic algorithm to find what objects -- | | | | are most busy. -------+----------+--------------------+----+----------------------------------------------------- set nocount on declare @delay char(8) declare @objid int declare @max_objectname_size varchar(3) declare @exec_str varchar(2000) if @object_name is not null begin select @objid=ObjectID from master..monOpenObjectActivity where object_name(ObjectID,DBID)=@object_name if @objid is null return end if @num_sec_delay is null begin --Busy Object Report print "Top 20 Used Objects" print "" print " score = LogicalReads/100 + PhysicalReads/10 + PhysicalWrites + RowsInserted + RowsDeleted + RowsUpdated + LockRequests + Lockwaits" print "" set rowcount 20 select ObjName = db_name(DBID) + ".." + object_name(ObjectID, DBID), score = LogicalReads/100 + PhysicalReads/10 + PhysicalWrites + RowsInserted + RowsDeleted + RowsUpdated + LockRequests + LockWaits into #busy_report from master..monOpenObjectActivity where IndexID=0 and LogicalReads/100 + PhysicalReads/10 + PhysicalWrites + RowsInserted + RowsDeleted + RowsUpdated + LockRequests + LockWaits >1000
and DBName !=”tempdb”
and ( @object_name is null or @object_name=object_name(ObjectID, DBID))
order by score desc

set rowcount 0

select @max_objectname_size = convert(varchar(3), isnull( max(char_length(ObjName)), 1)) from #busy_report

select @exec_str = ‘select “Object Name” = convert(varchar(‘ + @max_objectname_size + ‘), ObjName), score
from #busy_report
order by score desc’

exec (@exec_str)

end
else
begin
select *
into #tmp
from master..monOpenObjectActivity
where IndexID=0 and db_name(DBID)!=’tempdb’
and ( @object_name is null or ObjectID=@objid )

if @num_sec_delay<10
select @delay=”00:00:0″+convert(char(1),@num_sec_delay)
else
select @delay=”00:00:”+convert(char(2),@num_sec_delay)

create table #busy_report_iter_obj (
ObjName varchar(255) not null,
Op bigint not null,
LogReads int not null,
PhysReads int not null,
PageReads int not null,
Writes int not null,
Ins int not null,
Del int not null,
Upd int not null,
Locks int not null,
LockWt int not null)

create table #busy_report_iter (
ObjName varchar(255) not null,
Op int not null,
Reads int not null,
Writes int not null,
NumRows bigint not null)

if @num_iter is null
select @num_iter=100
while @num_iter>0
begin
waitfor delay @delay

if( @object_name is not null )
begin
insert into #busy_report_iter_obj
select distinct ObjName = db_name(o.DBID) + “..” + object_name(o.ObjectID,o.DBID),
Op=o.Operations-i.Operations,
LogReads=o.LogicalReads – i.LogicalReads,
PhysReads=o.PhysicalReads- i.PhysicalReads,
PageReads=o.PagesRead- i.PagesRead,
Writes=o.PhysicalWrites-i.PhysicalWrites,
Ins=o.RowsInserted -i.RowsInserted,
Del= o.RowsDeleted – i.RowsDeleted,
Upd= o.RowsUpdated – i.RowsUpdated ,
Locks= o.LockRequests – i.LockRequests,
LockWt= o.LockWaits – i.LockWaits
from master..monOpenObjectActivity o,#tmp i
where o.IndexID=i.IndexID
and o.ObjectID=i.ObjectID
and o.DBID=i.DBID
and o.IndexID=i.IndexID
and o.IndexID=0
and i.IndexID=0

select @max_objectname_size = convert(varchar(3), isnull( max(char_length(ObjName)), 1)) from #busy_report_iter_obj

select @exec_str = ‘select “Object Name” = convert(varchar(‘ + @max_objectname_size + ‘), ObjName),
Op,
LogReads,
PhysReads,
PageReads,
Writes,
Ins,
Del,
Upd,
Locks,
LockWt
from #busy_report_iter_obj’

exec (@exec_str)

delete #busy_report_iter_obj
end
else
begin
set rowcount 10

insert into #busy_report_iter
select distinct ObjName = db_name(o.DBID) + “..” + object_name(o.ObjectID,o.DBID),
Op = o.Operations-i.Operations,
Reads = o.LogicalReads+ o.PhysicalReads- i.LogicalReads- i.PhysicalReads,
Writes = o.PhysicalWrites-i.PhysicalWrites,
NumRows = o.RowsInserted + o.RowsDeleted + o.RowsUpdated-i.RowsInserted – i.RowsDeleted – i.RowsUpdated
from master..monOpenObjectActivity o,#tmp i
where o.IndexID=i.IndexID
and o.ObjectID=i.ObjectID
and o.DBID=i.DBID
and o.IndexID=i.IndexID
and o.IndexID=0
and i.IndexID=0
order by
o.LogicalReads/100 +
o.PhysicalReads/10 +
o.PhysicalWrites +
o.RowsInserted +
o.RowsDeleted +
o.RowsUpdated +
o.LockRequests/100 +
o.LockWaits –
i.LogicalReads/100 –
i.PhysicalReads/10 –
i.PhysicalWrites –
i.RowsInserted –
i.RowsDeleted –
i.RowsUpdated –
i.LockRequests/100 –
i.LockWaits desc

select @max_objectname_size = convert(varchar(3), isnull( max(char_length(ObjName)), 1)) from #busy_report_iter

select @exec_str = ‘select “Object Name” = convert(varchar(‘ + @max_objectname_size + ‘), ObjName),
Op,
Reads,
Writes,
NumRows
from #busy_report_iter’

exec (@exec_str)

delete #busy_report_iter
set rowcount 0

end

delete #tmp

insert #tmp
select *
from master..monOpenObjectActivity
where IndexID=0 and db_name(DBID)!=’tempdb’
and ( @object_name is null or ObjectID=@objid )

select @num_iter = @num_iter – 1
end
end

return

go

GRANT EXECUTE ON sp__monobj TO public
go[/sql]
sp__monwaits

Share Button

Ed Barlow Stored Procedures: sp__monrunning updated with formatting for SAP Sybase ASE 15 and higher

Mich Talebzadeh createdSAP Sybase the sp__monrunning stored procedure for displaying the procedures that are running for more than 100 ms as part of the excellent Extended Stored Procedures by Ed Barlow (AKA the Ed Barlow Stored Procedures).

I’ve cleaned up the format output and added the dont_format option:

$ diff sp__monrunning.old sp__monrunning.15
9c9,10
< create procedure sp__monrunning
---
> create procedure sp__monrunning(
>                      @dont_format char(1)=NULL)
11,16c12,18
< ------------------------------------------------------------------------------------------------
< -- Vers|  Date  |      Who           | DA | Description
< -------+--------+--------------------+----+-----------------------------------------------------
< -- 1.0 |07/04/06|  Mich Talebzadeh   |    | Statistics on processes currently being executed
< --     |        |                    |    | with Elapsed time > 100ms
< -------+--------+--------------------+----+-----------------------------------------------------
---
> --------------------------------------------------------------------------------------------------
> -- Vers|   Date   |      Who           | DA | Description
> -------+----------+--------------------+----+-----------------------------------------------------
> -- 1.1 |11/20/2013|  Jason Froebe      |    | Cleaned up the report format, added dont_format option
> -- 1.0 |07/04/2006|  Mich Talebzadeh   |    | Statistics on processes currently being executed
> --     |          |                    |    | with Elapsed time > 100ms
> -------+--  ------+--------------------+----+-----------------------------------------------------
18,22c20,24
< declare @time datetime
<       select @time = getdate()
<       --print ""
<       --print "Stats for various procedures at %1!. Server up since %2!", @time, @@boottime
<       --print ""
---
>     declare @max_loginname_size varchar(3)
>     declare @max_procname_size varchar(3)
>     declare @max_dbname_size varchar(3)
>     declare @exec_str varchar(2000)
>
35c37
< "Name" = substring(suser_name(p.suid),1,20),
---
>               "Name" = suser_name(p.suid),
37,39c39,42
< "Procedure" = ProcName,
<               "Database" = DBNAME,
<               "Elapsed Time/ms" = TimeMs
---
>               ProcName,
>               DBNAME,
>               TimeMs
>     into #proc_report
43c46,75
< order by TimeMs asc
---
>
>     select @max_loginname_size = convert(varchar(3), isnull( max(char_length(Name)), 1)),
>         @max_procname_size = convert(varchar(3), isnull( max(char_length(ProcName)), 1)),
>         @max_dbname_size = convert(varchar(3), isnull( max(char_length(DBNAME)), 1))
>     from #proc_report
>
>     if @dont_format is null
>     begin
>         select @exec_str = 'select
>             "Name" = convert(varchar(' + @max_loginname_size + '), Name),
>                   SPID,
>                   "Procedure" = convert(varchar(' + @max_procname_size + '), ProcName),
>                   "Database" = convert(varchar(' + @max_dbname_size + '), DBNAME),
>                   "Elapsed Time/ms" = TimeMs
>         from #proc_report
>           order by TimeMs asc'
>
>         exec (@exec_str)
>     end
>     else
>     begin
>         select
>             "Name" = Name,
>                   SPID,
>                   "Procedure" = ProcName,
>                   "Database" = DBNAME,
>                   "Elapsed Time/ms" = TimeMs
>         from #proc_report
>           order by TimeMs asc
>     end

Full SQL code of sp__monrunning:

use sybsystemprocs
go

IF EXISTS (SELECT * FROM sysobjects
           WHERE  name = "sp__monrunning"
           AND    type = "P")
   DROP PROC sp__monrunning
go
create procedure sp__monrunning(
                     @dont_format char(1)=NULL)
as
--------------------------------------------------------------------------------------------------
-- Vers|   Date   |      Who           | DA | Description
-------+----------+--------------------+----+-----------------------------------------------------
-- 1.1 |11/20/2013|  Jason Froebe      |    | Cleaned up the report format, added dont_format option
-- 1.0 |07/04/2006|  Mich Talebzadeh   |    | Statistics on processes currently being executed
--     |          |                    |    | with Elapsed time > 100ms
-------+--  ------+--------------------+----+-----------------------------------------------------
begin
    declare @max_loginname_size varchar(3)
    declare @max_procname_size varchar(3)
    declare @max_dbname_size varchar(3)
    declare @exec_str varchar(2000)

        select
                SPID,
                ProcName = isnull(object_name(ProcedureID, DBID),"UNKNOWN"),
                DBNAME = isnull(db_name(DBID), "UNKNOWN"),
                TimeMs = datediff(ms, min(StartTime), max(EndTime))
                into #performance
                from master..monSysStatement m
        where db_name(DBID) != 'sybsystemprocs'
        group by SPID, DBID, ProcedureID, BatchID
        having ProcedureID != 0

        select distinct
                "Name" = suser_name(p.suid),
                SPID,
                ProcName,
                DBNAME,
                TimeMs
    into #proc_report
        from #performance t, master..sysprocesses p
        where t.SPID = p.spid
        and TimeMs >= 100

    select @max_loginname_size = convert(varchar(3), isnull( max(char_length(Name)), 1)),
        @max_procname_size = convert(varchar(3), isnull( max(char_length(ProcName)), 1)),
        @max_dbname_size = convert(varchar(3), isnull( max(char_length(DBNAME)), 1))
    from #proc_report

    if @dont_format is null
    begin
        select @exec_str = 'select
            "Name" = convert(varchar(' + @max_loginname_size + '), Name),
                    SPID,
                    "Procedure" = convert(varchar(' + @max_procname_size + '), ProcName),
                    "Database" = convert(varchar(' + @max_dbname_size + '), DBNAME),
                    "Elapsed Time/ms" = TimeMs
        from #proc_report
            order by TimeMs asc'

        exec (@exec_str)
    end
    else
    begin
        select
            "Name" = Name,
                    SPID,
                    "Procedure" = ProcName,
                    "Database" = DBNAME,
                    "Elapsed Time/ms" = TimeMs
        from #proc_report
            order by TimeMs asc
    end
end
go
grant exec on sp__monrunning to public
go
exit

sp__monrunning

Share Button

Ed Barlow Stored Procedures: sp__monwaits updated with formatting for SAP Sybase ASE 15 and higher

Ed Barlow createdSAP Sybase the sp__monwaits stored procedure for displaying the wait times since server start in Sybase ASE as part of the excellent Extended Stored Procedures by Ed Barlow (AKA the Ed Barlow Stored Procedures).

The sp__monwaits often truncated the event description making it rather difficult to determine exactly which event occurred. I’ve expanded the size the field to reflect the length of the longest event description dynamically.

 Event                                    WaitTime    Waits
 ---------------------------------------- ----------- -----------
 xact coord: pause during idle loop           4001410       66688
 wait for buffer read to complete              532248    85475068
 wait for buffer write to complete              32235    15923057
 wait for buffer validation to complete          3680      162024
 wait for mass to stop changing                 30267    10315571
 wait for mass to finish changing               10774   502746724
 wait to acquire latch                          55839     7807761
 waiting for disk write to complete            115108    40656343
 waiting for disk write to complete             60530    11697654
 waiting for disk write to complete             27566     9398819
 waiting for disk write to complete             85976    50951106
 checkpoint process idle loop                  951594       16619
 hk: pause for some time                      2713933      530621
 wait for flusher to queue full DFLPIECE       151633      150265
 wait for data from client                       1933        6000
 wait until an engine has been offlined       1000427       33342
 wait for someone else to finish reading       372601    55500541
 waiting for semaphore                         391902    13636318
 waiting for CTLIB event to complete            87265   112396697
 waiting while allocating new client sock      997833       97155
 waiting while no network read or write i    11936037  1037354467
 waiting on run queue after yield              295191    72098512
 waiting on run queue after sleep              746636 -1313156962
 replication agent sleeping in retry slee        3839          64
 replication agent sleeping during flush      1840806    12733523
 waiting for incoming network data          201256524   265587239
 waiting for network send to complete         1175318   747115161
 waiting until last chance threshold is c        1144           3
 waiting for date or time in waitfor comm       68630         210

to

 Event                                              WaitTime    Waits
 -------------------------------------------------- ----------- -----------
 waiting for incoming network data                    221594153    12452930
 waiting for client connection request                  2760845      207231
 hk: pause for some time                                2754002      443443
 xact coord: pause during idle loop                      920462       15341
 wait until an engine has been offlined                  920462       30682
 Wait until heartbeat or check interval expires          920399        1534
 checkpoint process idle loop                            919400       18224
 replication agent sleeping during flush                 777679       91871
 replication agent sleeping in retry sleep               138301        2305
 wait for flusher to queue full DFLPIECE                  31029       31055
 waiting for regular buffer read to complete              11461    37289445
 waiting for last i/o on MASS to complete                  8079     2026180
 waiting on run queue after yield                          4914     4974455
 waiting on run queue after sleep                          3652    71141496
 wait for mass read to finish when getting page            3341     5365397
 wait for i/o to finish after writing last log page        2757     3624230
 waiting for network send to complete                      1404       58634
 waiting for buf write to complete before writing          1334     1452867

Differences:

$ diff sp__monwaits.old sp__monwaits.15
1,13d0
< <
< /*
< select distinct SPID,Id=substring(Login+"("+Application+")",1,30),SecondsWaiting=sum(SecondsWaiting),"Non Network Wait Reason"=c.Description
< from master..monProcess p,master..monWaitClassInfo c, master..monWaitEventInfo i
< where p.WaitEventID = i.WaitEventID
< and   i.WaitClassID = c.WaitClassID
< and   c.Description!="waiting for input from the network"
< group by c.WaitClassID,SPID
< order by SPID
< */
<
<
22d8
<
32a19,21
> declare @max_eventstr_size varchar(4)
> declare @exec_str varchar(2000)
>
34c23,25
< select "Event"=substring(i.Description,1,40),WaitTime,Waits
---
> begin
>       select "Event" = i.Description, WaitTime, Waits
>     into #tmp_nodelay
37a29,42
>
>     if @dont_format is null
>     begin
>         select @max_eventstr_size = convert(varchar(3), isnull(max(char_length(Event)), 1)) from #tmp_nodelay
>         select @exec_str = 'select "Event" = convert(varchar(' + @max_eventstr_size + '), Event), WaitTime, Waits from #tmp_nodelay order by WaitTime desc'
>         exec (@exec_str)
>     end
>     else
>     begin
>         select * from #tmp_nodelay order by WaitTime desc
>     end
>
>     delete #tmp_nodelay
> end
40c45
< select "Event"=substring(i.Description,1,40),WaitTime,Waits,s.WaitEventID
---
>       select "Event" = i.Description, WaitTime, Waits, s.WaitEventID
51a57
>
56,63c62
< select "Time"=convert(varchar(8),getdate(),8),
<                       "Event"=i.Event,
<                       WaitTime=s.WaitTime-i.WaitTime,
<                       Waits=s.Waits-i.Waits
<               from #tmp i, master..monSysWaits s
<               where (s.WaitTime>i.WaitTime or s.Waits>i.Waits)
< and s.WaitEventID= i.WaitEventID
<               order by WaitTime desc
---
>         select @max_eventstr_size = convert(varchar(3), isnull(max(char_length(Event)), 1)) from #tmp
64a64,73
>               select @exec_str = 'select "Time" = convert(varchar(8),getdate(),8),
>                          "Event" = convert(varchar(' + @max_eventstr_size + '), rtrim(i.Event)),
>                          WaitTime = s.WaitTime-i.WaitTime,
>                          Waits = s.Waits-i.Waits
>                  from #tmp i, master..monSysWaits s
>                  where (s.WaitTime > i.WaitTime or s.Waits > i.Waits)
>                  and s.WaitEventID= i.WaitEventID
>                  order by WaitTime desc'
>         exec (@exec_str)
>
68c77
< select "Event"=substring(i.Description,1,40),WaitTime,Waits,s.WaitEventID
---
>               select "Event"=i.Description, WaitTime, Waits, s.WaitEventID
70c79
< where s.WaitEventID= i.WaitEventID
---
>               where s.WaitEventID = i.WaitEventID

Full SQL text of sp__monwaits:

use sybsystemprocs
go
/* Procedure library copyright(c) 2004 by Edward M Barlow */

IF EXISTS (SELECT * FROM sysobjects
           WHERE  name = "sp__monwaits"
           AND    type = "P")
   DROP PROC sp__monwaits
go

CREATE PROC sp__monwaits(
                @num_sec_delay int=NULL,
                @num_iter int=NULL,
                @dont_format char(1)=NULL)
AS
set nocount on
declare @delay char(8)

declare @max_eventstr_size varchar(4)
declare @exec_str varchar(2000)

if @num_sec_delay is null
begin
        select "Event" = i.Description, WaitTime, Waits
    into #tmp_nodelay
        from master..monWaitEventInfo i, master..monSysWaits s
        where s.WaitEventID= i.WaitEventID
        and     WaitTime>1000

    if @dont_format is null
    begin
        select @max_eventstr_size = convert(varchar(3), isnull(max(char_length(Event)), 1)) from #tmp_nodelay
        select @exec_str = 'select "Event" = convert(varchar(' + @max_eventstr_size + '), Event), WaitTime, Waits from #tmp_nodelay order by WaitTime desc'
        exec (@exec_str)
    end
    else
    begin
        select * from #tmp_nodelay order by WaitTime desc
    end

    delete #tmp_nodelay
end
else
begin
        select "Event" = i.Description, WaitTime, Waits, s.WaitEventID
        into #tmp
        from master..monWaitEventInfo i, master..monSysWaits s
        where s.WaitEventID= i.WaitEventID

        if @num_sec_delay&lt;10
                select @delay="00:00:0"+convert(char(1),@num_sec_delay)
        else
                select @delay="00:00:"+convert(char(2),@num_sec_delay)

        if @num_iter is null
                select @num_iter=100

        while @num_iter>0
        begin
                waitfor delay @delay

        select @max_eventstr_size = convert(varchar(3), isnull(max(char_length(Event)), 1)) from #tmp

                select @exec_str = 'select "Time" = convert(varchar(8),getdate(),8),
                           "Event" = convert(varchar(' + @max_eventstr_size + '), rtrim(i.Event)),
                           WaitTime = s.WaitTime-i.WaitTime,
                           Waits = s.Waits-i.Waits
                   from #tmp i, master..monSysWaits s
                   where (s.WaitTime > i.WaitTime or s.Waits > i.Waits)
                   and s.WaitEventID= i.WaitEventID
                   order by WaitTime desc'
        exec (@exec_str)

                delete #tmp

                insert #tmp
                select "Event"=i.Description, WaitTime, Waits, s.WaitEventID
                from master..monWaitEventInfo i, master..monSysWaits s
                where s.WaitEventID = i.WaitEventID

                select @num_iter = @num_iter - 1
        end
end

return

go

GRANT EXECUTE ON sp__monwaits  TO public
go

sp__monwaits

Share Button

Ed Barlow Stored Procedures: sp__monunusedindex updated with formatting, including owner and all indexes for SAP Sybase ASE 15 and higher

Ed Barlow createdSAP Sybase the sp__monunusedindex stored procedure for displaying the indexes that have not been used since server start in Sybase ASE as part of the excellent Extended Stored Procedures by Ed Barlow (AKA the Ed Barlow Stored Procedures).

I’ve updated the stored procedure to include the table owner and all the indexes & index keys. I’ve also fixed the formatting of the data.

$ diff sp__monunusedindex.old sp__monunusedindex.15
21a22,27
> --------------------------------------------------------------------------------------------------
> -- Vers|   Date   |      Who           | DA | Description
> -------+----------+--------------------+----+-----------------------------------------------------
> -- 1.1 |11/20/2013|  Jason Froebe      |    | Show full index name and full index keys
> -- 1.0 |  2006    |  Edward Barlow     |    | show indexes that have not been used since server start
> -------+----------+--------------------+----+-----------------------------------------------------
23c29,34
< select Dbname=db_name(DBID),Object=object_name(ObjectID,DBID),IndexID,IndexName=i.name, ObjectID
---
>
> declare @max_ownername_size varchar(3)
> declare @max_keylist_size varchar(4)
> declare @exec_str varchar(2000)
>
> select Dbname=db_name(DBID), Object=object_name(ObjectID,DBID), IndexID, IndexName=i.name, ObjectID
27,39c38,45
< and   a.ObjectID=o.id  and DBID=db_id()
< and   o.id = i.id and a.IndexID=i.indid
< and   o.type='U'
< and       (LastOptSelectDate is null and OptSelectCount=0)
< -- and   RowsUpdated=0
< -- and   RowsDeleted=0
< -- and   PhysicalReads=0
< -- and   PhysicalWrites=0
< -- and   PagesRead=0
< and   object_name(ObjectID,DBID) not like 'sys%'
< and   object_name(ObjectID,DBID) not like 'rs_%'
< and    object_name(ObjectID,DBID) not like 'spt_%'
< and   IndexID!=0
---
>    and   a.ObjectID=o.id  and DBID=db_id()
>    and   o.id = i.id and a.IndexID=i.indid
>    and   o.type='U'
>    and       (LastOptSelectDate is null and OptSelectCount=0)
>    and   object_name(ObjectID,DBID) not like 'sys%'
>    and   object_name(ObjectID,DBID) not like 'rs_%'
>    and    object_name(ObjectID,DBID) not like 'spt_%'
>    and        IndexID!=0
42,45c48,51
< owner      char(30) not null,
<    uid        smallint not null,
<    name       char(30) not null,
<    index_name char(30) not null,
---
>    owner      sysname not null,
>    uid        int not null,
>    name       longsysname not null,
>    index_name longsysname not null,
53c59
< keylist    char(127) null,
---
>    keylist    varchar(2000) null,
58,73c64,79
< insert into   #indexlist
<    select owner      = user_name(o.uid),
<           o.uid,
<           name       = o.name,
<           index_name = i.name,
<           id = i.id,
<           indexid    = i.indid,
<           clust      = convert(char(1),null),
<           allow_dup  = convert(char(1),null),
<           ign_dup_key  = convert(char(1),null),
<           uniq       = convert(char(1),null),
<           suspect    = convert(char(1),null),
<           keylist    = convert(char(127),"N.A."),
<           status      = status, status2=i.status2
<    from   sysobjects o, sysindexes i, #tmp t
<    where  i.id   = o.id
---
> insert into   #indexlist
> select owner      = user_name(o.uid),
>    o.uid,
>    name       = o.name,
>    index_name = i.name,
>    id = i.id,
>    indexid    = i.indid,
>    clust      = convert(char(1),null),
>    allow_dup  = convert(char(1),null),
>    ign_dup_key  = convert(char(1),null),
>    uniq       = convert(char(1),null),
>    suspect    = convert(char(1),null),
>    keylist    = "N.A.",
>    status      = status, status2=i.status2
> from   sysobjects o, sysindexes i, #tmp t
> where  i.id   = o.id
75d80
< --and    o.type in ("U",@show_type)
106c111
< or     status2&8192= 8192
---
>    or status2 & 8192= 8192
111c116
< while ( @count < 17 )   /* 16 appears to be the max number of indexes */
---
> while ( @count < 250 )   /* max number of indexes = 250 */
113d117
<
120c124
<       set    keylist=rtrim(keylist)+","+index_col(name,indexid,@count,uid)
---
>       set    keylist = convert(varchar(2000), rtrim(keylist)+", "+index_col(name,indexid,@count,uid))
123c127,128
< if @@rowcount=0   break
---
>    if @@rowcount=0
>       break
132,138c137,143
< if @no_print is null
<         begin
<         print "   INDEX KEY:     c = clustered            u = unique"
<         print "                  a = allow dup row        s = suspect"
<         print "                  i = ignore dup key "
<         print ""
<               end
---
> if @no_print is null
> begin
>    print "   INDEX KEY:     c = clustered            u = unique"
>    print "                  a = allow dup row        s = suspect"
>    print "                  i = ignore dup key "
>    print ""
> end
140,146c145,173
< select "Name" = rtrim(name)+"."+index_name,
<        c   = isnull(clust,""),
<        u   = isnull(uniq,""),
<        i   = isnull(ign_dup_key,""),
<        a   = isnull(allow_dup,""),
<        s   = isnull(suspect,""),
<        "List of Index Keys"    = keylist
---
> if @dont_format is null
> begin
>    select @max_ownername_size = convert(varchar(3), isnull(max(char_length(owner)) + max(char_length(name)) + max(char_length(index_name)) + 2, 1) )
>       from #indexlist
>
>    select @max_keylist_size = convert(varchar(4), isnull(max(char_length(keylist)), 1) )
>       from #indexlist
>
>    select @exec_str =
>       'select "Name" = convert(varchar(' + @max_ownername_size + '), rtrim(owner) + "." + rtrim(name) + "." + index_name),
>          c   = isnull(clust,""),
>          u   = isnull(uniq,""),
>          i   = isnull(ign_dup_key,""),
>          a   = isnull(allow_dup,""),
>          s   = isnull(suspect,""),
>          "List of Index Keys"    = convert(varchar(' + @max_keylist_size + '), keylist)
>       from #indexlist
>       order by owner,name,indexid'
>    exec (@exec_str)
> end
> else
> begin
>    select "Name" = rtrim(owner) + "." + rtrim(name) + "." + index_name,
>       c   = isnull(clust,""),
>       u   = isnull(uniq,""),
>       i   = isnull(ign_dup_key,""),
>       a   = isnull(allow_dup,""),
>       s   = isnull(suspect,""),
>       "List of Index Keys"    = keylist
148a176
> end

full code of sp__monunusedindex:

use sybsystemprocs
go
/* Procedure copyright(c) 2006 by Edward M Barlow */

/******************************************************************************
**
** Name : sp__monunusedindex
**
** Created By : Ed Barlow
**
******************************************************************************/

IF EXISTS (SELECT * FROM sysobjects
WHERE name = “sp__monunusedindex”
AND type = “P”)
DROP PROC sp__monunusedindex

go

create proc sp__monunusedindex( @dont_format char(1) = null, @no_print char(1) = null)
as
————————————————————————————————–
— Vers| Date | Who | DA | Description
——-+———-+——————–+—-+—————————————————–
— 1.1 |11/20/2013| Jason Froebe | | Show full index name and full index keys
— 1.0 | 2006 | Edward Barlow | | show indexes that have not been used since server start
——-+———-+——————–+—-+—————————————————–
begin

declare @max_ownername_size varchar(3)
declare @max_keylist_size varchar(4)
declare @exec_str varchar(2000)

select Dbname=db_name(DBID), Object=object_name(ObjectID,DBID), IndexID, IndexName=i.name, ObjectID
into #tmp
from master..monOpenObjectActivity a, sysobjects o, sysindexes i
where RowsInserted=0
and a.ObjectID=o.id and DBID=db_id()
and o.id = i.id and a.IndexID=i.indid
and o.type=’U’
and (LastOptSelectDate is null and OptSelectCount=0)
and object_name(ObjectID,DBID) not like ‘sys%’
and object_name(ObjectID,DBID) not like ‘rs_%’
and object_name(ObjectID,DBID) not like ‘spt_%’
and IndexID!=0

create table #indexlist (
owner sysname not null,
uid int not null,
name longsysname not null,
index_name longsysname not null,
id int not null,
indexid smallint not null,
clust char(1) null,
allow_dup char(1) null,
ign_dup_key char(1) null,
uniq char(1) null,
suspect char(1) null,
keylist varchar(2000) null,
status smallint not null,
status2 smallint not null
)

insert into #indexlist
select owner = user_name(o.uid),
o.uid,
name = o.name,
index_name = i.name,
id = i.id,
indexid = i.indid,
clust = convert(char(1),null),
allow_dup = convert(char(1),null),
ign_dup_key = convert(char(1),null),
uniq = convert(char(1),null),
suspect = convert(char(1),null),
keylist = “N.A.”,
status = status, status2=i.status2
from sysobjects o, sysindexes i, #tmp t
where i.id = o.id
and i.id = ObjectID and i.indid=IndexID
and indid > 0

/* delete multiple rows */
delete #indexlist
from #indexlist a, #indexlist b
where a.indexid = 0
and b.indexid != 0
and a.name = b.name

update #indexlist
set clust=’Y’
where indexid = 1
or status&16=16
or status2&512 = 512

update #indexlist
set uniq = ‘Y’
where status & 2 = 2

update #indexlist
set ign_dup_key = ‘Y’
where status & 1 = 1

update #indexlist
set allow_dup = ‘Y’
where status & 64 = 64

update #indexlist
set suspect = ‘Y’
where status & 32768 = 32768
or status2 & 8192= 8192

declare @count int
select @count=1

while ( @count < 250 ) /* max number of indexes = 250 */ begin if @count=1 update #indexlist set keylist=index_col(name,indexid,1,uid) where index_col(name,indexid,@count,uid) is not null else update #indexlist set keylist = convert(varchar(2000), rtrim(keylist)+", "+index_col(name,indexid,@count,uid)) where index_col(name,indexid,@count,uid) is not null if @@rowcount=0 break select @count=@count+1 end update #indexlist set name=convert(char(30), rtrim(rtrim(substring(owner,1,15)) + "." +name)) where owner!="dbo" if @no_print is null begin print " INDEX KEY: c = clustered u = unique" print " a = allow dup row s = suspect" print " i = ignore dup key " print "" end if @dont_format is null begin select @max_ownername_size = convert(varchar(3), isnull(max(char_length(owner)) + max(char_length(name)) + max(char_length(index_name)) + 2, 1) ) from #indexlist select @max_keylist_size = convert(varchar(4), isnull(max(char_length(keylist)), 1) ) from #indexlist select @exec_str = 'select "Name" = convert(varchar(' + @max_ownername_size + '), rtrim(owner) + "." + rtrim(name) + "." + index_name), c = isnull(clust,""), u = isnull(uniq,""), i = isnull(ign_dup_key,""), a = isnull(allow_dup,""), s = isnull(suspect,""), "List of Index Keys" = convert(varchar(' + @max_keylist_size + '), keylist) from #indexlist order by owner,name,indexid' exec (@exec_str) end else begin select "Name" = rtrim(owner) + "." + rtrim(name) + "." + index_name, c = isnull(clust,""), u = isnull(uniq,""), i = isnull(ign_dup_key,""), a = isnull(allow_dup,""), s = isnull(suspect,""), "List of Index Keys" = keylist from #indexlist order by owner,name,indexid end end go[/sql] sp__monunusedindex

Share Button

Ed Barlow Procedures: sp__groupprotect updated to show the group name properly for SAP Sybase ASE 15 and higher

Ed Barlow createdSAP Sybase the sp__groupprotect stored procedure for reporting group permissions in Sybase ASE as part of the excellent Extended Stored Procedures by Ed Barlow (AKA the Ed Barlow Stored Procedures).

The problem is that the group name is truncated often making it difficult to determine exactly which group a particular line applies to:

 type grp             tot    sel    upd    del    ins    rev    exe
 ---- --------------- ------ ------ ------ ------ ------ ------ ------
 D    application_gro 253    0      0      0      0      0      0
 D    dtm_tm_role     253    0      0      0      0      0      0
 D    ha_role         253    0      0      0      0      0      0
 D    webservices_rol 253    0      0      0      0      0      0

I’ve updated the stored procedure to show the entire group name:

 type grp                tot    sel    upd    del    ins    rev    exe
 ---- ------------------ ------ ------ ------ ------ ------ ------ ------
 D    application_group  253    0      0      0      0      0      0
 D    dtm_tm_role        253    0      0      0      0      0      0
 D    ha_role            253    0      0      0      0      0      0
 D    webservices_role   253    0      0      0      0      0      0
$ diff groupprotect.sql groupprotect.15
23c23
< create procedure sp__groupprotect( @dont_format char(1) = NULL )
---
> create procedure dbo.sp__groupprotect( @dont_format char(1) = NULL )
26a27,36
> --------------------------------------------------------------------------------------------------
> -- Vers|   Date   |      Who           | DA | Description
> -------+----------+--------------------+----+-----------------------------------------------------
> -- 1.1 |11/18/2013|  Jason Froebe      |    | Fix formatting
> -- 1.0 |          |  Edward M Barlow   |    | Stored procedure reporting group permissions
> -------+----------+--------------------+----+-----------------------------------------------------
>
> declare @max_name_size varchar(3)
> declare @exec_str varchar(2000)
>
110,112c120,125
< select type,grp=convert(char(15),group_name),tot=convert(char(6),total),sel=convert(char(6),s),upd=convert(char(6),u),del=convert(char(6),d),ins=convert(char(6),i),rev=convert(char(6),r),exe=convert(char(6),e)
<         from #objects
<         order by type,group_name
---
>         select @max_name_size = convert(varchar(3), max(char_length(group_name))) from #objects
>         select @exec_str =
>             'select type,grp=convert(char(' + @max_name_size + '),group_name),tot=convert(char(6),total),sel=convert(char(6),s),upd=convert(char(6),u),del=convert(char(6),d),ins=convert(char(6),i),rev=convert(char(6),r),exe=convert(char(6),e)
>             from #objects
>             order by type,group_name'
>         exec (@exec_str)

Here’s the full stored procedure code:

/* Procedure copyright(c) 1995 by Edward M Barlow */

/******************************************************************************
**
** Name        : sp__groupprotect.sql
**
**   permissions by object type / user group vs sel/upd/ins/del
**
******************************************************************************/
:r database
go
:r dumpdb
go

if exists (select * from sysobjects
           where  name = "sp__groupprotect"
           and    type = "P")
begin
   drop proc sp__groupprotect
end
go

create procedure dbo.sp__groupprotect( @dont_format char(1) = NULL )
as
set nocount on

--------------------------------------------------------------------------------------------------
-- Vers|   Date   |      Who           | DA | Description
-------+----------+--------------------+----+-----------------------------------------------------
-- 1.1 |11/18/2013|  Jason Froebe      |    | Fix formatting
-- 1.0 |          |  Edward M Barlow   |    | Stored procedure reporting group permissions
-------+----------+--------------------+----+-----------------------------------------------------

declare @max_name_size varchar(3)
declare @exec_str varchar(2000)

select distinct type,uid=sysusers.uid,group_name=sysusers.name,total=0,s=0,u=0,d=0,i=0,r=0,e=0
into   #objects
from   sysusers,   sysobjects
where  sysusers.uid=sysusers.gid
and    sysobjects.uid=1
and    ( sysusers.uid>=16390 or sysusers.uid < 16000 )

select distinct action,id,uid,protecttype,type="  "
into  #p
from  sysprotects p

update  #p
set     type=o.type
from    sysobjects o
where   o.id=#p.id
and     o.uid=1

update #objects set total=(select count(*) from sysobjects o
                                where o.type=n.type
                                and    o.uid=1)
from #objects n

update  #objects
set     s=(select count(*)
                from    #p
                where   #p.action=193
                and     #p.uid = #objects.uid
                and     #p.protecttype<=1
                and     #p.type = #objects.type
)
from #objects

update  #objects
set     u=(select count(*)
                from    #p
                where   #p.action=197
                and     #p.uid = #objects.uid
                and     #p.protecttype<=1
                and     #p.type = #objects.type
)
from #objects

update  #objects
set     d=(select count(*)
                from    #p
                where   #p.action=196
                and     #p.uid = #objects.uid
                and     #p.protecttype<=1
                and     #p.type = #objects.type
)
from #objects

update  #objects
set     i=(select count(*)
                from    #p
                where   #p.action=195
                and     #p.uid = #objects.uid
                and     #p.protecttype<=1
                and     #p.type = #objects.type
)
from #objects

update  #objects
set     r=(select count(*)
                from    #p
                where   #p.protecttype=2
                and     #p.uid = #objects.uid
                and     #p.type = #objects.type
)
from #objects

update  #objects
set     e=(select count(*)
                from    #p
                where   #p.action=224
                and     #p.uid = #objects.uid
                and     #p.protecttype<=1
                and     #p.type = #objects.type
)
from #objects

if @dont_format is null
begin
        select @max_name_size = convert(varchar(3), max(char_length(group_name))) from #objects
        select @exec_str =
            'select type,grp=convert(char(' + @max_name_size + '),group_name),tot=convert(char(6),total),sel=convert(char(6),s),upd=convert(char(6),u),del=convert(char(6),d),ins=convert(char(6),i),rev=convert(char(6),r),exe=convert(char(6),e)
            from #objects
            order by type,group_name'
        exec (@exec_str)
end
else
begin
        select type,grp=group_name,tot=convert(char(6),total),sel=convert(char(6),s),upd=convert(char(6),u),del=convert(char(6),d),ins=convert(char(6),i),rev=convert(char(6),r),exe=convert(char(6),e)
        from #objects
        order by type,group_name
end

return (0)
go
grant execute on sp__groupprotect to public
go

groupprotect

Share Button

Ed Barlow Stored Procedures: sp__helprotect updated for SAP Sybase ASE 15 and higher

Ed Barlow createdSAP Sybase the sp__helprotect stored procedure for extracting permissions in Sybase ASE as part of the excellent Extended Stored Procedures by Ed Barlow (AKA the Ed Barlow Stored Procedures). I’ve updated it for v15 and higher:

$ diff helpprotect.10 helpprotect.15
26a23,29
> --------------------------------------------------------------------------------------------------
> -- Vers|   Date   |      Who           | DA | Description
> -------+----------+--------------------+----+-----------------------------------------------------
> -- 1.1 |11/18/2013|  Jason Froebe      |    | Updated for ASE v15
> -- 1.0 |          |  Edward M Barlow   |    | Stored procedure extracting permissions for objects
> -------+----------+--------------------+----+-----------------------------------------------------
>
135a139
> -- protecttype column can contain these values: 0 for grant with grant. 1 for grant. 2 for revoke
137c141,152
< set     ending = " WITH GRANT OPTION", protecttype_text="GRANT"
---
> set     protecttype_text =
>    case
>      when protecttype = 0
>        then "GRANT"
>      when protecttype = 1
>        then "GRANT"
>      when protecttype = 2
>        then "REVOKE"
>    end
>
> update  #protects
> set     ending = " WITH GRANT OPTION"
145c160,171
< select substring(rtrim(protecttype_text)+" "+rtrim(action_text)+" on "+rtrim(object_name(id))+column_name+" to "+rtrim(user_name(uid))+ending,1,59)
---
>         select
>          substring(
>            rtrim(protecttype_text)
>            + " "
>            + rtrim(action_text)
>            + case when id = 0 then " " else " on " end
>            + rtrim(object_name(id))
>            + column_name
>            + " to "
>            + rtrim(user_name(uid))
>            + ending
>          ,1,59)
146a173
>         where rtrim(action_text) != ""
149c176,187
< select substring(rtrim(protecttype_text)+" "+rtrim(action_text)+" on "+rtrim(object_name(id))+column_name+" to "+rtrim(user_name(uid))+ending,1,79)
---
>         select
>          substring(
>            rtrim(protecttype_text)
>            + " "
>            + rtrim(action_text)
>            + case when id = 0 then " " else " on " end
>            + rtrim(object_name(id))
>            + column_name
>            + " to "
>            + rtrim(user_name(uid))
>            + ending
>          ,1,79)
150a189
>         where rtrim(action_text) != ""
153c192,203
< select substring(rtrim(protecttype_text)+" "+rtrim(action_text)+" on "+rtrim(object_name(id))+column_name+" to "+rtrim(user_name(uid))+ending,1,131)
---
>         select
>          substring(
>            rtrim(protecttype_text)
>            + " "
>            + rtrim(action_text)
>            + case when id = 0 then " " else " on " end
>            + rtrim(object_name(id))
>            + column_name
>            + " to "
>            + rtrim(user_name(uid))
>            + ending
>          ,1,131)
154a205
>         where rtrim(action_text) != ""
/* Procedure copyright(c) 1995 by Edward M Barlow */
/*  Stored Procedure is under GPL v2 */

/******************************************************************************
**
** Name        : sp__helpprotect.sql
**
******************************************************************************/
if exists (select * from sysobjects
           where  name = "sp__helprotect"
           and    type = "P")
begin
   drop proc sp__helprotect
end
go

create procedure sp__helprotect
        @parameter varchar(30) = NULL            /* name of object or user to check     */,
        @do_system_tables char(1) = null, /* if not null will include system tbls */
        @dont_format char(1) = null,
        @groups_only char(1) = null
as
--------------------------------------------------------------------------------------------------
-- Vers|   Date   |      Who           | DA | Description
-------+----------+--------------------+----+-----------------------------------------------------
-- 1.1 |11/18/2013|  Jason Froebe      |    | Updated for ASE v15
-- 1.0 |          |  Edward M Barlow   |    | Stored procedure extracting permissions for objects
-------+----------+--------------------+----+-----------------------------------------------------

        declare @type char(2), @uid int, @msg varchar(255), @objid int

        if @parameter is NULL
                select @objid=null
        else
                select @objid = object_id(@parameter)

        /* define our table */
        select   id,uid,action,protecttype,columns,grantor,
                        column_name             = "                               "
                        ,action_text            = "                               "
                        ,protecttype_text = "                               "
                        ,ending                                 = "                               "
        into    #protects
        from    sysprotects
        where 1=2

        /* Either a passed object or all objects */
        if @objid is not null or @parameter is null
        begin

                select uid,gid into #groups from sysusers

                if @groups_only is not null
                        delete  #groups
                        where    uid != gid

                /* IT IS AN OBJECT */
                insert  #protects
                select  id,p.uid,action,protecttype,columns,grantor,"","","",""
                from    sysprotects p, #groups g
                where   id=isnull(@objid,id)
                and      p.uid = g.uid

                /* REVOKES ON COLUMNS */
                insert  #protects
                select  id,p.uid,action,2,columns,grantor,
                        "("+col_name(p.id,c.number)+")","","",""
                from            sysprotects p, master.dbo.spt_values c, #groups g
                where   p.columns is not null
                and             convert(tinyint,substring(p.columns,c.low,1)) & c.high=0
                and             c.type = "P"
                and             c.number < = 255
                and             c.number>0
                and             c.low>1
                and             col_name(p.id,c.number) is not null
                and             id=isnull(@objid,id)
                and      p.uid=g.uid

                if @do_system_tables is null and @objid is null
                        delete #protects
                        from   #protects p, sysobjects o
                        where  p.id = o.id
                        and    o.type = 'S'
        end
        else
        begin

                /* IS IT A USER */
                select @uid = uid from sysusers where name=@parameter
                if @@rowcount = 0 or @uid is null
                begin
                   print "No User Or Object Found"
                   return (1)
                end

                insert  #protects
                select  distinct id,uid,action,protecttype,columns,grantor,"","","",""
                from    sysprotects p
                where   uid=@uid
                /* and          isnull( p.columns,0x01 ) = 0x01 */

                /* REVOKES ON COLUMNS */
                insert  #protects
                select  id,uid,action,2,columns,grantor,
                        "("+col_name(p.id,c.number)+")", "","",""
                from    sysprotects p, master.dbo.spt_values c
                where isnull( p.columns,0x01 ) != 0x01
                and     convert(tinyint, substring(p.columns, c.low, 1)) & c.high = 0
                and     c.type = "P"
                and     c.number < = 255
                and     c.number>0
                and     c.low>1
                and     col_name(p.id,c.number) is not null
                and     uid=@uid

                if @do_system_tables is null
                        delete #protects
                        from   #protects p, sysobjects o
                        where  p.id = o.id
                        and    o.type = 'S'
        end

/* References etc */
delete  #protects
where   action in(151,207,222,233,236)

update  #protects
set     action_text = name
from    master.dbo.spt_values v
where   v.type='T'
and     v.number = #protects.action

update  #protects
set     protecttype_text = name
from    master.dbo.spt_values v
where   v.type='T'
and     v.number = #protects.protecttype +204

-- protecttype column can contain these values: 0 for grant with grant. 1 for grant. 2 for revoke
update  #protects
set     protecttype_text =
   case
     when protecttype = 0
       then "GRANT"
     when protecttype = 1
       then "GRANT"
     when protecttype = 2
       then "REVOKE"
   end

update  #protects
set     ending = " WITH GRANT OPTION"
where   protecttype = 0

declare @max_len int
select @max_len = max(char_length( rtrim(protecttype_text)+" "+rtrim(action_text)+" on "+rtrim(object_name(id))+column_name+" to "+rtrim(user_name(uid))+ending))
from #protects

if @max_len < 60
        select
         substring(
           rtrim(protecttype_text)
           + " "
           + rtrim(action_text)
           + case when id = 0 then " " else " on " end
           + rtrim(object_name(id))
           + column_name
           + " to "
           + rtrim(user_name(uid))
           + ending
         ,1,59)
        from #protects
        where rtrim(action_text) != ""
        order by object_name(id),protecttype_text
else if @max_len < 80
        select
         substring(
           rtrim(protecttype_text)
           + " "
           + rtrim(action_text)
           + case when id = 0 then " " else " on " end
           + rtrim(object_name(id))
           + column_name
           + " to "
           + rtrim(user_name(uid))
           + ending
         ,1,79)
        from #protects
        where rtrim(action_text) != ""
        order by object_name(id),protecttype_text
else if @max_len < 132
        select
         substring(
           rtrim(protecttype_text)
           + " "
           + rtrim(action_text)
           + case when id = 0 then " " else " on " end
           + rtrim(object_name(id))
           + column_name
           + " to "
           + rtrim(user_name(uid))
           + ending
         ,1,131)
        from #protects
        where rtrim(action_text) != ""
        order by object_name(id),protecttype_text


return (0)
go
grant execute on sp__helprotect to public
go

helpprotect

Share Button

Ed Barlow stored procedures: sp__indexspace updated for large tables on SAP Sybase 15x

Ed Barlow createdSAP Sybase the sp__indexspace stored procedure for reporting on index usage in Sybase ASE as part of the excellent Extended Stored Procedures by Ed Barlow (AKA the Ed Barlow Stored Procedures). If you have large tables, you will receive an arithmetic overflow error. I’ve updated the stored procedure to correct the error and to format the name of the tables and indexes.

$ diff indexspace.15.old indexspace.15
25a26,33
> --------------------------------------------------------------------------------------------------
> -- Vers|   Date   |      Who           | DA | Description
> -------+----------+--------------------+----+-----------------------------------------------------
> -- 1.1 |11/18/2013|  Jason Froebe      |    | Fix Arithmetic overflow error by using a bigint
> --     |          |                    |    | instead of integers.  Fix formatting to show entire
> --     |          |                    |    | table.index name
> -- 1.0 |          |  Edward M Barlow   |    | Stored procedure giving index usage
> -------+----------+--------------------+----+-----------------------------------------------------
28a37,38
> declare @max_name_size varchar(3)
> declare @exec_str varchar(2000)
64a75,77
> select @max_name_size = convert(varchar(3), max(char_length(name))) from #indexspace
> select @max_name_size
>
66,76c79,91
< select
<    convert(char(22),name)                       "Name",
<    convert(char(8),row_cnt)                                                "Rows",
<    convert(char(16),rtrim(convert(char(30),(reserved*@pagesize)/1024))+"/"+
<    rtrim(convert(char(30),(data*@pagesize)/1024))+"/"+
<    rtrim(convert(char(30),(index_size*@pagesize)/1024))) "Used/Data/Idx KB",
<    str((row_cnt*1024)/(convert(float,data+index_size)*@pagesize),6,2) "Rows/KB",
<    convert(char(12),segname) "Segment"
< from #indexspace
< where indid<=1
< order by name
---
> select @exec_str =
>     'select
>         convert(char(' + @max_name_size + '),name) "Name",
>         convert(char(20),row_cnt) "Rows",
>         convert(char(30),rtrim(convert(char(30),(reserved*@pagesize)/1024))+"/"+
>         rtrim(convert(char(30),(data*@pagesize)/1024))+"/"+
>         rtrim(convert(char(30),(index_size*@pagesize)/1024))) "Used/Data/Idx KB",
>         str((row_cnt*1024)/(convert(float,data+index_size)*@pagesize),6,2) "Rows/KB",
>         convert(char(12),segname) "Segment"
>     from #indexspace
>     where indid< =1
>     order by name'
> exec (@exec_str)
80,85c95,101
< select
<         convert(char(22),name)           "Name",
<    convert(char(8),row_cnt)                                                "Rows",
<    convert(char(16),rtrim(convert(char(30),(reserved*@pagesize)/1024))+"/"+
<    rtrim(convert(char(30),(data*@pagesize)/1024))+"/"+
<    rtrim(convert(char(30),(index_size*@pagesize)/1024))) "Used/Data/Idx KB",
---
> select @exec_str =
>     'select
>         convert(char(' + @max_name_size + '),name) "Name",
>         convert(char(20),row_cnt) "Rows",
>         convert(char(30),rtrim(convert(char(30),(reserved*@pagesize)/1024))+"/"+
>         rtrim(convert(char(30),(data*@pagesize)/1024))+"/"+
>         rtrim(convert(char(30),(index_size*@pagesize)/1024))) "Used/Data/Idx KB",
88,90c104,107
< from #indexspace
< where indid>1
< order by name
---
>     from #indexspace
>     where indid>1
>     order by name'
> exec (@exec_str)
/* Procedure copyright(c) 1995 by Edward M Barlow */

/******************************************************************************
**
** Name        : sp__indexspace
**
** Created By  : Ed Barlow
**
******************************************************************************/
:r database
go
:r dumpdb
go

IF EXISTS (SELECT * FROM sysobjects
           WHERE  name = "sp__indexspace"
           AND    type = "P")
   DROP PROC sp__indexspace
go

CREATE PROC sp__indexspace(
                @objname        varchar(92) = NULL ,
                                        @dont_format char(1) = null
                                                 )
AS
--------------------------------------------------------------------------------------------------
-- Vers|   Date   |      Who           | DA | Description
-------+----------+--------------------+----+-----------------------------------------------------
-- 1.1 |11/18/2013|  Jason Froebe      |    | Fix Arithmetic overflow error by using a bigint
--     |          |                    |    | instead of integers.  Fix formatting to show entire
--     |          |                    |    | table name + index name
-- 1.0 |          |  Edward M Barlow   |    | Stored procedure giving index usage
-------+----------+--------------------+----+-----------------------------------------------------
BEGIN

declare @pagesize int                   /* Bytes Per Page */
declare @max_name_size varchar(3)
declare @exec_str varchar(2000)

set nocount on

select  @pagesize = low
from    master..spt_values
where   number = 1
and     type = "E"

select name = o.name,
       idxname = i.name,
       owner_id = o.uid,
       row_cnt  = row_count(db_id(), i.id),
       reserved = reserved_pages(db_id(), i.id, i.indid),
       data     = data_pages(db_id(), i.id, i.indid),
       index_size = data_pages(db_id(), i.id, i.indid),
       segname = s.name,
       indid
into   #indexspace
from   sysobjects o, sysindexes i, syssegments s
where  i.id = o.id
and    (o.type = "U" or o.name = "syslogs")
and    s.segment = i.segment
and    isnull(@objname,o.name)=o.name

update #indexspace
set    name=user_name(owner_id)+'.'+name
where  owner_id>1

update #indexspace
set    name=name+'.'+idxname
where  indid!=0

update #indexspace
set    row_cnt=-1
where  row_cnt>99999999

select @max_name_size = convert(varchar(3), max(char_length(name))) from #indexspace
select @max_name_size

print "Data Level (Index Type 0 or 1)"
select @exec_str =
    'select
        convert(char(' + @max_name_size + '),name) "Name",
        convert(char(20),row_cnt) "Rows",
        convert(char(30),rtrim(convert(char(30),(reserved*@pagesize)/1024))+"/"+
        rtrim(convert(char(30),(data*@pagesize)/1024))+"/"+
        rtrim(convert(char(30),(index_size*@pagesize)/1024))) "Used/Data/Idx KB",
        str((row_cnt*1024)/(convert(float,data+index_size)*@pagesize),6,2) "Rows/KB",
        convert(char(12),segname) "Segment"
    from #indexspace
    where indid< =1
    order by name'
exec (@exec_str)

print ""
print "Non Clustered Indexes"
select @exec_str =
    'select
        convert(char(' + @max_name_size + '),name) "Name",
        convert(char(20),row_cnt) "Rows",
        convert(char(30),rtrim(convert(char(30),(reserved*@pagesize)/1024))+"/"+
        rtrim(convert(char(30),(data*@pagesize)/1024))+"/"+
        rtrim(convert(char(30),(index_size*@pagesize)/1024))) "Used/Data/Idx KB",
        str((row_cnt*1024)/(convert(float,data+index_size)*@pagesize),6,2) "Rows/KB",
        convert(char(12),segname) "Segment"
    from #indexspace
    where indid>1
    order by name'
exec (@exec_str)

drop table #indexspace

return(0)

END

go

GRANT EXECUTE ON sp__indexspace TO public
go

indexspace

Share Button

Ed Barlow Procedures: sp__montableusage updated for SAP Sybase ASE 15 and higher

Back in 2006, Mich Talebzadeh createdSAP Sybase the sp__montableusage stored procedure for reporting on table and index usage based on the MDA tables in Sybase ASE 12.5x using the MDA tables as part of the excellent Extended Stored Procedures by Ed Barlow (AKA the Ed Barlow Stored Procedures). In v15, Sybase deprecated the rowcnt() and reserved_pgs() functions that was used to report on the estimated number of rows in favor of the row_count() and reserved_pages() functions respectfully. I’ve updated the stored procedure to use the row_count() and reserved_pages() functions.

$ diff sp__montableusage.12 sp__montableusage.15
10,15c10,17
< ------------------------------------------------------------------------------------------------
< -- Vers|  Date  |      Who           | DA | Description
< -------+--------+--------------------+----+-----------------------------------------------------
< -- 1.0 |07/04/06|  Mich Talebzadeh   |    | Stored procedure giving comprehensive report
< --     |        |                    |    | about table and index usage based on the MDA tables
< -------+--------+--------------------+----+-----------------------------------------------------
---
> --------------------------------------------------------------------------------------------------
> -- Vers|   Date   |      Who           | DA | Description
> -------+----------+--------------------+----+-----------------------------------------------------
> -- 1.1 |11/18/2013|  Jason Froebe      |    | Replace rowcnt with row_count() and reserved_pgs with
> --     |          |                    |    | reserved_pages()
> -- 1.0 |07/04/2006|  Mich Talebzadeh   |    | Stored procedure giving comprehensive report
> --     |          |                    |    | about table and index usage based on the MDA tables
> -------+----------+--------------------+----+-----------------------------------------------------
27,28c29,30
< rowtotal = rowcnt(i.doampg),
<         reserved = (reserved_pgs(i.id, i.doampg) + reserved_pgs(i.id, i.ioampg)) * (low / 1024)
---
>         rowtotal = row_count(db_id(), o.id),
>         reserved = reserved_pages(db_id(), o.id, i.indid) * (low / 1024)

use sybsystemprocs
go
IF EXISTS(SELECT 1 FROM sysobjects WHERE type = ‘P’ AND name = ‘sp__montableusage’)
BEGIN
DROP PROCEDURE sp__montableusage
END
go
create procedure sp__montableusage
as
————————————————————————————————–
— Vers| Date | Who | DA | Description
——-+———-+——————–+—-+—————————————————–
— 1.1 |11/18/2013| Jason Froebe | | Replace rowcnt with row_count() and reserved_pgs with
— | | | | reserved_pages()
— 1.0 |07/04/2006| Mich Talebzadeh | | Stored procedure giving comprehensive report
— | | | | about table and index usage based on the MDA tables
——-+———-+——————–+—-+—————————————————–

begin
set nocount on

— First of all work out the table rows and sizes (reserved)

select
“Owner” = user_name(o.uid),
TableName = o.name,
IndexName = i.name,
low = d.low,
rowtotal = row_count(db_id(), o.id),
reserved = reserved_pages(db_id(), o.id, i.indid) * (low / 1024)
into #t1
from sysobjects o, sysindexes i, master.dbo.spt_values d
where
o.type = ‘U’
and i.id = o.id
and d.number = 1
and d.type = “E”

select distinct
Owner,
TableName,
RowTotal = convert(char(11), sum(rowtotal)),
Reserved = sum(reserved)
into #table_size
from #t1
group by Owner, TableName

— Identify tables accessed with a table scan
SELECT
“Owner” = user_name(o.uid),
“TableName” = o.name,
“LogicalReads” = m.LogicalReads,
“PagesRead” = m.PagesRead,
“WhenLastUsed” = m.LastUsedDate,
“Used” = m.UsedCount
INTO #tabscan
from
sysobjects o,
master..monOpenObjectActivity m
where
o.type = ‘U’
and o.id = m.ObjectID
and m.IndexID = 0
and m.DBID = db_id()
and object_name(m.ObjectID, m.DBID) not like ‘sa_%’
and object_name(m.ObjectID, m.DBID) not like ‘%__sa%’
and object_name(m.ObjectID, m.DBID) not like ‘rs_%’
and m.UsedCount > 0

print “”
print ‘Tables accessed with Table scans ONLY, no index usage’
print “”
SELECT
“TableName” = substring(t.Owner+”.”+t.TableName, 1, 30),
“Rows” = convert(numeric(10,0),s.RowTotal),
“Size/KB” = convert(numeric(10,0),s.Reserved),
“LogicalReads” = t.LogicalReads,
“PagesRead” = t.PagesRead,
“Table scanned” = str(t.Used, 8, 0),
“When last table scanned” = t.WhenLastUsed
FROM
#tabscan t,
#table_size s
WHERE
t.Owner = s.Owner
and t.TableName = s.TableName
and not exists (select 1 from master..monOpenObjectActivity m
where object_name(m.ObjectID, m.DBID) = t.TableName
and object_name(m.ObjectID, m.DBID) = s.TableName
and m.DBID = db_id()
and m.IndexID > 0
and m.LastUsedDate is not NULL)
ORDER BY
t.Owner,
t.TableName


— Identify tables with no DML

SELECT
“Owner” = user_name(o.uid),
“TableName” = o.name,
“LogicalReads” = m.LogicalReads,
“LockRequests” = m.LockRequests,
“Operations” = m.Operations,
“Selected” = m.OptSelectCount,
“WhenLastSelected” = m.LastOptSelectDate,
“Used” = m.UsedCount,
“WhenLastUsed” = m.LastUsedDate
INTO
#dormant
from
sysobjects o,
master..monOpenObjectActivity m
where
object_name(m.ObjectID, m.DBID) = o.name
and o.type = ‘U’
and o.id = m.ObjectID
and m.IndexID = 0 — Only tables!
and m.DBID = db_id()
and object_name(m.ObjectID, m.DBID) not like ‘sa_%’
and object_name(m.ObjectID, m.DBID) not like ‘%__sa%’
and object_name(m.ObjectID, m.DBID) not like ‘rs_%’
and m.RowsInserted = 0
and m.RowsUpdated = 0
and m.RowsDeleted = 0

PRINT “”
PRINT “Displaying dormant tables with no DML activity, table scan or index usage”
PRINT “”
SELECT
“TableName” = substring(t.Owner+”.”+t.TableName, 1, 30),
“Rows” = convert(numeric(10,0),s.RowTotal),
“Size/KB” = convert(numeric(10,0),s.Reserved),
“LogicalReads” = t.LogicalReads,
“LockRequests” = t.LockRequests
FROM
#dormant t,
#table_size s
WHERE
t.Owner = s.Owner
and t.TableName = s.TableName
and t.WhenLastUsed is NULL — table has never been used by the optimiser
— and no index of this table has been used by the optimiser
and not exists (select 1 from master..monOpenObjectActivity m
where object_name(m.ObjectID, m.DBID) = t.TableName
and object_name(m.ObjectID, m.DBID) = s.TableName
and m.DBID = db_id()
and m.IndexID > 0
and m.LastUsedDate is not NULL)
ORDER BY
t.Owner,
t.TableName


— Identify tables with DML activity

SELECT
“Owner” = user_name(o.uid),
“TableName” = o.name,
“LogicalReads” = m.LogicalReads,
“LockRequests” = m.LockRequests,
“Operations” = m.Operations,
“Selected” = m.OptSelectCount,
“WhenLastSelected” = m.LastOptSelectDate,
“Used” = m.UsedCount,
“WhenLastUsed” = m.LastUsedDate,
“Inserted” = m.RowsInserted,
“Updated” = m.RowsUpdated,
“Deleted” = m.RowsDeleted
INTO
#temp
from
sysobjects o,
master..monOpenObjectActivity m
where
object_name(m.ObjectID, m.DBID) = o.name
and o.type = ‘U’
and o.id = m.ObjectID
and m.IndexID = 0
and m.DBID = db_id()
and object_name(m.ObjectID, m.DBID) not like ‘sa_%’
and object_name(m.ObjectID, m.DBID) not like ‘%__sa%’
and object_name(m.ObjectID, m.DBID) not like ‘rs_%’
and (m.RowsInserted > 0 or m.RowsUpdated > 0 or m.RowsDeleted > 0)

SELECT
“TableName” = object_name(m.ObjectID, m.DBID),
“IndexName” = i.name,
“Selected” = m.OptSelectCount,
“WhenLastSelected” = m.LastOptSelectDate,
“Used” = m.UsedCount,
“WhenLastUsed” = m.LastUsedDate
into #used
from master..monOpenObjectActivity m,
sysindexes i
where
m.IndexID > 0
and m.IndexID <> 255 — ignore text, image data chain
and m.IndexID = i.indid
and m.ObjectID = i.id
and m.DBID = db_id()
print “”
if exists(select 1 from #used where Selected = 0 and Used = 0)
begin
print “”
print ‘Indexes never selected or used by the optimizer’
print “”
select
“TableName” = substring(i.Owner+”.”+i.TableName, 1, 30),
u.IndexName,
“IndexSize/KB” = i.reserved,
u.Selected,
u.Used
from #used u,
#t1 i
where u.TableName = i.TableName
and u.IndexName = i.IndexName
and u.Selected = 0 and u.Used = 0
order by u.TableName,
u.IndexName
end
if exists(select 1 from #used where Selected > 0 and Used = 0)
begin
print “”
print ‘Indexes selected by the optimizer but never used in query’
print “”
select
“TableName” = substring(i.Owner+”.”+i.TableName, 1, 30),
u.IndexName,
“IndexSize/KB” = i.reserved,
u.Selected,
“When Last selected” = u.WhenLastSelected
from #used u,
#t1 i
where u.TableName = i.TableName
and u.IndexName = i.IndexName
and u.Selected > 0 and u.Used = 0
order by u.TableName,
u.IndexName
end
if exists(select 1 from #used where Selected = 0 and Used > 0)
begin
print “”
print ‘Indexes Used by the optimizer but never selected’
print “”
select
“TableName” = substring(i.Owner+”.”+i.TableName, 1, 30),
u.IndexName,
“IndexSize/KB” = i.reserved,
u.Selected
from #used u,
#t1 i
where u.TableName = i.TableName
and u.IndexName = i.IndexName
and u.Selected = 0 and u.Used > 0
order by u.TableName,
u.IndexName
end
PRINT “”
PRINT “Displaying tables with DML activity”
PRINT “”
SELECT
“TableName” = substring(t.Owner+”.”+t.TableName, 1, 30),
“Rows” = convert(numeric(10,0),s.RowTotal),
“Size/KB” = convert(numeric(10,0),s.Reserved),
t.Inserted,
t.Updated,
t.Deleted,
t.LockRequests,
“SUM DML ACTIVITY/ROWS ” =
CASE
WHEN t.Inserted+t.Updated+t.Deleted > 0 and convert(numeric(10,0),s.RowTotal) > 0
THEN convert(varchar(9),(t.Inserted+t.Updated+t.Deleted)/convert(numeric(10, 0),s.RowTotal))
WHEN t.Inserted+t.Updated+t.Deleted > 0 and convert(numeric(10,0),s.RowTotal) = 0
THEN ” ==> Update stats advisable”
END
FROM
#temp t,
#table_size s
WHERE
t.Owner = s.Owner
and t.TableName = s.TableName
ORDER BY
t.Owner,
t.TableName

— work out sum of index usage for tables where index(s) have been used

SELECT TableName,
SumUsed =sum(Used)
into #sumused
from #used
where Used > 0
group by TableName
print “”
select TableName,
IndexName,
Selected,
Used,
WhenLastUsed
into #clean
from #used
where Used > 0
print “”
print ‘Tables accessed with Table scans and index usage as well’
print “”
SELECT
“TableName” = substring(t.Owner+”.”+t.TableName, 1, 30),
“Rows” = convert(numeric(10,0),s.RowTotal),
“Size/KB” = convert(numeric(10,0),s.Reserved),
“LogicalReads” = t.LogicalReads,
“PagesRead” = t.PagesRead,
“Table Scans” = str(t.Used, 8, 0),
“Index Usage” = str(u.SumUsed, 8, 0),
“IndexUsage/TableScan” = str(u.SumUsed*1.0/t.Used*1.0, 9, 2)
FROM
#tabscan t,
#table_size s,
#sumused u
WHERE
t.Owner = s.Owner
and t.TableName = s.TableName
and t.TableName = u.TableName
and s.TableName = u.TableName
and u.SumUsed > 0
ORDER BY
t.Owner,
t.TableName

SELECT TableName,
IndexName,
Selected,
Used,
“Selected_over_sum_selected” = convert(numeric(10,2),Selected*1.0/sum(Selected)*1.0),
“Used_over_sum_used” = convert(numeric(10,2),Used*1.0/sum(Used)*1.0),
“Used_over_selected” = convert(numeric(10,2),Used*1.0/Selected*1.0)
into #results
from #clean
group by TableName
if exists (select 1 from #results)
begin
print “”
print ‘Index usage analysis’
print “”
select
“TableName” = substring(i.Owner+”.”+i.TableName, 1, 30),
r.IndexName,
“IndexSize/KB” = i.reserved,
r.Selected,
r.Used,
–“Selected/SUM(Selected)” = r.Selected_over_sum_selected,
“Used/SUM(Used)” = convert(numeric(10,2),r.Used_over_sum_used)
–“Used/Selected” = r.Used_over_selected
from #results r,
#t1 i
where
r.TableName = i.TableName
and r.IndexName = i.IndexName
–and r.Used_over_sum_used < 1.0 order by r.TableName, r.Used_over_sum_used desc end end go grant exec on sp__montableusage to public go exit[/sql] sp__montableusage

Share Button

Ed Barlow Procedures: sp__monusedtables updated for SAP Sybase ASE 15 and higher

Back in 2006, Mich Talebzadeh createdSAP Sybase the sp__monusedtables stored procedure for reporting on table usage in Sybase ASE 12.5x using the MDA tables as part of the excellent Extended Stored Procedures by Ed Barlow (AKA the Ed Barlow Stored Procedures). In v15, Sybase deprecated the rowcnt() function that was used to report on the estimated number of rows in favor of the row_count() function. I’ve updated the stored procedure to use the row_count() function.

diff sp__monusedtables.12 sp__monusedtables.15
14,18c14,19
< ------------------------------------------------------------------------------------------------
< -- Vers|  Date  |      Who           | DA | Description
< -------+--------+--------------------+----+-----------------------------------------------------
< -- 1.0 |07/04/06|  Mich Talebzadeh   |    | Reports on table usage via MDA tables
< -------+--------+--------------------+----+-----------------------------------------------------
---
> --------------------------------------------------------------------------------------------------
> -- Vers|   Date   |      Who           | DA | Description
> -------+----------+--------------------+----+-----------------------------------------------------
> -- 1.1 |11/18/2013|  Jason Froebe      |    | Update to use row_count
> -- 1.0 |07/04/2006|  Mich Talebzadeh   |    | Reports on table usage via MDA tables
> -------+----------+--------------------+----+-----------------------------------------------------
31a33
>              "DBID" = m.DBID,
64c66
< "Rows_in_table" = rowcnt(i.doampg)
---
>       "Rows_in_table" = row_count(t.DBID, o.id)
USE sybsystemprocs
go
IF EXISTS(SELECT 1 FROM sysobjects WHERE type = 'P' AND name = 'sp__monusedtables')
BEGIN
  DROP PROCEDURE sp__monusedtables
END
go
CREATE PROCEDURE sp__monusedtables
(
        @OWNER VARCHAR(30) = NULL,
   @OBJNAME VARCHAR(30) = NULL
)
AS
--------------------------------------------------------------------------------------------------
-- Vers|   Date   |      Who           | DA | Description
-------+----------+--------------------+----+-----------------------------------------------------
-- 1.1 |11/18/2013|  Jason Froebe      |    | Update to use row_count
-- 1.0 |07/04/2006|  Mich Talebzadeh   |    | Reports on table usage via MDA tables
-------+----------+--------------------+----+-----------------------------------------------------
BEGIN
   IF NOT EXISTS( SELECT 1
                    FROM sysobjects
                   WHERE type = 'U'
                     AND name = ISNULL(@OBJNAME, name)
                     AND USER_NAME(uid) = ISNULL(@OWNER, USER_NAME(uid)) )
   BEGIN
      PRINT "Table: %1!.%2! was not found", @OWNER, @OBJNAME
      RETURN -1
   END

  SELECT
                         "Owner" = ISNULL(@OWNER, USER_NAME(o.uid)),
                         "DBID" = m.DBID,
                         "TableName" = ISNULL(@OBJNAME, o.name),
                         "LogicalReads" = m.LogicalReads,
                         "LockRequests" = m.LockRequests,
                         "Operations" = m.Operations,
                         "Selected" = m.OptSelectCount,
                         "WhenLastSelected" = m.LastOptSelectDate,
                         "Used" = m.UsedCount,
                         "WhenLastUsed" = m.LastUsedDate,
                         "Inserted" = m.RowsInserted,
                         "Updated" = m.RowsUpdated,
                         "Deleted" = m.RowsDeleted
  INTO
      #temp
  from
       sysobjects o,
       master..monOpenObjectActivity m
  where
       object_name(m.ObjectID, m.DBID) = o.name
       and user_name(o.uid) = ISNULL(@OWNER, user_name(o.uid))
       and o.name = ISNULL(@OBJNAME, o.name)
       and o.type = 'U'
       and o.id = m.ObjectID
       and m.IndexID = 0
       and m.DBID = db_id()
       and object_name(m.ObjectID, m.DBID) not like 'sa_%'
       and object_name(m.ObjectID, m.DBID) not like '%__sa%'
       and object_name(m.ObjectID, m.DBID) not like 'rs_%'
       --and (m.RowsInserted > 0 or m.RowsUpdated > 0 or  m.RowsDeleted > 0)

  SELECT
      "Owner" = user_name(o.uid),
      "TableName" =  o.name,
      "Rows_in_table" = row_count(t.DBID, o.id)
  INTO
      #tab1
  FROM
       sysobjects o,
       sysindexes i,
       master.dbo.spt_values d,
       #temp t
  WHERE
       user_name(o.uid) = t.Owner
       and o.name = t.TableName
       and o.type = 'U'
       and i.id = o.id
       and d.number = 1
       and d.type = 'E'

  SELECT
       Owner,
       TableName,
       "RowTotal" = sum(Rows_in_table)
  INTO
       #tab2
  FROM
       #tab1
  GROUP BY
       Owner,
       TableName

  PRINT ""
  PRINT "Displaying table statistics"
  PRINT ""
  SELECT
      --t.Owner,
      t.TableName,
      "Rows" = b.RowTotal,
                t.Inserted,
                t.Updated,
                t.Deleted,
      t.LogicalReads,
      t.LockRequests
  FROM
       #temp t,
       #tab2 b
  WHERE
       t.Owner = b.Owner
       and t.TableName = b.TableName
       --and t.WhenLastSelected is NOT NULL
       --and t.WhenLastUsed is NOT NULL
       --and t.LockRequests = 0
  ORDER BY
        t.Operations
END
go
GRANT ALL ON sp__monusedtables TO PUBLIC
go
exit

sp__monusedtables

Share Button