I'm using SQL Server running on an Azure VM with 8 SSDs. The SSDs are grouped together in Storage Spaces as 1 disk - in order to increase the capacity and also to combine the IOPS/Throughput. But the "combine the IOPS" part just doesn't seem to be working as far as I can tell by all of my tests/benchmarks (the "combine the throughput" part is working though). In fact, it looks like the SSD performance (IOPS) are better on 1 single disk than the whole 8-physical-disk virtual disk. So, I'm thinking about just forgetting about Storage Spaces and splitting up my data across 8 disks.
But what would be the best way to do that? (I don't have much experience with mulitple files, or filegroups, or partitioning tables, and that sort of thing.)
Just make 8 mdf files (1 on each disk) and let SQL Server redistribute the data across all of these files? If so, I would like to know how SQL Server knows which disk a given record is on. Would doing this speed things up?
And maybe split up the ldf files too?
What about multiple filegroups? I really don't know what the practical difference is between multiple files and filegroups.
What about splitting up the big tables somehow by using a partitioning function? Would that help, since now, maybe, SQL Server would "have a better idea" of where (in which file) a given record would be - since that is defined by a partition function?
