locked
Question about database performance with low autogrowth sizing RRS feed

  • Question

  • I support the SQL infrastructure for a group here at my organization. I'm not directly responsible for the databases, but they require some help. They pull data on a weekly basis from an Oracle database and import or merge that data into their SQL DB. They've been complaining for a long time about how long this process takes. Sometimes it's multiple hours for a single table. Network is not a bottleneck in this case and the DB's are located on a Flash array, so the disk shouldn't be a bottleneck either.

    I noticed today that they have the autogrowth setting for the database file set at 1MB. I then did a quick check on unallocated space and over half of the size of the MDF file is unallocated. The DB is 575GB's and the free space is showing just over 331GB's. The log file is maintained with hourly transaction log backups and only has 35MB's of unallocated space. 

    So here are my questions. Does the 1MB autogrowth size have any bearing on overall database performance after it's been grown and should I worry about shrinking it since it's more than 50% empty?

    Thursday, October 27, 2016 8:59 PM

Answers

  • It is not helping. Set it to 100 Mg autogrowth.

    You likely have multiple VLFs, check for this:

    DBCC LOGINFO ('DatabaseName') WITH NO_INFOMSGS

    A few hindered could be ok. Thousands will be a problem.

    If you have a large number and you probably will shrink your tlog as much as you can after doing several out of band log backups. Then let the log grow on its own with scheduled log backups (perhaps every 20 min).

    The log size and vlf number should read a steady state.

    The log file could be 99% empty and then grow to 99% full during an index rebuild. This is normal.

    HTH

    Thursday, October 27, 2016 9:27 PM