patternsqlModerate
Autogrowth - Percentage Use?
Viewed 0 times
usepercentageautogrowth
Problem
Is it best not to use Percentage file growth for autogrowth settings?
The below recommends using percent value growth for databases under 500GB but is this recommended?
http://performance-expert.blogspot.ie/2012/06/tuning-autogrow-settings-of-sql-server.html
If disk space is limited or databases cannot be sized, you should configure the >autogrowth value to a fixed percentage. For example, configure the autogrowth value to >10 percent for databases under 500 GB and to a fixed number of megabytes if a database >exceeds 500 GB.
Thanks!
UPDATE: Below I have added a snapshot of Autogrowth on some databases. Any recommendations on this?
Also what is best way to predict database size?
The below recommends using percent value growth for databases under 500GB but is this recommended?
http://performance-expert.blogspot.ie/2012/06/tuning-autogrow-settings-of-sql-server.html
If disk space is limited or databases cannot be sized, you should configure the >autogrowth value to a fixed percentage. For example, configure the autogrowth value to >10 percent for databases under 500 GB and to a fixed number of megabytes if a database >exceeds 500 GB.
Thanks!
UPDATE: Below I have added a snapshot of Autogrowth on some databases. Any recommendations on this?
Also what is best way to predict database size?
Solution
There are three answers and some great advice is included in each answer in part. That said I wanted to add a bit more from another perspective.
Talking about database files here...
Right Sizing Is Best
As most have said or hinted at - it is far better to "right-size" your database for current and future needs. TomTom is right to point out that there is a performance hit there, but KookieMonster is also right to point out that Instant File Initialization (IFI because I'm lazy) helps that.. Even with IFI - I prefer to find out about the sizing needs of my databases at their deployment and in their possible futures as best as I can. And then I try to size for that plus an "overage" based on what I know about the project/about the potential to grow.... Note: This is not an exact science, and there will be times (many times) that you get this wrong, that's okay.. Autogrowth is a good thing.. I just prefer to try and stay on top of it.
Why? Because I don't want to have that awkward conversation with a SAN admin when I start running out of space. And I don't want a SAN admin to have to make that choice to do some ugly things behind the scenes to give me space. I like to preallocate, watch my free space used in the database over time and use
Auto-Growth As an Emergency Helper
Autogrowth for data files is not evil, especially with IFI. But I like to monitor space used inside of a DB file and use the ability to automatically grow as a band-aid. This way I stay on top of the growth of the databases I'm responsible for as a DBA. Your monitoring regiment should include checking for used space and looking at that.
What Setting Is Best?
So with this in mind, it almost shouldn't matter what you use because you are helping manage the growth yourself. If you see your space used curve steeper than you like, you can look at your calculations and preallocate more space in one big growth. Even still, I am not a big fan of percentages. To me it is non-deterministic and a sign that someone isn't managing the growth in a lot of situations. I just prefer to have that level of control, and I pick a space that I feel is appropriate based on the needs of the database.
Big Caveat
"It Depends" - if your database is small and likely not going to be a big boon on disk ever then I wouldn't cry about keeping it at a percentage or even paying a bit less attention to it's setting. If I go to a client and see a 750GB database still at default growth percentages and no log file management, I cry a little inside. If I go to a client with a 1.25GB database that's been around for 3 years and still set to the defaults? I mention it in my report, but I have the whole "There are best practices.. And then there are situations where you are fine either way" conversation with them.. Now if IFI was disabled, and they had a valid reason to keep it disabled? I'd still probably say something with more seriousness to the smaller database, and I'd really say something like "Let's go crazy and grow this 4 times to preallocate some space" and risk "wasting" 2 to 3 GB of their disk space.
Transaction Log Files
Are a different matter. Log files do not/cannot take advantage of IFI. And they don't like autogrowth because of a little thing called VLF fragmentation (that link takes you to a lot more links all about the topic). I prefer to right size my transaction logs, watch them, and reevaluate what that right size is. I keep autogrowth on (a production transaction log file filling up also makes me cry inside.. actually outside too). And I right size them in "chunks" as per the guidance on the link provided for VLF.
Talking about database files here...
Right Sizing Is Best
As most have said or hinted at - it is far better to "right-size" your database for current and future needs. TomTom is right to point out that there is a performance hit there, but KookieMonster is also right to point out that Instant File Initialization (IFI because I'm lazy) helps that.. Even with IFI - I prefer to find out about the sizing needs of my databases at their deployment and in their possible futures as best as I can. And then I try to size for that plus an "overage" based on what I know about the project/about the potential to grow.... Note: This is not an exact science, and there will be times (many times) that you get this wrong, that's okay.. Autogrowth is a good thing.. I just prefer to try and stay on top of it.
Why? Because I don't want to have that awkward conversation with a SAN admin when I start running out of space. And I don't want a SAN admin to have to make that choice to do some ugly things behind the scenes to give me space. I like to preallocate, watch my free space used in the database over time and use
Auto-Growth As an Emergency Helper
Autogrowth for data files is not evil, especially with IFI. But I like to monitor space used inside of a DB file and use the ability to automatically grow as a band-aid. This way I stay on top of the growth of the databases I'm responsible for as a DBA. Your monitoring regiment should include checking for used space and looking at that.
What Setting Is Best?
So with this in mind, it almost shouldn't matter what you use because you are helping manage the growth yourself. If you see your space used curve steeper than you like, you can look at your calculations and preallocate more space in one big growth. Even still, I am not a big fan of percentages. To me it is non-deterministic and a sign that someone isn't managing the growth in a lot of situations. I just prefer to have that level of control, and I pick a space that I feel is appropriate based on the needs of the database.
Big Caveat
"It Depends" - if your database is small and likely not going to be a big boon on disk ever then I wouldn't cry about keeping it at a percentage or even paying a bit less attention to it's setting. If I go to a client and see a 750GB database still at default growth percentages and no log file management, I cry a little inside. If I go to a client with a 1.25GB database that's been around for 3 years and still set to the defaults? I mention it in my report, but I have the whole "There are best practices.. And then there are situations where you are fine either way" conversation with them.. Now if IFI was disabled, and they had a valid reason to keep it disabled? I'd still probably say something with more seriousness to the smaller database, and I'd really say something like "Let's go crazy and grow this 4 times to preallocate some space" and risk "wasting" 2 to 3 GB of their disk space.
Transaction Log Files
Are a different matter. Log files do not/cannot take advantage of IFI. And they don't like autogrowth because of a little thing called VLF fragmentation (that link takes you to a lot more links all about the topic). I prefer to right size my transaction logs, watch them, and reevaluate what that right size is. I keep autogrowth on (a production transaction log file filling up also makes me cry inside.. actually outside too). And I right size them in "chunks" as per the guidance on the link provided for VLF.
Context
StackExchange Database Administrators Q#53895, answer score: 11
Revisions (0)
No revisions yet.