English

How To: Improve the performance of ArcSDE with SQL Server

Summary

Instructions provided describe how to troubleshoot and improve the performance of ArcSDE with SQL Server. There are several factors that can affect the performance of ArcGIS 9.x when working with ArcSDE 9.x and SQL Server 2000/2005.

Procedure

  • Check the free RAM available on the client and server machines. The computer with ArcGIS needs to have at least 512 MB free and the computer with ArcSDE server needs to have at least 1 GB free. Check for the available memory in Windows task manager. Free RAM on the computer by closing non-related applications on the client or by adjusting Memory allocated to the Database Instance in Enterprise Manager. Test if there are any performance improvements.
  • Increase the frequency of compressing the database. See the link in the Related Information section below for more information on how to compress a versioned database to state. After doing a compress, try the field calculation and see if performance improves. For datasets with frequent editing, a weekly compress of the database often improves performance.
  • Improve ArcSDE performance by rebuilding indexes on tables. See the link in the Related Information section below for more information. The SQL Server script runs in Query Analyzer, which is located in Enterprise Manager > Tools > SQL Query Analyzer. After indexes are rebuilt, see if there is any change in performance.
  • Increase the frequency of updating statistics on feature classes with the 'Analyze' function in ArcCatalog. Right-click on a feature class and select Analyze and then select all tables.
  • Check the network traffic between the SDE server and the client applications. Does performance depend on the number of users connected to the server? High network traffic or slow connections may be impacting performance at certain times of the day.
  • Test using a Direct connection to the database in ArcCatalog with the following parameters and test if performance is different:

    Code:
    Server : <blank>
    Service : sde:sqlserver:<DATASOURCE>
    Database : sde
    Username : sde
    Password : <SDE_User_Password>

    The <DATASOURCE> is the name of the SQL Server instance. If one was not specified when SQL server was installed then it inherits the server name by default.
  • Improve performance with ArcSDE layers. See the link in the Related Information section below for more information.
  • In the %SDEHOME%\etc\giomgr.defs file, change MINBUFSIZE & MAXBUFSIZE to the new values below and import in the new values using ArcSDE command:

    Code:
    MINBUFSIZE 409600 # minimum buffer size > 4096
    MAXBUFSIZE 819200 # maximum buffer size > MINBUFSIZE

    Use the sdeconfig command to import in the new values above. For example:

    Code:
    sdeconfig -o import -f C:\arcgis\ArcSDE\sqlexe\etc\giomgr.defs -i 5151 -D DBOG -u sde -p sde

  • Check that the latest Service Packs for ArcSDE 9.x and ArcGIS 9.x are applied. See the links in the Related Information section below.
  • Perform a Session Trace of the Database for one user to check which SQL statements are taking the most time.

Related Information