CODEWITHSUNDEEP
sql-server
Jimmymcnulty
Jimmymcnulty

Reputation: 842

How to spread tempdb over multiple files?

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?

Upvotes: 25

Views: 86401

Answers (2)

SchmitzIT
SchmitzIT

Reputation: 9552

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.

Upvotes: 9

Jesse C. Slicer
Jesse C. Slicer

Reputation: 20157

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).

Upvotes: 40

Related Questions