0

I have an OLTP Database with a big table (30M records a day / 400TPS). I want to implement Partitioning & Sliding Window on that table with Monthly Partitions plus a temp Stage-Table.

The time to Switch-Out (Archive) my data is clear for me: 'End of Month' for the Last Month of my window. But my problem is when should I Switch-In new data? Because this a OLTP DB and users needs access to new data (at least after a minutes or so). It's not really a good practice for me to leave the data in Stage-Table & transfer it at the end of month

Also I'm using SQLServer version 2017, could I use a different FG for the Stage-Table other than the one in the main Paritioned-Table to make the Switch-In work?

Improve this question

1 Answer 1

Reset to default
3

In OLTP you would rarely, if ever, switch-in partitions for exactly the reasons you state. Instead consider using Columnstore with compression delay. See Get started with Columnstore for real-time operational analytics

Improve this answer
2
  • Thanks @David, to be clear: 1-you mean that on a OLTP DB I shouldn't use Partitioning or Sliding-Window or I could use both but don't have to use Switch-in & insert directly? (I need to take out older than 2 year data) About the Column-Store scenario(Article), it somehow matched my case, thanks! But needs more space! Could I just use Column-Store Index as my Clustered Index ?
    – MShNajar
    Commented Sep 5, 2021 at 9:24
  • Partitioning and switching out is fine.
    – David Browne - Microsoft
    Commented Sep 5, 2021 at 14:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.