Technical Articles

FAQ:  How can ArcSDE performance be improved?
Article ID:     24518
Software:      ArcSDE 8.3, 9.0, 9.1, 9.2, 9.3, 9.3.1, 10, 10.1
Platforms:     N/A
Question
How can ArcSDE performance be improved?

Answer
ArcSDE performance can be improved by analyzing the spatial data and rebuilding their indexes. Analysis of tables and rebuilding of indexes are extremely important for regular maintenance of versioned Geodatabases. Performed regularly, these two functions can help keep performance of the versioned Geodatabase at a maximum.

Below 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.

 If any tables are owned by users who don't own any actual layers, then this script will need to be modified.

These scripts are not a solution for every performance related ArcSDE issue,
but are something that should be done on a regular basis as scheduled maintenance.

 Analyzing the data should be done regularly on a dataset that is changing. This updates the statistics that are used by the Oracle Cost Based Optimizer, to work out the best execution plan for SQL. ArcSDE contains features in the underlying SQL that rely on the Oracle Cost Based Optimizer, and rely on the statistics being up-to-date. Also, be advised there are other scripts and tools for these types of operations. For example, Oracle tools can be used to update the stats and rebuild the other indexes when appropriate.



Oracle 8i/9i/10G (needs to be run as the 'SYS' user) :

SET SERVEROUTPUT ON

DECLARE

    CURSOR Owner_Cur IS
        SELECT DISTINCT(OWNER) owner
    FROM sde.table_registry
    ORDER BY owner;

    CURSOR Index_Cur IS
        SELECT owner, index_name
        FROM dba_indexes
        WHERE owner IN
             (SELECT DISTINCT(owner)
          FROM sde.table_registry)
        AND INDEX_TYPE = 'NORMAL'
        ORDER BY owner, index_name;

    SQL_STMT VARCHAR2(200);

BEGIN

    DBMS_OUTPUT.ENABLE (100000);

    FOR IndexRec IN Index_Cur LOOP
        SQL_STMT := ('ALTER INDEX ' || IndexRec.owner || '.' || IndexRec.index_name || ' REBUILD');
    DBMS_OUTPUT.PUT_LINE(SQL_STMT);
        EXECUTE IMMEDIATE SQL_STMT;
        DBMS_OUTPUT.NEW_LINE;
    END LOOP;

    FOR OwnerRec IN Owner_Cur LOOP
        DBMS_OUTPUT.PUT_LINE('Analyzing schema : ' || OwnerRec.owner);
        DBMS_STATS.GATHER_SCHEMA_STATS(OwnerRec.owner);
        DBMS_OUTPUT.NEW_LINE;
    END LOOP;

END;

/





SQL Server 2000/2005 - Single Database Model (needs to be run as the 'SA' user or as a 'DBO' user - depending on how the SDE
Respository has been created) :

-- Rebuild indexes on tables for all owners in the table registry.
-- ***************************************************************
--  Run under the Database that contains the SDE Repository tables.
--  If the SDE schema is owned by 'DBO', change:
--  select distinct owner from SDE.sde_table_registry
--  to
--  select distinct owner from DBO.sde_table_registry

use <Admin_Database>

Declare @OwnerName varchar(50)
Declare @TableName varchar(50)
Declare @QualifiedName varchar(100)

Declare OwnerCursor cursor for
select distinct owner from SDE.sde_table_registry

open OwnerCursor
Fetch OwnerCursor into @OwnerName
while (@@fetch_status=0)
begin
 Declare TableCursor cursor for
 -- Get all the tables for this user
 select so.name from sysobjects so join sysusers su on so.uid = su.uid  where type = 'U' and su.name = @OwnerName
 open TableCursor
 Fetch TableCursor into @TableName
 while (@@fetch_status=0)
 begin
  set @QualifiedName = @OwnerName + '.' + @TableName
  print 'Rebilding indexes on  : ' + @QualifiedName
  dbcc dbreindex(@QualifiedName) WITH NO_INFOMSGS
  print 'Updating Statistics on: ' + @QualifiedName
  EXEC ('UPDATE STATISTICS ' + @QualifiedName )
  print ''
  Fetch TableCursor into @TableName
 end
 Fetch OwnerCursor into @OwnerName
 close TableCursor
 Deallocate TableCursor
end
close OwnerCursor
Deallocate OwnerCursor




 To obtain the <Admin_Database>, execute the 'sdeservice -o list ...' command directly on the SDE Server.



SQL Server 2000/2005 - Multiple Database Model (needs to be run as the 'SA' user or as a 'DBO' user - depending on how the SDE
Respository has been created) :

-- Rebuild indexes on tables for all owners in the table registry.
-- ***************************************************************
--  Run under the Database that contains the SDE Repository tables.
--  If the SDE schema is owned by 'DBO', change:
--  select distinct owner from SDE.sde_table_registry
--  to
--  select distinct owner from DBO.sde_table_registry

--  In a Multiple Database Model, you need to run this script in
--  every database that contains data registered with ArcSDE.

use <Database_where_spatial_data_resides>

Declare @OwnerName varchar(50)
Declare @TableName varchar(50)
Declare @QualifiedName varchar(100)

Declare OwnerCursor cursor for
select distinct owner from SDE.SDE.sde_table_registry
open OwnerCursor
Fetch OwnerCursor into @OwnerName
while (@@fetch_status=0)
begin
 Declare TableCursor cursor for
 -- Get all the tables for this user
 select so.name from sysobjects so join sysusers su on so.uid = su.uid  where type = 'U' and su.name = @OwnerName
 open TableCursor
 Fetch TableCursor into @TableName
 while (@@fetch_status=0)
 begin
  set @QualifiedName = @OwnerName + '.' + @TableName
  print 'Rebilding indexes on  : ' + @QualifiedName
  dbcc dbreindex(@QualifiedName) WITH NO_INFOMSGS
  print 'Updating Statistics on: ' + @QualifiedName
  EXEC ('UPDATE STATISTICS ' + @QualifiedName )
  print ''
  Fetch TableCursor into @TableName
 end
 Fetch OwnerCursor into @OwnerName
 close TableCursor
 Deallocate TableCursor
end
close OwnerCursor
Deallocate OwnerCursor





 To obtain a list of all the databases that contain data registered with ArcSDE, execute the following query:

select distinct(database_name) from SDE.SDE.sde_table_registry;
go

Source :

http://support.esri.com/de/knowledgebase/techarticles/detail/24518


Thanks.