5

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?

enter image description here

  • It would be interesting to see NoOfTimesEventFired as well. – Kin Shah Nov 26 '13 at 16:36
11

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.

5

On my side, I have to admit that I usually find percentage growth a bit easier to manage. With that said, here a few of the things I try to keep in mind:

2

Taking into consideration what @MikeWalsh and @KookieMonster, I would suggest you to monitor your autogrowth events overtime and then go with a number that suits your environment - Applies to both Data and Log files (By getting a number through monitoring and keeping a 10% buffer, it will help you minimize the number of autogrowth events that kicks in due to poor autogrowth settings).

Auto-growth events are expensive operations that slow down the performance of your database. Always, pre-Size your databases and establish appropriate autogrowth settings for your databases based on their growth profile.

One more time to stress, enable Instant File Initialization so Data files can leverage its awesomeness.

Below is how you can check the autogrowth events using the default Trace (which is running by default - unless you have turned it off. You can adjust the script by changing -- change this AS PER YOUR NEED !!)

set nocount ON
if exists (SELECT 1 FROM ::fn_trace_getinfo(DEFAULT) WHERE traceid = 1 AND property = 2) 
begin
IF OBJECT_ID('tempdb..#autogrowthTotal') IS NOT NULL
    DROP TABLE #autogrowthTotal;
IF OBJECT_ID('tempdb..#autogrowthTotal_Final') IS NOT NULL
    DROP TABLE #autogrowthTotal_Final;

DECLARE @filename NVARCHAR(1000);
DECLARE @bc INT;
DECLARE @ec INT;
DECLARE @bfn VARCHAR(1000);
DECLARE @efn VARCHAR(10);

-- Get the name of the current default trace
SELECT @filename = CAST(value AS NVARCHAR(1000))
FROM ::fn_trace_getinfo(DEFAULT)
WHERE traceid = 1 AND property = 2;

-- rip apart file name into pieces
SET @filename = REVERSE(@filename);
SET @bc = CHARINDEX('.',@filename);
SET @ec = CHARINDEX('_',@filename)+1;
SET @efn = REVERSE(SUBSTRING(@filename,1,@bc));
SET @bfn = REVERSE(SUBSTRING(@filename,@ec,LEN(@filename)));

-- set filename without rollover number
SET @filename = @bfn + @efn

-- process all trace files
SELECT
  ftg.StartTime
,te.name AS EventName
,DB_NAME(ftg.databaseid) AS DatabaseName  
,ftg.[FileName]
,(ftg.IntegerData*8)/1024.0 AS GrowthMB
,(ftg.duration/1000)AS DurMS
into #autogrowthTotal
FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg
INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id  
WHERE (ftg.EventClass = 92  -- Data File Auto-grow
    OR ftg.EventClass = 93) -- Log File Auto-grow
ORDER BY ftg.StartTime

select count(1) as NoOfTimesEventFired
        , CONVERT(VARCHAR(10), StartTime, 120) as StartTime
        , EventName
        , DatabaseName
        , [FileName]
        , SUM(GrowthMB) as TotalGrowthMB
        , SUM(DurMS) as TotalDurationMS
into #autogrowthTotal_Final
from #autogrowthTotal
group by CONVERT(VARCHAR(10), StartTime, 120),EventName,DatabaseName, [FileName]
having count(1) > 50    -- change this AS PER YOUR NEED !! 
or SUM(DurMS)/1000 > 60 -- change this AS PER YOUR NEED !! 
order by CONVERT(VARCHAR(10), StartTime, 120)

if exists (select * from #autogrowthTotal_Final)
begin
        select *  FROM #autogrowthTotal_Final
end
else
begin
        SELECT  ' There are minimal to none autogrowth events and these wont affect Database server performance. ' AS COMMENTS
            ,' Your databases are properly sized. Keep up the good work !! ' AS RECOMMENDATIONS
end
end
else
begin

        SELECT ' There is no Default trace enabled. Check [value_in_use = 1] for [sp_configure default trace enabled].  ' AS COMMENTS
            ,' Please enable the Default trace to do Detailed analysis for Auto Growth Events as no information could be obtained as Default trace is not enabled !! ' AS RECOMMENDATIONS
end
set nocount OFF

Below is the output from one of my sandbox that I play around with Autogrowth :

enter image description here

  • 1
    Not that this method is wrong if it works for your environment but a more universal method might be to look at the perf counter SQLServer:Databases::Log Growths for this event, as most systems I see the default trace is disabled. It will at least give count since last restart, can also pull this for those sad SQL Server 2000 instances. – Shawn Melton Nov 25 '13 at 17:59
  • @ShawnMelton Agreed that Perfmon can be used. But I prefer to use Default trace as there is no reason to disable it- its a light weight trace running that is useful for many situations as nicely described in The default trace in SQL Server - the power of performance and security auditing. Also, you can even use this for baseline and then do a trend analysis. – Kin Shah Nov 25 '13 at 18:03
0

As with most things SQL, it all depends, there is no correct autogrowth setting, although bigger tends to be better.

Each grow event takes a lot of effort for SQL server, writing to logs increasing the DB size, so ideally you want to have as few as possible events. You need to find the middle ground between 1MB on a 20MB DB and 50% growth on a 500GB DB. It's all about finding your own balance.

The best is to evaluate what you have and what growth you expect over the next 30 days or 90 days, punch those numbers in is as a % or fixed size and check it again in a month. Rinse and repeat.

As a rule of thumb our default is 10% autogrowth and that works fine for most of our DBs, from 10MB up to the 0.7TB ones. You just need to manage it.

  • 5
    I would never, ever allow a customer to set any file to autogrow at a 10% rate, particularly for log files. The problem is that it takes longer and longer over time, since 10% of a growing file is also constantly growing itself - it's like compound interest, but you're paying, not receiving. – Aaron Bertrand Nov 25 '13 at 15:33
0

Generally no autogroth at all. If it has to be - then no percentage as percentag grows with database size, and expansions are SLOW. 10% on a 1000gb database is a sudden 100gb file expansion - that can really take down performance.

I generally do not allow any autogroth on real databases - only on small low use databases.

  • 4
    Expansions aren't always slow. With instant file initialization, data file growths are very near instantaneous. And I'd rather set this large and have it happen less often. Log file is a different story, since it's not possible to avoid the zeroing-out which is what takes time. – Aaron Bertrand Nov 25 '13 at 15:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.