Database Maintenance Procedures

Not sure if it works for both, but we have a Progress database.

Jonathan


--- In vantage@yahoogroups.com, "acp2g99" <acp2g99@...> wrote:
>
> Do these steps just pertain to a Progress Vantage database or do
they
> also pertain to the SQL version of the Vantage database?
>
>
>
> --- In vantage@yahoogroups.com, "jonnyfatman666"
> <jonathan.waines@> wrote:
> >
> > Hi Jasper, (this is a re-worded email I sent to someone else
off
> the
> > site, so apologies if it doesn't make sense) We were sent an
> Epicor
> > developer to our site Epicor were so concerned about our lack of
> > enthusiasm with Vantage 8. I'm not sure exactly what commands he
> used
> > but the general points of note he highlighted was that our DB had
a
> high
> > 'scatter factor' which is similar to how a hard disk gets
> fragmented.
> > From the Vantage 8 Admin Guide
> > 15. Database Fragmentation or "Scatter Factor". The Db
> > Analysis report is a Progress generated text document which
> displays,
> > summary of information about index, record, free chain blocks and
a
> > Scatter Factor.
> >
> >
> >
> > The Scatter Factor is a indicator of how fragmented the Database
> is.
> > The indicator goes from 0 to 9. The closer higher the number
the
> more
> > database fragmentation exists.
> >
> >
> >
> > How to run:
> >
> > 1) At the Server, Stop the AppServers and the Database in
> > ProgressExplorer.
> >
> > 2) Go to [START] à Programs à OpenEdge 10 à Proenv
> >
> > 3) At the Proenv prompt, type the word: "prompt" and press
> > enter.
> >
> > 4) Change directory into the: C:\Epicor\Mfgsys80\Db
> folder
> >
> > 5) Type the command: proutil mfgsys -C dbanalys >
> dbanalys.wri
> >
> >
> >
> >
> > I'm not sure exactly which commands he ran but I'm sure Epicor
can
> tell
> > you or it might even be on the Progress website. We're still
going
> to
> > have to upgrade some client PCs to get the best performance, but
it
> > won't be as many as we first thought. Some recently purchased
dual
> core
> > Dells with 2GB RAM have typical loading times of 2 or 3 seconds
> without
> > memory caching which is reduced from around twice that. Another
> thing
> > the developer told me was about how the forms are cached locally
on
> the
> > server. There's basically 3 different screen loading times you can
> > expect.
> > 1. Slowest is just after new patch or if you've never opened
> that
> > form before. When opening the form you see the 'downloading from
> > database', 'caching default layouts'. This basically caches some
> files
> > in the C:\Docs and settings\All Users\Application
> Data\Epicor\Vantage
> > version folder. You won't see the 'downloading boxes again until
> there
> > is a new patch installed on the server. 2. Faster is the
first
> time
> > you load a form just after logging into Vantage. (having opened it
> > before as in 1.) 3. Fastest (excluding memory caching) is when
> > you've loaded the form previously in that session and are loading
it
> > again. You can only expect this speed if 1 and 2 have been
> completed,
> > and it only keeps up this speed for the session you're logged in.
> This
> > is representative of the 'every day' typical speed you'll see
> unless you
> > open a form in a morning and never close it.
> > Typically, on the AMD 4200+ dual core machine the times were as
> follows
> > for the Part File Maintenance screen after tweaking and install of
> > latest 803.
> > 1. 6 seconds after downloading and caching for first time
> 2. 3
> > seconds to load the form after logging in to Vantage 3. 1.5
> seconds
> > after form has been loaded previously in that session
> > The slowest machine we tried (2.6GHz Celeron, 768MB RAM) for part
> file
> > maintenance.
> > 1. 21 seconds! 2. 13 seconds 3. 7 seconds
> > This is a list of what the dev carried out. It may not work for
all
> > situations.
> > * Server side (Win 2k3 2GB RAM, single Xeon 3.2GHZ)
> > 1. Turn on Data Execution Prevention for Essential Windows
> services
> > only 2. Page file twice RAM size (open to debate but I'm not
> > arguing!) 3. Add admsrvc.exe, java.exe, prowin32.exe,
> mfgsys.exe,
> > _mprosrv.exe, _mprshut.exe, _proapsrv.exe to exclusion list on
> antivirus
> > 'realtime' scanner. Epicor folder, C:\Docs and settings\All
> > Users\Application Data\Epicor, mfgsysdata\reports should also be
> > excluded. (be more specific if concerned about security) 4.
> Tweak
> > -spin parameter. Higher for more/faster CPUs 5. Apparently,
it
> is
> > not not necessary to have the whole DB allocated space in the RAM
> of the
> > server (-B blocksize). We've got half of our database in there.
> Although
> > it only fills up with data once the data is accessed 6. Change
> > message buffer size from 1 to 4 megabytes (-Mm 4096) 7. Dump
and
> > load due to high 'scatter factor' 8. Main indexes needed to be
> > compacted. This was resolved in step 7 plus a full index
> rebuild 9.
> > Convert DB to 8k blocksize before dump and load supposedly
> increasing
> > throughput for Vantage.
> > 10. Client side
> > 1. Remove C:\Docs and settings\All Users\Application
> Data\Epicor and
> > client software directory from 'realtime' antivirus scanner 2.
> > Ensure mfgsys.exe and AutoUpdate.exe are also excluded from
> antivirus
> >
> > Most of the list mentioned above were in addition to existing
> tweaks on
> > our server, so it is not complete by any means. Hope this is of
> some
> > help. Jonathan
> >
> >
> >
> > --- In vantage@yahoogroups.com, "Jasper Recto" <jrecto@> wrote:
> > >
> > > Does anybody have a list of database maintenance procedures and
> the
> > frequency they should be run?
> > >
> > > I'm looking for anything that will examine the database for any
> > problems and help keep the integrity of the progress database as
> clean
> > as possible. Also, anything to speed up the database would be
> helpful.
> > >
> > > What daily, weekly, monthly and yearly maintenance does
everybody
> do
> > on their vantage database?
> > >
> > >
> > > Thanks,
> > > Jasper
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
Does anybody have a list of database maintenance procedures and the frequency they should be run?

I'm looking for anything that will examine the database for any problems and help keep the integrity of the progress database as clean as possible. Also, anything to speed up the database would be helpful.

What daily, weekly, monthly and yearly maintenance does everybody do on their vantage database?


Thanks,
Jasper


[Non-text portions of this message have been removed]
Hi Jasper, (this is a re-worded email I sent to someone else off the
site, so apologies if it doesn't make sense) We were sent an Epicor
developer to our site Epicor were so concerned about our lack of
enthusiasm with Vantage 8. I'm not sure exactly what commands he used
but the general points of note he highlighted was that our DB had a high
'scatter factor' which is similar to how a hard disk gets fragmented.
From the Vantage 8 Admin Guide
15. Database Fragmentation or "Scatter Factor". The Db
Analysis report is a Progress generated text document which displays,
summary of information about index, record, free chain blocks and a
Scatter Factor.



The Scatter Factor is a indicator of how fragmented the Database is.
The indicator goes from 0 to 9. The closer higher the number the more
database fragmentation exists.



How to run:

1) At the Server, Stop the AppServers and the Database in
ProgressExplorer.

2) Go to [START] à Programs à OpenEdge 10 à Proenv

3) At the Proenv prompt, type the word: "prompt" and press
enter.

4) Change directory into the: C:\Epicor\Mfgsys80\Db folder

5) Type the command: proutil mfgsys -C dbanalys > dbanalys.wri




I'm not sure exactly which commands he ran but I'm sure Epicor can tell
you or it might even be on the Progress website. We're still going to
have to upgrade some client PCs to get the best performance, but it
won't be as many as we first thought. Some recently purchased dual core
Dells with 2GB RAM have typical loading times of 2 or 3 seconds without
memory caching which is reduced from around twice that. Another thing
the developer told me was about how the forms are cached locally on the
server. There's basically 3 different screen loading times you can
expect.
1. Slowest is just after new patch or if you've never opened that
form before. When opening the form you see the 'downloading from
database', 'caching default layouts'. This basically caches some files
in the C:\Docs and settings\All Users\Application Data\Epicor\Vantage
version folder. You won't see the 'downloading boxes again until there
is a new patch installed on the server. 2. Faster is the first time
you load a form just after logging into Vantage. (having opened it
before as in 1.) 3. Fastest (excluding memory caching) is when
you've loaded the form previously in that session and are loading it
again. You can only expect this speed if 1 and 2 have been completed,
and it only keeps up this speed for the session you're logged in. This
is representative of the 'every day' typical speed you'll see unless you
open a form in a morning and never close it.
Typically, on the AMD 4200+ dual core machine the times were as follows
for the Part File Maintenance screen after tweaking and install of
latest 803.
1. 6 seconds after downloading and caching for first time 2. 3
seconds to load the form after logging in to Vantage 3. 1.5 seconds
after form has been loaded previously in that session
The slowest machine we tried (2.6GHz Celeron, 768MB RAM) for part file
maintenance.
1. 21 seconds! 2. 13 seconds 3. 7 seconds
This is a list of what the dev carried out. It may not work for all
situations.
* Server side (Win 2k3 2GB RAM, single Xeon 3.2GHZ)
1. Turn on Data Execution Prevention for Essential Windows services
only 2. Page file twice RAM size (open to debate but I'm not
arguing!) 3. Add admsrvc.exe, java.exe, prowin32.exe, mfgsys.exe,
_mprosrv.exe, _mprshut.exe, _proapsrv.exe to exclusion list on antivirus
'realtime' scanner. Epicor folder, C:\Docs and settings\All
Users\Application Data\Epicor, mfgsysdata\reports should also be
excluded. (be more specific if concerned about security) 4. Tweak
-spin parameter. Higher for more/faster CPUs 5. Apparently, it is
not not necessary to have the whole DB allocated space in the RAM of the
server (-B blocksize). We've got half of our database in there. Although
it only fills up with data once the data is accessed 6. Change
message buffer size from 1 to 4 megabytes (-Mm 4096) 7. Dump and
load due to high 'scatter factor' 8. Main indexes needed to be
compacted. This was resolved in step 7 plus a full index rebuild 9.
Convert DB to 8k blocksize before dump and load supposedly increasing
throughput for Vantage.
10. Client side
1. Remove C:\Docs and settings\All Users\Application Data\Epicor and
client software directory from 'realtime' antivirus scanner 2.
Ensure mfgsys.exe and AutoUpdate.exe are also excluded from antivirus

Most of the list mentioned above were in addition to existing tweaks on
our server, so it is not complete by any means. Hope this is of some
help. Jonathan



--- In vantage@yahoogroups.com, "Jasper Recto" <jrecto@...> wrote:
>
> Does anybody have a list of database maintenance procedures and the
frequency they should be run?
>
> I'm looking for anything that will examine the database for any
problems and help keep the integrity of the progress database as clean
as possible. Also, anything to speed up the database would be helpful.
>
> What daily, weekly, monthly and yearly maintenance does everybody do
on their vantage database?
>
>
> Thanks,
> Jasper
>
>
> [Non-text portions of this message have been removed]
>




[Non-text portions of this message have been removed]
Do these steps just pertain to a Progress Vantage database or do they
also pertain to the SQL version of the Vantage database?



--- In vantage@yahoogroups.com, "jonnyfatman666"
<jonathan.waines@...> wrote:
>
> Hi Jasper, (this is a re-worded email I sent to someone else off
the
> site, so apologies if it doesn't make sense) We were sent an
Epicor
> developer to our site Epicor were so concerned about our lack of
> enthusiasm with Vantage 8. I'm not sure exactly what commands he
used
> but the general points of note he highlighted was that our DB had a
high
> 'scatter factor' which is similar to how a hard disk gets
fragmented.
> From the Vantage 8 Admin Guide
> 15. Database Fragmentation or "Scatter Factor". The Db
> Analysis report is a Progress generated text document which
displays,
> summary of information about index, record, free chain blocks and a
> Scatter Factor.
>
>
>
> The Scatter Factor is a indicator of how fragmented the Database
is.
> The indicator goes from 0 to 9. The closer higher the number the
more
> database fragmentation exists.
>
>
>
> How to run:
>
> 1) At the Server, Stop the AppServers and the Database in
> ProgressExplorer.
>
> 2) Go to [START] à Programs à OpenEdge 10 à Proenv
>
> 3) At the Proenv prompt, type the word: "prompt" and press
> enter.
>
> 4) Change directory into the: C:\Epicor\Mfgsys80\Db
folder
>
> 5) Type the command: proutil mfgsys -C dbanalys >
dbanalys.wri
>
>
>
>
> I'm not sure exactly which commands he ran but I'm sure Epicor can
tell
> you or it might even be on the Progress website. We're still going
to
> have to upgrade some client PCs to get the best performance, but it
> won't be as many as we first thought. Some recently purchased dual
core
> Dells with 2GB RAM have typical loading times of 2 or 3 seconds
without
> memory caching which is reduced from around twice that. Another
thing
> the developer told me was about how the forms are cached locally on
the
> server. There's basically 3 different screen loading times you can
> expect.
> 1. Slowest is just after new patch or if you've never opened
that
> form before. When opening the form you see the 'downloading from
> database', 'caching default layouts'. This basically caches some
files
> in the C:\Docs and settings\All Users\Application
Data\Epicor\Vantage
> version folder. You won't see the 'downloading boxes again until
there
> is a new patch installed on the server. 2. Faster is the first
time
> you load a form just after logging into Vantage. (having opened it
> before as in 1.) 3. Fastest (excluding memory caching) is when
> you've loaded the form previously in that session and are loading it
> again. You can only expect this speed if 1 and 2 have been
completed,
> and it only keeps up this speed for the session you're logged in.
This
> is representative of the 'every day' typical speed you'll see
unless you
> open a form in a morning and never close it.
> Typically, on the AMD 4200+ dual core machine the times were as
follows
> for the Part File Maintenance screen after tweaking and install of
> latest 803.
> 1. 6 seconds after downloading and caching for first time
2. 3
> seconds to load the form after logging in to Vantage 3. 1.5
seconds
> after form has been loaded previously in that session
> The slowest machine we tried (2.6GHz Celeron, 768MB RAM) for part
file
> maintenance.
> 1. 21 seconds! 2. 13 seconds 3. 7 seconds
> This is a list of what the dev carried out. It may not work for all
> situations.
> * Server side (Win 2k3 2GB RAM, single Xeon 3.2GHZ)
> 1. Turn on Data Execution Prevention for Essential Windows
services
> only 2. Page file twice RAM size (open to debate but I'm not
> arguing!) 3. Add admsrvc.exe, java.exe, prowin32.exe,
mfgsys.exe,
> _mprosrv.exe, _mprshut.exe, _proapsrv.exe to exclusion list on
antivirus
> 'realtime' scanner. Epicor folder, C:\Docs and settings\All
> Users\Application Data\Epicor, mfgsysdata\reports should also be
> excluded. (be more specific if concerned about security) 4.
Tweak
> -spin parameter. Higher for more/faster CPUs 5. Apparently, it
is
> not not necessary to have the whole DB allocated space in the RAM
of the
> server (-B blocksize). We've got half of our database in there.
Although
> it only fills up with data once the data is accessed 6. Change
> message buffer size from 1 to 4 megabytes (-Mm 4096) 7. Dump and
> load due to high 'scatter factor' 8. Main indexes needed to be
> compacted. This was resolved in step 7 plus a full index
rebuild 9.
> Convert DB to 8k blocksize before dump and load supposedly
increasing
> throughput for Vantage.
> 10. Client side
> 1. Remove C:\Docs and settings\All Users\Application
Data\Epicor and
> client software directory from 'realtime' antivirus scanner 2.
> Ensure mfgsys.exe and AutoUpdate.exe are also excluded from
antivirus
>
> Most of the list mentioned above were in addition to existing
tweaks on
> our server, so it is not complete by any means. Hope this is of
some
> help. Jonathan
>
>
>
> --- In vantage@yahoogroups.com, "Jasper Recto" <jrecto@> wrote:
> >
> > Does anybody have a list of database maintenance procedures and
the
> frequency they should be run?
> >
> > I'm looking for anything that will examine the database for any
> problems and help keep the integrity of the progress database as
clean
> as possible. Also, anything to speed up the database would be
helpful.
> >
> > What daily, weekly, monthly and yearly maintenance does everybody
do
> on their vantage database?
> >
> >
> > Thanks,
> > Jasper
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
> [Non-text portions of this message have been removed]
>