use sybsystemprocs
go
drop procedure sp_archvcacheagg
go
drop procedure sp_archvcontext
go
drop procedure sp_archvdelay
go
drop procedure sp_archvindex
go
drop procedure sp_archvlock
go
drop procedure sp_archvmode
go
drop procedure sp_archvnetwork
go
drop procedure sp_archvsummary
go
drop procedure sp_archvulc
go
drop procedure sp_archv_datetime
go

set quoted_identifier on
go

create procedure sp_archv_datetime (@ENDTM1  varchar (6),
                                    @ENDTM2  varchar (6),
                                    @TIME1   smalldatetime  output,
                                    @TIME2   smalldatetime  output) as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare @len       tinyint,
           @worktime  char (14)

   select @TIME1 = min (end_time)
   from ##database.dbo.archivesummary
   where end_time >= convert (smalldatetime, @ENDTM1)

   select @TIME1 = isnull (@TIME1, min (end_time))
   from ##database.dbo.archivesummary

   select @len = char_length (@ENDTM2)

   if (@len = 2)
      begin
         select @worktime = @ENDTM2 + '1231 11:59PM'
      end
   else if (@len = 4)
      begin
         select @worktime = @ENDTM2 + '31 11:59PM'
      end
   else
      begin
         select @worktime = @ENDTM2 + ' 11:59PM'
      end

   select @TIME2 = min (end_time)           -- EARLIEST ROW PAST THE DESIRED TIME
   from ##database.dbo.archivesummary
   where end_time > @worktime

   select @TIME2 = max (end_time)           -- SHIFT BACK A SEQUENCE
   from ##database.dbo.archivesummary
   where end_time < @TIME2

   select @TIME2 = isnull (@TIME2, max (end_time))
   from ##database.dbo.archivesummary
go

create procedure sp_archvcacheagg (@ENDTM1  smalldatetime = NULL,
                                   @ENDTM2  smalldatetime = NULL) 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 @time1  smalldatetime,
           @time2  smalldatetime

   print ' '

   create table #archvbuffer
      (end_time    smalldatetime     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 #archvcacheagg
      (start_time  smalldatetime     not null,
       end_time    smalldatetime     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)

   if (@ENDTM2 < @ENDTM1)
      begin
         print '    DATETIME1 <%1> MUST BE EARLIER THAN DATETIME2 <%2!>.',
               @ENDTM1, @ENDTM2

         print ' '
      end
   else
      begin
         exec sp_archv_datetime @ENDTM1, @ENDTM2, @time1 output, @time2 output

         insert into #archvbuffer
            select end_time, group_name, field_name, field_id, value
            from ##database.dbo.archivedetail
            where end_time   between @time1 and @time2
              and group_name    like 'buffer_%'

         insert into #archvcacheagg
            select distinct end_time, end_time, sum (value), 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
            from #archvbuffer
            where field_name = 'bufsearch_calls'
            group by end_time
            having field_name = 'bufsearch_calls'

         update #archvcacheagg
         set start_time = uhs.start_time
         from #archvcacheagg m,
              ##database.dbo.archivesummary uhs
         where m.end_time = uhs.end_time

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

         update #archvcacheagg
         set gpages = (select m.gpages + (2 * sum (d.value))
                       from #archvbuffer d
                       where m.end_time   = d.end_time
                         and d.field_name = 'bufgrab_4k')
         from #archvcacheagg m

         update #archvcacheagg
         set gpages = (select m.gpages + (4 * sum (d.value))
                       from #archvbuffer d
                       where m.end_time   = d.end_time
                         and d.field_name = 'bufgrab_8k')
         from #archvcacheagg m

         update #archvcacheagg
         set gpages = (select m.gpages + (8 * sum (d.value))
                       from #archvbuffer d
                       where m.end_time   = d.end_time
                         and d.field_name = 'bufgrab_16k')
         from #archvcacheagg m

         update #archvcacheagg
         set iso0strts = (select d.value
                          from ##database.dbo.archivedetail d
                          where m.end_time   = d.end_time
                            and d.group_name = 'access'
                            and d.field_name = 'dirty_read_restarts')
         from #archvcacheagg m

         --  FINAL COMPUTATIONS AND REPORT

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

         update #archvcacheagg
         set io_req = 1
         where io_req = 0

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

         update #archvcacheagg
         set wash = 99.9
         where wash > 99.9

         update #archvcacheagg
         set wash = 0
         where hits = 0

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

         print '                                      <--------- PAGES ----------> <- ISO 0 ->'

         select 'END TIME' = convert (char (6), end_time, 12)
                             + ' ' + substring (convert (char (8), end_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), 6, 1),
                GRAB       = substring (convert (char (14),
                                                 convert (money, gpages),
                                                 1),
                                        1,
                                        11),
                'USE'      = substring (convert (char (14),
                                                 convert (money, upages),
                                                 1),
                                        1,
                                        11),
                DIRT       = substring (convert (char (7),
                                                 convert (money, dirty),
                                                 1),
                                        1,
                                        4),
                PAGES      = substring (convert (char (10),
                                                 convert (money, iso0pgs),
                                                 1),
                                        1,
                                        7),
                RST        = substring (convert (char (6),
                                                 convert (money, iso0strts),
                                                 1),
                                        1,
                                        3)
         from #archvcacheagg
         order by end_time
      end
go
sp_addcomment sp_archvcacheagg, 18, 'Lists archival Caache aggregate info between ENDTIME1 and ENDTIME2'
go
sp_procxmode sp_archvcacheagg, anymode
go

create procedure sp_archvcontext (@ENDTM1  smalldatetime = NULL,
                                  @ENDTM2  smalldatetime = NULL) 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 @time1  smalldatetime,
           @time2  smalldatetime

   print ' '

   print '    VY:   Voluntary Yields'

   print '    CSM:  Cache Search Misses'

   print '    SDW:  System Disk Writes'

   print '    IOP:  I/O Pacing'

   print '    LLC:  Logical Lock Contention'

   print '    ALC:  Address Lock Contention'

   print '    LSC:  Log Semaphore Contention'

   print '    GCS:  Group Commit Sleeps'

   print '    LLPW: Last Log Page Writes'

   print '    MC:   Modify Conflicts'

   print '    IODC: I/O Device Contention'

   print '    NPR:  Network Packet Received'

   print '    NPS:  Network Packet Sent'

   print '    SL:   Sysindexes Lookup'

   print ' '

   create table #context
      (end_time  smalldatetime  not null,
       name      char (35)      not null,
       value     int            null)

   if (@ENDTM2 < @ENDTM1)
      begin
         print '    DATETIME1 <%1> MUST BE EARLIER THAN DATETIME2 <%2!>.',
               @ENDTM1, @ENDTM2

         print ' '
      end
   else
      begin
         exec sp_archv_datetime @ENDTM1, @ENDTM2, @time1 output, @time2 output

         insert into #context
            select distinct end_time, 'context switches', sum (value)
            from ##database.dbo.archivedetail
            where end_time   between @time1 and @time2
              and group_name    like 'engine_%'
              and field_name       = 'context_switches'
            group by end_time
            having end_time   between @time1 and @time2
               and group_name    like 'engine_%'
               and field_name       = 'context_switches'

         insert into #context
            select end_time, field_name, value
            from ##database.dbo.archivedetail
            where end_time   between @time1 and @time2
              and group_name       = 'kernel'
              and field_name       = 'yields'

         insert into #context
            select distinct end_time, 'cache misses', sum (value)
            from ##database.dbo.archivedetail
            where end_time   between @time1 and @time2
              and group_name    like 'buffer_%'
              and field_name       = 'bufread_read_waits'
            group by end_time
            having end_time   between @time1 and @time2
               and group_name    like 'buffer_%'
               and field_name       = 'bufread_read_waits'

         insert into #context
            select distinct end_time, 'system disk writes', sum (value)
            from ##database.dbo.archivedetail
            where end_time   between @time1 and @time2
              and group_name    like 'buffer_%'
              and field_name      in ('write_waits', 'hk_write_waits', 'restart_io_waits')
            group by end_time
            having end_time   between @time1 and @time2
               and group_name    like 'buffer_%'
               and field_name      in ('write_waits', 'hk_write_waits', 'restart_io_waits')

         insert into #context
            select distinct end_time, 'io pacing', sum (value)
            from ##database.dbo.archivedetail
            where end_time   between @time1 and @time2
              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 end_time
            having end_time   between @time1 and @time2
               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 #context
            select distinct end_time, 'logical lock', sum (value)
            from ##database.dbo.archivedetail
            where end_time    between @time1 and @time2
              and group_name        = 'lock'
              and field_name     like 'waited_%'
              and field_name not like 'waited_%_ADDR'
            group by end_time
            having end_time    between @time1 and @time2
               and group_name        = 'lock'
               and field_name     like 'waited_%'
               and field_name not like 'waited_%_ADDR'

         insert into #context
            select distinct end_time, 'address lock', sum (value)
            from ##database.dbo.archivedetail
            where end_time   between @time1 and @time2
              and group_name       = 'lock'
              and field_name    like 'waited_%_ADDR'
            group by end_time
            having end_time   between @time1 and @time2
               and group_name       = 'lock'
               and field_name    like 'waited_%_ADDR'

         insert into #context
            select end_time, 'log semaphore', value
            from ##database.dbo.archivedetail
            where end_time   between @time1 and @time2
              and group_name       = 'xls'
              and field_name       = 'log_lock_waited'
            group by end_time
            having end_time   between @time1 and @time2
               and group_name       = 'xls'
               and field_name       = 'log_lock_waited'

         insert into #context
            select distinct end_time, 'group commits', sum (value)
            from ##database.dbo.archivedetail
            where end_time   between @time1 and @time2
              and group_name    like 'buffer_%'
              and field_name      in ('my_other_log_waits', 'log_lastpage_pending_io_sleeps')
            group by end_time
            having end_time   between @time1 and @time2
               and group_name    like 'buffer_%'
               and field_name      in ('my_other_log_waits', 'log_lastpage_pending_io_sleeps')

         insert into #context
            select distinct end_time, 'last log page', sum (value)
            from ##database.dbo.archivedetail
            where end_time   between @time1 and @time2
              and group_name    like 'buffer_%'
              and field_name       = 'last_log_page_writes'
            group by end_time
            having end_time   between @time1 and @time2
               and group_name    like 'buffer_%'
               and field_name       = 'last_log_page_writes'

         insert into #context
            select distinct end_time, 'modify conflicts', sum (value)
            from ##database.dbo.archivedetail
            where end_time   between @time1 and @time2
              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 end_time
            having end_time   between @time1 and @time2
               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 #context
            select distinct end_time, 'device contention', sum (value)
            from ##database.dbo.archivedetail
            where end_time   between @time1 and @time2
              and group_name    like 'disk_%'
              and field_name       = 'p_misses'
            group by end_time
            having end_time   between @time1 and @time2
               and group_name    like 'disk_%'
               and field_name       = 'p_misses'

         insert into #context
            select distinct end_time, 'np received', value
            from ##database.dbo.archivedetail
            where end_time   between @time1 and @time2
              and group_name       = 'network'
              and field_name       = 'total_packets_received'
            group by end_time
            having end_time   between @time1 and @time2
               and group_name       = 'network'
               and field_name       = 'total_packets_received'

         insert into #context
            select end_time, 'np sent', value
            from ##database.dbo.archivedetail
            where end_time   between @time1 and @time2
              and group_name       = 'network'
              and field_name       = 'total_packets_sent'
            group by end_time
            having end_time   between @time1 and @time2
               and group_name       = 'network'
               and field_name       = 'total_packets_sent'

         insert into #context
            select distinct end_time, 'sysindexes', sum (value)
            from ##database.dbo.archivedetail
            where end_time   between @time1 and @time2
              and group_name       = 'sysind'
              and field_name    like 'ind_bufguess%'
            group by end_time
            having end_time   between @time1 and @time2
               and group_name       = 'sysind'
              and field_name     like 'ind_bufguess%'

         select 'END TIME' = convert (char (6), vy.end_time, 12)
                             + ' ' + substring (convert (char (8), vy.end_time, 8), 1, 5),
                'VY'       = convert (tinyint, (100 * vy.value) / cs.value),
                'CSM'      = convert (tinyint, (100 * cm.value) / cs.value),
                'SDW'      = convert (tinyint, (100 * sdw.value) / cs.value),
                'IOP'      = convert (tinyint, (100 * iop.value) / cs.value),
                'LLC'      = convert (tinyint, (100 * llc.value) / cs.value),
                'ALC'      = convert (tinyint, (100 * alc.value) / cs.value),
                'LSC'      = convert (tinyint, (100 * lsc.value) / cs.value),
                'GCS'      = convert (tinyint, (100 * gcs.value) / cs.value),
                'LLPW'     = convert (tinyint, (100 * llpw.value) / cs.value),
                'MC'       = convert (tinyint, (100 * mc.value) / cs.value),
                'IODC'     = convert (tinyint, (100 * dc.value) / cs.value),
                'NPR'      = convert (tinyint, (100 * npr.value) / cs.value),
                'NPS'      = convert (tinyint, (100 * nps.value) / cs.value),
                'SL'       = convert (tinyint, (100 * sl.value) / cs.value)
         from #context cs,
              #context vy,
              #context cm,
              #context sdw,
              #context iop,
              #context llc,
              #context alc,
              #context lsc,
              #context gcs,
              #context llpw,
              #context mc,
              #context dc,
              #context npr,
              #context nps,
              #context sl
         where cs.name       = 'context switches'
           and vy.end_time   = cs.end_time
           and vy.name       = 'yields'
           and cm.end_time   = cs.end_time
           and cm.name       = 'cache misses'
           and sdw.end_time  = cs.end_time
           and sdw.name      = 'system disk writes'
           and iop.end_time  = cs.end_time
           and iop.name      = 'io pacing'
           and llc.end_time  = cs.end_time
           and llc.name      = 'logical lock'
           and alc.end_time  = cs.end_time
           and alc.name      = 'address lock'
           and lsc.end_time  = cs.end_time
           and lsc.name      = 'log semaphore'
           and gcs.end_time  = cs.end_time
           and gcs.name      = 'group commits'
           and llpw.end_time = cs.end_time
           and llpw.name     = 'last log page'
           and mc.end_time   = cs.end_time
           and mc.name       = 'modify conflicts'
           and dc.end_time   = cs.end_time
           and dc.name       = 'device contention'
           and npr.end_time  = cs.end_time
           and npr.name      = 'np received'
           and nps.end_time  = cs.end_time
           and nps.name      = 'np sent'
           and sl.end_time   = cs.end_time
           and sl.name       = 'sysindexes'
         order by cs.end_time
      end
go
sp_addcomment sp_archvcontext, 18, 'Lists archival Context info beween ENDTIME1 and ENDTIME2'
go
sp_procxmode sp_archvcontext, anymode
go

create procedure sp_archvdelay (@ENDTM1  varchar (6) = NULL,
                                @ENDTM2  varchar (6) = NULL) 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,
           @time1  smalldatetime,
           @time2  smalldatetime

   print ' '

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

   create table #archvspin
      (end_time     datetime          not null,
       id           smallint          not null,
       spinw        double precision  not null,
       sping        int               not null)

   if (@ENDTM2 < @ENDTM1)
      begin
         print '    DATETIME1 <%1> MUST BE EARLIER THAN DATETIME2 <%2!>.',
               @ENDTM1, @ENDTM2

         print ' '
      end
   else
      begin
         exec sp_archv_datetime @ENDTM1, @ENDTM2, @time1 output, @time2 output

         insert into #archvdelay
            select distinct end_time, sum (value), 0, 0, 0, 0, 0, 0, 0
            from ##database.dbo.archivedetail
            where end_time   between @time1 and @time2
              and group_name    like 'disk_%'
              and field_name       = 'p_misses'

         insert into #archvspin
            select w.end_time, w.field_id, w.value, g.value
            from ##database.dbo.archivedetail w,
                 ##database.dbo.archivedetail g
            where w.end_time   between @time1 and @time2
              and w.group_name       = 'spinlock_w_0'
              and w.field_name       = 'Resource->rcaches[i]->cspin'
              and g.end_time         = w.end_time
              and g.field_id         = w.field_id
              and g.group_name       = 'spinlock_p_0'
              and g.field_name       = 'Resource->rcaches[i]->cspin'

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

         update #archvdelay
         set diskiodelay = (select d.value
                            from ##database.dbo.archivedetail d
                            where m.end_time   = d.end_time
                              and d.group_name = 'kernel'
                              and d.field_name = 'udalloc_sleeps')
         from #archvdelay m

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

         update #archvdelay
         set netdelay    = (select d.value
                            from ##database.dbo.archivedetail d
                            where m.end_time   = d.end_time
                              and d.group_name = 'kernel'
                              and d.field_name = 'ksalloc_sleeps')
         from #archvdelay m

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

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

         update #archvdelay
         set spinmax2 = (select max (s2.spinw)
                         from #archvspin s2
                         where m.end_time = s2.end_time)
         from #archvdelay m

         select @cachecnt = count (*)
         from #archvspin

         if (@cachecnt > 1)
            begin
               update #archvdelay
               set spinmax2id = (select max (id)
                                 from #archvspin s2
                                 where m.end_time = s2.end_time
                                   and m.spinmax2 = s2.spinw)
               from #archvdelay m

               update #archvdelay
               set spinmax1 = (select max (s.spinw)
                               from #archvspin s
                               where m.end_time    = s.end_time
                                 and m.spinmax2id <> s.id)
               from #archvdelay m

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

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

               select 'END TIME'   = convert (char (6), end_time, 12)
                                     + ' '
                                     + substring (convert (char (8), end_time, 8), 1, 5),
                      TIME         = substring (convert (char (8), end_time, 8), 1, 5),
                      DEVICE       = substring (convert (char (10), convert (money, diskiodelay), 1), 1, 7),
                      NETWORK      = substring (convert (char (10), convert (money, netdelay), 1), 1, 7),
                      'DEVICE '    = substring (convert (char (10), convert (money, diskwait), 1), 1, 7),
                      ULC          = substring (convert (char (10), convert (money, semulc), 1), 1, 7),
                      'LOG'        = substring (convert (char (10), convert (money, semlog), 1), 1, 7),
                      'CONTENTION' = convert (char (10), str (spinmax2, 10, 2))
               from #archvdelay m
               order by end_time
            end
      end
go
sp_addcomment sp_archvdelay, 18, 'Lists archival Wait info beween ENDTIME1 and ENDTIME2'
go
sp_procxmode sp_archvdelay, anymode
go

create procedure sp_archvindex (@ENDTM1  smalldatetime = NULL,
                                @ENDTM2  smalldatetime = NULL) 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 @time1  smalldatetime,
           @time2  smalldatetime

   print ' '

   create table #archvindex
      (end_time     smalldatetime     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 (@ENDTM2 < @ENDTM1)
      begin
         print '    DATETIME1 <%1> MUST BE EARLIER THAN DATETIME2 <%2!>.',
               @ENDTM1, @ENDTM2

         print ' '
      end
   else
      begin
         exec sp_archv_datetime @ENDTM1, @ENDTM2, @time1 output, @time2 output

         insert into #archvindex
            select distinct end_time, -1, -1, -1, -1, -1, -1, -1, -1, -1, sum (value)
            from ##database.dbo.archivedetail
            where end_time   between @time1 and @time2
              and group_name       = 'access'
              and field_name    like 'shrink%'
            group by end_time
            having end_time   between @time1 and @time2
               and group_name       = 'access'
               and field_name    like 'shrink%'

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

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

         from #archvindex m

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

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

         update #archvindex
         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 'END TIME' = convert (char (6), end_time, 12)
                             + ' ' + substring (convert (char (8), end_time, 8), 1, 5),
                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),
                PGS        = substring (convert (char (6),
                                                 convert (money, rid_pg),
                                                 1),
                                        1,
                                        3),
                'ROWS'     = substring (convert (char (8),
                                                 convert (money, rid_row),
                                                 1),
                                        1,
                                        5)
         from #archvindex
         order by end_time
      end
go
sp_addcomment sp_archvindex, 18, 'Lists archival Index Maintenance info between ENDTIME1 and ENDTIME2'
go
sp_procxmode sp_archvindex, anymode
go

create procedure sp_archvlock (@ENDTM1  smalldatetime = NULL,
                               @ENDTM2  smalldatetime = NULL) 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 @time1  smalldatetime,
           @time2  smalldatetime

   print ' '

   create table #archvlock
      (end_time     smalldatetime  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 (@ENDTM2 < @ENDTM1)
      begin
         print '    DATETIME1 <%1> MUST BE EARLIER THAN DATETIME2 <%2!>.',
               @ENDTM1, @ENDTM2

         print ' '
      end
   else
      begin
         exec sp_archv_datetime @ENDTM1, @ENDTM2, @time1 output, @time2 output

         insert into #archvlock
            select end_time, value, -1, -1, -1, -1, -2, -1
            from ##database.dbo.archivedetail
            where end_time   between @time1 and @time2
              and group_name       = 'lock'
              and (field_name   like 'granted%'  or  field_name like 'waited%')

         insert into #archvlock
            select distinct end_time, sum (requests), -1, -1, -1, -1, -1, -1
            from #archvlock
            group by end_time

         delete from #archvlock
         where ddlks = -2

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

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

         select 'END TIME' = convert (char (6), end_time, 12)
                             + ' ' + substring (convert (char (8), end_time, 8), 1, 5),
                REQUESTS   = substring (convert (char (14),
                                                 convert (money, requests),
                                                 1),
                                        1,
                                        11),
                WAITS      = substring (convert (char (10),
                                                 convert (money, waits),
                                                 1),
                                        1,
                                        7),
                EXCLUSIVE  = substring (convert (char (12),
                                                 convert (money, promoEX),
                                                 1),
                                        1,
                                        9),
                'SHARED'   = substring (convert (char (12),
                                                 convert (money, promoSH),
                                                 1),
                                        1,
                                        9),
                FAILED     = substring (convert (char (11),
                                                 convert (money, promo_fails),
                                                 1),
                                        1,
                                        8),
                FOUND      = substring (convert (char (8),
                                                 convert (money, ddlks),
                                                 1),
                                        1,
                                        5),
                SRCHD      = substring (convert (char (8),
                                                 convert (money, ddlk_srchs),
                                                 1),
                                        1,
                                        5)
         from #archvlock
         order by end_time
      end
go
sp_addcomment sp_archvlock, 18, 'Lists archival Lock info beween ENDTIME1 and ENDTIME2'
go
sp_procxmode sp_archvlock, anymode
go

create procedure sp_archvmode (@ENDTM1  smalldatetime = NULL,
                               @ENDTM2  smalldatetime = NULL) 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 @time1  smalldatetime,
           @time2  smalldatetime

   print ' '

   create table #mode
      (end_time  smalldatetime  not null,
       name      char (35)      not null,
       value     int            null)

   if (@ENDTM2 < @ENDTM1)
      begin
         print '    DATETIME1 <%1> MUST BE EARLIER THAN DATETIME2 <%2!>.',
               @ENDTM1, @ENDTM2

         print ' '
      end
   else
      begin
         exec sp_archv_datetime @ENDTM1, @ENDTM2, @time1 output, @time2 output

         insert into #mode
            select end_time, 'del direct', value
            from ##database.dbo.archivedetail
            where end_time   between @time1 and @time2
              and group_name       = 'access'
              and field_name       = 'delete'

         insert into #mode
            select end_time, 'del deferred', value
            from ##database.dbo.archivedetail
            where end_time   between @time1 and @time2
              and group_name       = 'access'
              and field_name       = 'delete_deferred'

         insert into #mode
            select end_time, 'ins heap', value
            from ##database.dbo.archivedetail
            where end_time   between @time1 and @time2
              and group_name       = 'access'
              and field_name       = 'ncinsert'

         insert into #mode
            select end_time, 'ins clustered', value
            from ##database.dbo.archivedetail
            where end_time   between @time1 and @time2
              and group_name       = 'access'
              and field_name       = 'cinsert'

         insert into #mode
            select end_time, 'upd deferred', value
            from ##database.dbo.archivedetail
            where end_time   between @time1 and @time2
              and group_name       = 'access'
              and field_name       = 'deferred_update'

         insert into #mode
            select end_time, 'upd inplace', value
            from ##database.dbo.archivedetail
            where end_time   between @time1 and @time2
              and group_name       = 'access'
              and field_name       = 'direct_inplace_update'

         insert into #mode
            select end_time, 'upd cheap', value
            from ##database.dbo.archivedetail
            where end_time   between @time1 and @time2
              and group_name       = 'access'
              and field_name       = 'direct_notinplace_update'

         insert into #mode
            select end_time, 'upd expensive', value
            from ##database.dbo.archivedetail
            where end_time   between @time1 and @time2
              and group_name       = 'access'
              and field_name       = 'direct_expensive_update'

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

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

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

               print ' '
            end

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

         select 'END TIME' = convert (char (6), ddir.end_time, 12)
                             + ' ' + substring (convert (char (8), ddir.end_time, 8), 1, 5),
                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 #mode ddir,
              #mode ddef,
              #mode ihp,
              #mode iclst,
              #mode uip,
              #mode uc,
              #mode ue,
              #mode udef
         where ddir.name      = 'del direct'
           and ddef.end_time  = ddir.end_time
           and ddef.name      = 'del deferred'
           and ihp.end_time   = ddir.end_time
           and ihp.name       = 'ins heap'
           and iclst.end_time = ddir.end_time
           and iclst.name     = 'ins clustered'
           and uip.end_time   = ddir.end_time
           and uip.name       = 'upd inplace'
           and uc.end_time    = ddir.end_time
           and uc.name        = 'upd cheap'
           and ue.end_time    = ddir.end_time
           and ue.name        = 'upd expensive'
           and udef.end_time  = ddir.end_time
           and udef.name      = 'upd deferred'
         order by ddir.end_time
      end
go
sp_addcomment sp_archvmode, 18, 'Lists archival Transaction Mode info between ENDTIME1 and ENDTIME2'
go
sp_procxmode sp_archvmode, anymode
go

create procedure sp_archvnetwork (@ENDTM1  smalldatetime = NULL,
                                  @ENDTM2  smalldatetime = NULL) 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),
           @time1          smalldatetime,
           @time2          smalldatetime,
           @unit           char (2)

   print ' '

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

   create table #archvnetwork
      (end_time    smalldatetime  not null,
       requests    int            not null,
       delays      int            not null,
       rcvd_bytes  int            not null,
       rcvd_tds    int            not null,
       sent_bytes  int            not null,
       sent_tds    int            not null,
       min_tds     int            not null,
       max_tds     int            not null)

   if (@ENDTM2 < @ENDTM1)
      begin
         print '    DATETIME1 <%1> MUST BE EARLIER THAN DATETIME2 <%2!>.',
               @ENDTM1, @ENDTM2

         print ' '
      end
   else
      begin
         exec sp_archv_datetime @ENDTM1, @ENDTM2, @time1 output, @time2 output

         insert into #engines
            select max (convert (tinyint, (substring (group_name, 8, 2)))), '  '
            from ##database.dbo.archivedetail
            where end_time   between @time1 and @time2
              and group_name    like 'engine_%'
            group by end_time
            having end_time   between @time1 and @time2
               and group_name    like 'engine_%'

         insert into #archvnetwork
            select distinct end_time, value, 0, 0, 0, 0, 0, 0, 0
            from ##database.dbo.archivedetail
            where end_time   between @time1 and @time2
              and group_name       = 'kernel'
              and field_name       = 'ksalloc_calls'

         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 #archvnetwork
         set delays     = (select d.value
                           from ##database.dbo.archivedetail d
                           where m.end_time   = d.end_time
                             and d.group_name = 'kernel'
                             and d.field_name = 'ksalloc_sleeps'),
             rcvd_bytes = (select sum (d.value)
                           from ##database.dbo.archivedetail d
                           where m.end_time      = d.end_time
                             and d.group_name like 'engine_%'
                             and d.field_name    = 'no_bytes_received'),
             rcvd_tds   = (select sum (d.value)
                           from ##database.dbo.archivedetail d
                           where m.end_time      = d.end_time
                             and d.group_name like 'engine_%'
                             and d.field_name    = 'no_packets_received'),
             sent_bytes = (select sum (d.value)
                           from ##database.dbo.archivedetail d
                           where m.end_time      = d.end_time
                             and d.group_name like 'engine_%'
                             and d.field_name    = 'no_bytes_sent'),
             sent_tds   = (select sum (d.value)
                           from ##database.dbo.archivedetail d
                           where m.end_time      = d.end_time
                             and d.group_name like 'engine_%'
                             and d.field_name    = 'no_packets_sent'),
             min_tds    = (select min (dr.value + ds.value)
                           from ##database.dbo.archivedetail dr,
                                ##database.dbo.archivedetail ds
                           where m.end_time       = dr.end_time
                             and dr.group_name like 'engine_%'
                             and dr.group_name   <> @engine_ignore
                             and dr.field_name    = 'no_packets_received'
                             and m.end_time       = ds.end_time
                             and ds.group_name like 'engine_%'
                             and ds.group_name   <> @engine_ignore
                             and ds.field_name    = 'no_packets_sent'),
             max_tds    = (select max (dr.value + ds.value)
                           from ##database.dbo.archivedetail dr,
                                ##database.dbo.archivedetail ds
                           where m.end_time       = dr.end_time
                             and dr.group_name like 'engine_%'
                             and dr.field_name    = 'no_packets_received'
                             and m.end_time       = ds.end_time
                             and ds.group_name like 'engine_%'
                             and ds.field_name    = 'no_packets_sent')
         from #archvnetwork m

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

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

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

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

         print '                                                                           TDS'

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

         select 'END TIME'  = convert (char (6), end_time, 12)
                              + ' ' + substring (convert (char (8), end_time, 8), 1, 5),
                REQUESTS    = substring (convert (char (13), convert (money, requests), 1), 1, 10),
                DELAYS      = substring (convert (char (10), convert (money, delays), 1), 1, 7),
                RECEIVED    = substring (convert (char (12), convert (money, rcvd_bytes), 1), 1, 9),
                SENT        = substring (convert (char (12), convert (money, sent_bytes), 1), 1, 9),
                'RECEIVED ' = substring (convert (char (12), convert (money, rcvd_tds), 1), 1, 9),
                'SENT '     = substring (convert (char (12), convert (money, sent_tds), 1), 1, 9),
                'MIN-MAX'   = substring (space (6 - char_length (convert (varchar (3), min_tds)) - char_length (convert (varchar (3), max_tds)))
                                         +  convert (varchar (3), min_tds)
                                         +  '-'
                                         +  convert (varchar (3), max_tds),
                                         1,
                                         7)
         from #archvnetwork m
         order by end_time
      end
go
sp_addcomment sp_archvnetwork, 18, 'Lists archival Network I/O info between ENDTIME1 and ENDTIME2'
go
sp_procxmode sp_archvnetwork, anymode
go

create procedure sp_archvsummary (@ENDTM1  smalldatetime = NULL,
                                  @ENDTM2  smalldatetime = NULL) 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 @time1  smalldatetime,
           @time2  smalldatetime

   if (@ENDTM2 < @ENDTM1)
      begin
         print '    DATETIME1 <%1> MUST BE EARLIER THAN DATETIME2 <%2!>.',
               @ENDTM1, @ENDTM2

         print ' '
      end
   else
      begin
         exec sp_archv_datetime @ENDTM1, @ENDTM2, @time1 output, @time2 output

         print ' '

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

         select 'END TIME' = convert (char (6), end_time, 12)
                              + ' ' + substring (convert (char (8), end_time, 8), 1, 5),
                'MIN-MAX'  = substring (space (6 - char_length (convert (varchar (3), cpu_min)) - char_length (convert (varchar (3), cpu_max)))
                                        +  convert (varchar (3), cpu_min)
                                        +  '-'
                                        +  convert (varchar (3), cpu_max),
                                        1,
                                        7),
                'READS  '  = substring (convert (char (10),
                                                 convert (money,
                                                          total_read),
                                                 1),
                                        1,
                                        7),
                ' WRITES'  = substring (convert (char (10),
                                                 convert (money,
                                                          total_write),
                                                 1),
                                        1,
                                        7),
                'WRITES '  = substring (convert (char (10),
                                                 convert (money,
                                                          log_write),
                                                 1),
                                        1,
                                        7),
                XACTS      = substring (convert (char (10),
                                                 convert (money,
                                                          xacts),
                                                 1),
                                        1,
                                        7),
                DATA       = str (dcache, 5, 1),
                ' PROC'    = str (pcache, 5, 1),
                DLK        = convert (tinyint, ddlks)
--              PRMOT      = str (auxdp2, 5, 1),
--              'GRANT'    = str (auxdp1, 5, 1)
         from ##database.dbo.archivesummary
         where end_time between @time1 and @time2
         order by end_time
      end
go
sp_addcomment sp_archvsummary, 18, 'Lists archival Summary info between ENDTIME1 and ENDTIME2'
go
sp_procxmode sp_archvsummary, anymode
go

create procedure sp_archvulc (@ENDTM1  smalldatetime = NULL,
                              @ENDTM2  smalldatetime = NULL) 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 @time1  smalldatetime,
           @time2  smalldatetime

   print ' '

   create table #ulc
      (end_time  smalldatetime     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,
       semwait   double precision  not null,
       semtot    int               not null,
       maxsz     smallint          null)

   if (@ENDTM2 < @ENDTM1)
      begin
         print '    DATETIME1 <%1> MUST BE EARLIER THAN DATETIME2 <%2!>.',
               @ENDTM1, @ENDTM2

         print ' '
      end
   else
      begin
         exec sp_archv_datetime @ENDTM1, @ENDTM2, @time1 output, @time2 output

         insert into #ulc
            select distinct end_time, sum (value), 0, 0, 0, 0, 0, 0, 0, 0
            from ##database.dbo.archivedetail
            where end_time   between @time1 and @time2
              and group_name       = 'xls'
              and field_name    like 'plc_flush_%'
            group by end_time
            having end_time   between @time1 and @time2
               and group_name       = 'xls'
               and field_name    like 'plc_flush_%'

         update #ulc
         set full    = (select d.value
                        from ##database.dbo.archivedetail d
                        where u.end_time   = d.end_time
                          and d.group_name = 'xls'
                          and d.field_name = 'plc_flush_full'),
             ex      = (select d.value
                        from ##database.dbo.archivedetail d
                        where u.end_time   = d.end_time
                          and d.group_name = 'xls'
                          and d.field_name = 'plc_flush_endxact'),
             dbc     = (select d.value
                        from ##database.dbo.archivedetail d
                        where u.end_time   = d.end_time
                          and d.group_name = 'xls'
                          and d.field_name = 'plc_flush_xdeschange'),
             slr     = (select d.value
                        from ##database.dbo.archivedetail d
                        where u.end_time   = d.end_time
                          and d.group_name = 'xls'
                          and d.field_name = 'plc_flush_slr_xact'),
             oth     = (select sum (d.value)
                        from ##database.dbo.archivedetail d
                        where u.end_time    = d.end_time
                          and d.group_name  = 'xls'
                          and d.field_name in ('plc_flush_unpin', 'plc_flush_discard',
                                               'plc_flush_pmscan')),
             semwait = (select d.value
                        from ##database.dbo.archivedetail d
                        where u.end_time   = d.end_time
                          and d.group_name = 'xls'
                          and d.field_name = 'log_lock_waited'),
             semtot  = (select sum (d.value)
                        from ##database.dbo.archivedetail d
                        where u.end_time    = d.end_time
                          and d.group_name  = 'xls'
                          and d.field_name in ('log_lock_granted', 'log_lock_waited')),
             maxsz   = (select d.value
                        from ##database.dbo.archivedetail d
                        where u.end_time   = d.end_time
                          and d.group_name = 'xls'
                          and d.field_name = 'plc_maxused')
         from #ulc u

         update #ulc
         set semwait = semwait / semtot
         where semtot <> 0

         print '              <----------- ULC FLUSHES TO XACT LOG -----------> SEMPHR MAX ULC'

         select 'END TIME'  = convert (char (6), end_time, 12)
                              + ' ' + substring (convert (char (8), end_time, 8), 1, 5),
                '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),
                'WAIT%'     = str (semwait, 6, 1),
                ' SIZE'     = substring (convert (char (10), convert (money, maxsz), 1), 1, 7)
         from #ulc
         order by end_time
      end
go
sp_addcomment sp_archvulc, 18, 'Lists archival User Log Cache info between ENDTIME1 and ENDTIME2'
go
sp_procxmode sp_archvulc, anymode
go
