I have a single table that contains 4 billions rows that I plan on splitting up in to separate tables for each Quarter of the year. Then I want to create a partitioned view across all tables.
At the moment I have one filegroup for data (where most of the data resides) and one filegroup for non-clustered Indexes (I have been slowly migrating indexes across to this filegroup)
The data filegroup and non-clustered indexes filegroup are located on different disks.
The individual tables will have primary keys and non-clustered Indexes added to them. Where should I put these? (In terms of filegroups) Should I create filegroups for each table, for each type of index etc?
Note: I can't use Data Partitioning as we don't have Enterprise edition.
Best Answer
If you're going with traditional partitioned views with each table containing a different yearly quarter, then you have a few levels of separation here:
So in other words, say you have 10 tables that the partitioned view accesses, and you have a clustered index and a nonclustered index on each table. In this case you are looking at 20 different data structures that you need to place accordingly.
As to where to put each of these will largely depend on your workload. For instance, if only the most recently two quarter tables get the most report queries and data modification, you'll want to ensure those are separate. But in the same respect, you could benefit from each of those tables having their indexes on separate disks.
I say disks here because filegroups are logical groupings of database data files. Even if you split each table and each index on their own filegroup (exaggerated to illustrate a point) but all the containing data files are on the same set of physical disks then you won't really see any underlying I/O performance gain. It's really just administrative at that point. You can't always divide the physical layer and the logical layer when squeezing performance out of a design.
So you need to visually lay out the physical disk separation, what type of common workload you will be receiving, and design your data placement in that manner.