How to Configure SQL Server tempdb?

How many tempdb data files should a SQL Server have? And where should they live? What about their growth settings? Should it be by percent? Or, should they grow by a certain size each time? Are there other tempdb configuration settings I should be concerned with?

In short: how should I configure my SQL Server tempdb so that it performs well and doesn’t become a bottleneck? And is there an easy way to tell if my SQL Server tempdb is configured properly?

Let’s start with how should your tempdb be configured.

How should the SQL Server tempdb be configured?

Configuring SQL Server tempdb is a bit of a balancing act. Settings appropriate for one instance may not be right for another instance. And since tempdb can have dramatic implications for the performance for all databases on the SQL instance, it’s important to get it right.

Microsoft has good information on the SQL Docs page to help you optimize the tempdb system for performance. I’ll summarize here, but you should really go read it yourself in SQL Docs.

• Number: This will depend on several factors, including the number logical processors available to the instance. A good starting point is:
  • 1 to 8 processors – create one tempdb file for each logical processor.
  • More than 8 processors – start with 8 tempdb files and monitor. If contention in tempdb becomes a bottleneck, increase the number of data files in increments of 4 and continue to monitor.
• Size: All tempdb data files should be the same size.
• Growth: Set growth increments to a reasonable size, and make sure it’s the same for all tempdb data files. Don’t grow by percentage.
• Location: Place your tempdb files somewhere other than on the operating system drive.

Is my SQL Server tempdb configured properly?

Okay, now that we know a good starting point for our tempdb configuration, are we there? Or do we need to make some changes?

Let’s check.

You can, of course, open SQL Server Management Studio and drill down into the tempdb setting to have a look. Or, you can write some T-SQL queries to return the configuration information. There’s a nice little query on SQL Docs for this.

My favorite way for a quick check, however, is to use the Test-DbaTempDbConfig command in dbatools. Use the command below, replacing the name of the instance with the instance name you’re interested in.

Test-DbaTempDbConfig -SqlInstance sql2019 | format-table -Wrap

Here’s the output from a test system.

The command returns six lines of output. These roughly correspond to the settings mentioned above – file size, location, growth settings, and max size.

The command also checks for Trace Flag 1118. Read more about this Trace Flag in KB328551.

Review your tempdb settings and refine, if needed

Regardless of whether you used the database properties in SSMS, ran a T-SQL query to return the settings, or used the dbatools powershell script above, review the current configuration for your tempdb. Determine if a change is warranted and update the settings, if needed. Add some additional files, resize the existing files, adjust the growth settings, etc.

As a general rule, these changes will require a restart to take effect. (Depending on the specific change, that’s not always the case, but it’s generally better to make the changes during a maintenance window and restart the SQL Server service.)

For more information on configuring your SQL Server tempdb

Want to know more about SQL Server tempdb and how it should be configured? See the following pages.

• tempdb database – SQL Server | Microsoft Docs
• 3 Ways to Find Your SQL Server tempdb Data Files
• How Many tempdb Data Files Should My SQL Server Have?
• TempDB in SQL Server: Temporary Data with Long Term Problems (sentryone.com)

Want to work with The SERO Group?

Want to learn more about how SERO Group helps organizations take the guesswork out of managing their SQL Servers? It’s easy and there is no obligation. 

Schedule a call with us to get started.