enterprise geodatabase - Does a high number of SDE_state_lineages slow performance?

In our SDE database, the SDE_state_lineages table has around 200,000 records, the SDE_states has around 900, and our SDE_versions table has around 1,300 records.

We've been having performance issues lately. The layers are very slow to draw. We compress daily, but we aren't able to reconcile the existing versions.

Is this high number of records in the SDE_state_lineages causing poor performance?

Answer

The answer is yes that the number of rows in the sde_state_lineages table directly impacts performance of the geodatabase. 200k rows is not considered "alot" but that is relative to your available resources and assuming there are no versioning issues that would require a diagnose/repair. Continue to compress often.

From your naming conventions it looks like you're using SQL Server as your underlying DBMS. The following articles apply to all RDBMS platforms but the queries are for Oracle. They can be adjusted for SQL Server, the same architecture applies. I've done so for the first one.

The official reason is:

When ArcGIS queries a versioned table for any given version, the version's lineage is used to derive the table's versioned representation (by joining the versioned table's adds, deletes and sde.state_lineages table). Performance can be impacted by the length of the version's lineage, because of the number of states that must be joined between the sde.state_lineages table and the delta tables.

HowTo: Report the length of a version lineage in Oracle http://support.esri.com/fr/knowledgebase/techarticles/detail/35500

Here's the query from that page rewritten for SQL Server:

SELECT v.owner +'.'+v.name "VERSION NAME", COUNT(sl.lineage_id) "LINEAGE LENGTH"
FROM sde.sde_states s, sde.sde_state_lineages sl, sde.sde_versions v
WHERE s.lineage_name = sl.lineage_name
AND sl.lineage_id <= s.state_id
AND v.state_id = s.state_id
GROUP BY v.owner, v.name, sl.lineage_name
ORDER BY "LINEAGE LENGTH";

This will tell you exactly how many states each version's lineage contains. This applies to transactional versions as well as replica system versions. You can also adjust this query to find versions that point to the same state as other versions; see #1.

Key Points

From your provided state count of 900 and version count of 1300, and your statement that you never reconcile, you have 400 versions that have never been edited. A version is a label that points to a state, and the only time multiple versions point to the same state is right after they have been reconciled or right after they have been created. I would look into removing those if your work order system allows it, as ArcGIS will scan the versions table for many functions. Your version count of 1300 could be more expensive than your state lineage count of 200k; it depends on what you're doing when you notice a performance hit.

Maintain the indexes on the sde_state_lineages table, which in SQL Server are state_lineages_pk and lineage_id_idx2. The majority of the queries to ArcSDE will perform a full or range index scan. Keeping these indexes up to date after your daily compress operations may assist performance. Here is an article on the topic:

SDE.STATE_LINEAGES table index growth http://support.esri.com/en/knowledgebase/techarticles/detail/20596

• State Identification

You can also query which version a state belongs to as well as how many states belong to a specific object edited by a specific version, using the following article:

HowTo: Discover the number of rows in the adds table for the DEFAULT version's lineage for a specific object class

http://support.esri.com/fr/knowledgebase/techarticles/detail/35030

• Tracking objects by version & delta table

-Here's a query for SQL Server that will give you the current state id of each versioned object along with what version is responsible and what delta table it can be found in:

SELECT v.owner version_owner,
         v.name version_name,
         v.state_id,
         mm.state_id as modified_id,
         mm.registration_id,

         tr.owner layer_owner,
         TR.TABLE_NAME layer_name
FROM sde.sde_versions v
         FULL JOIN sde.sde_mvtables_modified mm ON (v.state_id = MM.STATE_ID)
         LEFT JOIN sde.sde_table_registry tr
            ON (tr.registration_id = mm.registration_id)
ORDER BY tr.owner, TR.TABLE_NAME;

Hope this helps.