Slow ArcSDE Performance Troubleshooting

Why is my ArcSDE geodatabase running so slow?  Is it because I have too much data?  Probably not.  That’s why you bought the big enterprise DBMS, isn’t it?  Whether its a direct connection or an application server connection, sluggish SDE performance will happen from time to time.

Update statistics and rebuild indexes:

A good thing to get into is to analyze any new feature class or table brought into the geodatabase.  However, see this link to rebuild indexes and update statistics for the entire GDB……and add it to your GDB maintenance routine:

FAQ:  How can ArcSDE performance be improved?

Compress:

Compressing (not reck/post) moves edits stored in the Delta tables into the base table as well as remove any unreferenced states. Even if you’re a small shop with few editiors, letting these tables grow unmanaged will wreak havoc on performance over time.  Also, you don’t necessarily need to disconnect users, delete versions or unregister replicas to benefit from a compress.

Compressing an ArcSDE geodatabase helps maintain database performance by removing unused data.
Specifically it does two things:

• First, it removes unreferenced dates, and their associated delta table rows.

• Second, it moves entries in the delta tables that are common to all versions into the base tables, thus reducing the amount of data that the database searches through when executing queries. In effect, a compress will improve query performance and system response time by reducing the depth and complexity of the state tree.

When a large volume of uncompressed changes have accumulated in an ArcSDE geodatabase, a compress operation can take hours or even days. This is another very common cause of poor performance. To avoid this, you should compress on a regular basis (daily, weekly, and after periods of high editing activity). Users can stay connected to the geodatabase during a compress, but we suggest that all users be disconnected for the compress operation to be fully effective.
Remember to update statistics before and after a compress, and note the one exception mentioned earlier. The compress command is available in ArcCatalog. You add the command from the Customize dialog box, and you must be connected as the SDE user to execute it, or you could execute a compress with SDE commands.

The geodatabase compress operation

HowTo:  Compress a versioned database to state 0

 Five Best Practices for Maintaining an ArcSDE Geodatabase

 

Direct Connections(2 Tier) V/S the ArcSDE Service Connections(3 Tier):

Old habits are hard to break and the 3-tier application (ArcSDE) service is certainly one of them. When I know I have to use SDE commands for troubleshooting, I almost always set up a service so I don’t have to type the connection string repeatedly.
With that being said, for everyday use, the direct connection really is the way to go.  Consider this a choice between geodatabase transactions processed over the network or those same geodatabase transactions processed on the client machine.  Most computers today have more than enough processing power to handle this and other than the text in  the connection properties, ArcSDE functionality remains unchanged.
Check out this great link for more details:

Why should I be making direct connections to an ArcSDE geodatabase?

Here are a couple of direct connect syntax examples to get you started:

Sql Server – sde:sqlserver:<server_name\instance_name>
Oracle with ArcSDE 10 – sde:oracle11g*:<net service name>   * or oracle10g
Oracle pre v.10 – sde:oracle10g:\;LOCAL=<Oracle SID>
PostgreSQL – sde:postgresql:<server_name>  

Thanks.

NJ