September 26, 2023 at 1:46 am
Any one did Table partitioning in Prod, please list steps here.
we have a transaction DB with 4 Existing Large 60 M row Tables, WITHOUT partition storing Transaction Data from 2019 to 2023.
They asked me to come up with solution to partition this data , on azure MI ( assume only one FG is allowed ) , how to do it and do queries need to be redone>
For 2024 data how to create partition in advance and make sure data gets there correctly ?
Size of all Tables=500 GB on Azure why does it say 3.2 TB !!!
September 26, 2023 at 7:44 am
Don't start your partitioning adventure in production !
Read this thoroughly : https://www.sqlservercentral.com/forums/topic/partition-large-tables-needed#post-4292250
ref Partitioned tables and indexes
ref Create partitioned tables and indexes
ref SQL Server Partitioning: Getting Started
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 26, 2023 at 11:07 pm
Will the app continue to work ,
When we are swapping Data, partitions etc from un partitioned tables to partitioned tables, 24x 7 shop with only 1 hr downtime once a year.
September 27, 2023 at 5:29 am
There are some red fags over here, especially with "just implementing partitioning in production"!
You need to prepare for this all !! As jeff statted, it may not always be a win situation !
Your applications will keep on working, however, maybe not at the same speed !!
Since this is involving a logging table, one good thing is you are migrating to a now partitioned table, so you can load it, except for the newest rows, during normal operation time of your applications. Doing it this way, you only need to copy the last portion of "current" rows during the actual down time.
In our case, the partitioning function acts on datetime2 columns and pratitions on a per month basis.
For us, the most important reason for partitioning is to be able to delete the data without much overhead, hence by truncating a full partition at once.
We prepare for a couple of months up front, so we still have time to react is things fail.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply