use sybsystemprocs
go
drop procedure sp_moncache
go
drop procedure sp_moncacheagg
go
drop procedure sp_moncontext
go
drop procedure sp_mondelay
go
drop procedure sp_mondevice
go
drop procedure sp_mondeviceagg
go
drop procedure sp_monindex
go
drop procedure sp_monlock
go
drop procedure sp_monmode
go
drop procedure sp_monnetwork
go
drop procedure sp_monsummary
go
drop procedure sp_monulc
go
drop procedure sp_sysmonseq
go

set quoted_identifier on
go

create procedure sp_moncache (@CACHENAME  varchar (120) = 'default data cache',
                              @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 @buffername     varchar (10),
           @lastseq        smallint,              -- most recent sequence value
           @maxseq         smallint,              -- maximum sequence value
           @ps1            tinyint,               -- pool size 1
           @ps1txt         varchar (3),           -- pool size 1 text
           @ps2            tinyint,               -- pool size 2
           @ps2txt         varchar (3),           -- pool size 2 text
           @title1         char (22),             -- print title 1
           @title2         char (22)              -- print title 2

   print ' '

   create table #moncache
      (seq       tinyint           not null,
       time      datetime          not null,
       poolsize  smallint          not null,
       searches  int               not null,
       hits      double precision  not null,
       bufmru    double precision  not null,
       buftot    int               not null,
       io_req    int               not null,
       io_yes    int               not null,
       wash      double precision  not null,
       gpages    int               not null,
       upages    int               not null,
       dirty     int               not null)

   if (exists (select c.name
               from master.dbo.sysconfigures c,
                    master.dbo.syscurconfigs cc
               where c.parent   = 19
                 and c.config   = 19
                 and c.name     = @CACHENAME
                 and cc.config  = 19
                 and cc.comment = @CACHENAME))
      begin
         select @ps1    = 2,
                @ps1txt = '2',
                @title1 = '<----- 2K POOL ------>'

         select @buffername = 'buffer_'  +  convert (varchar (3), config_admin (15, 0, 0, 0, NULL, @CACHENAME)),
                @maxseq     = max (seq)
         from ##database.dbo.umonhistsummary

         select @lastseq = seq
         from ##database.dbo.umonhistsummary
         where end_time = (select max (end_time)
                           from ##database.dbo.umonhistsummary
                           where start_time <> end_time)

         --  DETERMINE POOL SIZES AND ADJUNCTS

         if (exists (select cf.config
                     from master.dbo.sysconfigures cf,
                          master.dbo.syscurconfigs cc
                     where cf.name    like '%' + @CACHENAME + '%'
                       and cf.comment    = '16K I/O Buffer Pool'
                       and cf.parent     = 19
                       and cc.config     = cf.config
                       and cc.comment    = cf.name))
            begin
               select @ps2    = 16,
                      @ps2txt = '16',
                      @title2 = '<----- 16K POOL ----->'
            end
         else if (exists (select cf.config
                          from master.dbo.sysconfigures cf,
                               master.dbo.syscurconfigs cc
                          where cf.name    like '%' + @CACHENAME + '%'
                            and cf.comment    = '8K I/O Buffer Pool'
                            and cf.parent     = 19
                            and cc.config     = cf.config
                            and cc.comment    = cf.name))
            begin
               select @ps2    = 8,
                      @ps2txt = '8',
                      @title2 = '<----- 8K POOL ------>'
            end
         else if (exists (select cf.config
                          from master.dbo.sysconfigures cf,
                               master.dbo.syscurconfigs cc
                          where cf.name    like '%' + @CACHENAME + '%'
                            and cf.comment    = '4K I/O Buffer Pool'
                            and cf.parent     = 19
                            and cc.config     = cf.config
                            and cc.comment    = cf.name))
            begin
               select @ps2    = 4,
                      @ps2txt = '4',
                      @title2 = '<----- 4K POOL ------>'
            end

         --  CACHE AND BUFFER POOL1 (2K) INFORMATION

         if (@lastseq > @INTERVALS)
            begin
               insert into #moncache
                  select seq, getdate (), 2, value, 0, 0, 0, 0, 0, 0, 0, 0, 0
                  from ##database.dbo.umonhistory
                  where seq + @INTERVALS  > @lastseq
                    and seq              <= @lastseq
                    and group_name        = @buffername
                    and field_name        = 'bufsearch_calls'
            end
         else
            begin
               insert into #moncache
                  select seq, getdate (), 2, value, 0, 0, 0, 0, 0, 0, 0, 0, 0
                  from ##database.dbo.umonhistory
                  where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
                    and group_name = @buffername
                    and field_name = 'bufsearch_calls'
            end

         update #moncache
         set time = uhs.end_time
         from #moncache m,
              ##database.dbo.umonhistsummary uhs
         where m.seq = uhs.seq

         update #moncache
         set hits   = (select d.value
                       from ##database.dbo.umonhistory d
                       where m.seq        = d.seq
                         and d.group_name = @buffername
                         and d.field_name = 'bufsearch_finds'),
             wash   = (select d.value
                       from ##database.dbo.umonhistory d
                       where m.seq        = d.seq
                         and d.group_name = @buffername
                         and d.field_name = 'bufsearch_finds_in_wash'),
             io_req = (select d.value
                       from ##database.dbo.umonhistory d
                       where m.seq        = d.seq
                         and d.group_name = @buffername
                         and d.field_name = 'prefetch_req'),
             io_yes = (select d.value
                       from ##database.dbo.umonhistory d
                       where m.seq         = d.seq
                         and d.group_name  = @buffername
                         and d.field_name in ('prefetch_as_requested',
                                              'prefetch_page_realigned')),
             bufmru = (select d.value
                       from ##database.dbo.umonhistory d
                       where m.seq        = d.seq
                         and d.group_name = @buffername
                         and d.field_name = 'bufunkeep_mru'),
             buftot = (select sum (d.value)
                       from ##database.dbo.umonhistory d
                       where m.seq         = d.seq
                         and d.group_name  = @buffername
                         and d.field_name in ('bufunkeep_lru', 'bufunkeep_mru')),
             gpages = (select d.value
                       from ##database.dbo.umonhistory d
                       where m.seq        = d.seq
                         and d.group_name = @buffername
                         and d.field_name = 'bufgrab_'  +  @ps1txt  +  'k'),
             upages = (select d.value
                       from ##database.dbo.umonhistory d
                       where m.seq        = d.seq
                         and d.group_name = @buffername
                         and d.field_name = 'bufgrab_ref_'  +  @ps1txt  +  'K'),
             dirty  = (select d.value
                       from ##database.dbo.umonhistory d
                       where m.seq        = d.seq
                         and d.group_name = @buffername
                         and d.field_name = 'bufgrab_dirty_'  +  @ps1txt  +  'k')
         from #moncache m
         where m.poolsize = @ps1

         --  BUFFER POOL2 INFORMATION

         if (@ps2 IS NOT NULL)
            begin
               if (@lastseq > @INTERVALS)
                  begin
                     insert into #moncache
                        select seq, getdate (), @ps2, 1, 1, 1, 1, 1, 1, 1, ((@ps2 * value) / 2), 1, 1
                        from ##database.dbo.umonhistory
                        where seq + @INTERVALS  > @lastseq
                          and seq              <= @lastseq
                          and group_name        = @buffername
                          and field_name        = 'bufgrab_'  +  @ps2txt  +  'k'
                  end
               else
                  begin
                     insert into #moncache
                        select seq, getdate (), @ps2, 1, 1, 1, 1, 1, 1, 1, ((@ps2 * value) / 2), 1, 1
                        from ##database.dbo.umonhistory
                        where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
                          and group_name = @buffername
                          and field_name = 'bufgrab_'  +  @ps2txt  +  'k'
                  end

               update #moncache
               set upages = (select d.value
                             from ##database.dbo.umonhistory d
                             where m.seq        = d.seq
                               and d.group_name = @buffername
                               and d.field_name = 'bufgrab_ref_'  +  @ps2txt  +  'K'),
                   dirty  = (select (@ps2 * d.value) / 2
                             from ##database.dbo.umonhistory d
                             where m.seq        = d.seq
                               and d.group_name = @buffername
                               and d.field_name = 'bufgrab_dirty_'  +  @ps2txt  +  'k')
               from #moncache m
               where m.poolsize = @ps2
            end

         --  FINAL COMPUTATIONS AND REPORT

         update #moncache
         set bufmru = bufmru / buftot
         where buftot > 0

         update #moncache
         set io_req = 1
         where io_req = 0

         update #moncache
         set wash = (100 * wash) / hits
         where hits  > 0

         update #moncache
         set wash = 999.9
         where wash > 999.9

         update #moncache
         set wash = 0
         where hits = 0

         update #moncache
         set hits = (100 * hits) / searches
         where searches > 0

         if (@ps2 IS NULL)
            begin
               print '           <------ CACHE%% ------> <-------- 2K POOL ------->'

               select SEQ   = seq,
                      TIME  = substring (convert (char (8), time, 8), 1, 5),
                      HIT   = str (hits, 5, 1),
                      WASH  = str (wash, 5, 1),
                      MRU   = str (bufmru, 4, 1),
                      LARGE = str (((100.0 * io_yes) / io_req), 5, 1),
                      GRAB  = substring (convert (char (12),
                                                  convert (money, gpages),
                                                  1),
                                         1,
                                         9),
                      'USE' = substring (convert (char (12),
                                                  convert (money, upages),
                                                  1),
                                         1,
                                         9),
                      DIRTY = substring (convert (char (9),
                                                  convert (money, dirty),
                                                  1),
                                         1,
                                         6)
               from #moncache
               where poolsize = @ps1
               order by time
            end
         else
            begin
               print '     <------ CACHE%% ------> %1! %2!', @title1, @title2

               select SEQ      = m1.seq,
                      HIT      = str (m1.hits, 5, 1),
                      WASH     = str (m1.wash, 5, 1),
                      MRU      = str (m1.bufmru, 4, 1),
                      LARGE    = str (((100.0 * m1.io_yes) / m1.io_req), 5, 1),
                      GRAB     = substring (convert (char (10),
                                                     convert (money, m1.gpages),
                                                     1),
                                            1,
                                            7),
                      'USE'    = substring (convert (char (10),
                                                     convert (money, m1.upages),
                                                     1),
                                            1,
                                            7),
                      'DIRTY'  = substring (convert (char (9),
                                                     convert (money, m1.dirty),
                                                     1),
                                            1,
                                            6),
                      'GRAB '  = substring (convert (char (10),
                                                     convert (money, m2.gpages),
                                                     1),
                                            1,
                                            7),
                      'USE '   = substring (convert (char (10),
                                                     convert (money, m2.upages),
                                                     1),
                                            1,
                                            7),
                      'DIRTY ' = substring (convert (char (9),
                                                     convert (money, m2.dirty),
                                                     1),
                                            1,
                                            6)
               from #moncache m1,
                    #moncache m2
               where m1.seq      = m2.seq
                 and m1.poolsize = @ps1
                 and m2.poolsize = @ps2
               order by m1.time
            end
      end
   else
      begin
         print '    CACHE <%1!> DOES NOT EXIST.', @CACHENAME

         print ' '
      end
go
sp_addcomment sp_moncache, 18, 'Lists monitor Cache/Pool info for CACHENAME for last INTERVALS'
go
sp_addcomment sp_moncache, 18, '   Default is "default data cache" for 15 intervals'
go
sp_procxmode sp_moncache, anymode
go

create procedure sp_moncacheagg (@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,              -- most recent sequence value
           @maxseq   smallint               -- maximum sequence value

   print ' '

   create table #monbuffer
      (seq           tinyint       not null,
       group_name    varchar (25)  not null,
       field_name    varchar (35)  not null,
       field_id      smallint      not null,
       value         int           not null)

   create table #moncacheagg
      (seq       tinyint           not null,
       time      datetime          not null,
       searches  int               not null,
       hits      double precision  not null,
       bufmru    double precision  not null,
       buftot    int               not null,
       io_req    int               not null,
       io_yes    int               not null,
       wash      double precision  not null,
       gpages    int               not null,
       upages    int               not null,
       dirty     int               not null,
       iso0pgs   int               not null,
       iso0strts int               not null)

   select @maxseq = max (seq)
   from ##database.dbo.umonhistsummary

   select @lastseq = seq
   from ##database.dbo.umonhistsummary
   where end_time = (select max (end_time)
                     from ##database.dbo.umonhistsummary
                     where start_time <> end_time)

   if (@lastseq > @INTERVALS)
      begin
         insert into #monbuffer
            select seq, group_name, field_name, field_id, value
            from ##database.dbo.umonhistory
            where seq + @INTERVALS    > @lastseq
              and seq                <= @lastseq
              and group_name       like 'buffer_%'
      end
   else
      begin
         insert into #monbuffer
            select seq, group_name, field_name, field_id, value
            from ##database.dbo.umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name like 'buffer_%'
      end

   insert into #moncacheagg
      select distinct seq, getdate (), sum (value), 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
      from #monbuffer
      where field_name = 'bufsearch_calls'
      group by seq
      having field_name = 'bufsearch_calls'

   update #moncacheagg
   set time = uhs.end_time
   from #moncacheagg m,
        ##database.dbo.umonhistsummary uhs
   where m.seq = uhs.seq

   update #moncacheagg
   set hits    = (select sum (d.value)
                  from #monbuffer d
                  where m.seq        = d.seq
                    and d.field_name = 'bufsearch_finds'),
       wash    = (select sum (d.value)
                  from #monbuffer d
                  where m.seq        = d.seq
                    and d.field_name = 'bufsearch_finds_in_wash'),
       io_req  = (select sum (d.value)
                  from #monbuffer d
                  where m.seq        = d.seq
                    and d.field_name = 'prefetch_req'),
       io_yes  = (select sum (d.value)
                  from #monbuffer d
                  where m.seq         = d.seq
                    and d.field_name in ('prefetch_as_requested',
                                         'prefetch_page_realigned')),
       bufmru  = (select sum (d.value)
                  from #monbuffer d
                  where m.seq        = d.seq
                    and d.field_name = 'bufunkeep_mru'),
       buftot  = (select sum (d.value)
                  from #monbuffer d
                  where m.seq         = d.seq
                    and d.field_name in ('bufunkeep_lru', 'bufunkeep_mru')),
       gpages  = (select sum (d.value)
                  from #monbuffer d
                  where m.seq        = d.seq
                    and d.field_name = 'bufgrab_2k'),
       upages  = (select sum (d.value)
                  from #monbuffer d
                  where m.seq           = d.seq
                    and d.field_name like 'bufgrab_ref_%'),
       dirty   = (select sum (d.value)
                  from #monbuffer d
                  where m.seq           = d.seq
                    and d.field_name like 'bufgrab_dirty_%'),
       iso0pgs = (select sum (d.value)
                  from #monbuffer d
                  where m.seq        = d.seq
                    and d.field_name = 'level0_bufpredirty')
   from #moncacheagg m

   update #moncacheagg
   set gpages = (select m.gpages + (2 * sum (d.value))
                 from #monbuffer d
                 where m.seq        = d.seq
                   and d.field_name = 'bufgrab_4k')
   from #moncacheagg m

   update #moncacheagg
   set gpages = (select m.gpages + (4 * sum (d.value))
                 from #monbuffer d
                 where m.seq        = d.seq
                   and d.field_name = 'bufgrab_8k')
   from #moncacheagg m

   update #moncacheagg
   set gpages = (select m.gpages + (8 * sum (d.value))
                 from #monbuffer d
                 where m.seq        = d.seq
                   and d.field_name = 'bufgrab_16k')
   from #moncacheagg m

   update #moncacheagg
   set iso0strts = (select d.value
                    from ##database.dbo.umonhistory d
                    where m.seq        = d.seq
                      and d.group_name = 'access'
                      and d.field_name = 'dirty_read_restarts')
   from #moncacheagg m

   --  FINAL COMPUTATIONS AND REPORT

   update #moncacheagg
   set bufmru = bufmru / buftot
   where buftot > 0

   update #moncacheagg
   set io_req = 1
   where io_req = 0

   update #moncacheagg
   set wash = (100 * wash) / hits
   where hits  > 0

   update #moncacheagg
   set wash = 99.9
   where wash > 99.9

   update #moncacheagg
   set wash = 0
   where hits = 0

   update #moncacheagg
   set hits = (100 * hits) / searches
   where searches > 0

   print '     <----- CACHE%% ------> <-------- TOTAL PAGES --------> <----- ISO 0 ----->'

   select SEQ     = seq,
          'HIT'   = str (hits, 5, 1),
          'WASH'  = str (wash, 4, 1),
          'MRU'   = str (bufmru, 4, 1),
          'LARGE' = str (((100.0 * io_yes) / io_req), 5, 1),
          GRAB    = substring (convert (char (14),
                                        convert (money, gpages),
                                        1),
                               1,
                               11),
          'USE'   = substring (convert (char (14),
                                        convert (money, upages),
                                        1),
                               1,
                               11),
          DIRTY   = substring (convert (char (10),
                                        convert (money, dirty),
                                        1),
                               1,
                               7),
          PAGES   = substring (convert (char (14),
                                        convert (money, iso0pgs),
                                        1),
                               1,
                               11),
          RESTRTS = substring (convert (char (10),
                                        convert (money, iso0strts),
                                        1),
                               1,
                               7)
   from #moncacheagg
   order by time
go
sp_addcomment sp_moncacheagg, 18, 'Lists aggregate monitor Cache info for last INTERVALS'
go
sp_procxmode sp_moncacheagg, anymode
go

create procedure sp_moncontext (@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

   print ' '

   print '    VY:   Voluntary Yields'

   print '    CSM:  Cache Search Misses'

   print '    SDW:  System Disk Writes'

   print '    IOP:  I/O Pacing'

   print '    LLC:  Logical Lock Contention'

   print '    ALC:  Address Lock Contention'

   print '    LSC:  Log Semaphore Contention'

   print '    GCS:  Group Commit Sleeps'

   print '    LLPW: Last Log Page Writes'

   print '    MC:   Modify Conflicts'

   print '    IODC: I/O Device Contention'

   print '    NPR:  Network Packet Received'

   print '    NPS:  Network Packet Sent'

   print '    SL:   Sysindexes Lookup'

   print ' '

   select @maxseq = max (seq)
   from ##database.dbo.umonhistsummary

   select @lastseq = seq
   from ##database.dbo.umonhistsummary
   where end_time = (select max (end_time)
                     from ##database.dbo.umonhistsummary
                     where start_time <> end_time)

   create table #moncontext
      (seq    tinyint     not null,
       name   char (35)   not null,
       value  int         null)

   if (@INTERVALS <= @lastseq)
      begin
         insert into #moncontext
            select distinct seq, 'context switches', sum (value)
            from ##database.dbo.umonhistory
            where seq + @INTERVALS    > @lastseq
              and seq                <= @lastseq
              and group_name       like 'engine_%'
              and field_name          = 'context_switches'
            group by seq
            having seq + @INTERVALS    > @lastseq
               and seq                <= @lastseq
               and group_name       like 'engine_%'
               and field_name          = 'context_switches'

         insert into #moncontext
            select seq, field_name, value
            from ##database.dbo.umonhistory
            where seq + @INTERVALS  > @lastseq
              and seq              <= @lastseq
              and group_name        = 'kernel'
              and field_name        = 'yields'

         insert into #moncontext
            select distinct seq, 'cache misses', sum (value)
            from ##database.dbo.umonhistory
            where seq + @INTERVALS    > @lastseq
              and seq                <= @lastseq
              and group_name       like 'buffer_%'
              and field_name          = 'bufread_read_waits'
            group by seq
            having seq + @INTERVALS    > @lastseq
               and seq                <= @lastseq
               and group_name       like 'buffer_%'
               and field_name          = 'bufread_read_waits'

         insert into #moncontext
            select distinct seq, 'system disk writes', sum (value)
            from ##database.dbo.umonhistory
            where seq + @INTERVALS    > @lastseq
              and seq                <= @lastseq
              and group_name       like 'buffer_%'
              and field_name         in ('write_waits', 'hk_write_waits', 'restart_io_waits')
            group by seq
            having seq + @INTERVALS    > @lastseq
               and seq                <= @lastseq
               and group_name       like 'buffer_%'
               and field_name         in ('write_waits', 'hk_write_waits', 'restart_io_waits')

         insert into #moncontext
            select distinct seq, 'io pacing', sum (value)
            from ##database.dbo.umonhistory
            where seq + @INTERVALS    > @lastseq
              and seq                <= @lastseq
              and group_name       like 'buffer_%'
              and field_name         in ('my_start_waits_log', 'my_start_waits_non-log', 'my_other_waits_non-log')
            group by seq
            having seq + @INTERVALS    > @lastseq
               and seq                <= @lastseq
               and group_name       like 'buffer_%'
               and field_name         in ('my_start_waits_log', 'my_start_waits_non-log', 'my_other_waits_non-log')

         insert into #moncontext
            select distinct seq, 'logical lock', sum (value)
            from ##database.dbo.umonhistory
            where seq + @INTERVALS        > @lastseq
              and seq                    <= @lastseq
              and group_name              = 'lock'
              and field_name           like 'waited_%'
              and field_name       not like 'waited_%_ADDR'
            group by seq
            having seq + @INTERVALS    > @lastseq
               and seq                    <= @lastseq
               and group_name              = 'lock'
               and field_name           like 'waited_%'
               and field_name       not like 'waited_%_ADDR'

         insert into #moncontext
            select distinct seq, 'address lock', sum (value)
            from ##database.dbo.umonhistory
            where seq + @INTERVALS    > @lastseq
              and seq                <= @lastseq
              and group_name          = 'lock'
              and field_name       like 'waited_%_ADDR'
            group by seq
            having seq + @INTERVALS    > @lastseq
               and seq                <= @lastseq
               and group_name          = 'lock'
               and field_name       like 'waited_%_ADDR'

         insert into #moncontext
            select seq, 'log semaphore', value
            from ##database.dbo.umonhistory
            where seq + @INTERVALS  > @lastseq
              and seq              <= @lastseq
              and group_name        = 'xls'
              and field_name        = 'log_lock_waited'
            group by seq
            having seq + @INTERVALS    > @lastseq
               and seq              <= @lastseq
               and group_name        = 'xls'
               and field_name        = 'log_lock_waited'

         insert into #moncontext
            select distinct seq, 'group commits', sum (value)
            from ##database.dbo.umonhistory
            where seq + @INTERVALS    > @lastseq
              and seq                <= @lastseq
              and group_name       like 'buffer_%'
              and field_name         in ('my_other_log_waits', 'log_lastpage_pending_io_sleeps')
            group by seq
            having seq + @INTERVALS    > @lastseq
               and seq                <= @lastseq
               and group_name       like 'buffer_%'
               and field_name         in ('my_other_log_waits', 'log_lastpage_pending_io_sleeps')

         insert into #moncontext
            select distinct seq, 'last log page', sum (value)
            from ##database.dbo.umonhistory
            where seq + @INTERVALS    > @lastseq
              and seq                <= @lastseq
              and group_name       like 'buffer_%'
              and field_name          = 'last_log_page_writes'
            group by seq
            having seq + @INTERVALS    > @lastseq
               and seq                <= @lastseq
               and group_name       like 'buffer_%'
               and field_name          = 'last_log_page_writes'

         insert into #moncontext
            select distinct seq, 'modify conflicts', sum (value)
            from ##database.dbo.umonhistory
            where seq + @INTERVALS    > @lastseq
              and seq                <= @lastseq
              and group_name       like 'buffer_%'
              and field_name         in ('changing_state_waits', 'bufwrite_changing_waits',
                                         'bufpredirty_write_waits', 'bufpredirty_changing_waits',
                                         'bufnewpage_changing_waits', 'ind_bufguess_changing_waits',
                                         'ind_bufguess_writing_waits')
            group by seq
            having seq + @INTERVALS    > @lastseq
               and seq                <= @lastseq
               and group_name       like 'buffer_%'
               and field_name         in ('changing_state_waits', 'bufwrite_changing_waits',
                                          'bufpredirty_write_waits', 'bufpredirty_changing_waits',
                                          'bufnewpage_changing_waits', 'ind_bufguess_changing_waits',
                                          'ind_bufguess_writing_waits')

         insert into #moncontext
            select distinct seq, 'device contention', sum (value)
            from ##database.dbo.umonhistory
            where seq + @INTERVALS    > @lastseq
              and seq                <= @lastseq
              and group_name       like 'disk_%'
              and field_name          = 'p_misses'
            group by seq
            having seq + @INTERVALS    > @lastseq
               and seq                <= @lastseq
               and group_name       like 'disk_%'
               and field_name          = 'p_misses'

         insert into #moncontext
            select distinct seq, 'np received', value
            from ##database.dbo.umonhistory
            where seq + @INTERVALS  > @lastseq
              and seq              <= @lastseq
              and group_name        = 'network'
              and field_name        = 'total_packets_received'
            group by seq
            having seq + @INTERVALS    > @lastseq
               and seq              <= @lastseq
               and group_name        = 'network'
               and field_name        = 'total_packets_received'

         insert into #moncontext
            select seq, 'np sent', value
            from ##database.dbo.umonhistory
            where seq + @INTERVALS  > @lastseq
              and seq              <= @lastseq
              and group_name        = 'network'
              and field_name        = 'total_packets_sent'
            group by seq
            having seq + @INTERVALS    > @lastseq
               and seq              <= @lastseq
               and group_name        = 'network'
               and field_name        = 'total_packets_sent'

         insert into #moncontext
            select distinct seq, 'sysindexes', sum (value)
            from ##database.dbo.umonhistory
            where seq + @INTERVALS    > @lastseq
              and seq                <= @lastseq
              and group_name          = 'sysind'
              and field_name       like 'ind_bufguess%'
            group by seq
            having seq + @INTERVALS    > @lastseq
               and seq                <= @lastseq
               and group_name          = 'sysind'
              and field_name        like 'ind_bufguess%'
      end
   else
      begin
         insert into #moncontext
            select distinct seq, 'context switches', sum (value)
            from ##database.dbo.umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name like 'engine_%'
              and field_name    = 'context_switches'
            group by seq
            having ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
               and group_name like 'engine_%'
               and field_name    = 'context_switches'

         insert into #moncontext
            select seq, field_name, value
            from ##database.dbo.umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name  = 'kernel'
              and field_name  = 'yields'

         insert into #moncontext
            select distinct seq, 'cache misses', sum (value)
            from ##database.dbo.umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name like 'buffer_%'
              and field_name    = 'bufread_read_waits'
            group by seq
            having ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
               and group_name like 'buffer_%'
               and field_name    = 'bufread_read_waits'

         insert into #moncontext
            select distinct seq, 'system disk writes', sum (value)
            from ##database.dbo.umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name like 'buffer_%'
              and field_name   in ('write_waits', 'hk_write_waits', 'restart_io_waits')
            group by seq
            having ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
               and group_name like 'buffer_%'
               and field_name   in ('write_waits', 'hk_write_waits', 'restart_io_waits')

         insert into #moncontext
            select distinct seq, 'io pacing', sum (value)
            from ##database.dbo.umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name like 'buffer_%'
              and field_name   in ('my_start_waits_log', 'my_start_waits_non-log', 'my_other_waits_non-log')
            group by seq
            having ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
               and group_name like 'buffer_%'
               and field_name   in ('my_start_waits_log', 'my_start_waits_non-log', 'my_other_waits_non-log')

         insert into #moncontext
            select distinct seq, 'logical lock', sum (value)
            from ##database.dbo.umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name        = 'lock'
              and field_name     like 'waited_%'
              and field_name not like 'waited_%_ADDR'
            group by seq
            having ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
               and group_name        = 'lock'
               and field_name     like 'waited_%'
               and field_name not like 'waited_%_ADDR'

         insert into #moncontext
            select distinct seq, 'address lock', sum (value)
            from ##database.dbo.umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name    = 'lock'
              and field_name like 'waited_%_ADDR'
            group by seq
            having ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
               and group_name    = 'lock'
               and field_name like 'waited_%_ADDR'

         insert into #moncontext
            select seq, 'log semaphore', value
            from ##database.dbo.umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name  = 'xls'
              and field_name  = 'log_lock_waited'
            group by seq
            having ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
               and group_name  = 'xls'
               and field_name  = 'log_lock_waited'

         insert into #moncontext
            select distinct seq, 'group commits', sum (value)
            from ##database.dbo.umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name like 'buffer_%'
              and field_name   in ('my_other_log_waits', 'log_lastpage_pending_io_sleeps')
            group by seq
            having ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
               and group_name like 'buffer_%'
               and field_name   in ('my_other_log_waits', 'log_lastpage_pending_io_sleeps')

         insert into #moncontext
            select distinct seq, 'last log page', sum (value)
            from ##database.dbo.umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name like 'buffer_%'
              and field_name    = 'last_log_page_writes'
            group by seq
            having ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
               and group_name like 'buffer_%'
               and field_name    = 'last_log_page_writes'

         insert into #moncontext
            select distinct seq, 'modify conflicts', sum (value)
            from ##database.dbo.umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name like 'buffer_%'
              and field_name   in ('changing_state_waits', 'bufwrite_changing_waits',
                                   'bufpredirty_write_waits', 'bufpredirty_changing_waits',
                                   'bufnewpage_changing_waits', 'ind_bufguess_changing_waits',
                                   'ind_bufguess_writing_waits')
            group by seq
            having ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
               and group_name like 'buffer_%'
               and field_name   in ('changing_state_waits', 'bufwrite_changing_waits',
                                    'bufpredirty_write_waits', 'bufpredirty_changing_waits',
                                    'bufnewpage_changing_waits', 'ind_bufguess_changing_waits',
                                    'ind_bufguess_writing_waits')

         insert into #moncontext
            select distinct seq, 'device contention', sum (value)
            from ##database.dbo.umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name like 'disk_%'
              and field_name    = 'p_misses'
            group by seq
            having ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
               and group_name like 'disk_%'
               and field_name    = 'p_misses'

         insert into #moncontext
            select seq, 'np received', value
            from ##database.dbo.umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name  = 'network'
              and field_name  = 'total_packets_received'
            group by seq
            having ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
               and group_name  = 'network'
               and field_name  = 'total_packets_received'

         insert into #moncontext
            select seq, 'np sent', value
            from ##database.dbo.umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name  = 'network'
              and field_name  = 'total_packets_sent'
            group by seq
            having ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
               and group_name  = 'network'
               and field_name  = 'total_packets_sent'

         insert into #moncontext
            select distinct seq, 'sysindexes', sum (value)
            from ##database.dbo.umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name    = 'sysind'
              and field_name like 'ind_bufguess%'
            group by seq
            having ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
               and group_name    = 'sysind'
               and field_name like 'ind_bufguess%'
      end

   select SEQ     = uhs.seq,
          'VY%'   = convert (tinyint, (100 * vy.value) / cs.value),
          'CSM%'  = convert (tinyint, (100 * cm.value) / cs.value),
          'SDW%'  = convert (tinyint, (100 * sdw.value) / cs.value),
          'IOP%'  = convert (tinyint, (100 * iop.value) / cs.value),
          'LLC%'  = convert (tinyint, (100 * llc.value) / cs.value),
          'ALC%'  = convert (tinyint, (100 * alc.value) / cs.value),
          'LSC%'  = convert (tinyint, (100 * lsc.value) / cs.value),
          'GCS%'  = convert (tinyint, (100 * gcs.value) / cs.value),
          'LLPW%' = convert (tinyint, (100 * llpw.value) / cs.value),
          'MC%'   = convert (tinyint, (100 * mc.value) / cs.value),
          'IODC%' = convert (tinyint, (100 * dc.value) / cs.value),
          'NPR%'  = convert (tinyint, (100 * npr.value) / cs.value),
          'NPS%'  = convert (tinyint, (100 * nps.value) / cs.value),
          'SL%'   = convert (tinyint, (100 * sl.value) / cs.value)
   from ##database.dbo.umonhistsummary uhs,
        #moncontext cs,
        #moncontext vy,
        #moncontext cm,
        #moncontext sdw,
        #moncontext iop,
        #moncontext llc,
        #moncontext alc,
        #moncontext lsc,
        #moncontext gcs,
        #moncontext llpw,
        #moncontext mc,
        #moncontext dc,
        #moncontext npr,
        #moncontext nps,
        #moncontext sl
   where cs.seq    = uhs.seq
     and cs.name   = 'context switches'
     and vy.seq    = cs.seq
     and vy.name   = 'yields'
     and cm.seq    = cs.seq
     and cm.name   = 'cache misses'
     and sdw.seq   = cs.seq
     and sdw.name  = 'system disk writes'
     and iop.seq   = cs.seq
     and iop.name  = 'io pacing'
     and llc.seq   = cs.seq
     and llc.name  = 'logical lock'
     and alc.seq   = cs.seq
     and alc.name  = 'address lock'
     and lsc.seq   = cs.seq
     and lsc.name  = 'log semaphore'
     and gcs.seq   = cs.seq
     and gcs.name  = 'group commits'
     and llpw.seq  = cs.seq
     and llpw.name = 'last log page'
     and mc.seq    = cs.seq
     and mc.name   = 'modify conflicts'
     and dc.seq    = cs.seq
     and dc.name   = 'device contention'
     and npr.seq   = cs.seq
     and npr.name  = 'np received'
     and nps.seq   = cs.seq
     and nps.name  = 'np sent'
     and sl.seq    = cs.seq
     and sl.name   = 'sysindexes'
   order by uhs.end_time
go
sp_addcomment sp_moncontext, 18, 'Lists monitor Context info for last INTERVALS'
go
sp_procxmode sp_moncontext, anymode
go

create procedure sp_mondelay (@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 @cachecnt  smallint,
           @lastseq   smallint,
           @maxseq    smallint

   print ' '

   create table #mondelay
      (seq          tinyint           not null,
       end_time     datetime          not null,
       diskwait     int               not null,
       diskiodelay  int               not null,
       netdelay     int               not null,
       semulc       int               not null,
       semlog       int               not null,
       spinmax1     double precision  not null,
       spinmax2     double precision  not null,
       spinmax2id   smallint          not null)

   create table #monspin
      (seq          tinyint           not null,
       id           smallint          not null,
       spinw        double precision  not null,
       sping        int               not null)

   select @maxseq = max (seq)
   from ##database.dbo.umonhistsummary

   select @lastseq = seq
   from ##database.dbo.umonhistsummary
   where end_time = (select max (end_time)
                     from ##database.dbo.umonhistsummary
                     where start_time <> end_time)

   if (@lastseq > @INTERVALS)
      begin
         insert into #mondelay
            select seq, end_time, 0, 0, 0, 0, 0, 0, 0, 0
            from ##database.dbo.umonhistsummary
            where seq + @INTERVALS  > @lastseq
              and seq              <= @lastseq

         insert into #monspin
            select w.seq, w.field_id, w.value, g.value
            from ##database.dbo.umonhistory w,
                 ##database.dbo.umonhistory g
            where w.seq + @INTERVALS  > @lastseq
              and w.seq              <= @lastseq
              and w.group_name        = 'spinlock_w_0'
              and w.field_name        = 'Resource->rcaches[i]->cspin'
              and g.seq               = w.seq
              and g.field_id          = w.field_id
              and g.group_name        = 'spinlock_p_0'
              and g.field_name        = 'Resource->rcaches[i]->cspin'
      end
   else
      begin
         insert into #mondelay
            select seq, end_time, 0, 0, 0, 0, 0, 0, 0, 0
            from ##database.dbo.umonhistsummary
            where seq              <= @lastseq
               or seq + @INTERVALS  > @maxseq + @lastseq

         insert into #monspin
            select w.seq, w.field_id, w.value, g.value
            from ##database.dbo.umonhistory w,
                 ##database.dbo.umonhistory g
            where ((w.seq <= @lastseq)  or (w.seq + @INTERVALS > @maxseq + @lastseq))
              and w.group_name = 'spinlock_w_0'
              and w.field_name = 'Resource->rcaches[i]->cspin'
              and g.seq        = w.seq
              and g.field_id   = w.field_id
              and g.group_name = 'spinlock_p_0'
              and g.field_name = 'Resource->rcaches[i]->cspin'
      end

   update #mondelay
   set end_time = uhs.end_time
   from #mondelay m,
        ##database.dbo.umonhistsummary uhs
   where m.seq = uhs.seq

   update #mondelay
   set diskwait  = (select isnull (sum (d.value), 0)
                    from ##database.dbo.umonhistory d
                    where m.seq           = d.seq
                      and d.group_name like 'disk_%'
                      and d.field_name    = 'p_misses')
   from #mondelay m

   update #mondelay
   set diskiodelay = (select d.value
                      from ##database.dbo.umonhistory d
                      where m.seq        = d.seq
                        and d.group_name = 'kernel'
                        and d.field_name = 'udalloc_sleeps')
   from #mondelay m

   update #mondelay
   set diskiodelay = (select m.diskiodelay + sum (d.value)
                      from ##database.dbo.umonhistory d
                      where m.seq           = d.seq
                        and d.group_name like 'engine_%'
                        and d.field_name like 'AIOs_delayed_due_to_%_limit')
   from #mondelay m

   update #mondelay
   set netdelay    = (select d.value
                      from ##database.dbo.umonhistory d
                      where m.seq        = d.seq
                        and d.group_name = 'kernel'
                        and d.field_name = 'ksalloc_sleeps')
   from #mondelay m

   update #mondelay
   set semulc = (select d.value
                 from ##database.dbo.umonhistory d
                 where m.seq        = d.seq
                   and d.group_name = 'xls'
                   and d.field_name = 'plc_lock_waits'),
       semlog = (select d.value
                 from ##database.dbo.umonhistory d
                 where m.seq        = d.seq
                   and d.group_name = 'xls'
                   and d.field_name = 'log_lock_waited')
   from #mondelay m

   update #monspin
   set spinw = (100 * spinw) /sping
   where sping <> 0

   update #mondelay
   set spinmax2 = (select max (s2.spinw)
                   from #monspin s2
                   where m.seq = s2.seq)
   from #mondelay m

   select @cachecnt = count (*)
   from #monspin

   if (@cachecnt > @INTERVALS)
      begin
         update #mondelay
         set spinmax2id = (select max (id)
                           from #monspin s2
                           where m.seq      = s2.seq
                             and m.spinmax2 = s2.spinw)
         from #mondelay m

         update #mondelay
         set spinmax1 = (select max (s.spinw)
                         from #monspin s
                         where m.seq         = s.seq
                           and m.spinmax2id <> s.id)
         from #mondelay m

         print '           <-- DELAYS ---> <--- SEMAPHOR WAIT --->  BUSIEST'

         select SEQ         = seq,
                TIME        = substring (convert (char (8), end_time, 8), 1, 5),
                DEVICE      = substring (convert (char (10), convert (money, diskiodelay), 1), 1, 7),
                NETWORK     = substring (convert (char (10), convert (money, netdelay), 1), 1, 7),
                'DEVICE '   = substring (convert (char (10), convert (money, diskwait), 1), 1, 7),
                ULC         = substring (convert (char (10), convert (money, semulc), 1), 1, 7),
                'LOG'       = substring (convert (char (10), convert (money, semlog), 1), 1, 7),
                'SPINLOCK%' = convert (char (4), str (spinmax1, 4, 1))
                              + ' ' + convert (char (4), str (spinmax2, 4, 1))
         from #mondelay m
         order by end_time
      end
   else
      begin
         print '           <-- DELAYS ---> <--- SEMAPHOR WAIT --->  SPINLOCK'

         select SEQ          = seq,
                TIME         = substring (convert (char (8), end_time, 8), 1, 5),
                DEVICE       = substring (convert (char (10), convert (money, diskiodelay), 1), 1, 7),
                NETWORK      = substring (convert (char (10), convert (money, netdelay), 1), 1, 7),
                'DEVICE '    = substring (convert (char (10), convert (money, diskwait), 1), 1, 7),
                ULC          = substring (convert (char (10), convert (money, semulc), 1), 1, 7),
                'LOG'        = substring (convert (char (10), convert (money, semlog), 1), 1, 7),
                'CONTENTION' = convert (char (10), str (spinmax2, 10, 2))
         from #mondelay m
         order by end_time
      end
go
sp_addcomment sp_mondelay, 18, 'Lists monitor Delay info for last INTERVALS'
go
sp_procxmode sp_mondelay, anymode
go

create procedure sp_mondevice (@DEVICE     varchar (30),
                               @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,
           @phyname        varchar (25)

   print ' '

   select @phyname = 'disk_' + convert (varchar (4), low / 16777216)
   from master.dbo.sysdevices
   where name           = @DEVICE
     and (status & 16) <> 16

   if (@phyname IS NULL)
      begin
         if (exists (select *
                     from master.dbo.sysdevices
                     where name = @DEVICE))
            begin
                print '    DEVICE <%1!> IS NOT A DISK DEVICE.', @DEVICE

                print ' '
            end
         else
            begin
                print '    DEVICE <%1!> DOES NOT EXIST.', @DEVICE

                print ' '
            end
      end
   else
      begin
         create table #mondevice
            (seq     tinyint     not null,
             time    datetime    not null,
             totio   int         not null,
             dread   int         null,
             dwrite  int         null,
             sgrant  int         not null,
             swait   int         not null)

         select @maxseq = max (seq)
         from ##database.dbo.umonhistsummary

         select @lastseq = seq
         from ##database.dbo.umonhistsummary
         where end_time = (select max (end_time)
                           from ##database.dbo.umonhistsummary
                           where start_time <> end_time)

         if (@lastseq > @INTERVALS)
            begin
               insert into #mondevice
                  select distinct seq, getdate (), sum (value), 0, 0, 0, 0
                  from ##database.dbo.umonhistory
                  where seq + @INTERVALS    > @lastseq
                    and seq                <= @lastseq
                    and group_name       like 'disk_%'
                    and field_name         in ('total_reads', 'total_writes')
                  group by seq
                  having seq + @INTERVALS    > @lastseq
                     and seq                <= @lastseq
                     and group_name       like 'disk_%'
                     and field_name         in ('total_reads', 'total_writes')
            end
         else
            begin
               insert into #mondevice
                  select distinct seq, getdate (), sum (value), 0, 0, 0, 0
                  from ##database.dbo.umonhistory
                  where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
                    and group_name like 'disk_%'
                    and field_name   in ('total_reads', 'total_writes')
                  group by seq
                  having ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
                     and group_name like 'disk_%'
                     and field_name   in ('total_reads', 'total_writes')
            end

         update #mondevice
         set time = uhs.end_time
         from #mondevice m,
              ##database.dbo.umonhistsummary uhs
         where m.seq = uhs.seq

         update #mondevice
         set dread  = (select isnull (value, 0)
                       from ##database.dbo.umonhistory d
                       where m.seq        = d.seq
                         and d.group_name = @phyname
                         and d.field_name = 'total_reads'),
             dwrite = (select isnull (value, 0)
                       from ##database.dbo.umonhistory d
                       where m.seq        = d.seq
                         and d.group_name = @phyname
                         and d.field_name = 'total_writes'),
             sgrant = (select isnull (value, 0)
                       from ##database.dbo.umonhistory d
                       where m.seq        = d.seq
                         and d.group_name = @phyname
                         and d.field_name = 'p_hits'),
             swait  = (select isnull (value, 0)
                       from ##database.dbo.umonhistory d
                       where m.seq        = d.seq
                         and d.group_name = @phyname
                         and d.field_name = 'p_misses')
         from #mondevice m

         print '                                 <--SEMAPHORE-->'

         select SEQ       = seq,
                TIME      = substring (convert (char (8), time, 8), 1, 5),
                READS     = substring (convert (char (10), convert (money, dread), 1), 1, 7),
                WRITES    = substring (convert (char (10), convert (money, dwrite), 1), 1, 7),
                'I/O%'    = str ((100.0 * (dread + dwrite)) / totio, 5, 1),
                'GRANT'   = substring (convert (char (10), convert (money, sgrant), 1), 1, 7),
                '   WAIT' = substring (convert (char (10), convert (money, swait), 1), 1, 7)
         from #mondevice m
         order by time
      end
go
sp_addcomment sp_mondevice, 18, 'Lists monitor I/O info for DEVICE for last INTERVALS'
go
sp_procxmode sp_mondevice, anymode
go

create procedure sp_mondeviceagg (@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

   print ' '

   -- DETERMINE DEVICES DEDICATED TO LOGS.

   select phyname = 'disk_' + convert (varchar (4), d.low / 16777216),
          low     = d.low,
          high    = d.high
   into #mdalog
   from master.dbo.sysdevices d
   where d.cntrltype      = 0
     and (d.status & 16) <> 16
     and exists (select u.dbid
                 from master.dbo.sysusages  u
                 where d.low  <= u.vstart + u.size
                   and d.high >= u.vstart + u.size - 1)

   delete #mdalog
   from #mdalog l
   where exists (select u.dbid
                 from master.dbo.sysusages  u
                 where l.low          <= u.vstart + u.size
                   and l.high         >= u.vstart + u.size - 1
                   and (u.segmap & 7) <> 4)

   -- DETERMINE DEVICES DEDICATED TO tempdb.  THIS ASSUMES THAT tempdb (AND
   -- ONLY tempdb) IS ON DEVICES like '%tempdb%'.

   select phyname = 'disk_' + convert (varchar (4), low / 16777216)
   into #mdatempdb
   from master.dbo.sysdevices
   where name          like '%tempdb%'
     and cntrltype        = 0
     and (status & 16)   <> 16

   -- IF THERE IS A tempdb LOG ITS ACTIVITY WILL BE CONSIDERED AS A LOG
   -- ACTIVITY.

   delete #mdatempdb
   from #mdatempdb t
   where exists (select l.phyname
                 from #mdalog l
                 where l.phyname = t.phyname)

   create table #mondevice
      (seq         tinyint       not null,
       group_name  varchar (25)  not null,
       field_name  varchar (35)  not null,
       field_id    smallint      not null,
       value       int           not null)

   create table #mondevagg
      (seq         tinyint       not null,
       end_time    datetime      not null,
       dread       int           not null,
       tmpread     int           not null,
       logread     int           not null,
       dwrite      int           not null,
       tmpwrite    int           not null,
       logwrite    int           not null,
       sgrant      int           not null,
       swait       int           not null,
       iodelay     int           not null)

   select @maxseq = max (seq)
   from ##database.dbo.umonhistsummary

   select @lastseq = seq
   from ##database.dbo.umonhistsummary
   where end_time = (select max (end_time)
                     from ##database.dbo.umonhistsummary
                     where start_time <> end_time)

   if (@lastseq > @INTERVALS)
      begin
         insert into #mondevice
            select *
            from ##database.dbo.umonhistory
            where seq + @INTERVALS    > @lastseq
              and seq                <= @lastseq
              and group_name       like 'disk_%'
      end
   else
      begin
         insert into #mondevice
            select *
            from ##database.dbo.umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name like 'disk_%'
      end

   insert into #mondevagg
      select distinct seq, getdate (), sum (value), 0, 0, 0, 0, 0, 0, 0, 0
      from #mondevice
      where field_name = 'total_reads'
      group by seq
      having field_name = 'total_reads'

   update #mondevagg
   set end_time = uhs.end_time
   from #mondevagg m,
        ##database.dbo.umonhistsummary uhs
   where m.seq = uhs.seq

   update #mondevagg
   set dwrite   = (select isnull (sum (d.value), 0)
                   from #mondevice d
                   where m.seq        = d.seq
                     and d.field_name = 'total_writes'),
       sgrant   = (select isnull (sum (d.value), 0)
                   from #mondevice d
                   where m.seq        = d.seq
                     and d.field_name = 'p_hits'),
       swait    = (select isnull (sum (d.value), 0)
                   from #mondevice d
                   where m.seq        = d.seq
                     and d.field_name = 'p_misses')
   from #mondevagg m

   update #mondevagg
   set tmpread  = (select isnull (sum (d.value), 0)
                   from #mondevice d,
                        #mdatempdb t
                   where m.seq        = d.seq
                     and d.group_name = t.phyname
                     and d.field_name = 'total_reads'),
       tmpwrite = (select isnull (sum (d.value), 0)
                   from #mondevice d,
                        #mdatempdb t
                   where m.seq        = d.seq
                     and d.group_name = t.phyname
                     and d.field_name = 'total_writes')
   from #mondevagg m

   update #mondevagg
   set logread  = (select isnull (sum (d.value), 0)
                   from #mondevice d,
                        #mdalog l
                   where m.seq        = d.seq
                     and d.group_name = l.phyname
                     and d.field_name = 'total_reads'),
       logwrite = (select isnull (sum (d.value), 0)
                   from #mondevice d,
                        #mdalog l
                   where m.seq        = d.seq
                     and d.group_name = l.phyname
                     and d.field_name = 'total_writes')
   from #mondevagg m

   update #mondevagg
   set dwrite = dwrite - tmpwrite - logwrite

   update #mondevagg
   set iodelay = (select d.value
                  from ##database.dbo.umonhistory d
                  where m.seq        = d.seq
                    and d.group_name = 'kernel'
                    and d.field_name = 'udalloc_sleeps')
   from #mondevagg m

   update #mondevagg
   set iodelay = (select m.iodelay + sum (d.value)
                  from ##database.dbo.umonhistory d
                  where m.seq           = d.seq
                    and d.group_name like 'engine_%'
                    and d.field_name like 'AIOs_delayed_due_to_%_limit')
   from #mondevagg m

   print '     <------- READS -------> <------ WRITES -------> <--SEMAPHORE-->   I/O'

   select SEQ       = seq,
          'DATA '   = substring (convert (char (10), convert (money, dread), 1), 1, 7),
          'TEMPDB ' = substring (convert (char (10), convert (money, tmpread), 1), 1, 7),
          'LOG '    = substring (convert (char (10), convert (money, logread), 1), 1, 7),
          DATA      = substring (convert (char (10), convert (money, dwrite), 1), 1, 7),
          TEMPDB    = substring (convert (char (10), convert (money, tmpwrite), 1), 1, 7),
          LOG       = substring (convert (char (10), convert (money, logwrite), 1), 1, 7),
          'GRANT'   = substring (convert (char (10), convert (money, sgrant), 1), 1, 7),
          '   WAIT' = substring (convert (char (10), convert (money, swait), 1), 1, 7),
          ' DELAY'  = substring (convert (char (10), convert (money, iodelay), 1), 1, 7)
   from #mondevagg m
   order by end_time
go
sp_addcomment sp_mondeviceagg, 18, 'Lists aggregate monitor I/O info for last INTERVALS'
go
sp_procxmode sp_mondeviceagg, anymode
go

create procedure sp_monindex (@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

   print ' '

   select @maxseq = max (seq)
   from ##database.dbo.umonhistsummary

   select @lastseq = seq
   from ##database.dbo.umonhistsummary
   where end_time = (select max (end_time)
                     from ##database.dbo.umonhistsummary
                     where start_time <> end_time)

   create table #monindex
      (seq          tinyint           not null,
       time         datetime          not null,
       del_op       int               not null,
       del_pg       int               not null,
       del_nc       double precision  not null,
       insupd_op    int               not null,
       insupd_pg    int               not null,
       insupd_nc    double precision  not null,
       rid_pg       int               not null,
       rid_row      int               not null,
--     iscans       int               not null,
       split        int               not null,
       shrink       int               not null)

   if (@lastseq > @INTERVALS)
      begin
         insert into #monindex
            select distinct seq, getdate (), -1, -1, -1, -1, -1, -1, -1, -1, -1, sum (value)
            from ##database.dbo.umonhistory
            where seq + @INTERVALS    > @lastseq
              and seq                <= @lastseq
              and group_name          = 'access'
              and field_name       like 'shrink%'
            group by seq
            having seq + @INTERVALS    > @lastseq
               and seq                <= @lastseq
               and group_name          = 'access'
               and field_name       like 'shrink%'
      end
   else
      begin
         insert into #monindex
            select distinct seq, getdate (), -1, -1, -1, -1, -1, -1, -1, -1, -1, sum (value)
            from ##database.dbo.umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name    = 'access'
              and field_name like 'shrink%'
            group by seq
            having ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
               and group_name    = 'access'
               and field_name like 'shrink%'
      end

   update #monindex
   set time = mh.end_time
   from #monindex mi,
        ##database.dbo.umonhistsummary mh
   where mh.seq = mi.seq

   update #monindex
   set del_pg    = (select sum (d.value)
                    from ##database.dbo.umonhistory d
                    where m.seq         = d.seq
                      and d.group_name  = 'access'
                      and d.field_name in ('delete', 'delete_deferred')),
       insupd_pg = (select sum (d.value)
                    from ##database.dbo.umonhistory d
                    where m.seq         = d.seq
                      and d.group_name  = 'access'
                      and d.field_name in ('ncnsert', 'cinsert', 'deferred_update',
                                           'direct_inplace_update', 'direct_notinplace_update',
                                           'direct_expensive_update'))
   from #monindex m

   update #monindex
   set del_op    = (select d.value
                    from ##database.dbo.umonhistory d
                    where m.seq        = d.seq
                      and d.group_name = 'access'
                      and d.field_name = 'ncdelete'),
       del_nc    = (select d.value
                    from ##database.dbo.umonhistory d
                    where m.seq        = d.seq
                      and d.group_name = 'access'
                      and d.field_name = 'ncdelete_indexes'),
       insupd_op = (select d.value
                    from ##database.dbo.umonhistory d
                    where m.seq        = d.seq
                      and d.group_name = 'access'
                      and d.field_name = 'ncupdate'),
       insupd_nc = (select d.value
                    from ##database.dbo.umonhistory d
                    where m.seq        = d.seq
                      and d.group_name = 'access'
                      and d.field_name = 'ncupdate_indexes'),
       rid_pg    = (select d.value
                    from ##database.dbo.umonhistory d
                    where m.seq        = d.seq
                      and d.group_name = 'access'
                      and d.field_name = 'ncrid_update'),
       rid_row   = (select d.value
                    from ##database.dbo.umonhistory d
                    where m.seq        = d.seq
                      and d.group_name = 'access'
                      and d.field_name = 'ncrid_update_indexes'),
       split     = (select sum (d.value)
                    from ##database.dbo.umonhistory d
                    where m.seq         = d.seq
                      and d.group_name  = 'access'
                      and d.field_name in ('split_index', 'split_root'))

   from #monindex m

   -- ELIMINATE ZERODIVIDES HERE INSTEAD OF (UGH) IN THE SELECT.

   update #monindex
   set del_pg = del_pg / del_op,
       del_nc = (100 * del_nc) / del_op
   where del_op <> 0

   update #monindex
   set insupd_pg = insupd_pg / insupd_op,
       insupd_nc = (100 * insupd_nc) / insupd_op
   where insupd_op <> 0

   print '     <------ DELETE -------> <--- INSERT/UPDATE ---> <- PAGES -> <-ROW ID ->'

   select SEQ         = seq,
          OPS         = substring (convert (char (10),
                                            convert (money, del_op),
                                            1),
                                   1,
                                   7),
          'PGS/OP'    = substring (convert (char (12),
                                            convert (money, del_pg),
                                            1),
                                   1,
                                   9),
          'NCI%'      = str (del_nc, 5, 1),
          'OPS '      = substring (convert (char (10),
                                            convert (money, insupd_op),
                                            1),
                                   1,
                                   7),
          'PGS/OP '   = substring (convert (char (12),
                                            convert (money, insupd_pg),
                                            1),
                                   1,
                                   9),
          'NCI% '     = str (insupd_nc, 5, 1),
          SPLIT       = substring (convert (char (8),
                                            convert (money, split),
                                            1),
                                   1,
                                   5),
          MERGE       = substring (convert (char (8),
                                            convert (money, shrink),
                                            1),
                                   1,
                                   5),
          PAGES       = substring (convert (char (8),
                                            convert (money, rid_pg),
                                            1),
                                   1,
                                   5),
          'ROWS'      = substring (convert (char (8),
                                            convert (money, rid_row),
                                            1),
                                   1,
                                   5)
   from #monindex
   order by time
go
sp_addcomment sp_monindex, 18, 'Lists monitor Index Maintenance info for last INTERVALS'
go
sp_procxmode sp_monindex, anymode
go

create procedure sp_monlock (@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

   print ' '

   select @maxseq = max (seq)
   from ##database.dbo.umonhistsummary

   select @lastseq = seq
   from ##database.dbo.umonhistsummary
   where end_time = (select max (end_time)
                     from ##database.dbo.umonhistsummary
                     where start_time <> end_time)

   create table #monlock
      (seq          tinyint  not null,
       requests     int      not null,
       waits        int      not null,
       promoEX      int      not null,
       promoSH      int      not null,
       promo_fails  int      not null,
       ddlks        int      not null,
       ddlk_srchs   int      not null)

   if (@lastseq > @INTERVALS)
      begin
         insert into #monlock
            select seq, value, -1, -1, -1, -1, -2, -1
            from ##database.dbo.umonhistory
            where seq + @INTERVALS  > @lastseq
              and seq              <= @lastseq
              and group_name        = 'lock'
              and (field_name like 'granted%'  or  field_name like 'waited%')
      end
   else
      begin
         insert into #monlock
            select seq, value, -1, -1, -1, -1, -2, -1
            from ##database.dbo.umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name     = 'lock'
              and (field_name like 'granted%'  or  field_name like 'waited%')
      end

   insert into #monlock
      select distinct seq, sum (requests), -1, -1, -1, -1, -1, -1
      from #monlock
      group by seq

   delete from #monlock
   where ddlks = -2

   update #monlock
   set waits       = (select sum (d.value)
                      from ##database.dbo.umonhistory d
                      where m.seq           = d.seq
                        and d.group_name    = 'lock'
                        and d.field_name like 'waited%'),
       promoEX     = (select sum (d.value)
                      from ##database.dbo.umonhistory d
                      where m.seq         = d.seq
                        and d.group_name  = 'lock'
                        and d.field_name in ('promoted_EX_PAGE', 'promoted_UP_PAGE')),
       promoSH     = (select d.value
                      from ##database.dbo.umonhistory d
                      where m.seq        = d.seq
                        and d.group_name = 'lock'
                        and d.field_name = 'promoted_SH_PAGE'),
       promo_fails = (select d.value
                      from ##database.dbo.umonhistory d
                      where m.seq        = d.seq
                        and d.group_name = 'lock'
                        and d.field_name = 'promotefailed'),
       ddlks       = (select d.value
                      from ##database.dbo.umonhistory d
                      where m.seq        = d.seq
                        and d.group_name = 'lock'
                        and d.field_name = 'deadlocks'),
       ddlk_srchs  = (select d.value
                      from ##database.dbo.umonhistory d
                      where m.seq        = d.seq
                        and d.group_name = 'lock'
                        and d.field_name = 'deadlock_search')
   from #monlock m

   print '           <----- LOCK ------> <-------- PROMOTES --------> <DEADLOCKS>'

   select SEQ       = uhs.seq,
          TIME      = substring (convert (char (8), uhs.end_time, 8), 1, 5),
          REQUESTS  = substring (convert (char (14),
                                          convert (money, ml.requests),
                                          1),
                                 1,
                                 11),
          WAITS     = substring (convert (char (10),
                                          convert (money, ml.waits),
                                          1),
                                 1,
                                 7),
          EXCLUSIVE = substring (convert (char (12),
                                          convert (money, ml.promoEX),
                                          1),
                                 1,
                                 9),
          'SHARED'  = substring (convert (char (12),
                                          convert (money, ml.promoSH),
                                          1),
                                 1,
                                 9),
          FAILED    = substring (convert (char (11),
                                          convert (money, ml.promo_fails),
                                          1),
                                 1,
                                 8),
          FOUND     = substring (convert (char (8),
                                          convert (money, ml.ddlks),
                                          1),
                                 1,
                                 5),
          SRCHD     = substring (convert (char (8),
                                          convert (money, ml.ddlk_srchs),
                                          1),
                                 1,
                                 5)
   from ##database.dbo.umonhistsummary uhs,
        #monlock ml
   where ml.seq = uhs.seq
   order by uhs.end_time
go
sp_addcomment sp_monlock, 18, 'Lists monitor Lock info for last INTERVALS'
go
sp_procxmode sp_monlock, anymode
go

create procedure sp_monmode (@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

   print ' '

   select @maxseq = max (seq)
   from ##database.dbo.umonhistsummary

   select @lastseq = seq
   from ##database.dbo.umonhistsummary
   where end_time = (select max (end_time)
                     from ##database.dbo.umonhistsummary
                     where start_time <> end_time)

   create table #monmode
      (seq    tinyint     not null,
       name   char (35)   not null,
       value  int         null)

   if (@lastseq > @INTERVALS)
      begin
         insert into #monmode
            select seq, 'del direct', value
            from ##database.dbo.umonhistory
            where seq + @INTERVALS  > @lastseq
              and seq              <= @lastseq
              and group_name        = 'access'
              and field_name        = 'delete'

         insert into #monmode
            select seq, 'del deferred', value
            from ##database.dbo.umonhistory
            where seq + @INTERVALS  > @lastseq
              and seq              <= @lastseq
              and group_name        = 'access'
              and field_name        = 'delete_deferred'

         insert into #monmode
            select seq, 'ins heap', value
            from ##database.dbo.umonhistory
            where seq + @INTERVALS  > @lastseq
              and seq              <= @lastseq
              and group_name        = 'access'
              and field_name        = 'ncinsert'

         insert into #monmode
            select seq, 'ins clustered', value
            from ##database.dbo.umonhistory
            where seq + @INTERVALS  > @lastseq
              and seq              <= @lastseq
              and group_name        = 'access'
              and field_name        = 'cinsert'

         insert into #monmode
            select seq, 'upd deferred', value
            from ##database.dbo.umonhistory
            where seq + @INTERVALS  > @lastseq
              and seq              <= @lastseq
              and group_name        = 'access'
              and field_name        = 'deferred_update'

         insert into #monmode
            select seq, 'upd inplace', value
            from ##database.dbo.umonhistory
            where seq + @INTERVALS  > @lastseq
              and seq              <= @lastseq
              and group_name        = 'access'
              and field_name        = 'direct_inplace_update'

         insert into #monmode
            select seq, 'upd cheap', value
            from ##database.dbo.umonhistory
            where seq + @INTERVALS  > @lastseq
              and seq              <= @lastseq
              and group_name        = 'access'
              and field_name        = 'direct_notinplace_update'

         insert into #monmode
            select seq, 'upd expensive', value
            from ##database.dbo.umonhistory
            where seq + @INTERVALS  > @lastseq
              and seq              <= @lastseq
              and group_name        = 'access'
              and field_name        = 'direct_expensive_update'
      end
   else
      begin
         insert into #monmode
            select seq, 'del direct', value
            from ##database.dbo.umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name  = 'access'
              and field_name  = 'delete'

         insert into #monmode
            select seq, 'del deferred', value
            from ##database.dbo.umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name  = 'access'
              and field_name  = 'delete_deferred'

         insert into #monmode
            select seq, 'ins heap', value
            from ##database.dbo.umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name  = 'access'
              and field_name  = 'ncinsert'

         insert into #monmode
            select seq, 'ins clustered', value
            from ##database.dbo.umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name  = 'access'
              and field_name  = 'cinsert'

         insert into #monmode
            select seq, 'upd deferred', value
            from ##database.dbo.umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name  = 'access'
              and field_name  = 'deferred_update'

         insert into #monmode
            select seq, 'upd inplace', value
            from ##database.dbo.umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name  = 'access'
              and field_name  = 'direct_inplace_update'

         insert into #monmode
            select seq, 'upd cheap', value
            from ##database.dbo.umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name  = 'access'
              and field_name  = 'direct_notinplace_update'

         insert into #monmode
            select seq, 'upd expensive', value
            from ##database.dbo.umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name  = 'access'
              and field_name  = 'direct_expensive_update'
      end

   update #monmode
   set value = m1.value - m2.value
   from #monmode m1, #monmode m2
   where m1.seq  = m2.seq
     and m1.name = 'del direct'
     and m2.name = 'del deferred'

   if (exists (select *
               from #monmode
               where value > 1000000))
      begin
         update #monmode
         set value = value /1000

         print '    THE FOLLOWING VALUES ARE IN UNITS OF 1,000.'

         print ' '
      end

   print '     <---DELETES---> <---INSERTS---> <-----------UPDATES----------->'

   select SEQ       = uhs.seq,
          DIRECT    = substring (convert (char (10),
                                          convert (money, abs (ddir.value)),
                                          1),
                                 1,
                                 7),
          DEFERRD   = substring (convert (char (10),
                                          convert (money, abs (ddef.value)),
                                          1),
                                 1,
                                 7),
          HEAP      = substring (convert (char (10),
                                          convert (money, abs (ihp.value)),
                                          1),
                                 1,
                                 7),
          CLUSTER   = substring (convert (char (10),
                                          convert (money, abs (iclst.value)),
                                          1),
                                 1,
                                 7),
          INPLACE   = substring (convert (char (10),
                                          convert (money, abs (uip.value)),
                                          1),
                                 1,
                                 7),
          CHEAP     = substring (convert (char (10),
                                          convert (money, abs (uc.value)),
                                          1),
                                 1,
                                 7),
          EXPENSV   = substring (convert (char (10),
                                          convert (money, abs (ue.value)),
                                          1),
                                 1,
                                 7),
          ' DEFERR' = substring (convert (char (10),
                                          convert (money, abs (udef.value)),
                                          1),
                               1,
                               7)
   from ##database.dbo.umonhistsummary uhs,
        #monmode ddir,
        #monmode ddef,
        #monmode ihp,
        #monmode iclst,
        #monmode uip,
        #monmode uc,
        #monmode ue,
        #monmode udef
   where ddir.seq   = uhs.seq
     and ddir.name  = 'del direct'
     and ddef.seq   = ddir.seq
     and ddef.name  = 'del deferred'
     and ihp.seq    = ddir.seq
     and ihp.name   = 'ins heap'
     and iclst.seq  = ddir.seq
     and iclst.name = 'ins clustered'
     and uip.seq    = ddir.seq
     and uip.name   = 'upd inplace'
     and uc.seq     = ddir.seq
     and uc.name    = 'upd cheap'
     and ue.seq     = ddir.seq
     and ue.name    = 'upd expensive'
     and udef.seq   = ddir.seq
     and udef.name  = 'upd deferred'
   order by uhs.end_time
go
sp_addcomment sp_monmode, 18, 'Lists monitor Transaction Mode info for last INTERVALS'
go
sp_procxmode sp_monmode, anymode
go

create procedure sp_monnetwork (@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 @engine_ignore  varchar (9),
           @lastseq        smallint,
           @maxseq         smallint,
           @unit           char (2)

   print ' '

   create table #engines
      (maxengine       tinyint      not null,
       maxengine_char  varchar (2)  not null)

   create table #monnetwork
      (seq         tinyint   not null,
       time        datetime  not null,
       requests    int       not null,
       delays      int       not null,
       rcvd_bytes  int       not null,
       rcvd_tds    int       not null,
       sent_bytes  int       not null,
       sent_tds    int       not null,
       min_tds     int       not null,
       max_tds     int       not null)

   select @maxseq = max (seq)
   from ##database.dbo.umonhistsummary

   select @lastseq = seq
   from ##database.dbo.umonhistsummary
   where end_time = (select max (end_time)
                     from ##database.dbo.umonhistsummary
                     where start_time <> end_time)

   if (@lastseq > @INTERVALS)
      begin
         insert into #engines
            select max (convert (tinyint, (substring (group_name, 8, 2)))), '  '
            from ##database.dbo.umonhistory
            where seq + @INTERVALS    > @lastseq
              and seq                <= @lastseq
              and group_name       like 'engine_%'
            group by seq
            having seq + @INTERVALS    > @lastseq
               and seq                <= @lastseq
               and group_name       like 'engine_%'

         insert into #monnetwork
            select distinct seq, getdate (), value, 0, 0, 0, 0, 0, 0, 0
            from ##database.dbo.umonhistory
            where seq + @INTERVALS  > @lastseq
              and seq              <= @lastseq
              and group_name        = 'kernel'
              and field_name        = 'ksalloc_calls'
      end
   else
      begin
         insert into #engines
            select max (convert (tinyint, (substring (group_name, 8, 2)))), '  '
            from ##database.dbo.umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name like 'engine_%'
            group by seq
            having ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
               and group_name like 'engine_%'

         insert into #monnetwork
            select distinct seq, getdate (), value, 0, 0, 0, 0, 0, 0, 0
            from ##database.dbo.umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name = 'kernel'
              and field_name = 'ksalloc_calls'
      end

   if (1 < (select count (distinct maxengine)
            from #engines))
      begin
         print ' '

         print '    THIS REPORT MAY BE INACCURATE OWING TO CHANGES IN THE MAX NUMBER OF ENGINES!'

         print ' '
      end

   update #engines
   set maxengine_char = convert (varchar (2), maxengine)

   select @engine_ignore = 'engine_'  +  maxengine_char
   from #engines
   where maxengine = (select max (maxengine)
                      from #engines)

   drop table #engines

   update #monnetwork
   set time = uhs.end_time
   from #monnetwork m,
        ##database.dbo.umonhistsummary uhs
   where m.seq = uhs.seq

   update #monnetwork
   set delays     = (select d.value
                     from ##database.dbo.umonhistory d
                     where m.seq        = d.seq
                       and d.group_name = 'kernel'
                       and d.field_name = 'ksalloc_sleeps'),
       rcvd_bytes = (select sum (d.value)
                     from ##database.dbo.umonhistory d
                     where m.seq           = d.seq
                       and d.group_name like 'engine_%'
                       and d.field_name    = 'no_bytes_received'),
       rcvd_tds   = (select sum (d.value)
                     from ##database.dbo.umonhistory d
                     where m.seq           = d.seq
                       and d.group_name like 'engine_%'
                       and d.field_name    = 'no_packets_received'),
       sent_bytes = (select sum (d.value)
                     from ##database.dbo.umonhistory d
                     where m.seq           = d.seq
                       and d.group_name like 'engine_%'
                       and d.field_name    = 'no_bytes_sent'),
       sent_tds   = (select sum (d.value)
                     from ##database.dbo.umonhistory d
                     where m.seq           = d.seq
                       and d.group_name like 'engine_%'
                       and d.field_name    = 'no_packets_sent'),
       min_tds    = (select min (dr.value + ds.value)
                     from ##database.dbo.umonhistory dr,
                          ##database.dbo.umonhistory ds
                     where m.seq            = dr.seq
                       and dr.group_name like 'engine_%'
                       and dr.group_name   <> @engine_ignore
                       and dr.field_name    = 'no_packets_received'
                       and m.seq            = ds.seq
                       and ds.group_name like 'engine_%'
                       and ds.group_name   <> @engine_ignore
                       and ds.field_name    = 'no_packets_sent'),
       max_tds    = (select max (dr.value + ds.value)
                     from ##database.dbo.umonhistory dr,
                          ##database.dbo.umonhistory ds
                     where m.seq            = dr.seq
                       and dr.group_name like 'engine_%'
                       and dr.field_name    = 'no_packets_received'
                       and m.seq            = ds.seq
                       and ds.group_name like 'engine_%'
                       and ds.field_name    = 'no_packets_sent')
   from #monnetwork m

   update #monnetwork
   set min_tds = (100 * min_tds) / (rcvd_tds + sent_tds),
       max_tds = (100 * max_tds) / (rcvd_tds + sent_tds)
   where (rcvd_tds + sent_tds) <> 0

   update #monnetwork
   set rcvd_bytes = rcvd_bytes / 1024,
       sent_bytes = sent_bytes / 1024

   if (exists (select *
               from #monnetwork
               where rcvd_bytes > 1048576
                  or sent_bytes > 1048576))
      begin
         update #monnetwork
         set rcvd_bytes = rcvd_bytes / 1024,
             sent_bytes = sent_bytes / 1024

         select @unit = 'MB'
      end
   else
      begin
         select @unit = 'KB'
      end

   print '                                                                        TDS'

   print '           <- NETWORK I/O --> <------ %1! -------> <-- TDS PACKETS -->  LOAD%%',
         @unit

   select SEQ             = seq,
          TIME            = substring (convert (char (8), time, 8), 1, 5),
          REQUESTS        = substring (convert (char (13), convert (money, requests), 1), 1, 10),
          DELAYS          = substring (convert (char (10), convert (money, delays), 1), 1, 7),
          RECEIVED        = substring (convert (char (12), convert (money, rcvd_bytes), 1), 1, 9),
          SENT            = substring (convert (char (12), convert (money, sent_bytes), 1), 1, 9),
          'RECEIVED '     = substring (convert (char (12), convert (money, rcvd_tds), 1), 1, 9),
          'SENT '         = substring (convert (char (12), convert (money, sent_tds), 1), 1, 9),
          'MIN-MAX'       = substring (space (6 - char_length (convert (varchar (3), min_tds)) - char_length (convert (varchar (3), max_tds)))
                                       +  convert (varchar (3), min_tds)
                                       +  '-'
                                       +  convert (varchar (3), max_tds),
                                       1,
                                       7)
   from #monnetwork m
   order by time
go
sp_addcomment sp_monnetwork, 18, 'Lists monitor Network I/O info for last INTERVALS'
go
sp_procxmode sp_monnetwork, anymode
go

create procedure sp_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 ##database.dbo.umonhistsummary

   select @lastseq = seq
   from ##database.dbo.umonhistsummary
   where end_time = (select max (end_time)
                     from ##database.dbo.umonhistsummary
                     where start_time <> end_time)

   print ' '

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

   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),
          PRMOT     = str (auxdp2, 5, 1),
          'GRANT'   = str (auxdp1, 5, 1)
   from ##database.dbo.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)))))
   order by end_time
go
sp_addcomment sp_monsummary, 18, 'Lists monitor Summary info for last INTERVALS'
go
sp_procxmode sp_monsummary, anymode
go

create procedure sp_monulc (@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

   print ' '

   select @maxseq = max (seq)
   from ##database.dbo.umonhistsummary

   select @lastseq = seq
   from ##database.dbo.umonhistsummary
   where end_time = (select max (end_time)
                     from ##database.dbo.umonhistsummary
                     where start_time <> end_time)

   create table #ulc
      (seq      tinyint           not null,
       time     datetime          not null,
       tot      int               not null,
       full     int               not null,
       ex       int               not null,
       dbc      int               not null,
       slr      int               not null,
       oth      int               not null,
       semulc   int               not null,
       semlog   int               not null,
       maxsz    smallint          not null)

   if (@lastseq > @INTERVALS)
      begin
         insert into #ulc
            select distinct seq, getdate (), sum (value), 0, 0, 0, 0, 0, 0, 0, 0
            from ##database.dbo.umonhistory
            where seq + @INTERVALS    > @lastseq
              and seq                <= @lastseq
              and group_name          = 'xls'
              and field_name       like 'plc_flush_%'
            group by seq
            having seq + @INTERVALS    > @lastseq
               and seq                <= @lastseq
               and group_name          = 'xls'
               and field_name       like 'plc_flush_%'
      end
   else
      begin
         insert into #ulc
            select distinct seq, getdate (), sum (value), 0, 0, 0, 0, 0, 0, 0, 0
            from ##database.dbo.umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name    = 'xls'
              and field_name like 'plc_flush_%'
            group by seq
            having ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
               and group_name    = 'xls'
               and field_name like 'plc_flush_%'
      end

   update #ulc
   set time = uhs.end_time
   from #ulc u,
        ##database.dbo.umonhistsummary uhs
   where u.seq = uhs.seq

   update #ulc
   set full    = (select d.value
                  from ##database.dbo.umonhistory d
                  where u.seq        = d.seq
                    and d.group_name = 'xls'
                    and d.field_name = 'plc_flush_full'),
       ex      = (select d.value
                  from ##database.dbo.umonhistory d
                  where u.seq        = d.seq
                    and d.group_name = 'xls'
                    and d.field_name = 'plc_flush_endxact'),
       dbc     = (select d.value
                  from ##database.dbo.umonhistory d
                  where u.seq        = d.seq
                    and d.group_name = 'xls'
                    and d.field_name = 'plc_flush_xdeschange'),
       slr     = (select d.value
                  from ##database.dbo.umonhistory d
                  where u.seq        = d.seq
                    and d.group_name = 'xls'
                    and d.field_name = 'plc_flush_slr_xact'),
       oth     = (select sum (d.value)
                  from ##database.dbo.umonhistory d
                  where u.seq         = d.seq
                    and d.group_name  = 'xls'
                    and d.field_name in ('plc_flush_unpin', 'plc_flush_discard',
                                         'plc_flush_pmscan')),
       semulc  = (select d.value
                  from ##database.dbo.umonhistory d
                  where u.seq        = d.seq
                    and d.group_name = 'xls'
                    and d.field_name = 'plc_lock_waits'),
       semlog  = (select d.value
                  from ##database.dbo.umonhistory d
                  where u.seq        = d.seq
                    and d.group_name = 'xls'
                    and d.field_name = 'log_lock_waited'),
       maxsz   = (select d.value
                  from ##database.dbo.umonhistory d
                  where u.seq        = d.seq
                    and d.group_name = 'xls'
                    and d.field_name = 'plc_maxused')
   from #ulc u

   print '     <----------- ULC FLUSHES TO XACT LOG -----------> < SMPHR WAIT -> MAX ULC'

   select SEQ         = seq,
          'FULL ULC ' = substring (convert (char (12), convert (money, full), 1), 1, 9),
          'END XACT ' = substring (convert (char (12), convert (money, ex), 1), 1, 9),
          'DB CHANGE' = substring (convert (char (12), convert (money, dbc), 1), 1, 9),
          'SYS LOG  ' = substring (convert (char (12), convert (money, slr), 1), 1, 9),
          'OTHER    ' = substring (convert (char (12), convert (money, oth), 1), 1, 9),
          ULC         = substring (convert (char (10), convert (money, semulc), 1), 1, 7),
          '    LOG'   = substring (convert (char (10), convert (money, semlog), 1), 1, 7),
          ' SIZE'     = substring (convert (char (10), convert (money, maxsz), 1), 1, 7)
   from #ulc
   order by time
go
sp_addcomment sp_monulc, 18, 'Lists monitor User Log Cache info for last INTERVALS'
go
sp_procxmode sp_monulc, anymode
go

create procedure sp_sysmonseq (@SEQ tinyint) as

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

   set transaction isolation level 3

   declare @BlankLine     char (1),
           @ClrStatTime   datetime,
           @GetStatTime   datetime,
           @NumElapsedMs  int,
           @NumEngines    tinyint,
           @NumXacts      int,
           @RptLine       char (80),
           @SampleMin     smallint,
           @SampleSec     smallint,
           @Section       char (80),
           @Status        int

   set nocount on

   select @Section = '==============================================================================='

   select @BlankLine = ' '

   select group_name, field_name, field_id, value
   into #tempmonitors
   from ##database.dbo.umonhistory
   where seq = @SEQ

   create clustered index #tempmon_ndx on #tempmonitors (group_name, field_name, field_id)

   select @NumEngines = value
   from master.dbo.syscurconfigs
   where config = 126

   select @ClrStatTime = start_time,
          @GetStatTime = end_time
   from ##database.dbo.umonhistsummary
   where seq = @SEQ

   select @NumElapsedMs = datediff (ms, @ClrStatTime, @GetStatTime),
          @SampleMin    = datediff (mi, @ClrStatTime, @GetStatTime)

   select @SampleSec = datediff (ss, @ClrStatTime, @GetStatTime) - (60 * @SampleMin)

   select @NumXacts = value
   from #tempmonitors
   where group_name = 'access'
     and field_name = 'xacts'

   if (@NumXacts = 0)
      begin
         select @NumXacts = 1          /* avoid divide by 0 for all per xact calcs */
      end

   print @Section

   print '              Sybase SQL Server System Performance Report'

   print @Section

   print @BlankLine

   select @RptLine = convert (char (12), @GetStatTime, 107)

   print 'Run Date:           %1!', @RptLine

   select @RptLine = convert (char (8), @ClrStatTime, 108)

   print 'Statistics Cleared:     %1!', @RptLine

   select @RptLine = convert (char (8), @GetStatTime, 108)

   print 'Statistics Sampled:     %1!', @RptLine

   select @RptLine = 'Sample Interval:'
                     + space (7 - char_length (convert (varchar (6), @SampleMin)) - char_length (convert (varchar (6), @SampleSec)))
                     + convert (varchar (6), @SampleMin)
                     + ' minutes, '
                     + convert (varchar (6), @SampleSec)
                     + ' seconds'
   print @RptLine

   print @BlankLine

   print @Section

   exec @Status = sp_sysmon_kernel @NumEngines, @NumElapsedMs, @NumXacts

   if (@Status = 1)
      begin
         return 1
      end

   print @Section

   exec @Status = sp_sysmon_taskmgmt @NumEngines, @NumElapsedMs, @NumXacts

   if (@Status = 1)
      begin
         return 1
      end

   print @Section

   exec @Status = sp_sysmon_xactsum @NumElapsedMs, @NumXacts

   if (@Status = 1)
      begin
         return 1
      end

   print @Section

   exec @Status = sp_sysmon_xactmgmt @NumElapsedMs, @NumXacts

   if (@Status = 1)
      begin
         return 1
      end

   print @Section

   exec @Status = sp_sysmon_index @NumElapsedMs, @NumXacts

   if (@Status = 1)
      begin
         return 1
      end

   print @Section

   exec @Status = sp_sysmon_locks @NumElapsedMs, @NumXacts

   if (@Status = 1)
      begin
         return 1
      end

   print @Section

   exec @Status = sp_sysmon_dcache @NumElapsedMs, @NumXacts

   if (@Status = 1)
      begin
         return 1
      end

   print @Section

   exec @Status = sp_sysmon_pcache @NumElapsedMs, @NumXacts

   if (@Status = 1)
      begin
         return 1
      end

   print @Section

   exec @Status = sp_sysmon_memory @NumElapsedMs, @NumXacts

   if (@Status = 1)
      begin
         return 1
      end

   print @Section

   exec @Status = sp_sysmon_recovery @NumElapsedMs, @NumXacts

   if (@Status = 1)
      begin
         return 1
      end

   print @Section

   exec @Status = sp_sysmon_diskio @NumEngines, @NumElapsedMs, @NumXacts

   if (@Status = 1)
      begin
         return 1
      end

   print @Section

   exec @Status = sp_sysmon_netio @NumEngines, @NumElapsedMs, @NumXacts

   if (@Status = 1)
      begin
         return 1
      end

   print '=============================== End of Report ================================='

   return 0
go
exec sp_procxmode 'sp_sysmonseq', 'anymode'
go
sp_addcomment sp_sysmonseq, 18, 'Lists sp_sysmon data for SEQ'
go
