Forum Discussion

IL's avatar
IL
New Contributor II
2 days ago

Shrink & reindex, and elastic pool usage

Hi, I have recently reduced a PROD data table by several million rows but the Allocated table size has remained largely unchanged, as the reduction in the Used portion has just shifted to an increase in the Unused portion and the elastic pool usage has also remained unchanged.

I'm assuming that if I run an app copy of PROD into DEV then OS will try to copy over the entire elastic pool size rather than differentiating between what is Used and Unused and only copying over the Used portion.

A shrink and reindex has been suggested as a solution to reduce the Unused portions but I have been advised that running a shrink is a risky move as it can cause performance issues. I therefore wanted to check whether a shrink and reindex in the PROD environment, at least during monthly maintenance, is widely used and generally benefits the environment rather than being detrimental to it?

Data Management

4 Replies

  • IL's avatar
    IL
    New Contributor II
    2 days ago

    Great, thanks very much

  • IL's avatar
    IL
    New Contributor II
    2 days ago

    Thanks, so sounds like performance will slow down when the shrink is being run but provided we run a shrink followed by a reindex during off/down hours then there should be no downturn to performance the next day (in fact, performance may actually improve)?

    • T_Kress's avatar
      T_Kress
      Valued Contributor
      2 days ago

      In general, yes.  The shrink and re-index do have a performance cost, which is why you want to pick an off-hours window. But after they are run, if your performance was related to not re-indexing a database in a while or not shrinking the database, then yes performance should improve.   But that is with the caveat that performance depends on many, many factors.

  • T_Kress's avatar
    T_Kress
    Valued Contributor
    2 days ago

    Database shrinks run monthly (I believe the second Sunday of each month) as a part of OneStream's monthly maintenance window over a weekend.  The shrink will run on any database that was marked as "production" during app creation or app copy.  As far as I know this runs for all Cloud customers, so yes very widely used.

    After deleting a bunch of data or table records, it is quite common that a database shrink would be needed, unless you are willing to wait for the monthly maintenance window.

    There is a performance cost but normally if you work with support, they can schedule it during off or down hours.