Spread the Love with tempdb

We just made it through Thanksgiving dinner and there’s another big Christmas dinner coming up in few weeks. Sometimes you might find that one plate is not enough to hold all of that food. Just as multiple plates can make big dinners easier to manage, multiple tempdb data files can make the SQL Server workload easier to manage.

Multiple Man

Configuring tempdb with multiple data files helps spread the work but the number of files to use can vary. The most popular recommendation is to have a data file for each logical processor on the system with a max of 8 data files. That means that even if you have 24, 32, or anything beyond 8 logical processors, still stick with 8 data files to start with. If you’re seeing issues with this configuration and are looking to add more, Microsoft recommends adding four files at a time to help performance.

One setting to consider when configuring tempdb is autogrowth. You’ll want to make sure that each of the data files are set to grow consistently. You don’t want a mix of files growing by 10% while others grow by 10MB.

What is the easiest way to avoid this becoming an issue? Have a fast drive completely devoted to tempdb and configure the files to a size that utilizes as much of the drive as possible. That should set you up for success.

How Many tempdb Data Files Do I Have?

Do you want to find out how many data files you have configured for tempdb? You can use SSMS by expanding system databases in Object Explorer, right click on tempdb, select Properties:

Check the Files page:

Run the following if you want to check by running a script:

SELECT *
FROM sys.database_files
WHERE type = 0;
GO

Or if you simply want the number without the extra information you could run:

SELECT COUNT(*) TempDBFiles
FROM sys.database_files
WHERE type = 0
GO

Need More?

I’ll do a post later this week taking a closer look at adding additional tempdb files.

Thanks for reading!