When splitting out a data file into separate files is there any benefit in having tables usually queried together in the same file?
I'm against it but a vendor has suggested it as part of setup. May help with performance, the DB is currently 400 GB. The files will be on the same drive as well. This will just be a test, so I will split the file on a similar server and then run some tests to see if any performance gain is there. But when splitting the file should tables queried together be kept in (if possible) the same file?
This won't be different disks so not sure if we'll see any benefit from this, the data file is on 4 SSD drives in RAID 10 . I just need to split the file and as I have no idea how the vendors DB is queried without doing extensive monitoring not sure how to group the tables in different files.
I am referring to .ndf
files (filegroups). Is there a best practise in allocating tables to filegroups?