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

[Anonymous User · Accepted solution]

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!

[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]

[Anonymous User]

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]

[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