AnsweredAssumed Answered

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

Question asked by geonetadmin on Mar 7, 2014
Latest reply on Mar 8, 2014 by Jamal432@gmail.com

Like • Show 0 Likes0
Comment • 16

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

geonetadmin Mar 7, 2014 3:00 PM

Correct Answer

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!

See the reply in context

Attachments

Clip_95.jpg215.6 KB
Clip_94.jpg206.9 KB
Clip_93.jpg201.2 KB

No one else had this question

Outcomes

16 Replies

geonetadmin Mar 7, 2014 3:00 PM
Unmark Correct
Correct Answer
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!
Like • Show 0 Likes0
Actions
nidhinkn Mar 8, 2014 2:14 AM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
geonetadmin Mar 8, 2014 3:53 AM
Mark Correct
Correct Answer
Original User: vangelo

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

- V
Like • Show 0 Likes0
Actions
Jamal432@gmail.com Mar 8, 2014 7:32 AM
Mark Correct
Correct Answer
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]
Attachments
- Clip_101.jpg252.9 KB
- Clip_100.jpg8.0 KB
- Clip_102.jpg282.1 KB
- Clip_103.jpg136.0 KB
- Clip_99.jpg253.6 KB
Like • Show 0 Likes0
Actions
geonetadmin Mar 8, 2014 7:48 AM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
nidhinkn Mar 8, 2014 8:52 AM
Mark Correct
Correct Answer
How can ArcSDE performance be improved?
Like • Show 0 Likes0
Actions
geonetadmin Mar 8, 2014 8:57 AM
Mark Correct
Correct Answer
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]
Attachments
- Clip_104.jpg291.0 KB
Like • Show 0 Likes0
Actions
nidhinkn Mar 8, 2014 9:00 AM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
geonetadmin Mar 8, 2014 9:01 AM
Mark Correct
Correct Answer
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
Like • Show 0 Likes0
Actions
Jamal432@gmail.com Mar 8, 2014 9:12 AM
Mark Correct
Correct Answer
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]
Attachments
- Clip_105.jpg8.0 KB
Like • Show 0 Likes0
Actions
geonetadmin Mar 8, 2014 9:23 AM
Mark Correct
Correct Answer
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).
Like • Show 0 Likes0
Actions
crafty762 Mar 8, 2014 10:05 AM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
geonetadmin Mar 8, 2014 10:30 AM
Mark Correct
Correct Answer
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
Like • Show 0 Likes0
Actions
Jamal432@gmail.com Mar 8, 2014 10:47 AM
Mark Correct
Correct Answer
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]
Attachments
- Clip_109.jpg256.9 KB
Like • Show 0 Likes0
Actions
geonetadmin Mar 8, 2014 11:02 AM
Mark Correct
Correct Answer
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...
Like • Show 0 Likes0
Actions
Jamal432@gmail.com Mar 8, 2014 11:08 AM
Mark Correct
Correct Answer
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
Like • Show 0 Likes0
Actions

GeoNet

The Esri Community

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

Attachments

Outcomes

Attachments

Attachments

Attachments

Attachments

Related Content

Recommended Content