October 15, 2023 at 11:09 am
I have a database that is growing and growing an management does not want to entertain developing an archival process. We have indexes that are larger than some tables. The immediate thought would be to implement partitioning on the main table within this database. The only time data is removed from the database is when a customer leaves us. All of the other tables with the exception of the main tables are supporting reference tables for the most part. I entertained another idea of only keeping so many years of data in the main table and rolling of the other years to an archive table and build a view that would determine whether to query the main table and/or the main tables archive table. I'm looking for other ideas to this approach
Thanks in advance
October 15, 2023 at 4:43 pm
What problem are you trying to solve here?
If you are not going to be archiving and purging the data - then partitioning really serves no purpose. Your thoughts of moving older data to another table is archiving the data. The only difference is where you store that older data - which then becomes another layer of complexity that needs to be managed.
Do you have page compression enabled for the main tables? Enabling that could save quite a bit of space.
Do you have LOB columns in those main tables? If so - have you set the table option to move that data out of row?
Do you have large VARCHAR columns that are not MAX columns? If you do - then consider either compressing the columns directly or changing them to MAX columns and moving them out of row.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 15, 2023 at 5:42 pm
As Jeffrey stated.
Most importantly, make sure you have the best clustering index on all the main tables so that you limit the scan/search activity on the tables. (Hint: most often the best cluster key is not, repeat NOT, based on an identity column).
Partitioning could be useful if you need to rebuild the active part of the table, since that would prevent you from having to rebuild all the old/archived (in older partitions) data.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 15, 2023 at 9:38 pm
Any chance of you posting the CREATE TABLE statement along with the indexes and constraints? We might be able to make some additional suggestions, once we see it.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 17, 2023 at 1:06 pm
Partitioning is a data management process, not a performance enhancement process. In fact, unless you can with 100% accuracy, guarantee that ALL queries against a partitioned table will be using the partitioning key, 100% of the time, I promise you, partitioning will radically degrade performance. As others have said, what problem are you trying to solve?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 18, 2023 at 6:34 pm
Heh... apparently the OP has left the building!
--Jeff Moden
Change is inevitable... Change for the better is not.
October 26, 2023 at 4:23 am
This was removed by the editor as SPAM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply