I have the privilege of managing a large partitioned OLAP table. While reviewing this table I noticed one of the indexes is not aligned with the partitioning scheme. Since the author is not available and carefully crafted Google searches have not returned any useful results I am not sure if this was intentional or accidental.

Is there any reason not to partition align an index on SQL Server 2008?

  • 1
    Possibly. Query your index statistics usage to see if that index is being used. If so, use extended events or a server side trace to capture what queries are hitting that index. If they would perform better on a partition aligned index, great. If not, check to see if they actually need non aligned partitioned indexes. If they don't, I'd say make it partition aligned anyways as future queries can take advantage of it. Of course, if the cost/effort/outage is too high then don't. – Ali Razeghi May 1 '15 at 20:42
  • From what I have read best practice for indexing a partitioned table is to align the index with the partitioning scheme. This particular table got me thinking that I want to understand a use case to not align an index with the partition scheme. I am not trying to solve a specific problem so I do not have a query that needs to be analyzed. – Robin May 1 '15 at 22:43
  • Another reason to design a non-aligned index is a unique index (including primary key or unique constraint) that does not include the partitioning column. In this case, the index must be not partitioned at all or partitioned using a different function/scheme. – Dan Guzman May 2 '15 at 15:16
up vote 11 down vote accepted

The main advantage of not partitioning a (non-unique) index on a partitioned base object is that it works around a long-standing query optimizer limitation related to ordered data requests such as MIN, MAX, or TOP (n) queries.

On a partitioned index, the optimizer cannot generally translate MIN, MAX, or TOP (n) to the same operation per partition, followed by a final global aggregate over the per-partition partial aggregates. The optimizer instead chooses an execution plan that scans all partitions of the index. The exception to this is the single case where the aggregate or top operation is specified over the partitioning column.

I should mention that there are also very good reasons not to have any non-aligned indexes. Choosing to use a non-aligned index would have to be a very informed choice. I have done it myself (rarely) in the past, but in very specific circumstances where the benefits clearly outweighed the costs, or there was no other reasonable alternative.


Article by Itzik Ben-Gan explaining the issue.

There is the obvious issue around some constraints (eg. unique) requiring a non-aligned index.

Other than that the non-aligned indexes have a big cost (primarily they prevent many parallel operators from going thread-per-partition and the alternatives are memory expensive) and thus I would strongly advise against such indexes. Even with the cases listed by Paul, I would still advise against non-aligned indexes.

  • 1
    Yes. Choosing a non-aligned index would have to be a very informed choice. I have done it myself (rarely) in the past, but in very specific circumstances where the benefits outweighed the costs. – Paul White May 2 '15 at 15:26

Non-aligned indexes negate the primary benefit of partitioning which is switching partitions. If you do not depend on that and are partitioning for other reasons (different storage, read-only partitions, incremental stats, ...) then go ahead and create non-aligned indexex.

Non-aligned indexes are useful because you can enforce unique constraints on the entire table with them. Also, non-aligned indexes do not require the partitioning key to be an implicit seek prefix. Imagine having 10000 partitions and querying not based on the partitioning key. The execution plan will then have to seek into 10000 partitions if the index was aligned. The other answers have further examples.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.