Boromir gives tempdb advice

How to Move and Configure tempdb Files

Every SQL Server instance relies heavily on tempdb to function. Because of this it is important to properly setup and configure tempdb. If you work on a system with a sub optimal tempdb setup then you may have to move files. Read on to see a technique for getting this job done.

Review tempdb Configuration Best Practices

Since tempdb is used by all databases on the instance it is imperative to properly configure it. I wrote about how to set up smarter configurations in my SQL Server Environmental Diagnostics Guide.

The basic guidelines are:

  • Each tempdb data file should be the same initial size
  • Autogrowth to tempdb files should be an explicit value in MB instead of a percentage. Choose a reasonable value based on the workload. Ex. 64MB, 128MB, 256MB, etc.
  • The number of tempdb files should be 1 per logical processor core up to 8. At that point the performance should be monitored and if more tempdb files are needed they should be added in sets of 4.
  • Ideally the tempdb files are sized up to the max they will need and never have to autogrow.
  • Use trace flags 1117 and 1118 for versions of SQL Server < 2016. In SQL Server 2016 these trace flags are defaults.
    • Trace flag 1117: when a file in the filegroup meets the autogrow threshold, all files in the filegroup grow together
    • Trace flag 1118: Removes most single page allocations on the server, reducing contention on the SGAM page. TLDR; no more mixed extents – use the whole page.

Here is an example from the wild of how not to configure tempdb.

tempdb horror

There is a lot to unpack here. Pretty much everything that could be wrong is wrong. You may think that things are not this bad in the wild but you would be wrong.

  • The background is that there are about 40 logical CPU cores. I have no idea why there are 60+ tempdb data files because that is way too many. Not only are there diminishing returns to adding more files unnecessarily but it can actually hurt performance. Start with 8 and monitor.
  • Every tempdb file has a different initial size. This is really bad because the round robin and proportional fill algorithms that SQL Server uses to allocate tempdb files depends on the files growing together. Paul Randal has a very interesting post about this.
  • Autogrowth are set to percentages. This is much harder to manage than using concrete MB. The math changes the larger you get: 10% of 1GB is much different than 10% of 1TB.

Shake the horror off and let us see about what we can do to improve the situation.

Move and Configure tempdb

My tiny VM lab has 2 CPU cores. Let’s look at how we can move tempdb from where it currently resides to another location. We will reconfigure it along the way.

How many virtual CPU cores do you have? You can look at task manager to see:

Task Manager CPU Cores

Alternatively you can query:

Here is our starting location:

tempdb files from sys.master_files

tempdb files

The files live here:

starting file location for tempdb files

Now let’s put the files somewhere else and change the initial size along with the autogrowth.

I usually run it first with just the print statement and not the subsequent execution. That way I can give it a sanity check before letting it rip.

PROTIP: leave a print for variables to help debugging

Here is what the SQL will be:

alter tempdb sql

Now we can execute:

move tempdb files output

NOTE: you must restart SQL Server services to see the change in tempdb!

After restarting services here are the new files:

new tempdb files

new tempdb files configuration

The old files will still be here: C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA

Since nothing is pointing to them you can simply delete the files from this location.

Removing Files from tempdb

If you need to remove some tempdb files there is a trick to it. SQL Server will not let you remove the file unless it is emptied.

shrink file empty

Now the file is safe to remove:

remove tempdb file

The file will now be gone from tempdb:

no more tempdb file

If you check the file location you will see that it is no longer there.

Thanks for reading!

if you liked this then you might like Migration – How To Move SQL Server Database Files to a New Location

Did you find this helpful? Please subscribe!

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

One thought on “How to Move and Configure tempdb Files

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.