Without going into great technical detail (which on this topic I couldn’t do anyway), it seems after much reading that it is a recommended practice to spread you SQL Server TempDB across multiple files based on how many cores (or perhaps threads) your processor has.
To keep things simple, let’s say I have a 4 core CPU and no hyper-threading (I am not sure if the rule applies to physical cores or to threads), this means I want to split my TempDB up into four different files. However there is one caveat, you should only do this if you actually have four separate physical drives. Not separate files on one drive, not even separate files on separate partitions… this is only beneficial if you actually have separate physical drives based on what I read.
Quick Note: The command blocks below are to give you a reference point for syntax only, do not just copy and paste them, they need to be modified for your server/environment.
Before event getting to the point of adding new TempDB files, you might run into this issue first… someone stuck the initial tempdb file on the wrong drive… So you might want to start with this statement which “alters” to location of the tempdb file and basically moves it somewhere else:
go
Alter database tempdb modify file (name = tempdev, filename = 'U:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\tempdb.mdf')
go
UPDATE 02/17/2017: I recently ran the above command but rather than using a sub-directory tried to place my tempdb file directly on the drive root… i.e. ‘U:\tempdb.mdf’ …. This was apparently a mistake and SQL wouldn’t start. Ultimately I had to start SQL server in master only mode (so only the master database is active), then use SSMS and re-run the query after creating a sub-directory for the file to live in. I have no idea why I had to do this but once I did everything worked out fine. Lesson learned though, don’t put the file directly on the root of a drive…
That moves the tempdb file to the location specified in “filename” – you will need to restart SQL services for this to take affect.
Okay, if all of the above about having multiple physical disks in your system is true, then the SQL statement you want to use to add three additional tempdb files looks like this:
ADD FILE (NAME = tempdev2, FILENAME = 'W:\TempDB\tempdb2.mdf', SIZE = 256);
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev3, FILENAME = 'X:\TempDB\tempdb3.mdf', SIZE = 256);
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev4, FILENAME = 'Y:\TempDB\tempdb4.mdf', SIZE = 256);
GO
SQL server will now have the original tempDB file plus 3 additional files at 256 Mb each. The size of the new files should match the size of the original… i.e. all four files should be the same size.
I am not a Database admin by any stretch of the imagination so if something above is drastically incorrect feel free to correct me in the comments.
Cheers
Reference:
http://stackoverflow.com/questions/719869/how-to-spread-tempdb-over-multiple-files