Perry Whittle (12/5/2012)
Jeff Kelly-310227 (12/5/2012)
Are there a recommended number of files per Filegroup –some mathematical formula to # of cores/etc…?We understand that typically multiple files are used to spread out the load across different disks but in our case all these disks are the same ‘set’.
This is a Tempdb optimisation technique and not really relevant here. As Gail has explained for better IO throughput you could use multiple files per filegroup, but you have to remember that SQL server will attempt to stripe the files and has to switch between these files and maintain fill stats to support the proportional fill algorithm. As you stated, you don't have the disk setup to support this. In my opinion it's totally unnecessary.
Thx for the time Perry.
You bring up a good point, we should investigate the overhead involved with having 12 files per filegroup (but i suspect it's nothing to be concerned about).
Also you mention totally unnecessary, and if we were not trying to overcome the limitation of 64 MB/sec copy speeds per volume, we would not be doing this. So in our case, it is an unfortunate necessity.
So based on your input, i take away that the only downside you see to this solution is the possible overhead caused by stripping across multiple files.
good feedback, thx again!