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?
Please don't try to close this question because it seems very general or open-ended. Life is tough enough as it is. This is a very good question. And I'm sure there are a lot of people out there who could give very helpful, experienced answers to this which would help a lot of people. Just because there might not be one exact answer to this question, doesn't mean it's a bad question. And anyway, if you think about it, there IS one best answer to this question - there is a best way to do things in this - very common - situation.
The details you are asking in a single thread require too much of depth research. The use case varies from project to project.
I recommend you to go in-depth on Storage: Performance best practices for SQL Server on Azure VMs, Microsoft's official document. Go through the Checklist details. Refer the disk type most suitable for your use case based on IOPS. You will get the answers to all your queries within this document.