"Cleanable page" is not the same as "dirty page"

[Breck_Carter]

The documentation on the sa_clean_database system procedure is very careful to say "cleanable page" rather than "dirty page"... the checkpoint process handles dirty pages, not the database cleaner, they are two entirely different concepts.

Sadly, one of the examples in that Help topic makes several references to "dirty pages" when the code is dealing with cleanable pages... it's not just a matter of semantics, it's a mistreatment of an extremely complex topic (how the server handles physical data in the database) that can possibly lead developers down the wrong paths.

CREATE EVENT PeriodicCleaner
SCHEDULE
BETWEEN '9:00 am' and '5:00 pm'
EVERY 1 HOURS
HANDLER
BEGIN
     DECLARE @num_db_pages INTEGER;
     DECLARE @num_dirty_pages INTEGER;

-- Get the number of database pages
     SELECT (SUM( DB_EXTENDED_PROPERTY( 'FileSize', t.dbspace_id ) - 
                     DB_EXTENDED_PROPERTY( 'FreePages', t.dbspace_id ) ))
     INTO @num_db_pages
     FROM (SELECT dbspace_id FROM SYSDBSPACE) AS t;

-- Get the number of dirty pages to be cleaned
     SELECT (DB_PROPERTY( 'CleanablePagesAdded' ) - 
                     DB_PROPERTY( 'CleanablePagesCleaned' ))
     INTO @num_dirty_pages;

-- Check whether the number of dirty pages exceeds 20% of
     -- the size of the database
     IF @num_dirty_pages > @num_db_pages * 0.20 THEN
       -- Start cleaning the database for a maximum of 60 seconds
       CALL sa_clean_database( 60 );
     END IF;
END;

[MarkCulp]

Thanks Breck for pointing this out. I have made a comment for the doc team on this page for sa_clean_database in the v16 docs. (FWIW: Putting a comment in DCX is a better place for making suggestions on improvements to the docs... but I understand why you posted this here since it is related to this recent question)

[Breck_Carter]

I should have done both... forgetful, lazy, pick one 🙂