Windows Server 2012, 48Gb ram, SQL 2016 Standard with:
- 1 disk SAS for Windows and SQL binary (C: )
- 2 SSD disk for data (D: and E: )
Talking about performance, what is the better way to put tempdb, mdf and log of my OLTP Database (about 50Gb, about 100 simultaneous users)?
Maybe:
C: – OS and sql binary
D: – MyDB and Tempdb data file
E: – MyDB and TempDB log file
(D: and E: are physical disk, i don't think it's useful for performance creating more Logical partition to divide objects, or not?)
It's the "better" way?
Best Answer
It really depends on your workload but with only two drives you're going to have to make some concessions. Ideally you want to have tempdb, data and log files all on their own drives. There's a lot of nasty things going on in tempDB and you don't want that affecting your production databases.
As I said, you should consider your workload, if tempDB is made to work particularly hard or you've got a database that has few write operations then you might find that you're better off putting tempDB on it's own drive and bundling the data and log files together. In any other situation I'd put tempDB on the same disk as the data files.