TempDB Sizing Question

Question submitted at SteveStedman.com:

Is there any formula to calculate size of TempDB?

There are some great formula’s out there for instance to calculate the max server memory setting (https://bornsql.ca/s/memory/), however calculating the size of TempDB is not that simple?

Consider the situation of 2 SQL Server with the same size databases, exact same hardware, the same tables, but the programmers on one were very kind on their use of TempDB and the programmers on the other were very rough with TempDB. What I mean by being rough is excessive use of TempDB.

In one scenario you may get away with a very small TempDB file, but on the other you may need something significantly larger with much faster storage.

Think of the question of how much milk you need to buy at the store for a family. Some families drink more milk and other drink less milk. Unless you really understand the milk consumption habits for that family you have no way of knowing how much milk you need to buy for them. You can either study and learn over time, or simply always buy too much milk so that they never run out. TempDB is the same way, but the difference between TempDB and milk is that TempDB won’t spoil over time.

The thing with SQL Server database files, and TempDB is no different, is that if you have autogrowth turned on (which I hope you do), and you don’t have autoshrink turned on (which I hope you don’t), then your database will stay the same size or grow over time, it will never get smaller unless you manually shrink it.

Take the example of a SQL Server load that typically requires a TempDB size of around 6gb on a daily basis, but once a month there is a job that runs that is really heavy on TempDB and it takes up 20gb of TempDB. What will happen is that in the beginning your TempDB will grow to about 6gb in size, then it will hit that monthly job that causes it to grow to 20gb, which will be slowed down because growing the file is slow. Then it will stay at 20gb even though 6gb is only needed daily. Next month when the job runs it will use the 20gb that is available, with no additional growth.

You might think that it would be good to add a daily job to shrink the TempDB files to help recover from this situation, but all that will do is give you back (in this example) the 14gb of disk space. If that 14gb is disk space is more important than performance, then perhaps might need some more disk space. Keep in mind that growing the file is slow, and shrinking the file is even slower.

Now give that 6gb daily and 20gb monthly scenario, I would want to size TempDB to slightly more than 20gb.

But then you have someone who writes a crazy ad-hoc query that ends up needing 50gb of TempDB and your 20gb TempDB data file grows to 50gb. It might be that it is okay to leave your TempDB at 50gb just to handle this type of query, or it may be that it makes sense to at a slow time shrink your TempDB back down to slightly more than 20gb.

The simple rule is to size TempDB large enough that you don’t need to be growing it often. Also on systems with multiple cores it is recommended to have multiple TempDB files, and that they are all kept the same size for the best performance.

In reality your TempDB numbers might be smaller or much larger than what is explained, but the key to managing the sizes of TempDB is to monitor how big the files are regularly and how often they are growing.

TempDB Sizing Question

Leave a Reply Cancel reply

Login