Update Statistics is causing DB file to grow in always on envrionment

  • PJ_SQL

    SSCertifiable

    Points: 6596

    Hello!

    When I check what is causing DB files (.mdf) to grow, it is showing as Update Statistics.

    DB files are auto-growing by 1 GB.

    In about a month, it has grown by more than 10 GB.

    Could you please let me know the reason and how to prevent it from this?

     

    Thanks in advance.

     

     

     

  • Site Owners

    SSC Guru

    Points: 80378

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • John Mitchell-245523

    SSC Guru

    Points: 148675

    It's not clear - what is showing as Update Statistics?  Where did you find that information - in a log file, an audit, or somewhere else?

    John

  • PJ_SQL

    SSCertifiable

    Points: 6596

    It is showing in extended file events and also from script which shows what is causing database growth.

  • John Mitchell-245523

    SSC Guru

    Points: 148675

    OK, good.  Please will you share the relevant snippet from the Extended Events file, and the script you're using and the results it returns?

    John

  • PJ_SQL

    SSCertifiable

    Points: 6596

    I have attached the extended event snippet.

    I am using the script from https://ola.hallengren.com  and executing the script below from SQL Agent:

    EXECUTE dbo.IndexOptimize @databases = 'USER_DATABASES',

    @FragmentationLow = NULL,

    @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

    @FragmentationLevel1 = 5,

    @FragmentationLevel2 = 30,

    @UpdateStatistics = 'ALL',

    @OnlyModifiedStatistics = 'Y',

    @LogToTable = 'Y'

    Attachments:
    You must be logged in to view attached files.
  • John Mitchell-245523

    SSC Guru

    Points: 148675

    That's not (just) updating statistics - it's index maintenance.  When you rebuild an index, you should plan to have at least the same amount of free space in your database as the size of the index, and possibly more.  If you don't, the data file is likely to have to grow.

    John

  • PJ_SQL

    SSCertifiable

    Points: 6596

    Thank you for the reply.

    Right now, allocated space for the mdf file is 185746.88 MB and free space is 19268 MB(10%).

    So, how much of free space do I need? and how can I find the size of the index?

  • John Mitchell-245523

    SSC Guru

    Points: 148675

    Your stored procedure will process indexes one by one, I believe, so you need as much free space as the size of the largest index (or half as much as that again, just to be safe).  The largest index will usually be the clustered index on the largest table (assuming you have a clustered index on all your tables).  You can find out which table that is in SSMS by right-clicking on the database name and choosing Reports -> Standard Reports -> Disk Usage by Top Tables.

    John

  • PJ_SQL

    SSCertifiable

    Points: 6596

    I used sp_spaceused to find size of index and it is 40 GB and free space on the DB is around 19 GB.

    Will adding additional 40 GB or more to the disk where mdf files reside will help resolve this issue of DB growth during index rebuild?

     

     

  • John Mitchell-245523

    SSC Guru

    Points: 148675

    If that's your largest index then yes, that will probably be enough.  Don't forget to allow also for the growth of your data over time.

    John

  • PJ_SQL

    SSCertifiable

    Points: 6596

    Thank you so much for quick response, appreciate it.

  • Jeff Moden

    SSC Guru

    Points: 996098

    John Mitchell-245523 wrote:

    That's not (just) updating statistics - it's index maintenance.  When you rebuild an index, you should plan to have at least the same amount of free space in your database as the size of the index, and possibly more.  If you don't, the data file is likely to have to grow.

    John

    It's probably not due to index maintenance.  See the following setting in the code above...

    @OnlyModifiedStatistics = 'Y',

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 996098

    PJ_SQL wrote:

    I used sp_spaceused to find size of index and it is 40 GB and free space on the DB is around 19 GB.

    Will adding additional 40 GB or more to the disk where mdf files reside will help resolve this issue of DB growth during index rebuild?

    It sounds to me that free space problem might be due to an "improvement" on SQL Server 2016 and above "lovingly" known as "Fast Inserts".  If code uses certain type of bulk inserts (like "INSERT BULK", which should not be confused with "BULK INSERT") the code will generate an entire extent just to create 1 row (if the code is RBAR in nature even behind the scenes).  As you can well imagine, that will cause a huge amount of unused space, which is allocated to a certain object and will not be used to fill in other rows.  It supposedly only affects databases in the BULK LOGGED or SIMPLE Recovery Models.  I've not taken the time to prove that it doesn't happen in the FULL Recovery Model.

    So, see the following link on Trace Flags, find Trace Flag 692, and read about it.

     

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • John Mitchell-245523

    SSC Guru

    Points: 148675

    John

    Jeff Moden wrote:

    John Mitchell-245523 wrote:

    That's not (just) updating statistics - it's index maintenance.  When you rebuild an index, you should plan to have at least the same amount of free space in your database as the size of the index, and possibly more.  If you don't, the data file is likely to have to grow.

    John

    It's probably not due to index maintenance.  See the following setting in the code above...

    @OnlyModifiedStatistics = 'Y',

    Mmmm... the parameter is @OnlyModifiedStatistics, not @OnlyModifyStatistics.  As I understand it, all that means is that statistics are only updated if there have been any modifications to the data.  It doesn't mean that indexes are left untouched.  This is how the documentation suggests doing statistics only:

    EXECUTE dbo.IndexOptimize
    @Databases = 'USER_DATABASES',
    @FragmentationLow = NULL,
    @FragmentationMedium = NULL,
    @FragmentationHigh = NULL,
    @UpdateStatistics = 'ALL'

    John

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply