Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server tempdb one or multiple data files


By:   |   Read Comments (7)   |   Related Tips: More > System Databases

Attend a SQL Server Conference for FREE >> click to learn more


Problem

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.

Solution

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
ORDER BY c2

--Results:
--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
ORDER BY c2

--Results:
--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.  

Conclusion

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 Update:


signup button

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





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

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

 

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

 


Monday, July 01, 2013 - 12:42:18 PM - Jen Back To Top

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:

http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/


Tuesday, June 04, 2013 - 3:28:59 PM - Salu Back To Top

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

Great article! Saved me from a fail! :)


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

http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx


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

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 

 


Learn more about SQL Server tools