SQLTeam.com | Weblogs | Forums

Partition Aligned Indexes


#1

I am trying to understand the need for partition aligned indexes. We will NOT be switching out partitions.

Are there any advantages for building your table on a partition and creating a nonclustered index on the same partition vs. NOT building your table on a partition and creating a nonclustered index on a partition?


#2

It depends. What kind of table is it? By that, I mean, is the table partitioned by some date period and are the older periods 100% static?


#3

This is an OLTP database.
The tables, if they are to be partitioned would be on a numeric key (an integer), call it an LKey.
Each company that uses our product is assigned a unique LKey.
Therefore, each one of our tables in the database is shared by these various companies, and each companies data in these tables is identified by their unique LKey.
Nearly every (99%) query written in our stored procedures and reports filters on LKey.
The tables I am interested in partitioning vary in size from several million to hundreds of millions, some with older periods static, and others not static.
As stated in my post, we are not interested (at least our primary interest) in using the LKey partitioning is NOT for switching out partitions. It is for partition elimination in queries and reports, reducing query contention and reducing server core usage.

Are there any advantages for building your table on a partition and creating a nonclustered index on the same partition (partition aligned index) vs. NOT building your table on a partition and creating a nonclustered index on a partition (non-partition aligned index)?


#4

Based upon my earlier response, I am assuming that the answer to my question is "no". Thank you for your time and response.


#5

Apologies for the delay. I get several hundred emails a day and I occasionally miss a couple.

Because of the variances in the size and static nature of each of your tables and the fact that you have an eligible partitioning key in the "LKey" column, it would really be nice if MS allowed for a "duplicate partitioning key" consisting of the LKey and the Create_Date of any given row, perhaps by month. The advantage there would be that you could easily move or delete an entire company if the need should ever arise and, since your queries sound like they're usually based on one given company at a time, it may also help you with reducing query contention. Not sure it'll help with reducing server core usage, though.

Of course, partitioning is relatively a PITA, especially if something goes haywire. Further, proper indexing can save you a lot when it comes to reducing contention although you do need good code for that to happen. So far and though a lot of folks have made claims to the contrary, I've not yet seen any examples of partitioning that have actually sped code up. I've beaten a couple of such claims just by following good coding and indexing practices.

To get to your final point, there certainly ARE some HUGE advantages to adding, for example, a non-clustered index that's identical to the clustered index especially on wide tables and especially if the index covers what you need to retrieve. But that doesn't matter if the table is partitioned or not.

My bottom line on partitioning is that you should never partition for performance because it's not worth it. Partition to make maintenance and rolling off old data and, perhaps, importing large quantities of data. The latter 2 of those notion are the reasons why indexes need to be aligned.