nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Clip_93.jpg

Clip_94.jpg

Clip_95.jpg

MagdaUsarekWitek_1-1629900598723.png

MagdaUsarekWitek_0-1629900569376.png

DougBrowning_0-1629899360130.png

Encontre mais publicações marcadas com

Accepted answers

Anonymous User

Original User: asrujit

Jamal,

There could be several suggestions for you:

1. Shrink the database.
in SQL Server management Studio-->R-Click on the database-->Tasks--> Shrink

Shrinking the Transaction Log:
http://technet.microsoft.com/en-us/library/ms178037(v=sql.105).aspx

2. rebuild indexes
http://support.esri.com/cn/knowledgebase/techarticles/detail/24518 (the link is valid for new SQL Server versions as well)

3. Reconcile/Post all versions and compress to state 0

Give these a try and check whether this helps!

All comments

Anonymous User

NidhinKarthikeyan

To add on to what Asrujit said,

You can use Analyze Datasets (Data Management), to help get optimal performance from the RDBMS's query optimizer.

Anonymous User

Original User: vangelo

Then there's taking a class in the database of your choice, so you know how
to administer it optimally.

- V

JamalNUMAN

Jamal,

There could be several suggestions for you:

1. Shrink the database.
in SQL Server management Studio-->R-Click on the database-->Tasks--> Shrink

Shrinking the Transaction Log:
http://technet.microsoft.com/en-us/library/ms178037(v=sql.105).aspx

2. rebuild indexes
http://support.esri.com/cn/knowledgebase/techarticles/detail/24518 (the link is valid for new SQL Server versions as well)

3. Reconcile/Post all versions and compress to state 0

Give these a try and check whether this helps!

Many thanks guys for the help,

Dear Asrujit: the tools you have provided are very useful. Is the �??compress�?� tool does compress to state 0?

[ATTACH=CONFIG]32038[/ATTACH], [ATTACH=CONFIG]32039[/ATTACH], [ATTACH=CONFIG]32040[/ATTACH]

Dear Nidhin: does the �??analyze�?� tool help improving the performance as the �??analyze dataset�?� does?

[ATTACH=CONFIG]32041[/ATTACH], [ATTACH=CONFIG]32042[/ATTACH]

Original User: crafty762

Is the �??compress�?� tool does compress to state 0?
does the �??analyze�?� tool help improving the performance as the �??analyze dataset�?� does?

The compress tool trims the state lineage of your versioned geodatabase. Depending on how much of your edits have been reconciled and posted from child versions up to parent versions, compress will move records from the delta tables to the base tables. Compressing to state 0 essentially means that all records have been moved from the delta tables to the base tables. You can compress all you want, but you will only achieve state 0 if: (1) ALL edits have been reconciled / posted, and (2) all versions are either deleted or their state IDs are set to the same state ID of SDE.DEFAULT prior to compress (although this happens as you successfully reconcile and post, anyways). Compressing on a consistent basis is recommended, even if state 0 isn't your end goal.

The analyze tool and the analyze dataset tool both essentially do the same thing; however the analyze dataset tool will perform an analyze for all object classes within the entire input workspace (e.g., a feature dataset). The analyze tool only targets one specific object class at a time.

NidhinKarthikeyan

How can ArcSDE performance be improved?

Anonymous User

Original User: Jamal432@gmail.com

The compress tool trims the state lineage of your versioned geodatabase. Depending on how much of your edits have been reconciled and posted from child versions up to parent versions, compress will move records from the delta tables to the base tables. Compressing to state 0 essentially means that all records have been moved from the delta tables to the base tables. You can compress all you want, but you will only achieve state 0 if: (1) ALL edits have been reconciled / posted, and (2) all versions are either deleted or their state IDs are set to the same state ID of SDE.DEFAULT prior to compress (although this happens as you successfully reconcile and post, anyways). Compressing on a consistent basis is recommended, even if state 0 isn't your end goal.

The analyze tool and the analyze dataset tool both essentially do the same thing; however the analyze dataset tool will perform an analyze for all object classes within the entire input workspace (e.g., a feature dataset). The analyze tool only targets one specific object class at a time.

Many thanks William for the input.

Do we need really to delete the versions for better performance or is it sufficient jut to reconcile and post?

[ATTACH=CONFIG]32043[/ATTACH]

Clip_104.jpg

NidhinKarthikeyan

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.

Anonymous User

Original User: mboeringa2010

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

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]

Clip_105.jpg

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]

Clip_109.jpg

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

Participe da Conversa

Membros conectados podem postar, seguir atualizações e mais. Novo aqui? Registre uma conta gratuita.

Links Rápidos

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.