Table PArtition Files and FileGroups Vs Performance
We have 100 Million of records in one table. In this case we have five drives available. Due to some performance constrains, I wish to split those into five File groups. View1 : If I placed all five Filegroups in one drive…How will be the performance… View2 : If I placed 2 filegroups in one drive..like 10 file groups in 5 drives…what would be the result. Please suggest which one will choose for better performance..
Short answer: Physically separating filegroups is preferred. Partitioning is a great feature for improving performance of large tables. I typically describe the benefits with 3 general bullet points. 1. It breaks the table into what is essentially a collection of smaller tables (but still referenced as one). Any query filtered for a single key value (or values in a single range) can retrieve all of the necessary data much faster from a single partition. 2. Deleting large sets of data can be much faster using partition switching if you simply want to delete everything in a single partition. 3. Although not required, partitioning can greatly benefit from multiple physical storage locations. Queries that include data from multiple partitions can run in parallel and truly retrieve data simultaneously because they are accessing different physical devices. Without separating the physical storage, those parallel threads still have to wait for physical I/O access.