How to spread tempdb over multiple files?

Question

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?

Answer 1

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