ESRI User Conference 2004

1
ESRI User Conference 2004

• ArcSDE

2
Some Nuggets

• Setup
• Performance
• Distribution
• Geodatabase History

3
Setup

• Initial Database
• Create as large as possible to avoid fragmenting
  indexes
• Set growth rate to a large value for same reason
• Fillfactor
• Set very high for read only environments
• 95-100
• Set midrange for editing environments
• 75 default
• Users
• 9.0 supports Windows groups but they can not own
  data
• Windows groups are more efficient than individual
  logins
• ArcGIS checks sysprotects table for permissions

4
Setup (contd)

• Permissions
• Data Loader
• Create Table
• Create View
• Create Procedure
• Data Editor
• Select
• Insert
• Update
• Delete
• Execute
• Use ArcCatalog to assign permissions
• Associated tables may be missed if using DBMS to
  assign permissions

5
Setup (contd)

• Logfiles
• User logfiles
• An individual logfile table is created once for
  each user
• Session logfiles (recommended for SQL Server)
• An individual logfile table is created and
  destroyed for each login session
• Maxlog 0
• Allowsessionlog true
• Logfilepoolsize 0
• Pooled logfiles
• A series of logfile tables are created once
• Each login uses one of the pooled logfiles
• Need to provide enough tables for all logins

6
Setup (contd)

• Upgrading
• Backup
• Uninstall 8.3
• Install 9
• run checkschema.exe 25 errors at a time max.
• run custom post-installation dont create new
  dbase or SDE user if upgrading
• run migratory sample utility to convert each
  spatial database to a series of single standalone
  spatial databases
• Create new service for each single spatial
  database
• Recommended storing rasters in a separate
  database
• Only store separately if they are completely
  separate (no cross database queries supported)
• Follow up migration with sp_change_users_login to
  synchronize IDs

7
Performance

• Memory
• Initially set SQL Server memory to 50 RAM and
  move up from there
• Setting SQL Server memory too high will restrict
  gsrvr.exe
• 1 giomgr.exe is spawned at runtime
• 1 gsrvr.exe is spawned for each connection
• Make sure dbinit.sde is empty unless debugging
• Stores detailed initialization information
• Set TCPKeepAlive to ON to close orphaned
  gsrvr.exe processes

8
Performance (contd)

• Reference Classes
• Only use during editing
• All reference classes need to be loaded when used
  and this sucks up memory
• Excess number of privileges will slow things down
• Servers start queuing SQL statements when they
  hit 65 CPU usage
• Index Tuning Wizard
• Use to check often used queries for performance

9
Performance (contd)

• Loading
• Set the recovery model to simple
• Avoids tracking everything when you dont need to
  rollback
• Turn off the transaction logfile when loading
• Bottlenecks
• Server processor should never exceed 80-90
  capacity
• Memory buffer cache hit rate should always be
  below 90
• Average disk queue length should not exceed 2

10
Performance (contd)

• Logfiles (check these)
• Giomgr.log client connection information
• Sde.log detailed error logs
• Sdedc.log direct connect information
• Upgrade.log patch information
• Compress the database at least once a week
• The single most important thing that will effect
  performance of your geodatabase is The of
  states for a version (Lineage)

11
Distribution

• Geodatabase Replication
• Single Generation (9.0) (a.k.a. Disconnected
  Editing)
• Check out from one versioned GDB to another
• Check changes back into original
• Multi Generation (9.1)
• 1 way replication
• Used to replicate read only data (mirror)
• Only pumps changes to the child database
• 2 way replication
• Changes can be sent both ways
• Allows to independent sites to edit data and
  exchange edits
• Uses a Global ID (GUID) on each feature
• Works on specific versions
• Synchronization Process (Parent wins, Child wins,
  Manually Reconcile)

12
Distribution (contd)

• Replica Agent (9.1)
• Allows setting up a scheduled task to determine
  when changes move between the databases
• They are developing a web service based way of
  automating change movement
• Enables disconnected synchronization

13
Geodatabase History

• Store changes over time using versions (9.0)
• History of the DEFAULT version of the database
• Developer sample for inspecting the history of an
  individual feature (a features lineage)
• (-) Historical versions cannot be edited
• (-) Significant performance hit if require
  fine-grained history (i.e., OK for snapshot
  archiving, not OK for transaction level
  archiving)
• (-) If schema changes, history is lost.

14
Geodatabase History (contd)

• A Transaction Level History Solution (9.1)
• Each feature class (FC) has an associated history
  table (e.g., Parcel_history) identical to
  business table (except includes in date and
  out date)
• () History not lost with schema change.
• (?) Performance
• (-) Does not capture change in related tables

15
Geodatabase History (contd)

• Version Log (9.1)
• Compare two versions and show me the differences.
• Useful for situation where workflow dictates that
  another person review the edits before committing
  them to the database.