Partitioned Views - Storage of Indexes

Question

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.}

Answer 1

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:

1. Each Table
2. Each Index on Each Table

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.

Answer 2

I see various points to address here:

• partitioning of a large table
• filegroup placement of nonclustered indexes

Starting with the second one, separating non-clustered indexes might not be a good idea as a general purpose approach.
Having storage aligned (rows in a table, along with the indexes dependant upon those rows stored in the same file group) ensures that, if you want to backup or restore a partition, data and the corresponding indexes are kept together.

Other than this, if you will want to have a table with old data and one with just the most recent, you will want to work with SWITCH, which has among others the following requirements:

• The data and index for the source and target tables must be aligned
• Data cannot be moved from one filegroup to another