SQL Server seperate data files

Question

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?

Answer 1

NDF are not file groups. NDF files are additional data files. You may achieve better performance by adding additional files or you may not, it depends.

Read Benchmarking: do multiple data files make a difference? by Paul Randal.

What I think you had in mind are additional file groups, whereby you create an object in a new file group and put that group into separate file (NDF). To ease I/O, large, frequently accessed tables will achieve better performance in separate file groups, providing those file groups sit in separate files and those files are on different LUNs, and LUNs are on separate spindles, and so on.

If you only have one disk the difference may not be noticeable.

You may also take a look at Using Files and Filegroups from Microsoft.

With SSDs, it depends on your array config and class of the storage. SSDs have no more throughput but lower latency, so you should do some performance testing first, and see if is good enough to satisfy SLA with your production workload. At the end of the day, only thorough performance tests can clear your doubts.