Within the OnBase database, the most common tables and indexes that experience significant index fragmentation, particularly in high-volume or highly transactional systems, are the following:
-
hsi.lockkeys – all indexes
-
hsi.loggeduser – all indexes
-
hsi.registeredusers – all indexes
-
his.doccheckout – all indexes
-
hsi.itemlc – all indexes
-
hsi.itemdata – itemdata10
-
Keyword tables with a large number of inserts and deletes via the application, from either user activity or Workflow, in which documents are re-indexed, status values changed, etc.
In general, any table that experiences high volumes of inserts and deletes is a good candidate to monitor for index fragmentation. The sys.dm_db_index_physical_stats dynamic management view can be utilized to determine index fragmentation. The level of fragmentation can then be used to decide whether the index should be rebuilt or reorganized.