I have a table in MS SQL Server .
- Table Size: 806 GB
- Rows : 1.2 billion
- Index Space : 1.2 GB
Table Usage: Logging from the Web Service calls 99.9% is usage is from the logging, developers look rarely into this table in Prod(only when an issue is reported or researching).
Primary Key: Based on the "ID" which is of "INT" data type. There's a Clustered Index based on that "ID" column.
My Intentions for this change : Want to manage this table (as it has 10 years of data) and going forward (due to a new requirement), there's a possibility of developers/ analysts to dig into this table further (for few months only) and I don't want to create a new table for same purpose.
My Questions:
[Main question] Can I partition this table on the basis of "DateCreated" (DATETIME, NOT NULL column), without causing issues (logically/ performance wise).
[Good to know] How much time (I understand it depends upon DB space/ server memory and other details, but a ballpark # would be good) would it take to partition this huge table (If it's ok to have partition based on date). Asking this question as this is a Production table and rows gets inserted frequently (Now ~ 350 records/ min).
[Not exactly a question, but asking for recommendation] Is there a better plan to manage this table(Don't want to keep more than 3 years of data in Production, plan is mentioned below)?
Current Plan (I am new to MS SQL, so this is what I came up with):
- Keep 3 months of data in each partition.
- System to create partitions automatically before each quarter.
- Keep only 3 years worth of partitions in active table.
- Move other partitions to the OLD/ ARCHIEVE table (need to create this). Really OLD data to be purged.