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:

Don’t mind some weird values.

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!

One thought on “Spread the Love with tempdb”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s