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?

  • 2
    it's not for space savings, it's to allow for better parallel operations. If each query in your system requires an average of 75MB (what's up with that?) then each temp db file needs to be at least 75MB in size. But now tempdb can better serve multiple queries simultaneously as bottnecks in tempdb are reduced. (In majority of cases, each workload is unique, etc, etc). What version of SQL Server are you using? Pre 2016(?) you had to use a trace flag to keep the tempdb files growing at the same rate (or manage it yourself). – Jonathan Fite Jun 27 '17 at 12:37
  • Thanks for that... I'm with you I would rather have better parallel operations, but the reason for asking was the increase in disk space needed to support multiple files. I'll have to explain to the disk space police why I've quadrupled the space needed for tempdb as there is no point making the files smaller than the initial size of a single tempdb, – lemonje Jun 27 '17 at 13:33
  • You don't need extra disk space to support multiple files. If your system needs 1GB (because 100MB is ridiculously low) for tempdb and your system would benefit from 4 files, then use 4 256MB files. You don't need to create 3 extra 1GB files. Divide the number up, and then shrink the first file down so that it's the same size as the others. I had one tempdb that needed over 500GB. It had 8 files. We split the space by 8 for each file. – Tara Kizer Jun 27 '17 at 14:25

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.

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.