Database (de)fragmentation

[FrankDK]

How does fragmentation on the OneStream Datarecord tables impact performance? I have an application with a lot of tables having fragmentation > 90%, but how big of an impact does this have on performance? Or do the Datarecord tables/logic/databuffer work in a different way? Any knowledge/experience on this?

Cheers Frank

[JackLacava · Accepted solution]

Hey Frank,

• If you are a cloud customer, our team will take care of everything. If you think something needs addressing, you can raise a support ticket and they'll get on it.
• If you are on-premises, then it's up to you to maintain the database. On this:
  • The Installation guide says explicitly that "To enhance performance over time in the Application Databases, OneStream recommends re-indexing the Application Databases periodically"
  • You can leverage the "Active Check Update Interval ",  "Fragmentation Iteration Count", and "Fragmentation Percent Threshold" settings in Environment Monitoring section of the appserver config file, to manage how often those issues should be highlighted in logs. The product knows its own thresholds and will alert if things go over them, but won't take steps to fix things, since they might require downtime or service degradation, so it's better if the activities are scheduled by admins.
  • Very large tables will benefit from partitioning. I believe the most obvious candidates are already taken care of, but Solutions or custom tables might require attention (depending on usage patterns).
  • In addition to that, common sense applies - frequent backups, shrinks, etc.