7

I have a client's server with about 15 databases on this instance with a data total of about 100 GB. One of the databases is for a sysaid app. his temp db files are one mdf one ndf and of course an ldf. the mdf is on the same drive as the other mdfs and so if the ldf file.

When I set the autogrowth properties I know I should keep it as unrestricted growth but when I need to choose between autogrowth by % or MB what to choose? and how do I know what number to configure?

Thanks in advance!

migrated from stackoverflow.com Jan 25 '18 at 14:28

This question came from our site for professional and enthusiast programmers.

9

For me, the best practice is to just size the files large enough, at the outset, to accommodate the growths you expect.

Let's use some simple numbers. Say you have a 100 GB drive that you've allocated to tempdb (or 100 GB of some shared drive). Your instance has 4 tempdb files, and you've decided to set them initially at 100 MB and to grow 10 MB at a time. Initially, that 100 GB drive or slice looks like this (not to scale, obviously):

enter image description here

As your system uses tempdb resources, each tempdb file will grow, 10 MB by agonizing 10 MB. After a few growths:

enter image description here

Note that each growth event, while likely fast-ish, forces all connections to wait. This is more pronounced when Instant File Initialization is not enabled, and if the files are configured to grow together (which requires a trace flag in some versions, but this is the right thing to do, because you don't want any single file to be bigger than the others).

When you get to the end of the drive, all 4 files will fail to grow at the same time. And this is a business continuity event. When tempdb can't grow here, activity halts:

enter image description here

Now, if you're getting to this point, ever, you need to acknowledge that either you need to change the way you use tempdb, or 100 GB isn't enough and you need to allocate more space. But I would argue that it's better to simply start here and configure this size for your tempdb initial data file sizes:

enter image description here

Well, technically, I would size them at, say, 20 GB each, with a 500 MB autogrowth. This allows you to still be warned well in advance of filling up the 100GB slice, but without paying for all of those incremental file growths in the meantime.

After all, if tempdb is going to grow to fill that space, it's not like you can use it for anything else.

which size should i give the autogrowth

There is no magic number (the defaults have been traditionally way too conservative for practical use, but this is getting better). Your autogrowth setting will be based on the performance characteristics of your disk, and how often you expect to grow. If you have SSD or similar, and have IFI enabled, size is probably much more tied to how much warning you want to have before hitting the end of the slice. If you have spinny, mechanical drives, and/or don't have IFI enabled, you will probably want to test various settings and see where the time it takes to grow the file by different amounts meets your tolerance for all transactions on the system to wait.

  • "I just know the defaults are stupid" Note that SQL 2016+ have fixed the autogrow behavior, making it actually suitable for a default. – David Browne - Microsoft Jan 25 '18 at 22:07
  • @DavidBrowne-Microsoft It’s certainly better; I don’t know if any number is generally suitable. There is no one-size-fits-all, but suitable depends on the hardware and workload. Everything from 2008 -> 2016 was 1 MB for data, 10% for logs. Ugh. So, yes, 64 MB for both is certainly better. Do you know off-hand if you get the new defaults for new databases after upgrading? I haven't tested this and can't recall if the upgrade process changes model. – Aaron Bertrand Jan 25 '18 at 22:45
  • 1
    If there was a one-size-fits-all setting, there wouldn't even be a knob:) – David Browne - Microsoft Jan 26 '18 at 14:47
-4

You should not always set auto growth to unrestricted. If something is wrong you don't want to fill up the disk. And you want a heads up that something is not going as you expected. Pick a big number that you don't think you would need if things are going as expected.

It takes time to create and grow. If you start at 20GB less total time is taken compare to start at 10GB and grow by 10GB once.

Pick a starting number that will fit your normal needs. You can start small and see how big it quickly grows to in normal operation.

A fixed number of 10GB would be 9 growths to get to 100GB. If you double (100%) would be 4 growths to get to 160GB. I usually pick like 10-40%.

You get some fragmentation when you grow but that is not much of a factor unless you are growing a lot (like 20+).

If you have more physical drives then it is typically best to put it on another drive to level out traffic. TempDB has little to no need for redundancy.

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.