SQL Server – TEMPDB File Growth with Multiple Files

sql servertempdb

There are a lot of blog postings about splitting tempdb into multiple files, and I understand the logic and reasoning behind this, but there is one question that never seems to get asked about a multi-tempdb architecture. TempDB file growth and disk-space consumption.

The argument is always to grow tempdb files at the same rate to prevent undoing the benefits (and I may be about to answer my own question here).

Does this mean that a query when processed cannot make use of all the tempdb files? If I had a single tempdb of 100MB and most queries needed 75% of this file to process.

Then I decided to split the tempdb into 4 logical files of 25MB each (giving me the same 100MB in total). If a query cannot work with mutliple files at the same time, then it won't take long before all the tempdb files are grown to 75-100MB each and I've effectively quadrupled the space requirements.

And the more files you add to tempdb the more overall disk-space you have to commit. IS that correct or does the query processor use any combination of files if needed?

Best Answer

Answer to your question will depend on the answer to the following.

Most queries need 75% meaning 75MB in your case.

Do you mean all queries combined need 75MB?
Each of those queries need 75MB individually?

If all queries combined need 75MB then you can have multiple smaller files and you will not increase overall space because those files should not grow.

If each query needs 75MB then are they running in parallel or serial? If parallel you must be using more than 100MB today. If serial than I do not see why you need multiple tempDB files.

Are you seeing contention on PFS and SGAM pages?

I suggest you review these articles regarding your question.

Related Solutions

Sql-server – Best practice for tempdb log file

It really depends on how much data is going to flow through the transaction log. Look at how big the log gets today. You need to configure the log to be at least that size when SQL starts up. For most of my clients they end up with a 3-4 Gig transaction log for the tempdb, which contains just a few VLFs and everything works nice and smoothly.

SQL Server – Controlling Tempdb Activity Across Drives

File writes are distributed across files in the same file group proportionally based on the current size of each file in the file group. This is referred to as the "proportional fill algorithm" - look at http://sqlserver-performance-tuning.net/?p=2552 for some interesting details around that.

tempdb can have only a single filegroup. If you attempt to create a filegroup in tempdb you get the following:

Msg 1826, Level 16, State 1, Line 1
User-defined filegroups are not allowed on "tempdb".

If you have trace flag 1117 turned on, files within a group will autogrow simultaneously across files in the filegroup for each file that is not currently at its maximum size, and where space exists on the disk.

Does your instance have trace flag 1117 turned on? You'd probably want it off in this particular instance even though "best practices" often indicate having this turned on. There is an item on Microsoft Connect asking to have a setting like this that can be enabled/disabled on a per-database basis, here: https://connect.microsoft.com/SQLServer/feedback/details/781198/trace-flag-1117-autogrowth-of-data-files-is-instance-wide-would-like-a-flag-for-just-tempdb

Assuming the SSDs are devoted to tempdb, I agree with your assertion and suggest making the tempdb on the SSDs as big as possible (not 100% of the drive, perhaps leave 10% free). Make the tempdb files on disk as small as possible, say 1MB, with autogrowth, and maximum file size as big as you need. Monitor for tempdb filegrowth on the HDDs, and make the case for getting bigger SSDs if you think the company would benefit from it.

According to the documentation, SQL Server files can be created on raw partitions (partitions that haven't been formatted) by using just the drive letter of the partition in the ALTER DATABASE ... ADD FILE syntax. This apparently removes the need to grow or shrink the file since it inherently uses the entire raw partition, as needed. I'm uncertain if this would help your situation or not; just thought I'd throw it out there as an interesting factoid. See "If the file is on a raw partition, os_file_name must specify only the drive letter of an existing raw partition. Only one file can be put on each raw partition."

Best Answer

Related Solutions

Sql-server – Best practice for tempdb log file

SQL Server – Controlling Tempdb Activity Across Drives

Related Question