July 8, 2025 at 4:06 pm
Good morning,
Once again, I find myself Sr. DBA for a company. As I review this latest environment, I see something interesting.
I see one of the main production servers is configured a little differently than I've seen before. I usually place my MDF files in a Data Directory and place LDFs on a Log drive.
This current configuration has four databases. Each MDF is placed on a separate disk for each of the four MDF files. There is a log drive that has all of the log files.
Does anyone know of a best practice that suggests a separate disk drive for each database MDF file rather than placing all of the MDFs on a data drive?
Thanks.
Things will work out. Get back up, change some parameters and recode.
July 8, 2025 at 7:22 pm
I think it really depends on how much the data is going to be changing. Where I work, the data changing in the database is slow. Some of our databases are big, but it is more due to database history than frequent content. So for us, we have the data and log on the same disk and performance is good enough. Plus due to the OS having no visibility to the SAN, even if we had it on different logical disks, it could still end up being the same disk on the SAN.
Having 1 disk presented to a VM (for example) COULD still be 4 disks on the SAN and the SAN is handling the I/O to make sure there is no minimal I/O bottleneck.
For absolute best performance, you should have the data and log files on separate physical disks and on the fastest disks you can find and nothing else on the disk. But this isn't a realistic thing in most organizations. Log on fast disk will give you better performance boost than data on fast disk (based on demo's I've seen and personal experience, but YMMV). Shared disk with SSD's has minimal performance impact as SSD's have very very low seek time (near 0) due to no moving parts. If you have shared disk on HDD's, that'll hurt performance due to seek time. Plus, like I said with the SAN, you as a DBA likely have no visibility into the SAN so your 2 logical disks MAY be 10 SSD's on the SAN.
Best practice from my understanding really depends on your use cases and environment. For example, if the SQL instance is used mostly for reporting with a nightly ETL load, having data and log on slow disk may be good enough. If you are trying to handle 1 million CRUD operations per second, you will want everything on its own SSD. And even best practices have exceptions...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 9, 2025 at 11:04 am
Here are some recommendations documented by MS with regards to your question: Database files and filegroups
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 9, 2025 at 12:19 pm
I've seen all sorts of different setups. To my mind, there's not a single correct approach. It's down to understanding the architecture and where your bottlenecks are. Especially with a bit more of a read heavy system, separating databases to different disks could help alleviate I/O pressure. Writes will be a mixed bag since the logs, always involved in writes, are lumped together, you're only seeing I/O isolation for the database. The other consideration always also has to be hard drive controllers. I've seen people split up storage a ton, only to shove it all through a single controller, just moving the bottleneck around. Part of why all this is so hard is because so many different combinations can work, depending on the situation at hand.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 9, 2025 at 1:42 pm
Thanks everyone. I appreciate your responses and the various perspectives. It is very helpful. There is always something new to learn.
Things will work out. Get back up, change some parameters and recode.
July 11, 2025 at 6:37 am
As has already been said, the disk topology seen by Windows and the DBA in today's systems is typically logical, not physical. It is standard for storage administrators to carve out volumes that span multiple SSDs in the physical storage pool. It is worth you finding out how volumes are constructed in your organisation.
The concept of separating databases on to their own volumes is probably a legacy from the old days of 1 volume equals 1 physical disk or Raid set. It makes very little sense to continue with this pattern today.
Using multiple Windows volumes for SQL server increases the time needed to monitor space usage. The more volumes you have, the greater the risk of one filling up unexpectedly. (The issue of unused space on each volume is seldom a concern, as storage systems typically use sparse provisioning.)
The main reason today to have multiple volumes presented to Windows is managing the back-end storage. Very large allocations can be troublesome to move around the storage subsystem. However, this does not stop the volumes presented to Windows from being joined at the Windows level before being presented to SQL as a single volume.
The main concern of the DBA today should be IO load. It is worth working with the storage admins to understand the IO paths to the storage they provide, and for them to understand the business impact of IO bottlenecks. Getting these two groups taking together should be easy but often is stupidly difficult.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
July 22, 2025 at 2:40 pm
Good morning,
Once again, I find myself Sr. DBA for a company. As I review this latest environment, I see something interesting.
I see one of the main production servers is configured a little differently than I've seen before. I usually place my MDF files in a Data Directory and place LDFs on a Log drive.
This current configuration has four databases. Each MDF is placed on a separate disk for each of the four MDF files. There is a log drive that has all of the log files.
Does anyone know of a best practice that suggests a separate disk drive for each database MDF file rather than placing all of the MDFs on a data drive?
Thanks.
It's dependant upon how much performance is required and how much money you have to cater for all the extra storage required, If cost is no issue then separate disks will separate I\O between databases.
Azure SQL Managed Instance places each database file (data and log) onto separate disks, this is one of the reasons there is a max number of databases per instance.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply