vendredi 28 novembre 2014

Breaking up large transaction - worth it?


I'm in charge of monitoring the performance of an industrial chemistry process. Every hour, a process gathers 50 million rows from various subsystems and feed into the central database. The whole thing takes about 30 minutes.


A database consultant did some analysis on the database and saw the log size spike to 50GB during those load operations. His recommendation was to break the 50 million rows insert into smaller batches. Make sense until I think about our database: it's set to Full Recovery Mode.


Assuming that I do break the operation into smaller batches, their total log sizes would still add up to 50GB. That 50GB will just sit there until the transaction log is backed up. In effect, there will be no difference between the smaller batches and the big batch approach.


Am I missing something here?





Aucun commentaire:

Enregistrer un commentaire