topic Re: Increasing the performance of enterprise geodatabase (*.mdf), in Data Management Questions

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

Anonymous User — Fri, 07 Mar 2014 17:43:58 GMT

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

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

Anonymous User — Fri, 07 Mar 2014 23:00:19 GMT

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!

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

NidhinKarthikeyan — Sat, 08 Mar 2014 10:14:37 GMT

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.

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

Anonymous User — Sat, 08 Mar 2014 11:53:06 GMT

Original User: vangelo

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

- V

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

JamalNUMAN — Sat, 08 Mar 2014 15:32:03 GMT

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]

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

Anonymous User — Sat, 08 Mar 2014 15:48:42 GMT

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.

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

NidhinKarthikeyan — Sat, 08 Mar 2014 16:52:25 GMT

How can ArcSDE performance be improved?

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

Anonymous User — Sat, 08 Mar 2014 16:57:51 GMT

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]

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

NidhinKarthikeyan — Sat, 08 Mar 2014 17:00:58 GMT

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.

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

Anonymous User — Sat, 08 Mar 2014 17:01:46 GMT

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

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

JamalNUMAN — Sat, 08 Mar 2014 17:12:56 GMT

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]

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

Anonymous User — Sat, 08 Mar 2014 17:23:32 GMT

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).

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

WilliamCraft — Sat, 08 Mar 2014 18:05:27 GMT

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.

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

Anonymous User — Sat, 08 Mar 2014 18:30:47 GMT

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

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

JamalNUMAN — Sat, 08 Mar 2014 18:47:11 GMT

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]

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

Anonymous User — Sat, 08 Mar 2014 19:02:03 GMT

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...

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

JamalNUMAN — Sat, 08 Mar 2014 19:08:48 GMT

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