Sql-server – TempDB Configuration using 8 TempDB Data File

1. I would only use RAID 0 for the logs if you're okay with having reduced disaster recovery capability. You won't be able to make log tail backups in the event of a disk failure, and will only be able to restore to your most recent backup. However, this may be less of an issue if you're running Availability Groups, particularly with synchronous mirroring. You generally won't have any performance overhead for RAID 1 vs. a single non-RAID disk, but you will have better I/O performance with RAID 0 instead of RAID 1 for an equal number of disks.

2. Be aware that tempdb also has both data files and log files, and those could end up interfering with each other (performance wise) if placed on the same volume. You may want to consider foregoing the spare disk and using it for tempdb logs instead if performance still isn't acceptable. If your C: array is large enough, you might be able to get away with using it for on-disk backups, though you should probably partition it to prevent runaway backups from filling your OS disk.

Is it possible that this frequency of spills could be a primary culprit in our high tempdb write latency?

Yes it is possible, though typically it is the average size of the spills, and how deep they go (i.e. recursive hash spills, multi-pass sorts) that matters more than the frequency per se.

SQL Server provides a wide range of metrics and DMV information to help you troubleshoot the various contributing factors to tempdb pressure, many of which are discussed in the Microsoft Technical Article, "Working with tempdb in SQL Server 2005" (applies to all versions 2005 onward).

You should be able to use the guidance and diagnostic queries contained in that document to start identifying the primary causes of any tempdb pressure. Do not disregard e.g. version store activity simply because ALLOW_SNAPSHOT_ISOLATION is not enabled. Many features use the version store (e.g. triggers, MARS, RCSI) aside from snapshot isolation.

If sort and hash spills do turn out to be significant at a high level, you will probably need to set up some specific monitoring for this. Depending a little on your SQL Server version, this is not always a straightforward as one might hope. To connect sort and hash spills with the particular query that caused them requires Event Notifications or Extended Events. The SolidQ article, "Identifying and Solving Sort Warnings" contains details and some good general advice about resolving common causes.

You should also work with your storage team to determine how much of the high latency is attributable to your workload, how much comes from other shared uses, and what options there are for reconfiguration. Your analysis of SQL Server's metrics will help inform this discussion, as will any metrics the SAN people are able to provide.