Diagnose ArcSDE connection and performance issues using SDEINTERCEPT

Summary

ArcSDE SDEINTERCEPT can be used to profile an application. The SDEINTERCEPT log file helps identify and diagnose performance and connection issues.

Instructions provided describe how to use this function to diagnose these issues.

Procedure

• An incorrectly tuned database
• Slow disk devices where the database's data files reside
• Slow client machines
• Slow network connectivity between the client and server
• Inefficient client code (ArcGIS, custom ArcObject implementations, the ArcSDE Client)
• Inefficient server code (ArcSDE)

1. Create the ArcSDE intercept log file:

   Note:
   SDEINTERCEPT may be enabled on the ArcSDE Server or for the client ArcGIS application. If using direct connect, only enable the SDEINTERCEPT for the client. When using the ArcSDE Application server, enable SDEINTERCEPT on the server.

   1. Stop the application, exit the ArcGIS application, or stop the ArcSDE Application server.
   2. Enable SDEINTERCEPT by setting the following environment variables:

      set SDEINTERCEPT=crwtf
      set SDEINTERCEPTLOC=C:\temp\slow_connection_intercept

   3. Start the ArcGIS application and connect to the ArcSDE geodatabase.
   4. Locate the file generated from the location specified in the second variable set in step b. In this example, the SDEINTERCEPTLOC specified generates the file:

      c:\temp\slow_connection_intercept.001

      Note:
      If the application is run multiple times or multiple connections are established, a new file is created for each connection and the file name increments as a three-digit decimal (.000) number: for example, .002, .003, etc.

      Note:
      When using direct connect, two files are generated. One file is the ArcSDE intercept from the client and the second file is the ArcSDE direct connect log file.

2. Open the intercept log file in a text editor and search for lapses in time.
3. Based on where the lapses in time exist, it is possible to identify which component is slow.
   
   Here are the two common cases and examples of what they look like in the sdeintercept file:
   1. SDEINTERCEPT for a slow operating ArcSDE function GetAllFeatureTypes

      
      ========================================
      [W 18:49:24] Command: GetAllFeatureTypes
      [R 18:51:54] Long: 0
      [R 18:51:54] Long: 4001

      In the above log file snippet, the command was executed at 18:49:24 and returned its first results, "0" and "4001", at 18:51:54. This means the command was sent from the client and took 2 minutes and 30 seconds to complete. This is definitely a slow ArcSDE command.
      
      Possible Reasons:
      • The RDBMS. Trace the operation in the database to find out or eliminate the RDBMS as a suspect. If it is slow, the recommendations are to update statistics and possibly increase instance memory.
      • The ArcSDE server code. The ArcSDE server code was improved with a general update performance patch on top of 9.2 SP6 to specifically address this issue on Oracle, Informix, and SQL Server.
   2. SDEINTERCEPT for a slow operating client:

      ========================================
      [W 10:00:42] Command: GetAllFeatureTypes
      [R 10:00:45] Long: 0
      [R 10:00:45] Long: 2004
      ... ...
      ========================================
      [W 10:04:06] Command: StreamSetState
      [W 10:04:06] Long: 1
      [W 10:04:06] int64: 841
      [W 10:04:06] int64: -1
      [W 10:04:06] Long: 0
      [R 10:04:06] Long: 0
      ========================================

      
      In the above snippet, it took 3 seconds for the ArcSDE command to complete; however, it was 4 minutes before the next command was executed. Since this is an intercept of a connection, which is an automated process and there is no human intervention, this ~4 minute delay between processes is a client-side delay and unacceptable.
4. It may not be obvious, once a problem has been isolated, as to which component is consuming the resources. The next step is to replace/remove the components that may be causing the slow performance to see if there is a change in performance.
   
   Options to try:
   • If the server code is slow, it is recommended to to apply the latest service packs/patches to ensure that a fully tested, certified and supported configuration is being run, and that there are no interruptions or inconvenience caused by any existing issues in the software that have already been fixed.
   • If possible, run the client application on the same server as the database, which removes the network as a possible point of performance degradation.
   • Use direct connect, which executes the server application code within the client application.
   • Run the client application (ArcGIS) on faster hardware to determine if the client machine is the problem.