Performance of WorkFlow Manager?

Discussion created by jose3479 on Dec 22, 2011
We are a medium size organization with 5 full time power editors and 11 other partial time editors. On an average day we have maybe 8 people editing using JTX/PLTS to create products that resemble USGS Quad Maps.

Right now we have over 50 versions. Partially because we have a half of year backlog for QA/QC.

Daily and sometimes three times daily I perform the following:
Batch Reconcile and Post
Nightly DBMS_Update_Stats runs against everything in the database.

Even with close attention to the database we still get groups of versions with conflicts and hence do not get posted back to default. Not a problem we fix it and run the steps again.

We are constantly plagued by poor performance even with the diligent steps above.  I run SQL traces for a baseline. Sometimes I am lucky and can tell a query ran out of memory but when I think it is a smoking gun I investigate the layers in question and they only have a handful of edits in the A&D tabels. I have no idea why it brings the database to a halt when other users can be performing the same operation on another layer with 1000's of edits without problem.

I can trace the complete database lockup's back to users performing massivly large attribute and topology validation or in some cases Reconcile and Post operations.

My question for the ESRI Workflow Manager community is what is your database maintenance workflow? How many versions do you keep around, How do you address and troubleshoot poor performance? How many users are you supporting?  what does your hardware configuration look like?

We have a decent server dedicated to the database and some file sharing tasks:

2 x 4 core 2.66GHz CPU
WIN 2003/SQL 2005
ArcGIS Server 9.3.1 ArcGIS Desktop 9.3.1 all current on patches
Operating on a Fiber GB LAN

Our workstations are a little low on resources some having 2 or 4 GB of RAM. (we know we need to increase this)  We plan to move to ArcGIS 10 in a few months.