English

FAQ: How can ArcSDE performance be improved?

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.

Note:
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.

Note:
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) :
Code:

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) :
Code:
-- 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

Note:
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) :
Code:
-- 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


Note:
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