December 29, 2010 at 11:07 am
I have been researching on partitioning table. Index created on the partitioned table by default is also partitioned. I have also read that "An index does not have to participate in the same named partition function to be aligned with its base table. However, the partition function of the index and the base table must be essentially the same, in that 1) the arguments of the partition functions have the same data type, 2) they define the same number of partitions, and 3) they define the same boundary values for partitions." courtesy, http://technet.microsoft.com/en-us/library/ms187526.aspx
The bottomline answer to the question What exactly is table aligned index? seems to be that Index and Data of the partitioned table must reside in the same filegroup. I came to this conclusion based on i) All indexes on the partitioned tables participating in partition switching must be aligned and ii) Indexes of the partitioned table must reside in the same filegroup as the partition you are switching, courtesy, http://sqlserverpedia.com/wiki/Switching_Partitions_-_Prerequisites i.e, => ii) above implicitly states that indexes and data live in the same filegroup of the partition being switched.
Please forward your thoughts if my bottomline answer regarding what exactly is a table aligned index is correct, wrong or if it somewhere in between i.e, Index and Data of the partitioned table must reside in the same filegroup
Thanks for sharing your thoughts, expertise and time.
+ive
December 29, 2010 at 11:14 am
jeelanik (12/29/2010)
What exactly is table aligned index?
An index that follows the partitioning strategy of the base partitioned table e.g. same partitioning schema and partitioning column - nothing more, nothing less.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 29, 2010 at 11:32 am
Thanks for response.
An index that follows the partitioning strategy of the base partitioned table e.g. same partitioning schema and partitioning column - nothing more, nothing less.
By saying the same partitioning scheme, do you mean the same scheme of the table or a scheme following the same scheme strategy as the table but could have different filegroups for the index.
+ive
December 29, 2010 at 11:46 am
jeelanik (12/29/2010)
Thanks for response.An index that follows the partitioning strategy of the base partitioned table e.g. same partitioning schema and partitioning column - nothing more, nothing less.
By saying the same partitioning scheme, do you mean the same scheme of the table or a scheme following the same scheme strategy as the table but could have different filegroups for the index.
1- Partitioning Schema
2- Sources and Targets must reside on same filegroup - that doesn't mean tables and indexes have to reside on the same, it means table/table-partitions have to reside on same filegroup and, index/index-partiions have to reside in same filegroup - which may be different from the filegroup tables/table-partitions are stored. Reason for this is that when you switch you are not moving data but metadata - data stays put in the same physical place.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 29, 2010 at 12:04 pm
Thanks for the reply.
However, where ever I have researched, there seems to be only one partition mentioned in a switch statement, ALTER TABLE Orders
SWITCH PARTITION 1 TO OrdersOctober2002, courtesy http://msdn.microsoft.com/en-us/library/ms345146.aspx#sql2k5parti_topic24
If the table/table-partitions reside in one filegroup, index/index partitions reside in another filegroup, the above alter table switch partition only moves the table and do we need another alter index switch partition statement to move the index data?
alter index indexname switch partition 1 to indexnameclone
+ive
December 29, 2010 at 12:33 pm
Partitions are switched one at a time.
Index-paritions are switched alongside table-partitions - that's why we want them to be table-aligned.
Time to test, don't you think so? 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 30, 2010 at 11:05 am
Great. You read my mind.
I started testing with a partition table with two schemes, one for data and other for indexes. I was able to switch, split and merge. But, there was one question that I do not have an answer for yet. As part of populating the partitioned table, I populate the data filegroup first. The file in data filegroup shows a size of 20 MB. Immediately after creating an index on the partition table, this 20 MB moves from data file to the index file.
When I populate a similar non partitioned table without index, all 20 MB lives in the data file group. When I create the same index on the non partitioned table, the index file now also holds 20 MB. So there is 20 MB of data + 20 MB of index in a non partitioned table whereas in a partitioned table there is almost 0 MB of data file but 20 MB of index file.
Not sure where to start in trying to understand this behaviour.
Thanks.
+ive
December 30, 2010 at 11:54 am
jeelanik (12/30/2010)
I started testing with a partition table with two schemes, one for data and other for indexes. I was able to switch, split and merge. But, there was one question that I do not have an answer for yet. As part of populating the partitioned table, I populate the data filegroup first. The file in data filegroup shows a size of 20 MB. Immediately after creating an index on the partition table, this 20 MB moves from data file to the index file.When I populate a similar non partitioned table without index, all 20 MB lives in the data file group. When I create the same index on the non partitioned table, the index file now also holds 20 MB. So there is 20 MB of data + 20 MB of index in a non partitioned table whereas in a partitioned table there is almost 0 MB of data file but 20 MB of index file.
Not sure where to start in trying to understand this behaviour.
Not sure what queries are you using to check space allocation but I can assure you space utilization will be - almost - the same when you populate a partitioned and a non-partitioned version of the same table with the same data load.
The word "almost" is there because you may expect a slight overhead on partitioned table.
In regards to seeing data allocated to data or index and, not knowing which queries are you relying on to check that information I would ask a single question... is there a clustered index involved? remember that leaf level of a clustered index is actually part of the base table.
I wouldn't waste my time looking at space allocation - I would focus on functionality 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply