Ann,

> Sweep work in this way or such different? If yes i should see many reads and
> > only few writes to disk but this not happend. I see >1TB writes to disc
> > for db size 52.74GB.
> >
>
> That seems extreme, but leads to several questions.
>
> One is whether your application regularly makes large changes to the same
> record, or several changes in the same transaction. Back versions are
> normally stored as differences from the next newer version, so they're
> usually small. However, if you change more than 255 bytes in a record, or
> if you change the record more than once in a transaction, the whole back
> version is stored - generally much larger than a difference records and
> therefore more likely to go off page.

I think the key issue that Karol is pointing to is the ratio of Disk IO writes to the number of pages (size of database). I agree with him.

Even if there are off-page changes, these could be collected into a page group and written to disk as a group (since all of the extra pages are only related to the single original page).

While sweep is intended to cleanup old record versions, it is reasonable to think that Write IO should be page centric (flush the page group to disk when all rows on the pages have been sweeped) -- to minimize the amount of write IO (slow in general, can cause problems for MLC flash based SSDs).


Sean