Tara-1044200 (1/17/2013)
On sql 2008 R2 which has 40 CPU's and one SSD drive I want to do the following, please throw your concerns here, appreciate it.1. creating multiple tempdb data files (may be 10) on a single SSD drive.
2. pre allocating tempdb size to 1TB with restricted growth, each file 100gb.
3. If 1st file is filled does it uses the second file and so on untill it max out to 1TB or 10 files any query would not fail, correct?
Like most everything else pertaining to SQL Server, the "it depends" answer hold very true for TempDB. IMHO I wouldn't go beyond 1 data files per logical CPU (not to be confused with cores). I would also recommend you pre-size your datafiles to avoid any growth - period (exception being for the log file)...some general rules of thumb are to size total tempdb data files size to about 80-90% of the total drive space, making the single log file approximately double the size of a single data file. So if you you have 4 CPUs you could have:
Tempdb1.mdf @ 150GB
Tempdb2.mdf @ 150GB
Tempdb3.mdf @ 150GB
Tempdb4.mdf @ 150GB
Tempdb_log.ldf @ 300GB
(just an example of course)
Tempdb utilizes its data files in a round-robin manner and should always be of equal size to avoid SQL Server picking the largest data file to use first (SQL will continue to use the larger data file first, until the amount of free space is the same across all the other files...which will alleviate the point of having multiple files in the first place...which can lead to other issues - best to avoid this upfront)
Depending on your SSD drive model, SSD has about 10-20 times the amount of IOPS than regular SAS or SATA drives and are AWESOME/expensive hardware to have! As was previously mentioned, you may want to consider a RAID solution for your tempdb.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience