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;
asked 01 Mar '14, 13:10
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)
answered 01 Mar '14, 17:48