SQL Server tempdb one or multiple data files

By:   |   Updated: 2010-04-08   |   Comments (9)   |   Related: More > System Databases


Tempdb plays an important role on SQL Server performance. A tempdb database that resides on a slow set of disks or a tempdb database that has been sized incorrectly may have an overall impact on query performance. In this tip I will go over some best practices for performance related to Tempdb.


Best practice recommends placing tempdb on a fast I/O subsystem and to use disk striping to numerous direct attached disks. Best practice, also recommends creating many files to maximize disk bandwidth and to reduce contention in allocation structures. As a general guideline, best practice, suggests creating one data file per CPU. Each file should be set to the same size because this allows the proportional fill algorithm to distribute the allocation load uniformly with minimal contention.

Contention in tempdb is caused with PFS, GAM, and SGAM page allocation when a lot of very small temp tables are created. It is not the goal of this tip to provide a detailed explanation of tempdb contention. The  goal of this tip is to illustrate, with an example, that sometimes the blind application of best practices can be counterproductive to performance.

The SQL code that I will use in this example comes from my previous tip (SQL Server 2008 64bit Query Optimization Trick  http://www.mssqltips.com/sqlservertip/1955/sql-server-2008-64bit-query-optimization-trick/).

Test 1: Using SQL Server with 1 tempdb

 Using SQL Server with 1 tempdb

I ran the following query 3 times (keep in mind that this query spills the sort operation onto tempdb)

--- T-SQL script provided by: www.sqlworkshops.com  
DECLARE @c1 INT@c2 INT@c3 CHAR(2000)
SELECT @c1=c1@c2=c2@c3=c3 FROM tab7 
WHERE c1 100000

--CPU time = 1100 ms,  elapsed time = 11670 ms.
--CPU time = 2190 ms,  elapsed time = 12200 ms.
--CPU time = 1720 ms,  elapsed time = 12630 ms.

Average: (11670+12200+12630)/3= 12166

Test 2: Using SQL Server with 2 tempdb files with the same size files

 Using SQL Server with 2 tempdb files with the same size files

I ran the following query 3 times

--- T-SQL script provided by: www.sqlworkshops.com
DECLARE @c1 INT@c2 INT@c3 CHAR(2000)
SELECT @c1=c1@c2=c2@c3=c3 FROM tab7 
WHERE c1 100000

--CPU time = 1500 ms,  elapsed time = 13740 ms.
--CPU time = 1710 ms,  elapsed time = 14940 ms.
--CPU time = 1610 ms,  elapsed time = 14340 ms.

Average: (13740+14940+14340)/3= 14340

As we can see, on average, when the query runs on 2 tempdb files it is 2174 ms slower then when it runs on a single tempdb file. The reason why is that when sort operations spill over to tempdb they are pretty sequential. If we have two tempdb files, we are making 2 streams of sequential I/O which is not really sequential anymore unless our SAN is configured to handle 2 or more streams of sequential I/O concurrently.

The above picture depicts the two streams of I/O over the two tempdb files

The above picture depicts the two streams of I/O over the two tempdb files. Process Monitor shows that SQL alternates its writing in sequential order over tempdb.mdf and tempdev1.ndf.  


If our subsystem I/O is capable of handling concurrent streams of sequential I/O then the best practice of a tempdb file per core will help our query performance otherwise; it will be counterproductive to performance.

Next Steps

Last Updated: 2010-04-08

get scripts

next tip button

About the author
MSSQLTips author Matteo Lorini Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

View all my tips
Related Resources

Comments For This Article

Saturday, October 27, 2018 - 8:59:00 AM - Matteo Lorini Back To Top (78076)

I do not think you can prioritize the logwrite process because it is a sequential write operation.

Friday, October 26, 2018 - 6:23:06 PM - Brandon Forest Back To Top (78074)

 I understand why the recommendation is to only have one log file. I have a special circumstance why I want two, and I have a question related to that. Our Production SQL Server (SQL2012SP4) was over engineered. The physical boxes (clustered) have 515GB of memory each, of which  450GB are allocated to SQL server. I ran some metrics against peak-load times by totaling the input buffer pages, and determined that SQL was really only using ~224GB. I want to use some of that excess RAM memory to create a 128GB vRam disk, on which I want to put the default TempDB_Log1.ldf file. For safety sake, I am creating a secondary TempDB_Log2.ldf file for any time the vRam disk overflows. My question is, how do I prioritize the .ldf files so that the SQLOS knows to use the .ldf file on the vRam drive first?


Thursday, August 28, 2014 - 5:57:50 AM - mahmoud Back To Top (34304)

Subject: TempDB Adding more files

when the query runs on 2 tempdb files it is 2174 ms slower then when it runs on a single tempdb file. How is this while adding multiple data files to tempdb increase the perfromance?

Wednesday, January 22, 2014 - 2:51:26 PM - Rao Back To Top (28180)


Could you please clarify about Tempdb , if I created  4 ndf files, how the load will distribute to each tempdb file .


Monday, July 1, 2013 - 12:42:18 PM - Jen Back To Top (25652)

Salu- I have always heard that 8 was the upper limit, ie, you can add more files than that, but it will have little effect or benefit.  This article gives some good information and recommends you start with 8 files for a processor of this size:


Tuesday, June 4, 2013 - 3:28:59 PM - Salu Back To Top (25290)

So, if I have 64 cores. Do you suggest to create 64 ndf file for TEMPDB.

Friday, June 15, 2012 - 5:17:31 PM - Eric Schrader Back To Top (18039)

Great article! Saved me from a fail! :)

Sunday, September 12, 2010 - 11:54:14 PM - Patrick Flynn Back To Top (10156)
For a detailed discussion of this issue and considerations around TempDB have a look at Paul Randal's blog post:


Friday, April 16, 2010 - 9:28:43 AM - DavidB Back To Top (5256)

Thanks for confirming what I found as well. I ran performance tests on a database server comparing the tempdb with one and and tempd with one file per CPU. The test using multiple tempdb files took longer. Now I know it was not something specific to our hardware or something I did wrong.

 Thank You 



Recommended Reading

Restoring the SQL Server Master Database Even Without a Backup

Track SQL Server TempDB Space Usage

SQL Server Backup and Restore History Queries

SQL Server TempDB Tutorial

Rebuild System Databases in SQL Server

get free sql tips
agree to terms