Partition Table vs Archive Performance Considerations

Question

Partition Table vs Archive Performance Considerations

Welsh Corgi

SSC Guru

Points: 116520
More actions
November 20, 2015 at 7:39 am

#306512

I wrote a script to archive very large tables.
It was suggested that I partition the tables as a long term solution.
That sounds great but I'm concerned about what performance impacts may occur with the indexes. A little high risk.
I set up the archive procedure to use a WHILE Loop. It looks up the batch size and it has a flag that is used to stop the Loop.
I'm concerned about the risk of changing the indexes.
Any thoughts or recommendations?
Thank you.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 3 posts - 1 through 3 (of 3 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Partitioning tables is a great tool to manage the archiving process for large tables. However, this is something that has to be well planned and consider all the needs and possible problems.

I'm not sure what are your concerns on the indexes. Are you worried that you need to change the indexing on the tables? Or are you worried about fragmentation? Or "partition" of the indexes?

This might help you: http://www.brentozar.com/sql/table-partitioning-resources/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Luis Cazares (11/20/2015)
Partitioning tables is a great tool to manage the archiving process for large tables. However, this is something that has to be well planned and consider all the needs and possible problems.
I'm not sure what are your concerns on the indexes. Are you worried that you need to change the indexing on the tables? Or are you worried about fragmentation? Or "partition" of the indexes?
This might help you: http://www.brentozar.com/sql/table-partitioning-resources/

Thanks. I read the articles that you provided me.

If it was a Data Warehouse Load I would not be so concerned about performance problems.

If it was a new table I would not would feel more comfortable.

I'm concerned about hurting performance.

If I simply archive the Data no worries but implementing IMHO is very risky.

We are having performance issues and I do not want to contribute to that.

Thanks again.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/