Technical Articles >  ArcSDE  >  DBMS  >  Microsoft SQL Server  >  Performance

Knowledge Base - Technical Articles


Technical Article   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
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


Related Information


Created: 2/14/2003
Last Modified: 7/18/2011

Article Rating: (2)