July 10, 2009 at 2:57 am
Hi all,
I have some query regarding Aligned and non-aligned Partition Indexes
i would like to know what are the advantages and disadvantages of Aligned and non-aligned Partition Indexes
when Aligned and non-aligned Partition Indexes should be used.
Please suggest !!!
Regards
Ramu DBA
July 13, 2009 at 2:50 am
guys please suggest some thing
July 13, 2009 at 3:43 am
- With aligned indexes, sql will take the most advantage of the mechanisme when querying partitioned objects. (because the indexes will basically have the same algorithm applied internally, so in stead of being one big index, the aligned index will itself be physically split into the number of partitions you have ! )
- To enable partition switching, all indexes on the table must be aligned.
- BOL has good info ! topic "Special Guidelines for Partitioned Indexes "
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 13, 2009 at 5:33 am
thanks for reply
what about non aligned when it should be used
July 13, 2009 at 5:48 am
With partitioned objects, I would avoid nonaligned indexes !
(because it may narrow down features and options to perform tasks.)
Unless you can prove performance is better with a nonaligned index to suite your case.
Keep in mind, that aligned indexes can actually reside in other files. See conditions for an aligned index !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 13, 2009 at 6:45 am
thanks for the reply
November 19, 2010 at 4:02 am
Excerpt from BOL ...
Aligned index :
For aligned index ,Index is partitioned in same manner as the base table, using same partition scheme and partitioning column.
When a table and its indexes are in alignment, SQL Server can switch partitions quickly and efficiently while maintaining the partition structure of both the table and its indexes.
Non aligned Indexes :
Designing a partitioned index independently (unaligned) of the base table can be useful in the following cases:
The base table has not been partitioned.
The index key is unique and it does not contain the partitioning column of the table.
You want the base table to participate in collocated joins with more tables using different join columns.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply