I don’t have a SAN, so how many tempdb files do I need? Part 1 — When splitting tempdb into to many files can be counterproductive to performance

You can find some great and overwhelming load of not so good information about optimizing tempdb performance all over the Internet. This post has one purpose — to warn you of the consequences that you will face blindly following one of those “best practices”. Namely, if somebody tells you without even checking what your storage is and how tempdb is being used, that you should have as many tempdb files as processors, watch out. This recommendations comes from ancient, SQL Server 2000 times and since then everything has changed:

1. Modern servers have a lot more CPUs then those that were used more then 10 years ago.

2. SQL Server 2005 introduced, along many others, an important optimization — it may cache temporary objects (temporary tables and table variables) across invocations of a routine (like a stored procedure or a trigger). Because an IAM page and one date page of those temporary object will be kept in cache, they will not need to be allocated and deallocated every time you execute this routine.

This is why the recent recommendation changed to this:

1. If you have less than 8 cores, the number of tempdb files should be the same as the number of cores (cores, not logical processors).

2. Otherwise, the number of files should be 8.

3. If you are experiencing allocation page contention, add more files in 4-file chunks and monitor for this type of contention. Also consider enabling trace flag 1118 before a large number of file is added (enabling this flag means that SQL Server will be allocating full extents to each tempdb object).

You can also find a slightly different recommendation, that the number of tempdb files should be equal to ½ or ¼ of the number of cores.

Let’s see how this recommendation works for directly attached storage (DAS) when all tempdb files are on the same physical disk.

Basic facts

Fact 1. tempdb is a shared resource, which means that it can be monopolized by just one offending session. As a result the whole SQL Server instance may be brought to its knees.

Fact 2. tempdb performance is strictly correlated to the performance of I/O subsystem that it uses.

Fact 3. There can be only one filegroup for tempdb. Hence, SQL Server always uses the proportional fill algorithm plus the round robin one for this database. As a result, all you can do to optimize tempdb I/O performance is to create files of equal sizes and the same amount of data in each of them.

Fact 4. Even very efficient algorithms (like the round robin) have some overhead. Therefore, distributing data across many files is a little bit slower than writing the same amount of data to only one file.

Fact 5. Writing or reading from a single file will never be slower than writing or reading simultaneously to/from many files laid on the same DAS magnetic disk (either SATA or SCSI/SAS). The problem is that the opposite is often true — if your disk cannot sequentially handle many simultaneous I/O request (DAS drivers usually can’t), its performance will drop down by the factor of 10.

Seeing is believing

The test is fairly simple — I am going to execute simultaneously the same query and measure the total execution time. This query is written in such a way that every time it executes, it spills sort to tempdb. As I am using 4 core, 8 logical CPU Intel i7, the tempdb will be split into maximum 4 files. This test will be repeated four times:

1. For a single tempdb file (the data and the log file are on the same SATA 10.000 RPM drive).

2. For a tempdb divided into 4 equal size files (all data files and the log file are on the same SATA 10.000 RPM drive).

3. For a single tempdb file (the data and the log file are on the same SSD drive).

4. For a tempdb divided into 4 equal size files (all data files and the log file are on the same SSD drive).

Before each test, SQL Server is restarted — this is the only way to enforce tempdb configuration changes, and ensure that tempdb files are equally filled with data.

The first configuration looks like this:

USE [master]
GO
ALTER DATABASE [tempdb] 
MODIFY FILE ( NAME = N''tempdev'', FILENAME = N''K:\tempdb.mdf'' , SIZE = 500MB , 
FILEGROWTH = 40MB )
GO

ALTER DATABASE [tempdb] 
MODIFY FILE ( NAME = N''templog'', FILENAME = N''K:\templog.ldf'' , SIZE = 200MB , 
FILEGROWTH = 20MB )
GO

USE [tempdb];
EXEC sp_helpfile;
-----
name    fileid    filename        filegroup    size        maxsize    growth        usage
tempdev    1    K:\tempdb.mdf     PRIMARY     512000 KB    Unlimited    40960 KB    data only
templog    2    K:\templog.ldf    NULL        204800 KB    Unlimited    20480 KB    log only

 

The query I’m going to use comes from Ramesh Meyyappan — if you are not familiar with it, go to http://www.sqlworkshops.com and see his webcasts for additional explanations:

image

The execution time was 25 seconds.

Now let me change tempdb configuration and restart SQL Server:

USE [master]
GO
ALTER DATABASE [tempdb] 
MODIFY FILE ( NAME = N''tempdev'', FILENAME = N''K:\tempdb.mdf'' , SIZE = 125MB , 
FILEGROWTH = 10MB )
GO
ALTER DATABASE [tempdb] 
ADD FILE ( NAME = N''tempdev2'', FILENAME = N''K:\tempdb2.ndf'' , 
SIZE = 125MB , FILEGROWTH = 10MB )
GO
ALTER DATABASE [tempdb] 
ADD FILE ( NAME = N''tempdev3'', FILENAME = N''K:\tempdb3.ndf'' ,
SIZE = 125MB , FILEGROWTH = 10MB )
GO
ALTER DATABASE [tempdb] 
ADD FILE ( NAME = N''tempdev4'', FILENAME = N''K:\tempdb4.ndf'' , 
SIZE = 125MB , FILEGROWTH = 10MB )
GO

 

This time the same queries need 30 seconds to execute.

Now let me drop additional tempdb files and move both primary and log file to the SSD drive:

USE [master]
GO
ALTER DATABASE [tempdb] 
MODIFY FILE ( NAME = N''tempdev'', FILENAME = N''x:\tempdb.mdf'' , SIZE = 500MB , 
FILEGROWTH = 40MB )
GO
ALTER DATABASE [tempdb] 
MODIFY FILE ( NAME = N''templog'', FILENAME = N''x:\templog.ldf'' , SIZE = 200MB , 
FILEGROWTH = 20MB )
GO
USE tempdb
GO
DBCC SHRINKFILE (tempdev2,EMPTYFILE)
DBCC SHRINKFILE (tempdev3,EMPTYFILE)
DBCC SHRINKFILE (tempdev4,EMPTYFILE)
GO
ALTER DATABASE [tempdb] 
REMOVE FILE tempdev2
GO
ALTER DATABASE [tempdb] 
REMOVE FILE tempdev3
GO
ALTER DATABASE [tempdb] 
REMOVE FILE tempdev4
GO

EXEC sp_helpfile;
------
name    fileid    filename    filegroup        size        maxsize    growth        usage
tempdev    1    x:\tempdb.mdf    PRIMARY    512000 KB    Unlimited    40960 KB    data only
templog    2    x:\templog.ldf    NULL      204800 KB    Unlimited    20480 KB    log only

 

After the restart, the query was executed one more time. This time it completed in 6 seconds.

Finally, I have split tempdb into 4 files again, but this time placed all of them on a SSD drive. The execution time stays basically the same, at 6 seconds.

The following chart summarizes the results:

chart

Lesson learned — to really benefit from best practices, you should not pick just one of them. For example, if you decided to ignore the one about placing tempdb on a dedicated, fast disk with many spindles, following the other about splitting tempdb into multiple files may actually hurt you.

In the next part of this series we will see why splitting tempdb into multiple files is considered to be a best practice in the first place.

See you then

Share this article ...

Google Plus
Ihren LinkedIn Kontakten zeigen



This entry was posted in Administration, SQL Server, tempdb and tagged , , , by Marcin Szeliga. Bookmark the permalink.
Marcin Szeliga

About Marcin Szeliga

Since 2006 invariably awarded Microsoft Most Valuable Professional title in the SQL category. A consultant, lecturer, authorized Microsoft trainer with 15 years’ experience, and a database systems architect. He prepared Microsoft partners for the upgrade to SQL Server 2008 and 2012 versions within the Train to Trainers program. A speaker at numerous conferences, including Microsoft Technology Summit, SQL Saturday, SQL Day, Microsoft Security Summit, Heroes Happen {Here}, as well as at user groups meetings. The author of many books and articles devoted to SQL Server.

2 thoughts on “I don’t have a SAN, so how many tempdb files do I need? Part 1 — When splitting tempdb into to many files can be counterproductive to performance

  1. HI Marcin

    Interesting article. Unfortunately, your data does not support your initial conclusion. For spindles, there is clearly some advantage to having fewer files if you expect a lot of sequential traffic. However, note than when you hit a very large number of spindles with a highly concurrent workload (think 8 socket machines with dedicated disk shelves) the benefit of sequential becomes replace with a downside of contention (which is why GUID is faster than IDENTITY columns at high scale – another Mythbusting of SQL Server).

    On SSD/NAND where sequential is equal to random, the sequential benefit of a low number of files is a moot point. Which leaves us with fact 4

    If there was an overhead as per fact 4, you would expect more files to slow down the query, correct? But you do not measure this effect, you only measure that there the runtime is the same (which indicate that you are not creating enough contention to see the benefit of 4 files over 1)…Now, I happen to have measured the effect of contention on large machines all the way up to 128 files in a database (the guidance for tempdb applies to user database too by the way) – and there is no measurable overhead to having more files – fact 4 is a rounding error. There is only a incremental benefit. Admittedly, the benefit is rather small as you go beyond 8 files, there is a very sharp drop in the curve from 1-8, but the benefit is clearly there.

    In other words: I believe the guidance should be:

    If you run on smallish number of spindles: number of files equal number of cores, up to 8
    Every other situation: Number of files equal number of cores.

    • Hi Thomas
      Truth be told I can’t see a reason why splitting a database into multiple files could improve raw I/O performance. And I should say we agree at this point. However, there are some problems (as you said, related to contention) that can be dealt with by adding additional files. And this is the classical example — by creating additional GAM, SGAM and PFS pages we reduce PAGELATCH (not PAGEIOLATCH) tempdb allocation contention. My point is, there is a cost associated with this splitting. In the upcoming article I will try to show some alternative approaches to solve this problem.
      I think you get the main point when you said about pushing hard SQL Server. There are a lot of low-medium sized SQL Servers. Most of those are I/O bound. Hence, by adding unnecessary pressure to I/O subsystem, we actually make thing worse. I said unnecessary because splitting tempdb is a troubleshooting technique — it’s just a clever workaround around PAGELATCH contention.
      This leads to you second great observation — yes, the benefit of having up to 8 tempdb files may overwhelm the cost of splitting. Especially when I/O subsystem is capable of effective handling simultaneous I/O request and if there was a contention problem in the first place. However, for many of those low-medium servers neither of this is true.
      I also agree with you when you were talking about not measuring the effect of adding additional files. When one try to be as simple as possible, there is a risk of oversimplification. Let me postpone this test (I’m not questioning your results at all, but I’m talking about low to medium, not high-end, machines) and share the results later.