1

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?

3
  • 1
    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?
    – AMtwo
    Oct 31 '21 at 1:55
  • 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
    – variable
    Oct 31 '21 at 8:20
  • I found this to be of use: brentozar.com/archive/2008/08/…
    – D-K
    Nov 16 '21 at 19:03
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.