Poor multi-version SQL performance when doing queries

Last Published: April 25, 2020


Maintaining SQL performance with versioned queries when having a large number of edits and long lineages.


ArcSDE analyzes the SDE.STATE_LINEAGES table when the table is created during ArcSDE installation or during an upgrade. From that point on, ArcSDE does not analyze the table; the DBA is responsible of it.

Once the database is in a production environment, the number of rows in the table can grow significantly.

If the database statistics for the table are not current and accurate, the instance's optimizer might choose a sub-optimal execution plan for fetching rows. This can have a negative affect on all version queries, because the query performs a selection from the table to detect a version's lineage.

Solution or Workaround

Based on the frequency of new database states created during editing:

  • Set the DBA to analyze the SDE.STATE_LINEAGES table. For example:

    Connect as the SDE user in SQL and issue the following statement:


  • Set the DBA to create a database job that runs periodically (for example, nightly) to ensure the statistics remain current. Nightly updates are only required on tables that change frequently.

Article ID:000004378

  • Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Related Information

Discover more on this topic