January 15, 2024 at 11:46 am
Hello.
We have a couple of databases, that were novated as part of an acquisition, that have multiple NDF files as well as an MDF. This is not something that we have ever done deliberately, and the application vendor does not supply the starter DB in this form.
All of our Data files reside on the same Logical Drive. We do not manage the storage infrastructure, nor have much access to it but can find our more about it if I know the right questions to ask.
It may be that where previously hosted the files were places on different drives - but we have zero knowledge of what that looked like - so may have made sense on that basis. However this has come about in our infrastructure just from restoring the DB.
Given that we have a single logical data drive for all files - is there any benefit to leaving the DBs as they are with NDF files - vs draining them all back to an enlarged MDF?
I am not particularly au fait with allocation of extents in this scenario, but don't believe that the same issues are applicable as say Tempdb (SGAM, PFS etc).
I am also not that au fait with RAID implications, LUNs or anything else - the Network drives are just Black Box shares. Back n the day working on DS8000 our placement Best Practices went out the window and we didn't know where the logical volumes ended up once the physical volumes were carved up - never mind Dynamic sparing etc etc - so I wonder if the same applies of if it is just a matter of asking the right questions?
We don't have any problem - that I know of - so don't want to make any changes that might have a negative impact. I am coming at this from a starting point of looking at DB allocations and extending.
TIA for any wisdom.
Steve O.
January 15, 2024 at 8:04 pm
My understanding is this is often done for performance purposes. If you are on spinning disk (HDD) and the MDF + NDF are on the same disk, then the benefits are likely going to be lost. BUT with SSD's where seek time is fast or SAN setup where you can have multiple physical disks presented as a single disk to the OS, or in some RAID configurations, having the MDF and NDF allows you to read and write to the different files with less time waiting for a previous request to complete.
My opinion, this may be a case of "if it ain't broke, don't fix it". If things are working AND you are confident you can do a database restore, moving the NDF's into the MDF may be more work than it is worth for very little benefit.
I think this MAY be a question of "what problem are you trying to solve?" which leads to "how will you know when the problem is resolved?". Will it give you a performance boost by moving the NDFs bac to the MDF? probably not. Will it make things slower? Also, probably not. My opinion, I don't think I'd bother moving the NDF's back into the MDF. With newer SQL versions, tempdb by default creates multiple NDF files as a performance trick so you don't have everyone reading and writing the same file.
If it was me and I just acquired a new system, I would be more concerned with the back end magic of it. Database documentation so I can see how things are linked. Stored procedures, permissions, triggers, certificates, any encrypted data, and after reviewing those and seeing how things work and where the technical debt is, I'd start looking at slow running queries and query tuning. I'd also do some tests on the backup/restore process to make sure that I have known good backups and can successfully restore them. But that's just me.
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.
January 16, 2024 at 8:58 pm
There really isn't any benefit to splitting out the database into multiple data files - unless those data files are on different volumes. The volumes can either be a drive letter or mount point - but need to be separate volumes (not separate drive letters - actual volumes presented to the server).
If the files are all on the same volume - there would be little difference.
With that said, for a large database - splitting the database across multiple MDF/NDF files can have a benefit for your integrity checks. If you can ensure all data files are on separate volumes you can enable a trace flag that could improve the performance of the integrity check process.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 16, 2024 at 10:51 pm
As I understand it, some volumes now have more than one read-head so that even on the same volume you could theoretically get better performance (but, note, I am NOT a hardware person).
At any rate, it shouldn't hurt you to leave them as separate. And it can make it easier to rebuild tables and/or to reclaim space from them, if you ever need to do that.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 17, 2024 at 8:39 am
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
January 22, 2024 at 11:28 am
Thanks all for the responses.
To address a few points -
I am told that the DB files are all on fast SSD - and know they are all in the same Filegroup.
This system was acquired probably 5 years ago - and the attitude at that time was 'if it ain't broke don't fix it'. It has bubbled to the top half of my list of things to get to the bottom of after being given dozens of higher priorities in the interim.
I really do think that this is a result of the DB residing on rotational disks, with limited resources in the past and nobody looking at it since.
I was thinking to drain the NDFs back to a single MDF - as I thought that (like others it seems) there were no benefits to splitting the DB when all MDF/NDF parts are in the same filegroup on the same fast SSD. The unknown for me is the physical placement in the LUN/RAID config and if that might impact.
However there are suggestions that there may still be some benefits to doing this the deeper I dig - I have not had a chance to review the link on piecemeal restores as yet for instance.
All in this is not critical - as people have indicated - and may make no differerence either way.
I probably won't do anything for now - and am looking to get budget for a 3rd party to come in in for a Healthcheck and will add to the list of queries as part of that.
Thanks again.
Steve O.
January 22, 2024 at 9:24 pm
If all of the files are in the same filegroup - then it was almost certainly spread across multiple volumes (drives) originally. There are multiple factors to consider here:
If the database is a large database - I would consider adding separate mount points and moving the existing files to a dedicated mount point - and growing each file to the same size. Even if this doesn't have a noticeable impact on performance - it will have benefits to other processes as the data is spread across the files using that proportional fill algorithm.
Even if the data is on fast SSD's - separating the database across multiple volumes (drive or mount point) can have a positive impact on overall performance of the system.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 23, 2024 at 3:20 pm
The real key here is... are people going to carry you down the hall on their shoulders if you make any changes here? If not, I'd leave it all be. It's causing no pain and it doesn't make restores all that difficult if you practice your restores (which you should on a regular basis).
And, contrary to popular belief, I've found that I get a performance advantage on backups just by having separate filegroups and files for the data. I don't know about just separate files.
There can also be a huge advantage when it comes to space recovery during index maintenance for low page density. Are the separate files based on single large tables and (maybe) their NCIs???
--Jeff Moden
Change is inevitable... Change for the better is not.
January 23, 2024 at 3:33 pm
The real key here is... are people going to carry you down the hall on their shoulders if you make any changes here?
In a coffin?
A bit harsh!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 23, 2024 at 4:09 pm
Jeff Moden wrote:The real key here is... are people going to carry you down the hall on their shoulders if you make any changes here?
In a coffin?
A bit harsh!
Heh... in celebration or in a coffin... you have to consider the impact. 😀 It's like all the people that came before our group that named stored procedures with "sp_"... what's the ROI and other impact of fixing such a thing? Overall, probably no significant good impact.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 23, 2024 at 5:42 pm
Honestly not fussed on the harshness of response if it contributes to moving the discussion forward - obviously meant in good humour.
It does sound like there are different opinions on whether it matters or not which surprised me.
There is a bigger picture here around issues and if this difference is a factor - and if we cannot say conclusively one way or another, whether changing these DBs to be consistent with all others would potentially make a difference - and if there are any negatives - or reasons to not do this. However I am criticised for making questions too complicated so break them down into multiple smaller questions.
The file growth is part of the issue - so interesting that this came up as part of this as that is also on the agenda.
Thanks all - I may not bother with consolidation unless I can find a pressing reason - just need to understand options.
Regards
Steve O.
January 23, 2024 at 5:59 pm
Honestly not fussed on the harshness of response if it contributes to moving the discussion forward - obviously meant in good humour.
It does sound like there are different opinions on whether it matters or not which surprised me.
There is a bigger picture here around issues and if this difference is a factor - and if we cannot say conclusively one way or another, whether changing these DBs to be consistent with all others would potentially make a difference - and if there are any negatives - or reasons to not do this. However I am criticised for making questions too complicated so break them down into multiple smaller questions.
The file growth is part of the issue - so interesting that this came up as part of this as that is also on the agenda.
Thanks all - I may not bother with consolidation unless I can find a pressing reason - just need to understand options.
Regards
Steve O.
To be sure, my response wasn't intended to be harsh. It was intended to suggest that having multiple files just isn't going to matter for much, especially in this day and age. I have found an extreme advantage to having multiple file groups with 1 file per file group for large tables that need rebuilds to recover large amounts of space.
Regardless of who makes what suggestion, it's always best to test because I've seen a whole lot of supposed "experts" and "expert followers" be completely incorrect. And, I can only tell you what I've seen and where I've seen it matter to entice you to do some testing on your own because everyone's workload is different.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply