Re: Logical log write volumes

The following conversation is almost a definitive answer to the question of logical write volumes. Andy Kent wrote:- > >> Can anyone tell me how you work out how much gets written to the > logical>> logs during an insert-intensive application? And June Tong replied:- > > The calculation should be something like: > > 16 bytes + rowsize for each row inserted > 16 bytes + indexsize for each row/for each index on the table > (these numbers might be +/-4 in either direction) > > This is for an insert. Updates and deletes are different. > > Plus there will be another 12-16 bytes for each BEGIN WORK, COMMIT WORK, > andaround 24 bytes each time you have to add another extent to your > table. But these will probably be much less significant. > Andy wrote:- > >>What are the differenc > >> here between buffered and unbuffered logging? (I thought buffered d > >> "piggyback" writes, but I was on a site recently where the logging > >mode > didn't seem to make any difference to logging volumes) > Anf June replied:- > Logging mode WILL make a difference to the logging volumes, but how much > of a difference will depend on your transactions. Unbuffered logging > flushes thelog buffer to disk everytime you commit a transaction. Only > whole pages are written to disk. Therefore, there will be times when a > page which is not fullwill be written to the log on disk. With buffered > logging, most, if not all,of the pages will be full when they are > written to disk. Whether this isnoticeable or not will depend on the > length of your transactions. If you havea transaction that takes up > 20MB of log, and then commits and flushes a pagewhich is only half-full, > you probably won't notice any difference in the amountof logging. On > the other hand, if your transactions are very short -- say,insert 2 > short rows and commit -- you will be flushing partially used pagesvery > often, and you will probably see that your logs fill up more quickly. > > Malcolm is right when he says that the logging volume will not change, > if bythat he means that no more information is written to the logs. But > more emptyspace will be in the logs, and you will fill your logs faster. > So the answer would appear to be that you need to know the application very well. If the application performs multiple small transactions then the logs will fill up faster with unbuffered logging, but if the application uses long transactions there will be little difference in logging volumes. My personal view is that the inherent data vulnerability of buffered logging is not acceptable in the vast majority of applications. This has not changed my view. If the operation is indeed multiple small insert transactions the logs can be backed up as soon as they become full, it is only in the case where the operation is one long transaction that it is necessary to calculate the size, using the formula provided by June. However, the other operations on the machine at the same time will influence this. If the large number of inserts have to occur in parallel with other activity then the nature of this other activity will affect the sizing. In the worst case, if there is a large amount of small transaction activity going on in parallel with the long transaction the calculation of log size for the long transaction will need to assume that each small transaction will cause a flush of the logical log buffer. The speed of log filling in pages per minute would then be the same as the number of transactions per minute. The only thing that then remains is to estimate the length of time required for the long transaction and then multiply this by the log filling rate to calculate the total size of the logs required for this long transaction. BUT DON'T FORGET!!! Any insert/update/delete operation carried on outside of transactions is treated as a singleton transaction, as are any schema changes. They could seriously affect these figures. My worst experience of this effect was at a site where database had been set to NOLOG. The logs still filled up at an alarming rate. When I investigated I found that the application created 50 temp tables per operation. Now I see why the logs filled up so fast. Malcolm Weallans Online Database Consultancy 2 Arkley Court Maidenhead Berks SL6 2YR Phone 0628-72154 Fax 0628-37463 CIX - onlinedbc