Sql-server – ms/write on tempdb data files are dead slow

To move tempdb files, you simply need to do the following:

alter database tempdb
modify file
(
    name = tempdev,
    filename = 'C:\YourNewTempdbDir\tempdb.mdf'
)
go

alter database tempdb
modify file
(
    name = templog,
    filename = 'C:\YourNewTempdbDir\templog.ldf'
)
go

If you want to add a new file to tempdb, you simply need to do the following (provided you want to add it to the PRIMARY filegroup, or create your own):

alter database tempdb
add file
(
    name = tempdb2,
    filename = 'C:\YourNewTempdbDir\Tempdb2.ndf'
)
go

For these changes to take effect, you will need to restart the SQL Server service. So as far as minimizing downtime goes, you are constrained to the amount of time it will take for the service restart. You don't have to worry about moving the pre-existing tempdb database files, as SQL Server always recreates the files and the new locations/files will be created upon service startup.

As for the "1 tempdb data file per core", that is largely a myth. The correct approach is to monitor tempdb file contention for the Page Free Space (PFS), Global Allocation Map (GAM), and the Shared Global Allocation Map (SGAM) pages. Please reference this article to get a query (alternative link) that looks through the sys.dm_os_waiting_tasks DMV to see how much tempdb file contention there is. Then you need to go off of this, instead of just blanketing tempdb with the same amount of files as there are cores. It's the more advisable approach.

One thing to consider is that the log files are sequential writes where as the data files are non sequential. That is one of the reasons for separate LUNs. Log files write faster if they are on their own LUN because the spindles don't have to skip around, just write sequential. If you add in a data file then the spindles have to skip around and you lose some performance. I'm hoping I got the right terminology there as I'm not all that familiar with SANs themselves. The idea behind it should be however.

Frequently vendor recommendations are wrong when it comes to SQL Server. Just because SQL Server has different needs than most applications that use a SAN.