25

This blog http://blogs.msdn.com/sqlserverstorageengine/archive/2009/01/04/managing-tempdb-in-sql-server-tempdb-configuration.aspx states that it is a good idea to "Spread TempDB across at least as many equal sized files as there are COREs or CPUs."

So my basic question is how do i actually configure my SQL server to do that. So I right click the tempdb, choose properties, files and then add a file for each CPU? How does it know that it should spread the tempdb across these files? is there a flag I should set?

Have I misunderstood the article?

Jimmymcnulty
  • 822
  • 2
  • 9
  • 11

2 Answers2

40

This tip is best as long as you can spread the additional TempDB files across different hard disks. Otherwise, the different threads which create different temp tables will be in contention for the same physical disk.

You can indeed do exactly what you say to do and the work will be automatically spread across the TempDB data files. This can also be scripted as such:

ALTER DATABASE tempdb
ADD FILE (NAME = tempdev2, FILENAME = 'W:\tempdb2.mdf', SIZE = 256);
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev3, FILENAME = 'X:\tempdb3.mdf', SIZE = 256);
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev4, FILENAME = 'Y:\tempdb4.mdf', SIZE = 256);
GO

to get you three additional files (i.e. 4 CPU cores and 4 physical disks).

Jesse C. Slicer
  • 19,901
  • 3
  • 68
  • 87
  • Thanks Jesse. So I suppose I can also do those alter statement through the GUI. Is it the same for every db file. If I add an extra file to another database SQL server will automatically spread the load over those files? – Jimmymcnulty Apr 06 '09 at 10:38
  • That is exactly how I understand it, yes. Of course, if you do it for more CPUs/disks than you have, the returns will diminish somewhat. This is definitely one of those Your Mileage May Vary situations. Measure your performance early and often! – Jesse C. Slicer Apr 06 '09 at 13:50
  • What about the log files? Do we create new log files to match the new data files? Or are the corresponding log files auto created by SQL Server as and when needed? – Mark Dec 23 '13 at 11:28
  • 3
    No - log files should not be added and the additional tempdb data files may benefit even if they cannot be on separate disks. To quote Paul Randal, "If all you’re seeing it PAGELATCH_XX contention, separate storage makes no difference as the contention is on in-memory pages. For PAGEIOLATCH_XX waits, you most likely will need to use separate storage, but not necessarily – it may be that you need to move tempdb itself to different storage from other databases rather than just adding more tempdb data files. Analysis of what's stored where will be necessary to pick the correct path to take." – Thronk Apr 07 '14 at 14:51
  • 2
    Also, you don't need to split into 3 tasks ALTER DATABASE tempdb ADD FILE (NAME = tempdev2, FILENAME = 'W:\tempdb2.mdf', SIZE = 256) ,(NAME = tempdev3, FILENAME = 'X:\tempdb3.mdf', SIZE = 256) ,(NAME = tempdev4, FILENAME = 'Y:\tempdb4.mdf', SIZE = 256); GO – Thronk Apr 07 '14 at 15:08
  • 2
    So, a lot of this advice is passe now. If your system actually writes what’s in TempDB to the physical media, you’ve got a bigger issue. That stuff doesn’t ever get persisted to disk - the only time it should is when you’ve completely exhausted the buffer pool. However, you should have 4 files, appropriately (equally) sized, to deal with some very odd low-level latching around space allocation. – Jesse C. Slicer Oct 23 '15 at 19:12
  • 1. Hi I have 6 cores 12GB Ram since I have only 1 SSD HDD should I go for splitting in 4-6 temp files? 2. If I have two HDDs one for DB one for logs on which HDD is best to have the temp DB? – Valentin Petkov Feb 19 '19 at 18:34
9

I'm aware this is rather late, but the advice to create one file per CPU is actually a myth that Paul Randal debunks in his blog:

http://www.sqlskills.com/blogs/paul/post/A-SQL-Server-DBA-myth-a-day-%281230%29-tempdb-should-always-have-one-data-file-per-processor-core.aspx

Basic advice from the article: If you have > 8 cores, use 8 files and if you're seeing in-memory contention, add 4 more files at a time.

SchmitzIT
  • 9,227
  • 9
  • 65
  • 92