use sybsystemprocs
go
drop procedure sp_monsummary
go

use ##database
go
drop procedure monsummary
go
drop procedure updt_umonitor
go

create procedure monsummary (@INTERVALS  tinyint = 15) as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   if (@@trancount = 0)
      begin
         set chained off
      end

   set transaction isolation level 3

   declare @lastseq  smallint,
           @maxseq   smallint

   select @maxseq = max (seq)
   from umonhistsummary

   select @lastseq = seq
   from umonhistsummary
   where end_time = (select max (end_time)
                     from umonhistsummary
                     where start_time <> end_time)

   select seq, end_time, cpu_min, cpu_max, total_read, total_write, log_write,
          xacts, dcache, pcache, ddlks, auxdp1
   into #monsummary
   from umonhistsummary
   where (ddlks >= 0)
     and ((((@lastseq > @INTERVALS)        and
            (seq + @INTERVALS > @lastseq)  and
            (seq <= @lastseq))
       or ((@lastseq <= @INTERVALS)  and
           ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq)))))

   if (exists (select *
               from #monsummary
               where auxdp1 > 999999999))
      begin
         print '    CACHE HITS HAVE BEEN DIVIDED BY 1K.'

         print ' '

         update #monsummary
         set auxdp1 = auxdp1 / 1024
      end

   print ' '

   print '             CPU%%  <---DEVICE---->   LOG           <--CACHE%%->'

   select SEQ          = seq,
          TIME         = substring (convert (char (8), end_time, 8), 1, 5),
          'MIN-MAX'    = substring (space (6 - char_length (convert (varchar (3), cpu_min)) - char_length (convert (varchar (3), cpu_max)))
                                    +  convert (varchar (3), cpu_min)
                                    +  '-'
                                    +  convert (varchar (3), cpu_max),
                                    1,
                                    7),
          'READS  '    = substring (convert (char (10),
                                             convert (money,
                                                      total_read),
                                             1),
                                    1,
                                    7),
          ' WRITES'    = substring (convert (char (10),
                                             convert (money,
                                                      total_write),
                                             1),
                                    1,
                                    7),
          'WRITES '    = substring (convert (char (10),
                                             convert (money,
                                                      log_write),
                                             1),
                                    1,
                                    7),
          XACTS        = substring (convert (char (10),
                                             convert (money,
                                                      xacts),
                                             1),
                                    1,
                                    7),
          DATA         = str (dcache, 5, 1),
          ' PROC'      = str (pcache, 5, 1),
          DLK          = convert (tinyint, ddlks),
          'CACHE HITS' = substring (convert (char (14),
                                             convert (money,
                                                      auxdp1),
                                             1),
                                    1,
                                    11)
   from #monsummary
   order by end_time
go
sp_procxmode monsummary, anymode
go


create procedure updt_umonitor as
   set arithabort arith_overflow     off
   set flushmessage                  on

   declare @dp1     double precision,
           @dp2     double precision,
           @int1    int,
           @int2    int,
           @maxseq  smallint,
           @seq     smallint

   print ' '

   if (proc_role ('sa_role') <> 1)
     begin
        print 'You must have System Administrator (SA) role to execute this stored procedure.'

        return 1
     end

   if (@@trancount > 0)
      begin
         print 'Updt_umonitor cannot be run from within a transaction.'

         return 1
      end
   else
      begin
         set chained off
      end

   set transaction isolation level 1

   --
   --  TABLE HAS BEEN RE-INITIALIZED OR UPDT_UMONITOR TERMINATED?
   --

   select @seq = min (seq)
   from umonhistsummary ulast
   where start_time = end_time

   --
   --  OTHERWISE FIND THE OLDEST SEQUENCE VALUE.  HANDLE THE CYCLICAL
   --  LAST-FIRST PAIRING VIA ISNULL.
   --

   if (@seq IS NULL)
      begin
         select @seq = isnull (max (unew.seq), 1)
         from umonhistsummary ulast,
              umonhistsummary unew
         where unew.seq        = ulast.seq + 1
           and unew.start_time < ulast.end_time
      end

   update umonhistsummary
   set start_time   = getdate (),
       end_time     = getdate (),
       cpu_min      = -1,
       cpu_max      = -1,
       total_read   = -1,
       total_write  = -1,
       log_write    = -1,
       xacts        = -1,
       ddlks        = -1,
       dcache       = -1,
       pcache       = -1,
       auxdp1       = -1,
       auxdp2       = -1
   where seq = @seq

   print ' '

   dbcc monitor ('clear', 'all', 'on')

   dbcc monitor ('clear', 'spinlock_s', 'on')

   waitfor delay '00:##wait:00'              -- Server performance dependent

   dbcc monitor ('sample', 'spinlock_s', 'off')

   dbcc monitor ('sample', 'all', 'off')

   dbcc monitor ('select', 'spinlock_s', 'on')

   dbcc monitor ('select', 'all', 'on')

   delete from umonhistory
   where seq = @seq

   insert into umonhistory
      select @seq, group_name, field_name, field_id, value
      from master.dbo.sysmonitors holdlock

   --  UPDATE MIN-MAX CPU COLUMNS

   select @int1 = min ((100 * (c.value - i.value)) / c.value)
   from umonhistory c,
        umonhistory i
   where c.seq           = @seq
     and c.group_name like 'engine_%'
     and c.field_name    = 'clock_ticks'
     and c.value         > 0
     and i.seq           = @seq
     and i.group_name    = c.group_name
     and i.field_name    = 'idle_ticks'

   select @int2 = max ((100 * (c.value - i.value)) / c.value)
   from umonhistory c,
        umonhistory i
   where c.seq           = @seq
     and c.group_name like 'engine_%'
     and c.field_name    = 'clock_ticks'
     and c.value         > 0
     and i.seq           = @seq
     and i.group_name    = c.group_name
     and i.field_name    = 'idle_ticks'

   update umonhistsummary
   set end_time = getdate (),
       cpu_min  = @int1,
       cpu_max  = @int2
   where seq = @seq

   --  UPDATE READ/WRITE COUNT COLUMNS

   update umonhistsummary
   set total_read = (select sum (value)
                     from umonhistory
                     where seq           = @seq
                       and group_name like 'disk_%'
                       and field_name    = 'total_reads')
   where seq = @seq

   update umonhistsummary
   set total_write = (select sum (value)
                      from umonhistory
                      where seq           = @seq
                        and group_name like 'disk_%'
                        and field_name    = 'total_writes')
   where seq = @seq

   update umonhistsummary
   set log_write = (select sum (value)
                    from umonhistory
                    where seq           = @seq
                      and group_name like 'buffer_%'
                      and field_name    = 'log_page_writes')
   where seq = @seq

   -- UPDATE TRANSACT COUNT

   update umonhistsummary
   set xacts = (select value
                from umonhistory
                where seq        = @seq
                  and group_name = 'access'
                  and field_name = 'xacts')
   where seq = @seq

   -- UPDATE DEADLOCK COUNT

   update umonhistsummary
   set ddlks = (select value
                from umonhistory
                where seq        = @seq
                  and group_name = 'lock'
                  and field_name = 'deadlocks')
   where seq = @seq

   -- UPDATE DATA CACHE%

   select @dp1 = sum (value)
   from umonhistory
   where seq           = @seq
     and group_name like 'buffer%'
     and field_name    = 'bufsearch_finds'

   update umonhistsummary
   set auxdp1 = @dp1
   where seq = @seq

   select @dp2 = isnull (sum (value), 0)
   from umonhistory
   where seq           = @seq
     and group_name like 'buffer%'
     and field_name    = 'bufsearch_calls'

   if (@dp2 = 0)
      begin
         update umonhistsummary
         set dcache = 0
         where seq = @seq
      end
   else
      begin
         update umonhistsummary
         set dcache = (100 * @dp1) / @dp2
         where seq = @seq
      end

   -- UPDATE PROCEDURE CACHE%

   select @dp1 = value
   from umonhistory
   where seq        = @seq
     and group_name = 'procmgr'
     and field_name = 'procedure_requests'

   if (@dp1 = 0)
      begin
         update umonhistsummary
         set pcache = 100
         where seq = @seq
      end
   else
      begin
         select @dp2 = value
         from umonhistory
         where seq        = @seq
           and group_name = 'procmgr'
           and field_name = 'procedure_reads'

         update umonhistsummary
         set pcache = (100 * (@dp1 - @dp2)) / @dp1
         where seq = @seq
      end

   select @maxseq = max (seq)
   from umonhistsummary

   print ' '

   select SEQ            = seq,
          TIME           = convert (char (8), end_time, 8),
          'CPU%'         = convert (varchar (3), cpu_min) + '-' + convert (varchar (3), cpu_max),
          'TOTAL READS'  = total_read,
          'TOTAL WRITES' = total_write,
          'LOG WRITES'   = log_write,
          XACTS          = xacts,
          DDLKS          = convert (tinyint, ddlks)
   from umonhistsummary
   where (((@seq > 6)  and  (seq + 6 > @seq)  and  (seq <= @seq))
      or ((@seq <= 6)  and  ((seq <= @seq)  or  (seq + 6 > @maxseq + @seq))))
   order by end_time

   waitfor delay '00:##pause:00'             -- Server performance dependent
go


use sybsystemprocs
go

create procedure sp_monsummary (@INTERVALS  tinyint = 15) as

   exec ##database.dbo.monsummary @INTERVALS

go
sp_addcomment sp_monsummary, 18, 'Lists monitor Summary info for last INTERVALS'
go
sp_procxmode sp_monsummary, anymode
go
