December 14, 2023 at 4:30 am
Hello!!
We are building our system that will be up and running for 24x7 365 days. the data will be generated in millions each day. Kindly help with the best practices material where in we can implement smooth house keeping processes on each table so as to not to load the tables as well as fetching data smoothly without delaying our production systems.
Regards,
Saumik Vora
December 14, 2023 at 4:56 am
Weekly or monthly partitions is probably the way to go. There are a LOT of caveats to partitioning spread quite wide in the MS documentation. Make sure you read and understand it all and, contrary to popular belief and documentation, don't discount the idea of using partition views (not good for weekly partitions if you need to keep everything for 20 years or more).
Make sure that all tables have the LOBs set to out of row (which will seriously avoid "trapped short rows", which is a permanent form of fragmentation) and make sure that you create a one or more separate file groups for LOBs and set the TEXTIMAGE option for each table to point to those separate file groups to eliminate the very nasty 3rd form of fragmentation that boils down to simple "interleaving" of data.
For the tables destined to become the largest, put those Clustered Indexes in their own file group whether they fragment or not. This will also have a good performance impact on backups if your network can handle the bandwidth.
There's more but I don't have it all memorized. You'll "run into" most of the rest as you do the things above (like selecting the correct Clustered Index, which may not necessarily be the Primary Key of the table).
--Jeff Moden
Change is inevitable... Change for the better is not.
December 14, 2023 at 6:11 am
A good database design is a must with normal forms 1,2,3, others met.
+ good indexes
+ Partitioning is best.
+ High Availability groups
-> Hardware choices
+ RAID 10 or better
+ Load balancer
+ Disk striping
+ Light effective anti-virus
+ Proper auto growth size for databases
+ Virtual machines with proper logical core and memory
+ Database min memory for VM - 8 GB to 16 GB
+ Database max memory for VM - 128 GB - 256 GB
+ Max degree of parallelism.
best luck.
DBASupport
December 14, 2023 at 6:17 am
This was removed by the editor as SPAM
December 14, 2023 at 6:20 am
This was removed by the editor as SPAM
December 14, 2023 at 11:40 am
As Jeff said partitioning is the best way and fastest way to remove large chucks of data by date.
When you want to regularly remove old data from a database, there are a few things to consider. If your data isn't organised into partitions, it's a good idea to delete the old records in smaller groups rather than all at once. This helps manage the process more efficiently.
If your table is not configured to be accessed through a clustered index for accessing the rows you need to delete, you can create a temporary table to store information about the rows (columns that are on the clustered index) you want to delete or move. Essentially, you'll have one process that extracts the relevant data from the main table and puts it into the temporary table. Then, another process will use this temporary table to delete the corresponding rows from the main table in small batches.
This two-step approach helps optimise the deletion process and ensures that you're not deleting a large amount of data in one go, which could impact performance.
December 20, 2023 at 7:05 am
The text of the original post implies the delivery team are new to the technology and have a large system to deliver.
If so then you need far more help than is available on a free forum. You will be spending serious money on the hardware and you should be prepared to spend to get some help with system design. You need to find a consultant with a track record of delivering large systems. This is far more likely to be a small organisation than a big boy such as PWC.
In the UK I would definitely ask Coeo for help, but there are other choices available. Do some research for where you are based, and hopefully you will find somebody who has already succeeded in doing the type of thing you want to get done.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
December 20, 2023 at 7:09 am
The text of the original post implies to me that the delivery team are new to the technology and have a very big system to implement.
If so then you need far more help then you will get in a free forum. You will be spending serious money on the hardware needed for your system, but spending money is no guarantee of success.
I suggest you find a consultant with a track record of implementing large scale systems on your chosen platform. This is more likely to be an individual or small organisation than a big boy such as PWC.
In the UK Coeo could be a good choice, but other choices are available. Do some research for where you are based, and hopefully you will find someone who has already succeeded in doing the type of thing you want to do.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
December 20, 2023 at 7:14 am
This was removed by the editor as SPAM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply