Is it a good idea to Partition a table (in MS SQL) based on date, when there's a clustered index present on ID (INT)

Question

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:

1. [Main question] Can I partition this table on the basis of "DateCreated" (DATETIME, NOT NULL column), without causing issues (logically/ performance wise).

2. [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).

3. [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.

Answer 1

Short answer: no, you cannot do this. Per the docs...

When partitioning a clustered index, the clustering key must contain the partitioning column.

This means that in order to partition on [DateCreated], you must also cluster on [DateCreated].

---

This makes a little more sense when you think about what partitioning and clustering actually are under the hood.

• A clustered index is the logical ordering of your data

• Partitioning is a technique to manage the physical storage of your data based on a function

These two things can be used together as long as they don't conflict. If you try to make them conflict, you're gonna have a bad time. To answer your point-by-point items:

1. You cannot partition on [DateCreated] at all unless you drop and recreate the clustered index to rebuild the table on that column. This is possible, but it's probably a bigger project than you planned when you asked the question.
2. No one can answer this for you. You need to test it for yourself. Several years ago when I dropped and rebuilt a clustered index on a high-traffic OLTP table with an order of magnitude less data-and-index space used by GB than you describe, it took several months of benchmarking which approaches would be safest/fastest and then about 10 hours of off-hours work between 2 DBAs working to complete the rebuild without taking downtime. Addition of a partitioning scheme was not a part of that project but I don't imagine it simplifies things.
3. If it's really "a logging table that no one usually cares about", then maybe you can do all of this without worrying about keeping the table online. Almost certainly you don't get to do it without creating at least one extra table (and probably several other objects) too. I encourage you to try a number of techniques on a restored copy of the database. I suspect you will end up renaming the current table and recreating it so that logs can continue to be written to a new, empty table (presumably with the partition scheme) while you archive the hundreds of gigs of old data using whatever approach you choose

Answer 2

If you decide to do this in one go it's likely to be limited by the speed of the disk.

A comparable operation would be taking backups. Backups read a whole bunch of bytes from here and write a whole bunch of bytes over there. Just like your table rebuild will. So if your DB is, say, 1.6TB and a backup takes 2hrs likely rebuilding a 800GB table will take about 1hr (half the size, half the time).

There are, of course, a whole lot of differences between these two tasks. The backup drives may be different to data drives, concurrent workloads will affect them, network paths to a SAN, secondary indexes, yadda yadda. So the times will be different, but they needn't be orders of magnitude different.

---

Rather than a big bang you may be better tackling this one use-case at a time, in reverse chronological sequence.

Delete the oldest data that you want rid of. As there are 10 years and you want only 3 the problem instantly becomes 70% easier! This is best done by deleting chunks at a time, rather than all in a single query. If it can't be deleted outright how about moving it to an OLAP server or dumping to a compressed file format (parquet, ORC and their ilk)?

Create a new empty table with the partitioning scheme you desire and partitions defined for all 3 years of history and some into the future. Move the oldest data across first, in chunks, inserting into the new table and deleting from the current. I'm guessing the ID column is an IDENTITY so it should correlate well with the date partitions. While this is going on if anyone wants to research on this old data they can do so from this new partitioned table.

Eventually you will get to a stage where the only data in the current unpartitioned table is for the current 3-month range or where having data split over two tables is causing actual pain to users. This is the time to have an outage. Move the last of the data to the new partitioned table, drop the current table & rename the partitioned table.

Answer 3

Something not to overlook is that with 1.2 bn rows you are getting closer than you think to hitting the ceiling for the int datatype. So I think your change should also plan to address this limitation, probably by changing to a Bigint instead.

As previously pointed out 70% of your data is so old you no longer need to retain it but rather than batch deleting 70% I would consider insert / select on the 30% you want to retain and then truncate the original table. You can then either copy the rows back or rename the table.

Final point is that your Id column probably is added in ascending order such that rows < id x are earlier and > id x are later so you could build a mapping table that maps ids onto dates & then partition directly on id ?

Answer 4

[Main question] Can I partition this table on the basis of "DateCreated" (DATETIME, NOT NULL column), without causing issues (logically/ performance wise).

You can partition on DateCreated column after you make it a clustered index. Even if you manage to make it a clustered index key, there will be a performance problem - your indexes will consume more space (storage/memory) than before (when ID INT was clustered).

Because INT is 4 bytes and is unique values, but your DateCreated column is 8 bytes and most likely not unique, so additional 4 byte uniquifier will be added on top, making clustered index heavier to some extent. Since clustered index key is added to all nonclustered indexes, they will become heavier, too

[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).

This entirely depends on horse power of your server

[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)?

Don't use partitions. Use a separate historical table with same structure. Create a daily job that would move (insert into historical table / delete in parent table) all rows that are older than 3 years, based on DateCreated column.

Move not all rows at once (that are older than 3 years) daily, but in chunks of N rows with interval of X sec (or ms) between chunks. N is a number of rows per 1 chunk deletion. Make sure N won't cause Exclusive table lock escalation on parent table (find out number experimentally)