2

I am building a new environment using the SQL Server 2017 Always-On system.

This will be active 24/7 and there will be lots of DML processes.

I have a 100GB SAS for the OS, a 1TB SAS for Data and a 1TB SSD disk for the TempDB and log files.

  • C:/ - 100GB - SAS
  • D:/ - 1TB - SAS - Data files
  • E:/ - 1TB - SSD - Log/TempDB Files

Is the above structure correct for best performance/best practice? Or should I change Log/Tempfile to SAS instead of Datafiles ?

For a single production database, how many Data and Log files do I need to create? 1 data file and 1 log file is best practice?

closed as too broad by Marco, hot2use, Tom V, mustaccio, MDCCL Apr 5 '18 at 15:18

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    So you have single disks? Or a RAID subsystem? Are any of these volumes on a SAN? How is that SAN configured? Have you benchmarked any of the volumes? This question is too broad to answer. – Tom V Apr 5 '18 at 9:17
  • Discs? In 2018? What performance do you expect? I went all SSD 5 years ago and in 2018 there is no reason - particularly not for a small setup you have - to not go all SSD. – TomTom Apr 5 '18 at 10:17
2

Storage perspective Best Practices :

  1. Have separate drives for Data,Log and Tempdb. Do not keep the log files and Tempdb files on same drive as Tempdb is very active and needs good IOPS all the time. So add another drive for Tempdb.
  2. If you can, keep data files too in SSD. This will enhance your performance in case the db is very read/write intensive.
  3. Enable Instant File Initialization.

Also I want to ask that for single production database how much Datafile/logfile need to create? 1 datafile and 1 logfile is best practice?

Adding multiple log files does not help or improve the performance as the logging is done sequentially. Paul Randal has a great blog article where he proved why multiple log file is bad for you.

On data files, having one mdf file is good enough. If your database will be huge then later go for filegroup partition. There may or may not be any improvement in performance if you add multiple datafiles for user databases which is totally unpredicatable.Another blog from Paul Click here where he answers this. In my environment we only use single datafile unless we run out of space and an extra .ndf needs to be added directing data to another drive.

Below is a snippet from Microsoft Documentation :

Storage

Use different disks or disk partitions for the following:

  1. Windows operating system.
  2. Data files for the system databases.
  3. Log files for system and user databases.
  4. Data and log files for the TempDB database.

For optimal read/write performance, make sure that disks that are used for SQL Server data files are formatted using 64 KB block size.

Microsoft Documentation

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