Table Partitions

Question

Table Partitions

Kris-155042

Hall of Fame

Points: 3460
More actions
October 2, 2020 at 12:47 am

#3792405

Not sure I'm going to explain this clearly by I'll give it a try.
I have a table that has thousands of records inserted through out the day. Only the latest changes need to be kept so we have setup partitioning on the table. There is a stored procedure that runs to create a new partition every hour and then after 2 weeks it purges the old data. The table originally had a clustered index on it but it was causing performance issues so it was changed to a non-clustered index so now the stored procedure fails with the following message.
Error 4913 caught at line 29: ALTER TABLE SWITCH statement failed. The table 'DV_RAWVAULT.dbo.AsyncIGCMetadataCache_sync_audit_Staging' has clustered index 'IDX_AsyncIGCMetadataCache_sync_audit_Staging_START_TIME' while the table 'DV_RAWVAULT.dbo.AsyncIGCMetadataCache_sync_audit' does not have clustered index. [SQLSTATE 42000] (Error 50000) Purging partition 1 [SQLSTATE 01000] (Error 0). The step failed.
I have very little experience with partitions and have tried to google it but can't find anything on how to recreate the partition on a non-clustered index.
Would it also help if I included the stored procedure that gets run hourly?
Any help would be greatly appreciated and needed.

Thanks,
Kris

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Chad Franklin Grasshopper Points: 16 More actions · Answer 1

Hey Kris-155042,

The issue you're having there is that the main table and your stage table don't have the same index structure. One of the requirements of partition switching is that the source and target tables must have the same column structure and order. The tables basically need to be exactly the same with identical columns, indexes and use the same partition column.

So I would suggest that if you want to keep the changes you've made to the main table, which was converting the clustered index to a non-clustered index, you will need to make the same change in the stage table.

Hope that makes sense. Let me know if that helps.

Cheers,

Chad

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Yep... I agree with Chad on this.

I'd also like to add that I'd be really concerned with the "WHY and WHEN" the clustered index was causing performance issues. If the issues where occurring during, the problem would likely be due to out of order inserts and an incorrect fill factor. If the problem occurs during updates, is the performance issue being caused by "ExpAnsive" updates? There are several different methods for fixing such things especially if the updates are on LOBS. Changing the clustered index to a non-clustered index likely now means that the table is now a HEAP and, if either or both the problems I mentioned above are present, all you've done is delay what will become a much larger problem; Forwarded rows. If it occurs often enough on any given row, you could end up with "Cascading" forwarded rows, which becomes a really nasty problem in the future.

Of equal concern is why this table was partitioned to begin with (easy temporally based deletes, which is a good reason) vs what the clustered index was setup with for key columns. If the clustered index wasn't setup for temporal partitioning, that could be a part of the reason for performance issues along with the "ExpAnsive" update problem, which hasn't actually gone away just because the table is now a HEAP.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 3

Thanks Chad. Jeff, I tend to agree with you as I've never removed a clustered index unless it was a poorly performing one and even then I've generally replaced it with a more effective clustered index. However in this circumstance it wasn't my call so I have dropped the clustered index in the staging table and replaced it with the matching index from the main table and this now seems to have fixed the problem as you both suggested.

Thanks,

Kris

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

I dunno, Kris... Are you sure it wasn't that statistics needed to be updated, which is what you did with the index work you did?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 5

I didn't do it. I've just started here and was told they made this change due to performance and that's what broke the partitioning so could it be fixed.

Thanks,

Kris

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 6

I dropped the clustered index from the audit staging table and recreated a nonclustered index, I then ran the stored procedure and it truncated ALL the data from just the audit table. The stored procedure is to run every hour and only drop partitions older than 2 weeks old not everything. Obviously I have not implemented this in production because of this reason. Why would it truncate all the data? And how can I prevent this from happening in production. Please help.

Thanks,

Kris