Index Partition Alignment
This is used primarily for two purposes.
•Identify partitioned tables with no incremental statistics. Incremental statistics were introduced with SQL Server 2014 Enterprise edition.
•Identify misaligned or non partitioned indexes on a partitioned table or clustered index/clustered columnstore index.
Partition Alignment Grid
This is a banded grid. Remember you can only pin the band not the column. The reason for the banding is ease of visibility. You can compare the base table/index to other indexes on the table.
where the column is called 'Table' this represented the base index on the table or heap table, where the base index is a clustered index, or clustered columnstore index.
Band |
Column |
Description |
|---|---|---|
Main |
Database |
Database name |
Schema Name |
schema name |
|
Table Name |
Name of table without schema |
|
Table Full Name |
name of table with schema |
|
Index Details |
Index Partitioned |
|
Index Partition Aligned |
|
|
Index Name |
Table |
Name of base table index |
Index |
Name of index |
|
Incremental Statistics |
Table |
|
Index |
|
|
Index Type |
Table |
Type of index |
Index |
||
Partition Column |
Table |
column index is partitioned on |
Index |
||
Partition Alignment |
Table |
Partition alignment Left or Right |
Index |
||
Partition Col Type |
Table |
Partition column type |
Index |
||
Partition Count |
Table |
Partition count |
Index |
||
Partition Scheme |
Table |
Partition scheme |
Index |
||
Partition Function |
Table |
Partition function |
Index |
Notes
For an index to be partition aligned it must meet the following requirements compared to the base table
1.The base table is partitioned
2.Partition function is essentially the same as the base table. An index does not have to be in the same partition function as the table.
3.Partition function has same data type as the base table
4.Same partitioning column
5.Same number of partitions
6.Same boundary values for partitions.
Non Aligned Index
An index partitioned independently of the base table.
1.Different partition scheme
2.Different filegroup from base table
Reasons for Non Aligned Partitioned Index
1.Base table not partitioned
2.Index key is unique and does not contain the partition column of table
3.Partition elimination- query optimizer accesses only relevant partitions satisfying the filter criteria of a query
Additional Reading
Incremental statistics
https://docs.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes