I have a large production table:
- 64 columns
- around 800 millions of rows
- around 80 GB of data + index (18 GB for the index size)
with the following flow:
- around 1 million records deleted every day
- around 900.000 records inserted every day
- a query that will update another table with the modifications made every day (insertes & deleted) - based on a datetime column (indexation_day)
- some other small queries not very relevant
There is a maintenance plan that treats the fragmented indexes and the statistics that are not updated, but this maintenance plan also works very slow because of the large amount of data in the table and also large amount of modifications made every day.
I was thinking of partitioning the table, based on the datetime field(indexation_day) and wondering what would be the the impact on performance.
Some of my questions questions are:
- should i make the datetime field (on which the biggest query is based on) part of my clustered index (which is an int, an id at this point)? the next records inserted into the datetime field will be in ascending order.
- what will be the impact on maintenance on the partitions?
- should i split the partitions on different filegroups ? (read some articles how it would not be very good on performance)
- how can i use the SWITCH operator to allow extremely fast loading and removal of large amounts of data?
I have read on a website that 'Prescribing table partitioning is like recommending significant surgery'. I hope my questions are accurate, but my main issue is the partitioning affect on my existing (slow) performance and on the existing (slow) maintenance plan. Any thoughts on this will be extremely welcomed!
Thank you for reading a big posts and thank you in advance for every tip!