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

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%


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)

permanent link

answered 01 Mar '14, 17:48

Mark%20Culp's gravatar image

Mark Culp
24.9k10141297
accept rate: 41%

I should have done both... forgetful, lazy, pick one :)

(02 Mar '14, 04:32) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×92

question asked: 01 Mar '14, 13:10

question was seen: 2,023 times

last updated: 02 Mar '14, 04:32