August 12, 2014 at 3:27 pm
I have a partitioned table but there are some non-partitioned indexes in the table. When I tried doing a switch operation I get the following message:
'ALTER TABLE SWITCH' statement failed. The table 'db_name.dbo.PartitionedTableName' is partitioned while index 'IXNP_PartitionedTableName' is not partitioned
I know I can create non-partitioned indexes on a partition table, but I was aware that a partitioned table cannot switch a partition if the table contains non-partitioned indexes, or indexes partitioned on a different column with a different partition scheme/function.
Any help is appreciated.
August 12, 2014 at 3:46 pm
You cannot switch if there are indexes which are not partition-aligned (created on the same partition scheme as the table). Since you have unpartitioned indexes, you cannot perform a switch operation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2014 at 4:20 pm
Thanks - That's what I thought but hoped wasn't the case. Without the non-partitioned index it is very expensive to query for an individual item when we don't know the partition it is located.
August 12, 2014 at 11:39 pm
N_Muller (8/12/2014)
Thanks - That's what I thought but hoped wasn't the case. Without the non-partitioned index it is very expensive to query for an individual item when we don't know the partition it is located.
Why is it non-partitioned? Is it because it needs to be truly unique?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 13, 2014 at 12:06 am
Quick thought, if the indexes cannot be partition-aligned, how about dropping them before the switch and then re-create them?
😎
August 13, 2014 at 12:53 am
Eirikur Eiriksson (8/13/2014)
Quick thought, if the indexes cannot be partition-aligned, how about dropping them before the switch and then re-create them?
^^ This is what you'd have to do
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2014 at 8:08 am
Jeff Moden (8/12/2014)Why is it non-partitioned? Is it because it needs to be truly unique?
Events are received and stored in the partitioned table by date. Most of the time I know when the event took place and can query based on the date (partition). Sometimes I need to query for a specific event without known when it took place. That's when the non-partitioned index on the event ID comes handy.
August 13, 2014 at 8:08 am
Eirikur Eiriksson (8/13/2014)
Quick thought, if the indexes cannot be partition-aligned, how about dropping them before the switch and then re-create them?😎
That's definitely a possibility. Thanks for the suggestion.
August 13, 2014 at 11:34 am
N_Muller (8/13/2014)
Jeff Moden (8/12/2014)Why is it non-partitioned? Is it because it needs to be truly unique?
Events are received and stored in the partitioned table by date. Most of the time I know when the event took place and can query based on the date (partition). Sometimes I need to query for a specific event without known when it took place. That's when the non-partitioned index on the event ID comes handy.
There's nothing preventing a non-partitioned index from doing this quickly. Index on the event as the lead column and add the partitioning column and it should work just fine.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 13, 2014 at 12:51 pm
Jeff Moden (8/13/2014)There's nothing preventing a non-partitioned index from doing this quickly. Index on the event as the lead column and add the partitioning column and it should work just fine.
Jeff,
I think you meant "nothing preventing partitioned index from doing this quickly". Anyhow, from a performance standpoint, would it be better if the index is non-partitioned? Would you know if there is a significant overhead by having the index on the event ID potentially split into different filegroups and files?
There can be a significant overhead of dropping indexes before the partition is switched out and recreating them after the partition is switched out. Not only the switching operating will take much longer, any queries on the table will not have the benefit of the index while the table doesn't have the indexes.
August 13, 2014 at 2:39 pm
N_Muller (8/13/2014)
There can be a significant overhead of dropping indexes before the partition is switched out and recreating them after the partition is switched out. Not only the switching operating will take much longer, any queries on the table will not have the benefit of the index while the table doesn't have the indexes.
This is where not only size matters but also the edition, best case: prioritize the indexes, initiate the index creation in the priority order and with the Enterprise Edition, ONLINE=ON.
Mind you, dropping the indexes will never give you any performance problem, it is of course the re-creation which can be an effort. Having said this, I don't think it's such a penalty because if the indexes are not partition-align-able, most likely there will be fragmentation when a new partition is slid under. The drop and re-create will eliminate that problem.
😎
August 13, 2014 at 6:11 pm
N_Muller (8/13/2014)
Jeff Moden (8/13/2014)There's nothing preventing a non-partitioned index from doing this quickly. Index on the event as the lead column and add the partitioning column and it should work just fine.
Jeff,
I think you meant "nothing preventing partitioned index from doing this quickly". Anyhow, from a performance standpoint, would it be better if the index is non-partitioned? Would you know if there is a significant overhead by having the index on the event ID potentially split into different filegroups and files?
I certainly didn't spit that one out right. :blush: What I meant was that a partitioned/aligned index having a leading column other than the partitioning column will still be plenty fast. You do need to include the partitioning column in the index (or let the system add it for you but I prefer to explicitly control it to make the code obvious for the next guy) for it to be partitioned and it will slow down leaf level scans a bit, but it won't be a significant change (although this is a part of the reason I tell people not to partition for purposes of performance). As a side benefit, not only will this then allow you to use SWITCH, but it will also allow you to rebuild/reorg the index by partition instead of having to do the whole thing all the time.
There can be a significant overhead of dropping indexes before the partition is switched out and recreating them after the partition is switched out. Not only the switching operating will take much longer, any queries on the table will not have the benefit of the index while the table doesn't have the indexes.
Correct. I'd try making it into a partitioned index as previously suggested so that most of this becomes a moot problem. If it were me, the only way I wouldn't try this is if I had already tried it and it didn't work as well as I said it should.
Now... you don't have to try it on the production table. Try it on a smaller but still substantial sized copy of the production table. Trying it on a different temporary test database in the SIMPLE recovery mode would be best so that you're not killing the production log file in the process. If you can throw it over the wall to a test server, even better.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 13, 2014 at 11:01 pm
Thanks, Jeff. I'll give it a try.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply