Increasing the performance of enterprise geodatabase (*.mdf),

[Anonymous User]

Original User: Jamal432@gmail.com

Increasing the performance of enterprise geodatabase (*.mdf),

I have an *.mdf geodatabase which gets really slow when one of layer (stored in it) is accessed. The mdf size is about 4GB while the ldf size is 15GB.

[ATTACH=CONFIG]32022[/ATTACH]

13 versions are permanently linked to this enterprise geodatabase

[ATTACH=CONFIG]32023[/ATTACH]

To increase the performance, I have created a new mdf geodatabse and copied and pasted all the data from the old mdf to it. Then as I access the layers from the new mdf geodatabase, the performance increased dramatically (the versions had to be built again).

I???m sure that this is not the correct approach to increase the performance of an existing mdf geodatabase. What I???m looking for is some tools in the ArcGIS\SQL server that can improve the performance. The documents encourage to apply the ???compress geodatabase??? tool to increase the performance.

[ATTACH=CONFIG]32024[/ATTACH]

What other tools\recommendation might help to enhance the performance dramatically?


Thank you

Best

Jamal

[JamalNUMAN]

How can ArcSDE performance be improved?

Thanks Nidhin.

As I got from the very valuable inputs in this thread, the most important tools that helps in improving the performance are:

1. reconcile\post,
2. compress,
3. rebuild indexes
4. analyze
5. shrink (SQL server)

is the order important?

[ATTACH=CONFIG]32044[/ATTACH]

[Anonymous User]

Original User: nidhinkn

The sequence of step from 1-4 is correct. You can export the model to a python script and schedule it. Not sure about the order of 'Shrink' (SQL server).

[WilliamCraft]

Compress a versioned database to state 0 can be done only after deleting the versions (excluding the sde.DEFAULT version).

First you have to Reconcile and post all versions which are ready to be applied to the DEFAULT version. Alternatively, delete the versions and Compress the database.

This is typically the way in which people achieve state 0, yes.  However, as I eluded to above, it is possible to go to state 0 without deleting child versions of SDE.DEFAULT.  We've done it with replica versions and with other versions.  As long as the state ID of your child versions match the state ID of your SDE.DEFAULT version, compressing will achieve state 0 without having to delete those versions.  For our replica versions, this has typically involved a sequencing of compress followed by replica synchronization followed by compress again.  Doing this allows us to avoid the lengthy task of dropping and rebuilding our replicas when state 0 is needed.  As long as everything is reconciled and posted, this works.  While certainly not the most common workflow, it has proved technically feasible.

[Anonymous User]

Original User: Jamal432@gmail.com

And if you are wondering how other organizations are handling all this, and how you could organize your maintenance, I think William's good remarks in the below linked post of another thread should give you some idea:

Re: ArcSDE/SQL Server/weekly maintenance workflow question

Thanks Macro. This is useful

Best

Jamal

[JamalNUMAN]

This is typically the way in which people achieve state 0, yes.  However, as I eluded to above, it is possible to go to state 0 without deleting child versions of SDE.DEFAULT.  We've done it with replica versions and with other versions.  As long as the state ID of your child versions match the state ID of your SDE.DEFAULT version, compressing will achieve state 0 without having to delete those versions.  For our replica versions, this has typically involved a sequencing of compress followed by replica synchronization followed by compress again.  Doing this allows us to avoid the lengthy task of dropping and rebuilding our replicas when state 0 is needed.  As long as everything is reconciled and posted, this works.  While certainly not the most common workflow, it has proved technically feasible.

Sorry William but I got confused between versions\replica, �??synchronize changes�?� and �??reconcile versions�?� tools in you last input.

Do you refer to versions as replica? And �??reconcile�?� as �??synchronize�?�?

Well�?�what might be the best sequence in applying the tools that enhance the performance? Don�??t you agree with the sequence in the screenshot below?

[ATTACH=CONFIG]32050[/ATTACH]

[Anonymous User]

Original User: crafty762

Do you refer to versions as replica? And �??reconcile�?� as �??synchronize�?�?

Well�?�what might be the best sequence in applying the tools that enhance the performance? Don�??t you agree with the sequence in the screenshot below?

The ordered steps you show in your model are fine, in my opinion.  Reconcile is the mechanism of resolving any conflicts that may arise between versions just before posting edits to the parent version (which, in may cases, is SDE.DEFAULT).  You must reconcile your child version with its parent before being able to post the edits to the parent version; even if no conflicts are detected this is still required.  Synchronizing is different and is intended to apply to the concept of replication specifically.  Synchronizing is the process of transferring changes (edits or some very basic schema changes) from one replica to another in in effort to make one geodatabase match the other; it applies for both one-way (parent to child, or child to parent) and two-way replication.  Synchronization can occur in a connected or disconnected fashion. 

On a slightly-related note, replicas ARE versions themselves and they are built from a version in the geodatabase to begin with.  For example, you might have a replica called MyReplica which is built off of SDE.DEFAULT.  When querying the versions table in ArcSDE via SQL, you will see the SDE.DEFAULT version listed as well as the replica version itself which will look something like this: SYNC_SEND_102_2.  The "102" is the replica ID and the "2" represents the generation number of the replica.  The generation number increases sequentially each time your synchronize your replica. 

Hope this helps...

[JamalNUMAN]

The ordered steps you show in your model are fine, in my opinion.  Reconcile is the mechanism of resolving any conflicts that may arise between versions just before posting edits to the parent version (which, in may cases, is SDE.DEFAULT).  You must reconcile your child version with its parent before being able to post the edits to the parent version; even if no conflicts are detected this is still required.  Synchronizing is different and is intended to apply to the concept of replication specifically.  Synchronizing is the process of transferring changes (edits or some very basic schema changes) from one replica to another in in effort to make one geodatabase match the other; it applies for both one-way (parent to child, or child to parent) and two-way replication.  Synchronization can occur in a connected or disconnected fashion. 

On a slightly-related note, replicas ARE versions themselves and they are built from a version in the geodatabase to begin with.  For example, you might have a replica called MyReplica which is built off of SDE.DEFAULT.  When querying the versions table in ArcSDE via SQL, you will see the SDE.DEFAULT version listed as well as the replica version itself which will look something like this: SYNC_SEND_102_2.  The "102" is the replica ID and the "2" represents the generation number of the replica.  The generation number increases sequentially each time your synchronize your replica. 

Hope this helps...

Very much appreciated William. This is very useful.

Best

Jamal