1

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?

2
  • Best Practices recommemndations says Data and log files should be seperated and they should be kept on separate drives May 10, 2017 at 10:08
  • also the tempdb should be on seperate drive seperate from its log file May 10, 2017 at 10:09

5 Answers 5

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.

1
  • More the second situation, no heavvy work on tempdb but many read/write on software db. I think that dividing data and log files of sotfware db is essenctial, so the real question was where to put tempdb... I think your solution could be the best choice in my environment. Thanx! Paolo May 10, 2017 at 11:49
1

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.

1
  • As i explained in another response, the 2 SSD are 4 physical SSD in raid, so i write 2 SSD to simplify the "logic" ;) 64 Kb for SQL is ok, i know, sql pages are read 8K at a time, so 64K block size are ok for I/O Your "crossed" suddivision is very interesting, i like it! :-) Not sure about setting autogrowth off for TempDB... Initial dimensioning is very important, ok, but a failed transaction is not good... Sure we must trigger alert on disk usage. Very interesting response, thank you Paolo May 11, 2017 at 11:45
0

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

1
  • OK, maybe, or maybe D: MDF + TempDB and E: LDF ... Sure i'd keep MDF and LDF of production DB separed, i must choice where to put tempdb Thanx| Paolo May 10, 2017 at 11:52
0

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.

-2

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/

1
  • Hi, Brent's article is about how divide tempdb on multiple file, i'm talking about how divide data, log e temdb on same server on differente disk, but thank you! ;) Now i move my question on right channel! Thanks a lot Paolo
    – Paolo Bianchi
    May 10, 2017 at 10:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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