Sql-server – TempDB on Solid State Drives on SQL Server Cluster

Also striping tempdb on the same underlying spindle is just as likely to increase latency as it is to lower it, since this may actually make tempdb access slower - a single drive still has the same moving parts, and the more files you are trying to read/write simultaneously, the worse off you'll be.

I would start with 2 files at most and see if it improves the situation or not. You may even consider leaving one file where it is and adding a second on the new drive (depending on the other activity on the original drive).

Also make sure they are sized exactly the same and they have the same autogrowth setting. As soon as one file is larger than the other, the proportional fill algorithm will only use one file. Consider using trace flag 1117 to make sure that all data files autogrow at the same time, keeping the proportional fill algorithm in check. Just note that this trace flag affects all databases - so if you have other databases with multiple data files, you'll want to make sure their autogrow settings won't cause an issue.

File writes are distributed across files in the same file group proportionally based on the current size of each file in the file group. This is referred to as the "proportional fill algorithm" - look at http://sqlserver-performance-tuning.net/?p=2552 for some interesting details around that.

tempdb can have only a single filegroup. If you attempt to create a filegroup in tempdb you get the following:

Msg 1826, Level 16, State 1, Line 1
User-defined filegroups are not allowed on "tempdb".

If you have trace flag 1117 turned on, files within a group will autogrow simultaneously across files in the filegroup for each file that is not currently at its maximum size, and where space exists on the disk.

Does your instance have trace flag 1117 turned on? You'd probably want it off in this particular instance even though "best practices" often indicate having this turned on. There is an item on Microsoft Connect asking to have a setting like this that can be enabled/disabled on a per-database basis, here: https://connect.microsoft.com/SQLServer/feedback/details/781198/trace-flag-1117-autogrowth-of-data-files-is-instance-wide-would-like-a-flag-for-just-tempdb

Assuming the SSDs are devoted to tempdb, I agree with your assertion and suggest making the tempdb on the SSDs as big as possible (not 100% of the drive, perhaps leave 10% free). Make the tempdb files on disk as small as possible, say 1MB, with autogrowth, and maximum file size as big as you need. Monitor for tempdb filegrowth on the HDDs, and make the case for getting bigger SSDs if you think the company would benefit from it.

According to the documentation, SQL Server files can be created on raw partitions (partitions that haven't been formatted) by using just the drive letter of the partition in the ALTER DATABASE ... ADD FILE syntax. This apparently removes the need to grow or shrink the file since it inherently uses the entire raw partition, as needed. I'm uncertain if this would help your situation or not; just thought I'd throw it out there as an interesting factoid. See "If the file is on a raw partition, os_file_name must specify only the drive letter of an existing raw partition. Only one file can be put on each raw partition."