Large GeoDatabase Performance Optimization

[AbhishekSingh]

Hi,

I am working with ArcGIS 9.3.1 SP1, I am concerned with the overall performance and the health of the GeoDatabase. I will try to describe the structure followed with the questions -

1. The version of the GDB is 9.3.1 SP1 and RDBMS is SQL SERVER 2008.

2.The size of the GDB is around 150 GB with 2500 versions. Here, about 10 versions are edited using ArcMap (these versions are created -> reconciled -> posted and then deleted)., the remaining versions (around 2490) are used with various map service or feature service and are not deleted.

3. The storage type is ESRI Binary.

4. Database is frequently compressed (the end_state_count is ~ 3200), spatial index is also frequently re-calculated.

5. ArcFM and Geometric Netw@ork are involved.

I need any suggestions that can help improve the performance.

I have already started a direct connection with the database and upgrading to SP2 and applying QIP (9.3.1) is in the pipeline.

In order to suggest upgrading to version 10 or above and/or deleting versions, I will need some concrete justification.

I understand that this version is expired and therefore will not have any ESRI Technical Support, which is why I am trying my luck here.

Thanks in Advance,

Abhishek

[AsrujitSengupta]

Check the below links for suggestions on improving the sde performance:

24518 - How can ArcSDE performance be improved?

In the above link, there are scripts (for Oracle and SQL Server) that perform the following:

1) Rebuild every index by any user that owns SDE data.

2) Analyzes the schema of each user that owns SDE data.

30673 - Improve the performance of ArcSDE with SQL Server

[AbhishekSingh]

Hey Asrujit,

Thanks for the information, but I am afraid that we have already referred these technical articles and follow the suggestions of compressing and rebuilding indexes every day.

I am trying to run sdegdbrepair -o repair_tables command and today is the 7th day that the command is still running, however I think this is an expected behavior. We are doing this to have the database in the cleanest possible state before upgrading it to ArcGIS 9.3.1 SP2 and apply QIP to it.

I am more curious to make sure if it is recommended or safe to have ~2500 versions existing in the production database.

[AsrujitSengupta]

There is no hard-coded limit on the number of versions. 2500 versions should be fine if maintained properly.

How many records exist in the State_lineages and States tables?

[AbhishekSingh]

The size of these tables is about 150 MB, I did not have a chance to count the number of records but considering the size i think it is manageable.

Since the rate of versions getting deleted is far lower than the rate of versions getting created, the compress gets a huge number in the end_state_count, which leads to a larger size of the database. I think this something, as mentioned, that cannot have pin pointed answer, only textual speculations.

Thanks