Monday, March 19, 2012

[SQL Server 2005] Primary vs Secondary file-groups - deciding which table goes where


I created a set of primary and secondary data-files and organized them into file-groups. Now, I want to decide which tables to allocate to which file-group. What should be the factors on which I ought to decide this? Is there a significance of choosing the primary group over the secondary ones or vice versa? What is the impact of the choice in either case?filegroups in general are all about PERFORMANCE. you can gain significant performance gains if you leverage multiple disks with the multi-filegroup feature of sql server. there is no hard and fast but the general idea is to spread the workload evenly over the disks and to leverage faster disks/cache for the more intensive files|||

Thank you for your reply, Derek. Need one clarification though - would it be correct to say that a table created on a file-group gets evenly distributed across all the data-files in the file-group? In effect, what we are achieving is partitioning the table, right?

My earlier question was more about understanding how SQL Server determines which table belongs to which data-file and file-group. I suppose it might be part of metadata, which some DMV would be able to retrieve. I am not sure but, if this is indeed so, which is the DMV?

A related question that pops up is - should all (or most of the) tables that are most frequently accessed by allocated to the primary file-group? If SQL Server looks at the primary file-group first before scanning the others for the desired table-rows, it would make sense in allocating the most frequently accessed tables to the primary file-group.

No comments:

Post a Comment