ArcGIS Server Banner

An overview of tuning an ArcSDE geodatabase

An overview of tuning an ArcSDE geodatabase
Release 9.3 E-mail This TopicPrintable VersionGive Us feedback
Note:This topic was updated for 9.3.1.

Tuning is the process of sharing available resources among users by configuring the components of a database to minimize contention and maximize efficiency. The more people you have accessing your databases, the more tuning you will likely do.

If you are using ArcSDE geodatabases for SQL Server Express (an ArcSDE database server), there is very little tuning you would do. If you make a lot of changes to the data, you might perform a database shrink to see some performance improvement. But mostly, you would perform only regular maintenance, such as backups, compress operations, and rebuilding indexes, not tuning on these types of geodatabases.

Since ArcSDE geodatabases created under the ArcGIS Server Enterprise license are highly customizable and usually accessed by a large number of users, there are many things you can do to tune performance. Some of the tuning tasks you can perform to improve geodatabase performance levels are listed here. Each task contains links to pages that will either elaborate on the concept or describe how to perform the task using ArcGIS.


Tuning task Description Where to get more information on this task
Separate frequently used files to minimize disk input/output (I/O) contention. If a large number of connections are accessing the same files in the same location on disk, database performance will be slow because the connections are competing with one another for the same resources. To reduce this competition, you can store database files in different locations on disk. The files and locations will vary by database management system (DBMS) used. See Recommendations to minimize disk I/O contention for details on methods to reduce competition for resources.

To learn how parameter settings in the DBTUNE table affect the storage of database objects, see The dbtune file and DBTUNE system table and its associated topics DBTUNE configuration keywords and DBTUNE configuration parameter name-configuration string pairs.
Set initialization parameters and create and size storage spaces to tune memory. Components of the DBMS, such as temporary spaces and buffers, need to be created and sized properly to allow enough memory for ArcGIS/ArcSDE processes.

In addition, certain parameters set in the DBMS and ArcSDE control how resources are allocated to connections. You can alter the values of these parameters to better use memory resources.		 See Recommendations for tuning memory for information on memory use by the DBMS.

For information on initialization parameters in the database you can set to improve performance, see DBMS initialization parameter recommendations.

For information on ArcSDE initialization parameters, see ArcSDE initialization parameters.
Switch to load-only mode when loading large amounts of data. When you switch a feature class to load-only mode, the indexes are dropped. This can decrease the time it takes to load large amounts of data into a feature class because there is no index maintenance taking place. For details on how to load large amounts of data efficiently, see Workflow strategies for loading data in the "Adding datasets and other geodatabase elements" section of the help.

To learn how to switch to load-only mode using the sdelayer command or how to bulk load data using shp2sde, cov2sde, or sdeimport, consult the ArcSDE Administration Command Reference provided with the ArcSDE component of ArcGIS Server Enterprise.
Tune the spatial index grid size. ArcSDE uses a multilevel spatial index for data stored in the ArcSDE compressed binary and Open Geospatial Consortium well-known binary (OGCWKB) geometry storage types. Tuning the spatial index grid size may improve performance of spatial queries. To learn about spatial indexes and how to tune them, see Tuning spatial indexes.
Create views for common queries. You might find that users repeatedly perform the same queries. You could create views for these common queries to decrease the amount of time it takes to get information from the database.

NOTE: Views can also be used to control access to data. For instance, if you want a user to only see certain data from a table and not the entire table, you can create a view that contains only the desired data elements, then grant the user permission to access only the view, not the table.

 To learn about spatial and multiversioned views, see Using database views.
Shrink a geodatabase in SQL Server Express. Data files within your SQL Server Express databases may break into increasingly scattered fragments as data is edited, which can cause slight performance degradation. You can occasionally shrink the geodatabase to improve performance. See Shrinking geodatabases for details on the shrink operation and how to perform it.