use sybsystemprocs
go
drop procedure sp_monapfagg
go
drop procedure sp_monapp
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_monmeta
go
drop procedure sp_monmode
go
drop procedure sp_monnetwork
go
drop procedure sp_monulc
go
drop procedure sp_sysmonseq
go


use ##database
go
drop procedure monapfagg
go
drop procedure monapp
go
drop procedure moncache
go
drop procedure moncacheagg
go
drop procedure moncontext
go
drop procedure mondelay
go
drop procedure mondevice
go
drop procedure mondeviceagg
go
drop procedure monindex
go
drop procedure monlock
go
drop procedure monmeta
go
drop procedure monmode
go
drop procedure monnetwork
go
drop procedure monulc
go

set quoted_identifier on
go

create procedure monapfagg (@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 #monaccess
      (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 #monapfagg
      (seq       tinyint     not null,
       time      datetime    not null,
       requested int         not null,
       issued    int         not null,
       denyIO    int         not null,
       denylimit int         not null,
       denyreuse int         not null,
       cached    int         not null,
       cachespun int         not null,
       used      int         not null,
       wait      int         not null,
       discards  int         null)

   select @maxseq = max (seq)
   from umonhistsummary

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

   if (@lastseq > @INTERVALS)
      begin
         insert into #monaccess
            select seq, group_name, field_name, field_id, value
            from umonhistory
            where seq + @INTERVALS  > @lastseq
              and seq              <= @lastseq
              and group_name        = 'access'
              and field_name       in ('apf_IOs_issued',
                                       'apf_could_not_start_IO_immediately',
                                       'apf_configured_limit_exceeded',
                                       'apf_unused_read_penalty',
                                       'apf_found_in_cache_with_spinlock',
                                       'apf_found_in_cache_wo_spinlock')
      end
   else
      begin
         insert into #monaccess
            select seq, group_name, field_name, field_id, value
            from umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name  = 'access'
              and field_name in ('apf_IOs_issued',
                                 'apf_could_not_start_IO_immediately',
                                 'apf_configured_limit_exceeded',
                                 'apf_unused_read_penalty',
                                 'apf_found_in_cache_with_spinlock',
                                 'apf_found_in_cache_wo_spinlock')
      end

   insert into #monapfagg
      select distinct seq, getdate (), sum (value), 0, 0, 0, 0, 0, 0, 0, 0, 0
      from #monaccess
      group by seq

   update #monapfagg
   set issued     = (select sum (d.value)
                     from #monaccess d
                     where m.seq        = d.seq
                       and d.field_name = 'apf_IOs_issued'),
       denyIO     = (select sum (d.value)
                     from #monaccess d
                     where m.seq        = d.seq
                       and d.field_name = 'apf_could_not_start_IO_immediately'),
       denylimit  = (select sum (d.value)
                     from #monaccess d
                     where m.seq        = d.seq
                       and d.field_name = 'apf_configured_limit_exceeded'),
       denyreuse  = (select sum (d.value)
                     from #monaccess d
                     where m.seq         = d.seq
                       and d.field_name = 'apf_unused_read_penalty'),
       cached     = (select sum (d.value)
                     from #monaccess d
                     where m.seq        = d.seq
                       and d.field_name = 'apf_found_in_cache_wo_spinlock'),
       cachespun  = (select sum (d.value)
                     from #monaccess d
                     where m.seq        = d.seq
                       and d.field_name = 'apf_found_in_cache_with_spinlock')
   from #monapfagg m

   update #monapfagg
   set used       = (select d.value
                     from umonhistory d
                     where m.seq        = d.seq
                       and d.field_name = 'apf_IOs_used'),
       wait       = (select d.value
                     from umonhistory d
                     where m.seq        = d.seq
                       and d.field_name = 'apf_waited_for_IO_to_complete'),
       discards   = (select isnull (sum (d.value), 0)
                     from umonhistory d
                     where m.seq           = d.seq
                       and d.field_name like 'apf_%discard%')
   from #monapfagg m

   update #monapfagg
   set time = uhs.end_time
   from #monapfagg m,
        umonhistsummary uhs
   where m.seq = uhs.seq

   if (exists (select *
               from #monapfagg
               where requested > 9999999
                  or issued    > 999999))
      begin
         print '    REQUESTED AND ISSUED VALUES HAVE BEEN DIVIDED BY 1K.'

         print ' '

         update #monapfagg
         set requested = requested / 1024,
             issued    = issued /1024
      end

   if (exists (select *
               from #monapfagg
               where cached    > 9999999
                  or cachespun > 99999))
      begin
         print '    CLEAN AND LOCKED VALUES HAVE BEEN DIVIDED BY 1K.'

         print ' '

         update #monapfagg
         set cached    = cached / 1024,
             cachespun = cachespun /1024
      end

   if (exists (select *
               from #monapfagg
               where denyIO    > 999
                  or denylimit > 9999999
                  or denyreuse > 99999))
      begin
         print '    I/O, LIMIT, AND REUSE VALUES HAVE BEEN DIVIDED BY 1K.'

         print ' '

         update #monapfagg
         set denyIO    = denyIO / 1024,
             denylimit = denylimit /1024,
             denyreuse = denyreuse /1024
      end

   if (exists (select *
               from #monapfagg
               where used     > 99999
                  or wait     > 99999
                  or discards > 9999))
      begin
         print '    USED, WAIT, AND DISCARD VALUES HAVE BEEN DIVIDED BY 1K.'

         print ' '

         update #monapfagg
         set used     = used / 1024,
             wait     = wait /1024,
             discards = discards /1024
      end

   print '                       <--- CACHED ---> <--- DENIED ----> <----- OTHER ----->'

   select SEQ       = seq,
          REQUESTED = substring (convert (char (12),
                                          convert (money, requested),
                                          1),
                                 1,
                                 9),
          ISSUED    = substring (convert (char (10),
                                          convert (money, issued),
                                          1),
                                 1,
                                 7),
          CLEAN     = substring (convert (char (12),
                                          convert (money, cached),
                                          1),
                                 1,
                                 9),
          LOCKED    = substring (convert (char (9),
                                          convert (money, cachespun),
                                          1),
                                 1,
                                 6),
          'I/O'     = substring (convert (char (6),
                                          convert (money, denyIO),
                                          1),
                                 1,
                                 3),
          LIMIT     = substring (convert (char (10),
                                          convert (money, denylimit),
                                          1),
                                 1,
                                 7),
          REUSE     = substring (convert (char (8),
                                          convert (money, denyreuse),
                                          1),
                                 1,
                                 5),
          USED      = substring (convert (char (9),
                                          convert (money, used),
                                          1),
                                 1,
                                 6),
          WAIT      = substring (convert (char (9),
                                          convert (money, wait),
                                          1),
                                 1,
                                 6),
          DSCRD     = substring (convert (char (8),
                                          convert (money, discards),
                                          1),
                                 1,
                                 5)
   from #monapfagg
   order by time
go
sp_procxmode monapfagg, anymode
go

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

   create table #monapp
      (seq          tinyint           not null,
       end_time     datetime          not null,
       priohigh     int               null,
       priomed      int               null,
       priolow      int               null,
       exhhigh      int               null,
       exhmed       int               null,
       exhlow       int               null,
       engskips     int               null,
       scopechngs   int               null)

   select @maxseq = max (seq)
   from umonhistsummary

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

   if (@lastseq > @INTERVALS)
      begin
         insert into #monapp
            select seq, end_time, 0, 0, 0, 0, 0, 0, 0, 0
            from umonhistsummary
            where seq + @INTERVALS  > @lastseq
              and seq              <= @lastseq
      end
   else
      begin
         insert into #monapp
            select seq, end_time, 0, 0, 0, 0, 0, 0, 0, 0
            from umonhistsummary
            where seq              <= @lastseq
               or seq + @INTERVALS  > @maxseq + @lastseq
      end

   update #monapp
   set priohigh   = (select d.value
                     from umonhistory d
                     where m.seq        = d.seq
                       and d.group_name = 'kernel'
                       and d.field_name = 'priority_high'),
       priomed    = (select d.value
                     from umonhistory d
                     where m.seq        = d.seq
                       and d.group_name = 'kernel'
                       and d.field_name = 'priority_med'),
       priolow    = (select d.value
                     from umonhistory d
                     where m.seq        = d.seq
                       and d.group_name = 'kernel'
                       and d.field_name = 'priority_low'),
       exhhigh    = (select d.value
                     from umonhistory d
                     where m.seq        = d.seq
                       and d.group_name = 'kernel'
                       and d.field_name = 'tslice_high'),
       exhmed     = (select d.value
                     from umonhistory d
                     where m.seq        = d.seq
                       and d.group_name = 'kernel'
                       and d.field_name = 'tslice_med'),
       exhlow     = (select d.value
                     from umonhistory d
                     where m.seq        = d.seq
                       and d.group_name = 'kernel'
                       and d.field_name = 'tslice_low'),
       engskips   = (select isnull (sum (d.value), -1)
                     from umonhistory d
                     where m.seq           = d.seq
                       and d.group_name like 'engine_%'
                       and d.field_name    = 'incompatible_engine_task'),
       scopechngs = (select d.value
                     from umonhistory d
                     where m.seq        = d.seq
                       and d.group_name = 'kernel'
                       and d.field_name = 'calls_to_upsetenginemask')
   from #monapp m

   print '           <----- PRIORITY TO ------> <- EXHAUSTED SLICES -->   TASK   SCOPE'

   select SEQ          = seq,
          TIME         = substring (convert (char (8), end_time, 8), 1, 5),
          HIGH         = substring (convert (char (11), convert (money, isnull (priohigh, -1)), 1), 1, 8),
          MEDIUM       = substring (convert (char (11), convert (money, isnull (priomed, -1)), 1), 1, 8),
          LOW          = substring (convert (char (11), convert (money, isnull (priolow, -1)), 1), 1, 8),
          'HIGH '      = substring (convert (char (10), convert (money, isnull (exhhigh, -1)), 1), 1, 7),
          'MEDIUM '    = substring (convert (char (10), convert (money, isnull (exhmed, -1)), 1), 1, 7),
          'LOW '       = substring (convert (char (10), convert (money, isnull (exhlow, -1)), 1), 1, 7),
          ' SKIPS'     = substring (convert (char (10), convert (money, engskips), 1), 1, 7),
          CHANGES      = substring (convert (char (10), convert (money, isnull (scopechngs, -1)), 1), 1, 7)
   from #monapp
   order by end_time
go
sp_procxmode monapp, anymode
go

create procedure 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 umonhistsummary

         select @lastseq = seq
         from umonhistsummary
         where end_time = (select max (end_time)
                           from 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 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 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,
              umonhistsummary uhs
         where m.seq = uhs.seq

         update #moncache
         set hits   = (select d.value
                       from umonhistory d
                       where m.seq        = d.seq
                         and d.group_name = @buffername
                         and d.field_name = 'bufsearch_finds'),
             wash   = (select d.value
                       from 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 umonhistory d
                       where m.seq        = d.seq
                         and d.group_name = @buffername
                         and d.field_name = 'prefetch_req'),
             io_yes = (select d.value
                       from 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 umonhistory d
                       where m.seq        = d.seq
                         and d.group_name = @buffername
                         and d.field_name = 'bufunkeep_mru'),
             buftot = (select sum (d.value)
                       from 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 umonhistory d
                       where m.seq        = d.seq
                         and d.group_name = @buffername
                         and d.field_name = 'bufgrab_'  +  @ps1txt  +  'k'),
             upages = (select d.value
                       from 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 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 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 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 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 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_procxmode moncache, anymode
go

create procedure 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 umonhistsummary

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

   if (@lastseq > @INTERVALS)
      begin
         insert into #monbuffer
            select seq, group_name, field_name, field_id, value
            from 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 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,
        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 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_procxmode moncacheagg, anymode
go

create procedure 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               LLPW: Last Log Page Writes'

   print '    CSM: Cache Search Misses            MC:   Modify Conflicts'

   print '    SDW: System Disk Writes             IODC: I/O Device Contention'

   print '    IOP: I/O Pacing                     NPR:  Network Packet Received'

   print '    LLC: Logical Lock Contention        NPS:  Network Packet Sent'

   print '    ALC: Address Lock Contention        SL:   Sysindexes Lookup'

   print '    LSC: Log Semaphore Contention       OTR:  Other Causes'

   print '    GCS: Group Commit Sleeps'

   print ' '

   select @maxseq = max (seq)
   from umonhistsummary

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

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

   create table #monctxtprnt
      (seq    tinyint           not null,
       cs     double precision  not null,
       vy     double precision  not null,
       csm    double precision  not null,
       sdw    double precision  not null,
       iop    double precision  not null,
       llc    double precision  not null,
       alc    double precision  not null,
       lsc    double precision  not null,
       gcs    double precision  not null,
       llpw   double precision  not null,
       mc     double precision  not null,
       iodc   double precision  not null,
       npr    double precision  not null,
       nps    double precision  not null,
       sl     double precision  not null,
       oc     double precision  not null)

   if (@INTERVALS <= @lastseq)
      begin
         insert into #moncontext
            select distinct seq, 'context switches', sum (value)
            from 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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

   insert into #moncontext
      select seq, 'other causes', value
      from #moncontext
      where name = 'context switches'

   update #moncontext
   set value = (select oc.value - sum (k.value)
                from  #moncontext k
                where k.name <> 'other causes'
                  and k.name <> 'context switches'
                  and oc.seq  = k.seq)
   from #moncontext oc
   where oc.name = 'other causes'

   --  AVOID THE 16 TABLE JOIN LIMIT VIA AN INSERT AND UPDATE.

   insert into #monctxtprnt
      select uhs.seq, cs.value, vy.value, cm.value, sdw.value, iop.value,
             llc.value, alc.value, 0, 0, 0, 0, 0, 0, 0, 0, 0
      from umonhistsummary uhs,
           #moncontext cs,
           #moncontext vy,
           #moncontext cm,
           #moncontext sdw,
           #moncontext iop,
           #moncontext llc,
           #moncontext alc
      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'

   update #monctxtprnt
   set lsc  = (select value
               from #moncontext mc
               where mcp.seq = mc.seq
                 and mc.name = 'log semaphore'),
       gcs  = (select value
               from #moncontext mc
               where mcp.seq = mc.seq
                 and mc.name = 'group commits'),
       llpw = (select value
               from #moncontext mc
               where mcp.seq = mc.seq
                 and mc.name = 'last log page'),
       mc   = (select value
               from #moncontext mc
               where mcp.seq = mc.seq
                 and mc.name = 'modify conflicts'),
       iodc = (select value
               from #moncontext mc
               where mcp.seq = mc.seq
                 and mc.name = 'device contention'),
       npr  = (select value
               from #moncontext mc
               where mcp.seq = mc.seq
                 and mc.name = 'np received'),
       nps  = (select value
               from #moncontext mc
               where mcp.seq = mc.seq
                 and mc.name = 'np sent'),
       oc   = (select value
               from #moncontext mc
               where mcp.seq = mc.seq
                 and mc.name = 'other causes')
   from #monctxtprnt mcp

   select SEQ     = uhs.seq,
          'VY%'   = convert (tinyint, round ((100 * vy)   / cs, 0)),
          'CSM%'  = convert (tinyint, round ((100 * csm)  / cs, 0)),
          'SDW%'  = convert (tinyint, round ((100 * sdw)  / cs, 0)),
          'IOP%'  = convert (tinyint, round ((100 * iop)  / cs, 0)),
          'LLC%'  = convert (tinyint, round ((100 * llc)  / cs, 0)),
          'ALC%'  = convert (tinyint, round ((100 * alc)  / cs, 0)),
          'LSC%'  = convert (tinyint, round ((100 * lsc)  / cs, 0)),
          'GCS%'  = convert (tinyint, round ((100 * gcs)  / cs, 0)),
          'LLPW%' = convert (tinyint, round ((100 * llpw) / cs, 0)),
          'MC%'   = convert (tinyint, round ((100 * mc)   / cs, 0)),
          'IODC%' = convert (tinyint, round ((100 * iodc) / cs, 0)),
          'NPR%'  = convert (tinyint, round ((100 * npr)  / cs, 0)),
          'NPS%'  = convert (tinyint, round ((100 * nps)  / cs, 0)),
          'SL%'   = convert (tinyint, round ((100 * sl)   / cs, 0)),
          'OTR%'  = convert (tinyint, round ((100 * oc)   / cs, 0))
   from umonhistsummary uhs,
        #monctxtprnt mcp
   where mcp.seq  = uhs.seq
   order by uhs.end_time
go
sp_procxmode moncontext, anymode
go

create procedure 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               null,
       diskiodelay  int               null,
       netdelay     int               null,
       semulc       int               null,
       semlog       int               null,
       spinmax1     double precision  null,
       spinmax2     double precision  null,
       spinmax2id   smallint          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 umonhistsummary

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

   --
   --  WHEN BUG # 166130 IS FIXED THE DASHES SHOULD BE REMOVED FROM THE
   --  FOLLOWING IF-ELSE BLOCK.  THE SUBSEQUENT UPDATE OF #MONSPIN TO 0
   --  SHOULD ALSO BE ELIMINATED.
   --

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

         insert into #monspin
            select w.seq, w.field_id, w.value, g.value
            from umonhistory w,
                 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 umonhistsummary
            where seq              <= @lastseq
               or seq + @INTERVALS  > @maxseq + @lastseq

         insert into #monspin
            select w.seq, w.field_id, w.value, g.value
            from umonhistory w,
                 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 #monspin
   set spinw = 0,
       sping = 0

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

   update #mondelay
   set diskwait  = (select sum (d.value)
                    from 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 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 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 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 umonhistory d
                 where m.seq        = d.seq
                   and d.group_name = 'xls'
                   and d.field_name = 'plc_lock_waits'),
       semlog = (select d.value
                 from umonhistory d
                 where m.seq        = d.seq
                   and d.group_name = 'xls'
                   and d.field_name = 'log_lock_waited')
   from #mondelay m

   update #mondelay
   set diskwait = isnull (diskwait, -1)

   update #mondelay
   set diskiodelay = isnull (diskiodelay, -1)

   update #mondelay
   set netdelay = isnull (netdelay, -1)

   update #mondelay
   set semulc = isnull (semulc, -1)

   update #mondelay
   set semlog = isnull (semlog, -1)

   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 (isnull (spinmax1, 0), 4, 1))
                              + ' ' + convert (char (4), str (isnull (spinmax2, 0), 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 (isnull (spinmax2, 0), 10, 2))
         from #mondelay m
         order by end_time
      end
go
sp_procxmode mondelay, anymode
go

create procedure 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 umonhistsummary

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

         if (@lastseq > @INTERVALS)
            begin
               insert into #mondevice
                  select distinct seq, getdate (), sum (value), 0, 0, 0, 0
                  from 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 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,
              umonhistsummary uhs
         where m.seq = uhs.seq

         update #mondevice
         set dread  = (select isnull (value, 0)
                       from umonhistory d
                       where m.seq        = d.seq
                         and d.group_name = @phyname
                         and d.field_name = 'total_reads'),
             dwrite = (select isnull (value, 0)
                       from umonhistory d
                       where m.seq        = d.seq
                         and d.group_name = @phyname
                         and d.field_name = 'total_writes'),
             sgrant = (select isnull (value, 0)
                       from umonhistory d
                       where m.seq        = d.seq
                         and d.group_name = @phyname
                         and d.field_name = 'p_hits'),
             swait  = (select isnull (value, 0)
                       from 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_procxmode mondevice, anymode
go

create procedure 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 umonhistsummary

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

   if (@lastseq > @INTERVALS)
      begin
         insert into #mondevice
            select *
            from umonhistory
            where seq + @INTERVALS    > @lastseq
              and seq                <= @lastseq
              and group_name       like 'disk_%'
      end
   else
      begin
         insert into #mondevice
            select *
            from 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,
        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 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 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_procxmode mondeviceagg, anymode
go

create procedure 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 umonhistsummary

   select @lastseq = seq
   from umonhistsummary
   where end_time = (select max (end_time)
                     from 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 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 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,
        umonhistsummary mh
   where mh.seq = mi.seq

   update #monindex
   set del_pg    = (select sum (d.value)
                    from 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 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 umonhistory d
                    where m.seq        = d.seq
                      and d.group_name = 'access'
                      and d.field_name = 'ncdelete'),
       del_nc    = (select d.value
                    from umonhistory d
                    where m.seq        = d.seq
                      and d.group_name = 'access'
                      and d.field_name = 'ncdelete_indexes'),
       insupd_op = (select d.value
                    from umonhistory d
                    where m.seq        = d.seq
                      and d.group_name = 'access'
                      and d.field_name = 'ncupdate'),
       insupd_nc = (select d.value
                    from umonhistory d
                    where m.seq        = d.seq
                      and d.group_name = 'access'
                      and d.field_name = 'ncupdate_indexes'),
       rid_pg    = (select d.value
                    from umonhistory d
                    where m.seq        = d.seq
                      and d.group_name = 'access'
                      and d.field_name = 'ncrid_update'),
       rid_row   = (select d.value
                    from 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 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 (9),
                                            convert (money, rid_pg),
                                            1),
                                   1,
                                   6),
          'ROWS'      = substring (convert (char (9),
                                            convert (money, rid_row),
                                            1),
                                   1,
                                   6)
   from #monindex
   order by time
go
sp_procxmode monindex, anymode
go

create procedure 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 umonhistsummary

   select @lastseq = seq
   from umonhistsummary
   where end_time = (select max (end_time)
                     from 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 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 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 umonhistory d
                      where m.seq           = d.seq
                        and d.group_name    = 'lock'
                        and d.field_name like 'waited%'),
       promoEX     = (select sum (d.value)
                      from 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 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 umonhistory d
                      where m.seq        = d.seq
                        and d.group_name = 'lock'
                        and d.field_name = 'promotefailed'),
       ddlks       = (select d.value
                      from umonhistory d
                      where m.seq        = d.seq
                        and d.group_name = 'lock'
                        and d.field_name = 'deadlocks'),
       ddlk_srchs  = (select d.value
                      from 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 umonhistsummary uhs,
        #monlock ml
   where ml.seq = uhs.seq
   order by uhs.end_time
go
sp_procxmode monlock, anymode
go

create procedure monmeta (@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
           @maxdb          smallint,              -- "number of open databases"
           @maxindex       int,                   -- "number of open indexes"
           @maxobject      int,                   -- "number of open objects"
           @maxseq         smallint,              -- maximum sequence value
           @mcstring       varchar (20),          -- string for group_name
           @spinpstring    varchar (12),          -- string for field_name
           @spinwstring    varchar (12)           -- string for field_name

   print ' '

   create table #monmeta
      (seq           tinyint           not null,
       time          datetime          not null,
       fail          int               not null,
       dbfree        int               not null,
       dbreuse       int               not null,
       objectfree    int               not null,
       objectreuse   int               not null,
       objectspinw   double precision  not null,
       objectspinp   int               not null,
       indexfree     int               not null,
       indexreuse    int               not null,
       indexspinw    double precision  not null,
       indexspinp    int               not null,
       hashspinw     double precision  not null,
       hashspinp     int               not null)

   select @maxdb = r.value
   from master.dbo.sysconfigures c,
        master.dbo.syscurconfigs r
   where c.name   = 'number of open databases'
     and r.config = c.config

   select @maxobject = r.value
   from master.dbo.sysconfigures c,
        master.dbo.syscurconfigs r
   where c.name   = 'number of open objects'
     and r.config = c.config

   select @maxindex = r.value
   from master.dbo.sysconfigures c,
        master.dbo.syscurconfigs r
   where c.name   = 'number of open indexes'
     and r.config = c.config

   select @maxseq      = max (seq),
          @mcstring    = 'metadata_cache_stats',
          @spinpstring = 'spinlock_p_%',
          @spinwstring = 'spinlock_w_%'
   from umonhistsummary

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

   if (@lastseq > @INTERVALS)
      begin
         insert into #monmeta
            select distinct seq, getdate (), sum (value), 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
            from umonhistory
            where seq + @INTERVALS  > @lastseq
              and seq              <= @lastseq
              and group_name        = @mcstring
              and field_name       in ('open_database_reuse_fail',
                                       'open_object_reuse_fail',
                                       'open_index_reuse_fail')
            group by seq
            having seq + @INTERVALS  > @lastseq
               and seq              <= @lastseq
               and group_name        = @mcstring
               and field_name       in ('open_database_reuse_fail',
                                        'open_object_reuse_fail',
                                        'open_index_reuse_fail')
      end
   else
      begin
         insert into #monmeta
            select distinct seq, getdate (), sum (value), 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
            from umonhistory
            where ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
              and group_name  = @mcstring
              and field_name in ('open_database_reuse_fail', 'open_object_reuse_fail',
                                 'open_index_reuse_fail')
            group by seq
            having ((seq <= @lastseq)  or  (seq + @INTERVALS > @maxseq + @lastseq))
               and group_name  = @mcstring
               and field_name in ('open_database_reuse_fail', 'open_object_reuse_fail',
                                  'open_index_reuse_fail')
      end

   update #monmeta
   set time = uhs.end_time
   from #monmeta m,
        umonhistsummary uhs
   where m.seq = uhs.seq

   update #monmeta
   set dbfree      = (select (@maxdb - d.value)
                      from umonhistory d
                      where m.seq        = d.seq
                        and d.group_name = @mcstring
                        and d.field_name = 'active_open_databases'),
       objectfree  = (select (@maxobject - d.value)
                      from umonhistory d
                      where m.seq        = d.seq
                        and d.group_name = @mcstring
                        and d.field_name = 'active_open_objects'),
       objectreuse = (select d.value
                      from umonhistory d
                      where m.seq        = d.seq
                        and d.group_name = @mcstring
                        and d.field_name = 'open_object_reuse_requests'),
       objectspinw = (select sum (d.value)
                      from umonhistory d
                      where m.seq           = d.seq
                        and d.group_name like @spinwstring
                        and d.field_name    = 'desupd_spin[i]'),
       objectspinp = (select sum (d.value)
                      from umonhistory d
                      where m.seq           = d.seq
                        and d.group_name like @spinpstring
                        and d.field_name    = 'desupd_spin[i]'),
       indexfree   = (select (@maxindex - d.value)
                      from umonhistory d
                      where m.seq        = d.seq
                        and d.group_name = @mcstring
                        and d.field_name = 'active_open_indexes'),
       indexreuse  = (select d.value
                      from umonhistory d
                      where m.seq        = d.seq
                        and d.group_name = @mcstring
                        and d.field_name = 'open_index_reuse_requests'),
       indexspinw  = (select sum (d.value)
                      from umonhistory d
                      where m.seq           = d.seq
                        and d.group_name like @spinwstring
                        and d.field_name    = 'ides_spins[i]'),
       indexspinp  = (select sum (d.value)
                      from umonhistory d
                      where m.seq           = d.seq
                        and d.group_name like @spinpstring
                        and d.field_name    = 'ides_spins[i]'),
       hashspinw   = (select sum (d.value)
                      from umonhistory d
                      where m.seq           = d.seq
                        and d.group_name like @spinwstring
                        and d.field_name    = 'ideschain_spins[i]'),
       hashspinp   = (select sum (d.value)
                      from umonhistory d
                      where m.seq           = d.seq
                        and d.group_name like @spinpstring
                        and d.field_name    = 'ideschain_spins[i]')
   from #monmeta m

   update #monmeta
   set objectspinw = (100 * objectspinw) / objectspinp
   where objectspinp <> 0

   update #monmeta
   set indexspinw = (100 * indexspinw) / indexspinp
   where indexspinp <> 0

   update #monmeta
   set hashspinw = (100 * hashspinw) / hashspinp
   where hashspinp <> 0

   print '                    OPEN'

   print '           TOTAL <DATABASE> <--- OPEN OBJECT ---> <------- OPEN INDEX ------->'

   select SEQ        = seq,
          TIME       = substring (convert (char (8), time, 8), 1, 5),
          FAILS      = substring (convert (char (8),
                                           convert (money, fail),
                                           1),
                                  1,
                                  5),
          FREE       = substring (convert (char (7),
                                           convert (money, dbfree),
                                           1),
                                  1,
                                  4),
          REUSE      = substring (convert (char (8),
                                           convert (money, dbreuse),
                                           1),
                                  1,
                                  5),
          'FREE '    = substring (convert (char (9),
                                           convert (money, objectfree),
                                           1),
                                  1,
                                  6),
          'REUSE '   = substring (convert (char (10),
                                           convert (money, objectreuse),
                                           1),
                                  1,
                                  7),
          'SPIN%'    = str (objectspinw, 6, 2),
          'FREE  '   = substring (convert (char (9),
                                           convert (money, indexfree),
                                           1),
                                  1,
                                  6),
          'REUSE  '  = substring (convert (char (10),
                                           convert (money, indexreuse),
                                           1),
                                  1,
                                  7),
          'SPIN% '   = str (indexspinw, 6, 2),
          'HASH% '   = str (hashspinw, 6, 2)
   from #monmeta
   order by time
go
sp_procxmode monmeta, anymode
go

create procedure 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 umonhistsummary

   select @lastseq = seq
   from umonhistsummary
   where end_time = (select max (end_time)
                     from 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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_procxmode monmode, anymode
go

create procedure 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_avg    int       not null,
       sent_bytes  int       not null,
       sent_avg    int       not null,
       min_tds     int       not null,
       max_tds     int       not null)

   select @maxseq = max (seq)
   from umonhistsummary

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

   if (@lastseq > @INTERVALS)
      begin
         insert into #engines
            select max (convert (tinyint, (substring (group_name, 8, 2)))), '  '
            from 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 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 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 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,
        umonhistsummary uhs
   where m.seq = uhs.seq

   update #monnetwork
   set delays     = (select d.value
                     from 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 umonhistory d
                     where m.seq           = d.seq
                       and d.group_name like 'engine_%'
                       and d.field_name    = 'no_bytes_received'),
       rcvd_avg   = (select sum (d.value)
                     from 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 umonhistory d
                     where m.seq           = d.seq
                       and d.group_name like 'engine_%'
                       and d.field_name    = 'no_bytes_sent'),
       sent_avg   = (select sum (d.value)
                     from 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 umonhistory dr,
                          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 umonhistory dr,
                          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_avg + sent_avg),
       max_tds = (100 * max_tds) / (rcvd_avg + sent_avg)
   where (rcvd_avg + sent_avg) <> 0

   update #monnetwork                          -- avg = bytes / pkts
   set rcvd_avg = rcvd_bytes / rcvd_avg
   where rcvd_avg <> 0

   update #monnetwork                          -- avg = bytes / pkts
   set sent_avg = sent_bytes / sent_avg
   where sent_avg <> 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 '                                                    AVERAGE     TDS'

   print '           <- NETWORK I/O --> <------ %1! -------> <BYTES/PKT>  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),
          RCVD            = substring (convert (char (8), convert (money, rcvd_avg), 1), 1, 5),
          'SENT '         = substring (convert (char (8), convert (money, sent_avg), 1), 1, 5),
          '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_procxmode monnetwork, anymode
go

create procedure 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 umonhistsummary

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

   create table #monulc
      (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 #monulc
            select distinct seq, getdate (), sum (value), 0, 0, 0, 0, 0, 0, 0, 0
            from 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 #monulc
            select distinct seq, getdate (), sum (value), 0, 0, 0, 0, 0, 0, 0, 0
            from 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 #monulc
   set time = uhs.end_time
   from #monulc u,
        umonhistsummary uhs
   where u.seq = uhs.seq

   update #monulc
   set full    = (select d.value
                  from umonhistory d
                  where u.seq        = d.seq
                    and d.group_name = 'xls'
                    and d.field_name = 'plc_flush_full'),
       ex      = (select d.value
                  from umonhistory d
                  where u.seq        = d.seq
                    and d.group_name = 'xls'
                    and d.field_name = 'plc_flush_endxact'),
       dbc     = (select d.value
                  from umonhistory d
                  where u.seq        = d.seq
                    and d.group_name = 'xls'
                    and d.field_name = 'plc_flush_xdeschange'),
       slr     = (select d.value
                  from 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 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 umonhistory d
                  where u.seq        = d.seq
                    and d.group_name = 'xls'
                    and d.field_name = 'plc_lock_waits'),
       semlog  = (select d.value
                  from umonhistory d
                  where u.seq        = d.seq
                    and d.group_name = 'xls'
                    and d.field_name = 'log_lock_waited'),
       maxsz   = (select d.value
                  from umonhistory d
                  where u.seq        = d.seq
                    and d.group_name = 'xls'
                    and d.field_name = 'plc_maxused')
   from #monulc 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 #monulc
   order by time
go
sp_procxmode monulc, anymode
go


use sybsystemprocs
go

create procedure sp_monapfagg (@INTERVALS  tinyint = 15) as

   exec ##database.dbo.monapfagg @INTERVALS

go
sp_addcomment sp_monapfagg, 18, 'Lists aggregate monitor Asynchonous PreFetch info for last INTERVALS'
go


create procedure sp_monapp (@INTERVALS  tinyint = 15) as

   exec ##database.dbo.monapp @INTERVALS

go
sp_addcomment sp_monapp, 18, 'Lists monitor Application Management info for last INTERVALS'
go
sp_procxmode sp_monapp, anymode
go


create procedure sp_moncache (@CACHENAME  varchar (120) = 'default data cache',
                              @INTERVALS  tinyint       = 15) as

   exec ##database.dbo.moncache @CACHENAME, @INTERVALS

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

   exec ##database.dbo.moncacheagg @INTERVALS

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

   exec ##database.dbo.moncontext @INTERVALS

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

   exec ##database.dbo.mondelay @INTERVALS

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

   exec ##database.dbo.mondevice @DEVICE, @INTERVALS

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

   exec ##database.dbo.mondeviceagg @INTERVALS

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

   exec ##database.dbo.monindex @INTERVALS

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

   exec ##database.dbo.monlock @INTERVALS

go
sp_addcomment sp_monlock, 18, 'Lists monitor Lock info for last INTERVALS'
go
sp_procxmode sp_monlock, anymode
go


create procedure sp_monmeta (@INTERVALS  tinyint = 15) as

   exec ##database.dbo.monmeta @INTERVALS

go
sp_addcomment sp_monmeta, 18, 'Lists monitor Metadata info for last INTERVALS'
go
sp_procxmode sp_monmeta, anymode
go


create procedure sp_monmode (@INTERVALS  tinyint = 15) as

   exec ##database.dbo.monmode @INTERVALS

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

   exec ##database.dbo.monnetwork @INTERVALS

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_monulc (@INTERVALS  tinyint = 15) as

   exec ##database.dbo.monulc @INTERVALS

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,
                               @SECTIONS  varchar (134) = 'kernel,wpm,parallel,taskmgmt,appmgmt,esp,monaccess,xactsum,xactmgmt,indexmgmt,mdcache,locks,pcache,memory,recovery,diskio,netio') as
   set ansinull                      on
   set flushmessage                  on
   set nocount                       on
   set string_rtruncation            on

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

   set transaction isolation level 3

   declare @BlankLine     char (1),
           @CacheID       smallint,
           @CacheName     varchar (30),
           @CacheStatus   char (24),
           @CacheType     char (24),
           @CfgCacheRepl  char (24),
           @ConfigSize    char (24),
           @ClrStatTime   datetime,
           @DiskName      varchar (40),
           @GetStatTime   datetime,
           @Grp           int,
           @EngCount      tinyint,
           @NumElapsedMs  int,
           @NumEngines    tinyint,
           @NumXacts      int,
           @PhysName      varchar (127),
           @RptLine       char (80),
           @RunCacheRepl  char (24),
           @RunSize       char (24),
           @RunStat       smallint,
           @SampleMin     smallint,
           @SampleSec     smallint,
           @Section       char (80),
           @Stat          smallint,
           @Status        int,
           @TmpGrpName    varchar (80),
           @TmpNumEngines tinyint

   declare cache_info cursor for
       select convert (varchar (30), co.name),
              convert (char (24), co.value),
              co.status,
              cu.status,
              convert (char (24), cu.value)
       from master.dbo.sysconfigures co,
            master.dbo.syscurconfigs cu
       where co.parent = 19
         and co.config = 19
         and co.config = cu.config
         and co.name   = cu.comment
       order by co.name
   for read only

   declare disk_info cursor for
      select name, phyname, (((low & -16777216) / 16777216) & 255)
      from master.dbo.sysdevices
      where (status & 16) <> 16
   for read only

   select @BlankLine = ' ',
          @Section   = '==============================================================================='

   if ((@SECTIONS IS NULL)                             or
       (charindex ('esp',     lower (@SECTIONS)) > 0)  or
       (charindex ('locks',   lower (@SECTIONS)) > 0)  or
       (charindex ('mdcache', lower (@SECTIONS)) > 0)  or
       (charindex ('wpm',     lower (@SECTIONS)) > 0))
      begin
         select name  = c.name,
                value = convert (varchar (255), r.value)
         into #tempconfigures
         from master.dbo.sysconfigures c,
              master.dbo.syscurconfigs r
         where r.config  = c.config
           and c.parent <> 19
           and not (c.parent in (0, 1, 10, 14) and r.value = 0)
      end

   -- AN "mdcache" REQUEST AUTOMATICALLY INCLUDES THE "dcache" REPORT
   -- OWING TO THE STRING SEARCH ALGORITHMS USED BY sp_sysmonseq.

   if (charindex ('dcache', isnull (lower (@SECTIONS), 'dcache')) > 0)
      begin
         create table #cachemap
            (cid                     smallint,
             name                    varchar (30),
             group_name              varchar (30),
             cache_status            char (24)     null,
             cache_type              char (24)     null,
             cache_config_size       char (24)     null,
             cache_run_size          char (24)     null,
             config_replacement      char (24)     null,
             run_replacement         char (24)     null)

         open cache_info

         fetch cache_info into @CacheName, @ConfigSize, @Stat, @RunStat, @RunSize

         while (@@sqlstatus = 0)
            begin
               select @CacheID = config_admin (15, 0, 0, 0, NULL, @CacheName)

               if (@Stat & 2 = 2)
                  begin
                     select @CacheType = 'Mixed'
                  end

               if (@Stat & 4 = 4)
                  begin
                     select @CacheType = 'Log Only'
                  end

               if (@Stat & 1 = 1)
                  begin
                     select @CacheType = 'Default'
                  end

               if (@Stat & 32 = 32)
                  begin
                     select @CacheStatus = 'Active'
                  end

               if (@Stat & 64 = 64)
                  begin
                    select @CacheStatus = 'Pend/Act'
                  end

               if (@Stat & 128 = 128)
                  begin
                    select @CacheStatus = 'Act/Del'
                  end

               if (@Stat & 256 = 256)
                  begin
                     select @CfgCacheRepl = 'relaxed LRU'
                  end
               else
                  begin
                     select @CfgCacheRepl = 'strict LRU'
                  end

               if (@RunStat & 256 = 256)
                  begin
                     select @RunCacheRepl = 'relaxed LRU'
                  end
               else
                  begin
                     select @RunCacheRepl = 'strict LRU'
                  end

               insert into #cachemap
                  values (@CacheID,
                          @CacheName,
                          'buffer_' + convert (varchar (3), @CacheID),
                          @CacheStatus,
                          @CacheType,
                          @ConfigSize,
                          @RunSize,
                          @CfgCacheRepl,
                          @RunCacheRepl)

               fetch cache_info into @CacheName, @ConfigSize, @Stat, @RunStat, @RunSize
           end

         close cache_info

         deallocate cursor cache_info
      end

   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 @EngCount      = 0,
          @NumEngines    = 0,
          @TmpNumEngines = value
   from #tempmonitors
   where group_name = 'config'
     and field_name = 'cg_cmaxonline'

   while (@EngCount < @TmpNumEngines)
      begin
         select @TmpGrpName = 'engine_'  +  convert (varchar (2), @EngCount)

         if ((select value
              from #tempmonitors
              where group_name = @TmpGrpName
                and field_name = 'clock_ticks') > 0)
            begin
               select @NumEngines = @NumEngines + 1
            end

         select @EngCount = @EngCount + 1
      end

   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

   if (charindex ('kernel', isnull (lower (@SECTIONS), 'kernel')) > 0)
      begin
         exec @Status = sp_sysmon_kernel @NumEngines, @NumElapsedMs, @NumXacts

         if (@Status = 1)
            begin
               return 1
            end
      end

   if (charindex ('wpm', isnull (lower (@SECTIONS), 'wpm')) > 0)
      begin
         exec @Status = sp_sysmon_wpm @NumElapsedMs, @NumXacts, 'N'

         if (@Status = 1)
            begin
               return 1
            end
      end

   if (charindex ('parallel', isnull (lower (@SECTIONS), 'parallel')) > 0)
      begin
         exec @Status = sp_sysmon_parallel @NumElapsedMs, @NumXacts, 'N'

         if (@Status = 1)
            begin
               return 1
            end
      end

   if (charindex ('taskmgmt', isnull (lower (@SECTIONS), 'taskmgmt')) > 0)
      begin
         exec @Status = sp_sysmon_taskmgmt @NumEngines, @NumElapsedMs, @NumXacts

         if (@Status = 1)
            begin
               return 1
            end
      end

   if (charindex ('appmgmt', isnull (lower (@SECTIONS), 'appmgmt')) > 0)
      begin
         exec @Status = sp_sysmon_appmgmt @NumEngines, @NumElapsedMs, @NumXacts, 'N', 'no_appl'

         if (@Status = 1)
            begin
               return 1
            end
      end

   if (charindex ('esp', isnull (lower (@SECTIONS), 'esp')) > 0)
      begin
         exec @Status = sp_sysmon_esp @NumElapsedMs, @NumXacts

         if (@Status = 1)
            begin
               return 1
            end
      end

   if (charindex ('monaccess', isnull (lower (@SECTIONS), 'monaccess')) > 0)
      begin
         exec @Status = sp_sysmon_maccess @NumElapsedMs, @NumXacts, 'N'

         if (@Status = 1)
            begin
               return 1
            end
      end

   if (charindex ('xactsum', isnull (lower (@SECTIONS), 'xactsum')) > 0)
      begin
         exec @Status = sp_sysmon_xactsum @NumElapsedMs, @NumXacts

         if (@Status = 1)
            begin
               return 1
            end
      end

   if (charindex ('xactmgmt', isnull (lower (@SECTIONS), 'xactmgmt')) > 0)
      begin
         exec @Status = sp_sysmon_xactmgmt @NumElapsedMs, @NumXacts

         if (@Status = 1)
            begin
               return 1
            end
      end

   if (charindex ('indexmgmt', isnull (lower (@SECTIONS), 'indexmgmt')) > 0)
      begin
         exec @Status = sp_sysmon_index @NumElapsedMs, @NumXacts

         if (@Status = 1)
            begin
               return 1
            end
      end

   if (charindex ('mdcache', isnull (lower (@SECTIONS), 'mdcache')) > 0)
      begin
         exec @Status = sp_sysmon_mdcache @NumElapsedMs, @NumXacts, 'N'

         if (@Status = 1)
            begin
               return 1
            end
      end

   if (charindex ('locks', isnull (lower (@SECTIONS), 'locks')) > 0)
      begin
         exec @Status = sp_sysmon_locks @NumElapsedMs, @NumXacts, 'N'

         if (@Status = 1)
            begin
               return 1
            end
      end

   if (charindex ('dcache', isnull (lower (@SECTIONS), 'dcache')) > 0)
      begin
         select name    = name,
                io_size = convert (varchar (8), substring (comment, 1, charindex ('K', comment) - 1))
         into #pool_detail_per_cache
         from master.dbo.sysconfigures
         where parent     = 19
           and comment like '%Buffer Pool%'

         exec @Status = sp_sysmon_dcache @NumEngines, @NumElapsedMs, @NumXacts, 'N'

         if (@Status = 1)
            begin
               return 1
            end
      end

   if (charindex ('pcache', isnull (lower (@SECTIONS), 'pcache')) > 0)
      begin
         exec @Status = sp_sysmon_pcache @NumElapsedMs, @NumXacts

         if (@Status = 1)
            begin
               return 1
            end
      end

   if (charindex ('memory', isnull (lower (@SECTIONS), 'memory')) > 0)
      begin
         exec @Status = sp_sysmon_memory @NumElapsedMs, @NumXacts

         if (@Status = 1)
            begin
               return 1
            end
      end

   if (charindex ('recovery', isnull (lower (@SECTIONS), 'recovery')) > 0)
      begin
         exec @Status = sp_sysmon_recovery @NumElapsedMs, @NumXacts

         if (@Status = 1)
            begin
               return 1
            end
      end

   if (charindex ('diskio', isnull (lower (@SECTIONS), 'diskio')) > 0)
      begin
         create table #devicemap
            (name        varchar (40)   not null,
             phyname     varchar (127)  not null,
             group_name  char (8)       not null)

         open disk_info

         fetch disk_info into @DiskName, @PhysName, @Grp

         while (@@sqlstatus = 0)
            begin
               insert into #devicemap
                  values (@DiskName, @PhysName, 'disk_' + convert (char (3), @Grp))

               fetch disk_info into @DiskName, @PhysName, @Grp
            end

         close disk_info

         deallocate cursor disk_info

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

         if (@Status = 1)
            begin
               return 1
            end
      end

   if (charindex ('netio', isnull (lower (@SECTIONS), 'netio')) > 0)
      begin
         exec @Status = sp_sysmon_netio @NumEngines, @NumElapsedMs, @NumXacts

         if (@Status = 1)
            begin
               return 1
            end
      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 with SECTIONS option'
go
