2

SQL newbie here, trying to understand how tempdb work when there is more than one datafile. To keep this simple, let's assume the server has three tempdb datafiles, each 10GB, totaling 30GB and are all configured not to auto grow. If a user query were large enough to require 25GB of tempdb, would it: A: fail B: fill the first datafile then the second and into the third C: randomly spread the usage across all three datafiles D: something else entirely? If so, what is that something else?

Any insight into this is greatly appreciated.

  • Why don't you just try it? Create some small tempdb files with growth limited, then create a temp table and start adding records to it until it blows up--see if all of the tempdb files grew to max size or just one. – Tony Hinkle Jan 31 '18 at 3:11
  • 3? That's a rather odd choice. Usually you want the number of data files to some multiple/factor of the number of cores. – Aaron Bertrand Jan 31 '18 at 3:39
2

It's a mix of C and D, depending on the free space in each of your tempdb files.

SQL Server uses a Proportional Fill algorithm to determine which data file should be used for any given write. Paul Randall discusses this in detail here.

Provided that your tempdb data files have roughly the same amount of free space, writes will be performed in a round robin fashion (one extent at a time).

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.