Sql-server – n advantage to not partition aligning an index

indexpartitioningsql serversql-server-2008

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?

Best Answer

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.