Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Aligned and non-aligned Partition Indexex Expand / Collapse
Author
Message
Posted Friday, July 10, 2009 2:57 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, November 14, 2013 7:42 PM
Points: 326, Visits: 393
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
Post #750896
Posted Monday, July 13, 2009 2:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, November 14, 2013 7:42 PM
Points: 326, Visits: 393
guys please suggest some thing
Post #751863
Posted Monday, July 13, 2009 3:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:10 AM
Points: 6,997, Visits: 8,410
- 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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- 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
Post #751888
Posted Monday, July 13, 2009 5:33 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, November 14, 2013 7:42 PM
Points: 326, Visits: 393
thanks for reply
what about non aligned when it should be used
Post #751935
Posted Monday, July 13, 2009 5:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:10 AM
Points: 6,997, Visits: 8,410
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- 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
Post #751949
Posted Monday, July 13, 2009 6:45 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, November 14, 2013 7:42 PM
Points: 326, Visits: 393
thanks for the reply
Post #751986
Posted Friday, November 19, 2010 4:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 5:13 AM
Points: 103, Visits: 88
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.
Post #1023396
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse