SDE Compression Tips and Tricks

1
SDE CompressionTips and Tricks

• Jorge Villalobos

2
Benefits of SDE

• Workgroup to Enterprise databases
• Database backups
• Unlimited space size
• Performance (long transactions)
• Supports Raster data
• Data Integrity
• Multiuser (unlimited number of users)
• Multiuser editing

3
Multiuser Editing

• Grant users permission to edit
• Three options for editing
• Registered as Versioned
• Registered as Versioned with the option to move
  edits to base
• Not Registered as Versioned

4
Versioning

• Registered As Versioned
• Undo and Redo Edits
• Perform Long Transaction Edits
• Allow Archiving or Replica options
• Edits typically done to child versions
• Edits stored in Delta tables until database is
  compressed

5
Versioning

• Registered as Versioned with the Option to Move
  Edits to Base
• Perform versioned edits
• Designed to support 3rd party applications
• Edits stored in delta tables until edits are
  reconciled and posted (moved to base table)
• Cant do the following
• Edit feature classes within a topology or
  geometric network
• Archiving or replication

6
Versioning

• Not Registered as Versioned
• No Delta Tables
• Can NOT Undo and Redo Edits
• Edits saved to Base table immediately after
  saving
• No database compression necessary

7
Versioning

• Delta tables increase in size and number of
  states increase
• ArcGIS has to process delta tables and states
  every time a version is displayed
• Performance is impacted
• Spatial views will not display the latest version
  (Displays base table only)
• Multiversion views needed for querying data
  outside ArcGIS

8
Database Compression

• Moves entries from delta tables into the base
  tables
• Removes unreferenced states and associated delta
  table rows
• Database Performance
• Query performance
• System response time

9
Database Compression

• Compress on a regular basis
• Partial compression (daily)
• Not necessary to disconnect users
• Entries are partially moved to base tables based
  on the state of the version
• Full Compression (weekly, monthly, etc.)
• All connections must be closed
• All versions must be deleted except Default
• All entries are moved to based tables

10
Partial Compression

• Run compress command

11
Full Compression

• Kill all Connections
• SDE Service
• sdemon -o kill t
• Direct Connect
• EXEC KILL _at_SPID
• EXEC SDE_PURGE_PROCESSES
• Removes data from sde_process_information and any
  locks related to connection (spid)

12
Full Compression

• Delete SDE locks
• Close Open States

13
Full Compression

• Delete versions except Default

14
Full Compression

• Run compress command
• Analyze Objects (update statistics)

15
Full Compression

• Delete Orphan Rows

16
Questions?