How should I organize my mdf, log and tempdb on a 3 physical disk server

Question

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?

Answer 1

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.

Answer 2

You're obviously not working with the optimal disk layout, but my suggestions (and reasons behind them) are as follows:

• C: - OS and sql binaris using the Default block size
  • Applications and binaries work better with the default block size and this drive shouldn't be used for data files unless it's an emergency
• D: - MyDB data and Tempdb log file partitioned with 64KB block size
  • Database files (regardless if they are data or log files) generally work better with 64KB block size. Spills to TempDB often happen when large reads are occurring. This configuration will allow for large reads from D:\ to happen while permitting spills (writes) to E:\ occur concurrently, reducing I/O contention as compared to other routes. Be certain to set your TempDB log file to a static size and do not allow for auto-growth. Filling a TempDB log file results in a failed transaction as does filling a data file for a database (reads can still occur). This drive fills, transactions fail, but you don't have a full-scale service outage.
• E: - MyDB log and TempDB data file partitioned with 64KB block size
  • Similarly to the reasons I outline for D:\ above, placing db log files and tempdb data files on the same drive will allow for concurrent reads/spills and as another benefit this will likely allow for larger tempdb data files as database logfiles should grow slowly or remain relatively static in size (as compared to expected growth with data files). As with the tempdb log file, make your tempdb data file(s) consistently and appropriately sized and disable autogrowth. Keep an eye on free space with this drive. If a tempdb data file fills, you have a failed transaction, but if a database logfile fills, you have a database outage for that respective database.

If your server has additional bays, beg/borrow/steal drives and get them in there. If you can completely segregate TempDB off to (ideally) a SSD drive or drives, this is best. Also, it doesn't look like you're running any manner of RAID configuration, so I would also make sure you let the powers at be know the risk associated with a drive failure (which in your case would be disastrous). Budget for drive redundancy soon and get the server reconfigured, or update your resume for job redundancy later.

Answer 3

Do you have any other drives?

It is better to split mdf/ldf as separate and tempdb as separate if you have SSD's premium separate disks available for all these. It is to get maximum IOPS which is what you are trying to achieve here to get better performance.

My suggestion with your hardware:

D: MDF E: LDF + TempDB

It is better to split mdf's/tempdb mdf's into number of processors you have according to your disks to get maximum processing and IOPS

Answer 4

Talking about performance, what is the better way to put tempdb, mdf and log of my OLTP Database (about 50Gb, about 100 simultaneous users)

I would like to say that for performance always expert are suggesting to keep more size transaction file with separate Disk (If you have sufficient Physical Disk available). Even now a days i am preparing for Exadata X3-2 Exam, even Oracle Trainer also Suggest to us. try to keep more transaction growth file separate with OS ( for better performance).

Maybe:

C: - OS and sql binary
D: - MyDB and Tempdb data file
E: - MyDB and TempDB log file

As per my experience & expert recommended suggestion, I will suggest to you according to Hardware configure you can setup your environment below mention like this.

O.S       : C Drive
MDF & LDF : D Drive
Other small file's like Documentation notes etc : E Drive

I hope so that in your place the environment would be Windows.

Note:- Before doing the Development environment in your place always consult with your Development team & Senior one.

Answer 5

This sort of question would be better asked on the database administrators channel but Brent Ozar has some good advice https://www.brentozar.com/archive/2016/01/cheat-sheet-how-to-configure-tempdb-for-microsoft-sql-server/