Poor query performance on the sde.table_locks table

Description

Poor query performance on the sde.table_locks table can occur as the number of users and the number of shared table locks increase.

Cause

ArcSDE executes various queries against the TABLE_LOCKS table to manage shared and exclusive table_locks for client applications. For example, shared locks are acquired by the geodatabase when a feature class or objectlcass is opened and released when the reference is closed. Exclusive table locks are acquired by the geodatabase when the schema is modified.

Large organizations which use complex ArcMap documents that contain more than a handful of layers can cause poor query performance on the sde.table_locks table. For each layer in the document there will be one corresponding shared lock in the sde.table_locks table. As the number of users and locks increase the number of rows in the table can grow significantly. Therefore, the queries ArcSDE executes against the table will result in reading the entire table, causing sub optimal performance and additional overhead in the DBMS. Under these conditions, an index is required to improve query performance.

The following two queries are examples of the queries which will benefit when an index is present.

'SELECT SDE_ID,LOCK_TYPE,REGISTRATION_ID FROM SDE.TABLE_LOCKS WHERE REGISTRATION_ID = :b1'

'DELETE FROM TABLE_LOCKS WHERE SDE_ID = :b1 AND REGISTRATION_ID = :b2'

Solution or Workaround

Code:
CREATE INDEX <index_name> ON table_locks
(sde_id, registration_id) TABLESPACE <tablespace_name> STORAGE (INITIAL 256K NEXT 256K FREELISTS 4);