Carrell Alex wrote:

>
> Heres a table, total versions make 1% of the total.
>
> What are impacts of letting the below happen.
>
> PURCHTRAN (160)
> Primary pointer page: 1415, Index root page: 1416
> Average record length: 301.28, total records: 873801
> Average version length: 289.08, total versions: 9034, max versions: 29

This level of back versions probably won't cause much trouble. Back
versions are normally stored as 'deltas' - like a diff between the
new version and the old. Deltas are small so they fit in the
reserved space on pages. Back versions are expensive when they
go off the primary page.

A critical issue is the length of the chain - the number of back
versions for any single record. Twenty-nine could be a problem
because they probably don't fit on the page, even if they are small.
However, I've seen slow databases with hundreds of thousands of
back versions and max versions in the high tens of thousands. Those
are expensive, and they made the application unusable.

The problem with long back version chains is that your transactions
have to sniff out the whole chain each time the primary record is
referenced to see if the oldest versions are still important. When
the chain is short and on the same page, the cost is negligible.
But negligible times thirty or forty thousand stops being cheap.

Cheers,


Ann