You may be experiencing record locking.
We are also a SQL customer and recently had similar issues.
Make sure you run MRP with only one process.
Also, if you haven't already, I *highly* recommend you run through
Epicor's SQL Tuning support doc (pasted below). It resolved all of our
record locking issues. Knock on wood, MRP has never frozen since.
Below is the document Epicor sent to me.
Also, run the SQL script attached. It changes some indexes to
non-clustered.
I went through each step below and now we're set.
Good luck.
--------------------------------------------------
Summary: PERF Performance Tuning for an SQL DB
Book: Support Solutions
Page: 8723MPS
XA803
FURTHER INFORMATION: An SQL database does not use most of the Progress
settings, so the performance tuning that can be done is limited.
This document is for an SQL databases only.
Remember that Epicor recommends having one server for Vantage alone.
RECOMMENDATIONS:
A. The mfgsys.pf file. This file is the parameter file for the Vantage
database. For an SQL database, it controls the client connection to the
database. The first line of that file should have the following
settings:
OE 10.0B:
-Mm 1024 -mmax 65534 -Bt 1024 -s 8000 -yy 1970 -stsh 31 -inp 32000 -tok
4000 -TB 31 -TM 32 -D 500 -l 1000
OE 10.1B:
-Mm 1024 -mmax 65534 -Bt 1024 -s 8000 -yy 1970 -stsh 31 -inp 32000 -tok
4000 -TB 31 -TM 32 -D 500 -l 1000 -ttmarshal 5
If any are missing, they can be added in. Changes will not take effect
until the AppServers and database are stopped and started in the
Progress Explorer Tool.
B. There may be a problem with AppServers trimming when they are no
longer needed. Any unused AppServers will hang, and not be available
for future processes that need them. This problem has been addressed in
the most recent Open Edge service packs: OE 10.1B0307 (and higher) or
OE 10.0B0546 (and higher).
If these service packs cannot be applied immediately, configure the
AppServer to not trim any unused AppServers. That will prevent any from
hanging and leave them available for future processes, but will use more
server resources with them open. Use the following procedure to set
this up:
1. Go the Vantage/Vista server.
2. Go to Start->Programs->Open Edge 10.1B->Progress Explorer Tool.
3. Expand the AppServer folder to MfgSys803.
4. Right-click on MfgSys803 and choose Properties.
5. Expand Agent and highlight Pool Range.
6. Change the Initial, Minimum, and Maximum to the same value. Some
experimentation may be necessary to get the correct value.
7. Click OK to save the changes.
8. These changes will not take effect until the AppServers are stopped
and restarted in the Progress Explorer Tool. Stop the AppServers, then
start the AppServers in the Progress Explorer Tool.
NOTE: This step will address performance problems caused by failure to
trim servers. But the increased number of AppServers will use more RAM
on the server. Things may run slower, but there will be less failures
to connect. Open Edge 10.1B03 hot fix 7 and higher (or OE 10.1B05
hotfix 46 or higher) address this problem. After this Open Edge patch
is installed, these Pool Range settings should be set back to their
original values.
C. Some setting for the schema holder in the Progress Explorer Tool can
be adjusted. These settings will vary depending on server resources,
database size, and the number of users. These changes will not take
effect until the DB is stopped and restarted in the Progress Explorer
Tool. Stop the AppServers, then stop the database. Start database,
then start the AppServers for this database in the Progress Explorer
Tool. NOTE: This step will not have any effect if step D (below) is
implemented.
1. # Blocks in DB Buffer. The number of data blocks that will
be loaded into memory when the database is started. With an SQL
database, this value is not used, so it can be set very low. Steps for
changing the #Blocks in DB Buffer:
A. Go the Vantage/Vista server.
B. Go to Start->Programs->Open Edge 10.1B->Progress Explorer Tool.
C. Expand the Database folder to MfgSys803, to Configurations, to
DefaultConfiguration.
D. Right-click on DefaultConfiguration and choose Properties.
E. Change the # Blocks in DB Buffer to 10000. That is all that is
needed for the Progress schema holder.
F. Click OK to save the changes.
2. # Lock Table Entries. The number of record locks made to
the data at any given time. This number can be quite large when running
processes like MRP. Some performance problems result from too many
records being accessed at a given time. The error "Lock table overflow,
increase -L on server (915)" may occur in this case. This can be
addressed by increasing the # Lock Table Entries in the Progress
Explorer Tool:
A. Go the Vantage/Vista server.
B. Go to Start->Programs->Open Edge 10.1B->Progress Explorer Tool.
C. Expand the Database folder to MfgSys803, to Configurations, to
DefaultConfiguration.
D. Right-click on DefaultConfiguration and choose Properties.
E. Increase the # Lock Table Entries.
F. Increase that number to 409600 or higher. A multiple of 8192 is
recommended by Progress.
G. Click OK to save the changes.
3. Spin Locks. The number of times a process tries to acquire
a latch before pausing. If the process cannot acquire the resource's
latch, it continues the attempt. This iterative process is called
spinning. Use the following steps to adjust the Spin Locks:
A. Go the Vantage/Vista server.
B. Go to Start->Programs->Open Edge 10.1B->Progress Explorer Tool.
C. Expand the Database folder to MfgSys803, to Configurations, to
DefaultConfiguration.
D. Right-click on DefaultConfiguration and choose Properties.
E. Go to the Advanced section.
F. Increase Spin Locks Retries. Progress recommends 20000 per
processor. So, dual/dual core CPU should be set to 80000.
G. Click OK to save the changes.
4. Background Writers. For an SQL database, these processes
are not used in the Progress Explorer Tool. Shutting them down will
free up some of the server's resources:
A. Go the Vantage/Vista server.
B. Go to Start->Programs->Open Edge 10.1B->Progress Explorer Tool.
C. Expand the Database folder to MfgSys803, to Configurations, to
DefaultConfiguration.
D. Right-click on DefaultConfiguration and choose Properties.
E. Go to the Background Writers section.
F. Set the # APW's to start to 0.
G. Uncheck the Auto startup check boxes for the Before-image writer and
After-image writer.
H. Click OK to save the changes.
D. The schema holders for the database can be set to read-only. This
may help with client performance. This should not be done if there are
third party products connecting to the Vantage DB. See document 8782MPS
for details. If there are third party products, contact Support.
E There is an SQL indexing script that can be run. The script changes
the Progress_recid index of tables to be non-clustered. This will help
improve performance, in cases of locking, poor performance, or MRP
issues. See document 8927MPS for details. If there are any questions
about this document, contact Support.
F. AppServers can be set to see which client PC is causing a problem.
This requires replacing one file on the server. AppServers will then
track the client PC name. See document 8973MPS (for V8.03.305) or
8974MPS (for V8.03.403) for details.
G. Permissions on the Vantage server. Epicor recommends both the
\Epicor folder and the \Mfgsysdata folders be shared full control, for
everyone.
H. Hardware. Microsoft SQL is self-tuning by default. If more
hardware resources are added to the Vantage server, SQL will
automatically tune itself for the new hardware.
I. Documents and Settings (DNS) Version Tracking. Customers
experiencing workstation problems (lockups and display issues) should do
the following:
1. At the workstation, get out of Vantage.
2. Right mouse click the Windows [Start] button, select Explorer.
3. Go to C:\Documents and Settings\All Users\Application
Data\Epicor\Vantage or Vista
4. Delete ALL of the Versional folders below the Vista or Vantage
folder.
5. Log back into Vista\Vantage.
J. Hyperthreading should not be enabled if SQL is installed. In most
cases, SQL2005 will not install. See page 7663MPS for how to turn off
Hyperthreading.
K. Business Activity Query and SQL Issues
SQL function syntax is stricter than Progress syntax. If you previously
ran the application using a Progress
database but now have moved to SQL, you may experience these syntax
issues.
The main issue is that you can use abbreviations within Progress; for
example, ABSOLUTE can be abbreviated to ABS, ABSO, or ABSOL within
Progress. BAQ formulas are directly sent to SQL. As long as these
formulas do not contain any abbreviations, they work as expected.
However if a formula references an abbreviation, syntax issues occur.
The following table displays the functions which are not identical
between Progress and SQL. The characters contained between the
parentheses are optional characters in Progress.
Progress SQL (Before Translation)
SQL (After Translation)
ABS(OLUTE) ABS
abs
ASC(II) ASC
ascii
INT(EGER) INTEGER
convert
MAX(IMUM) MAXIMUM
dbo.maximum (user-defined function)
MIN(IMUM) MINIMUM
dbo.minimum (user-defined function)
L. Check External Company Maintenance. If there are External Companies
setup that are not being used, those may cause a problem. If an
External Company is not being used, it should be deleted. NOTE: It is
*vitally* important that External Companies being used are *not*
deleted.
M. Full Text Cataloging Set to Track Changes. In SQL Server 2005, if
the tracking option is set to automatic, it can cause performance issues
such as locking and speed degradation. This is a known Microsoft issue
and will be addressed in SQL Server 2008. See page 9494MPS for more
details.
[Non-text portions of this message have been removed]