Storage perspective Best Practices :
- 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.
- If you can, keep data files too in SSD. This will enhance your performance in case the db is very read/write intensive.
- 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 :
Use different disks or disk partitions for the following:
- Windows operating system.
- Data files for the system databases.
- Log files for system and user databases.
- 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.