Performance of temp db files on SAN

Question

It is generally recommended to put the tempdb database on disks that differ from those that are used by user databases.

I have 4 databases on my sql server, ranging from 5GB to 300GB in size.

D: drive is used for user dbs
E: drive for log files
F: drive for tempdb

Note: the drives are logical. They are not physically separate drives because they belong to SAN. Effectively this means that - based on SAN/RAID level, the data will be physically stored/spread across multiple disks. Is this a concern and going against the above recommendation?

• anon: Tempdb performance needs are generally not driven by the number or size of your database. Instead, it's based on actual workload. Are you seeing any indication that tempdb might be a performance bottleneck for your workload?
• variable: No performance issues, but if you think about it - physically the tempdb data files are spread across multiple disks (SAN) that are also shared by data and log files. There will definitely be performance effect when the number of user dbs/size/workload grows
• D-K: I found this to be of use: brentozar.com/archive/2008/08/…

Answer 1

Is this a concern and going against the above recommendation?

That depends on your workload, number and size of IO operations with data files and TempDB, and SAN configuration (number of LUNs/RAID groups, and how logical drives are tied to these)

If you see any signs of performance degradation:

• messages in the Event log "SQL Server has encountered occurrences of I/O requests taking longer than 15 seconds"
• PAGEIOLATCH wait types on queries
• WRITELOG waits
• Performance counters Avg Disk Sec/Read, Avg Disk Sec/Write became too high
• Slow storage reads or writes: https://www.brentozar.com/blitz/slow-storage-reads-writes/
• Other

Then you may want to move tempDB files to dedicated directly attached SSD drive, to offload its load from the SAN and separate it from data/log files