Please be aware that the SAP SQL Anywhere Forum will be shut down on August 29th, 2024 when all it's content will be migrated to the SAP Community.

The V12 Help says "Side effects - None", and while that may be true from a semantic point of view, I know for a fact it is not true from a performance point of view.

Also, I believe the effects on performance have evolved from release to release, AND that these effects are probably difficult to describe.

But please, someone take a shot (V12 only).

Justification: There are reasons, possibly not great reasons but reasons nonetheless, that someone may wish to issue an explicit CHECKPOINT. For example, SELECT count FROM SYSTAB is much faster than SELECT COUNT(*) but the value is only up-to-date after a checkpoint... as are other interesting counters in the system tables.

Also, folks might want to know what can happen when an automatic checkpoint occurs... all in one place in the docs (say, where the CHECKPOINT statement is described).

asked 31 Oct '10, 12:16

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

edited 31 Oct '10, 20:14

Volker%20Barth's gravatar image

Volker Barth

Not as an answer, but as a connected topic: Cf., dealing with the statements that issue an implicit checkpoint. That could be documented together, I guess.

(31 Oct '10, 20:19) Volker Barth

Remember, you asked for it...

  1. Acquire the "checkpoint gate": ie, wait until a checkpoint can be performed (no backups in progress, etc) then prevent others from performing checkpoints.

  2. Pre-flight for updates of system tables. Pre-flight means we reference all the pages we will need to update so that the pages are in cache and we won't need to wait from them to be read while running in exclusive mode.

  3. Suspend deferred growth. This will wait for any deferred growth of dbspaces to complete

  4. Pre-flight for update of orphaned blob information

  5. Enter exclusive mode: no other tasks can be executing on the database after we enter exclusive mode. You may also have heard us call it "Forbid" mode -- named after an AmigaOS API which has a similar function

  6. Display the "Starting checkpoint" message

  7. Actually update system tables & orphaned blobs. System table updates include users (login failure attempts, last login time), tables (row counts, index info), sequences, and statistics

  8. Log the checkpoint_begin in the redo log

  9. Flush the checkpoint log

  10. Write all dirty pages from cache

  11. Update the checkpoint log to complete the checkpoint, update last checkpoint time, clear the "active" (aka "dirty") bits. After this step, the checkpoint has officially happened.

  12. Log the checkpoint_end in the redo log

  13. Display the "Finished Checkpoint" message

  14. Truncate the redo log if using "-m"

  15. Remove unreferenced entries from the identifier hash table

  16. Purge any shared heaps (parsed proc definitions, etc) that are unreferenced but were held in cache because they had been referenced frequently

  17. Coalesce the main heaps

  18. Exit exclusive mode

  19. Resume deferred growth feature

  20. Release the checkpoint gate

permanent link

answered 02 Nov '10, 13:47

John%20Smirnios's gravatar image

John Smirnios
accept rate: 37%


Point 7 includes updating max_identity values, right? (And I love the AmigaOS reference - has it ever been a supported platform?)

(02 Nov '10, 14:41) Volker Barth

Yes, point 7 includes updating the max_identity values. I'm pretty sure there was never an AmigaOS port of SQLAnywhere. That might be fun -- I wonder what state the tools are in :) I was an Amiga programmer a long time ago and I still have my Amiga 1000 and 3000. I even had the A3000 running a few months ago and though it definitely felt foreign, it was surprising how quickly some of the AmigaDOS insanity came flooding back. Insanity such as "dir #?.c". Yeah, "#?" instead of "*". Strange, but it came back to me.

(02 Nov '10, 21:13) John Smirnios

For 'Background checkpoints' (also known 'lazy', 'idle', 'background' or 'overlapping' checkpoints) there is an unlocking/locking step just prior to and after step "10" in the above article:

(9.5) Exit exclusive mode (10) Write all dirty pages from cache (10.5) Enter exclusive mode

(28 Jul '11, 11:23) Jeff Albion
Replies hidden

Who does trigger such background checkpoints - are these the automatic checkpoints the server issues when he thinks it's time to do so - in contrast to checkpoints done explicitly or implicitly by certain statements?

(28 Jul '11, 11:39) Volker Barth

That is correct.

(28 Jul '11, 13:49) John Smirnios

So it would mean automatic checkpoints allow tasks to do some work during the "Forbid" phase - by temporarily "unlock the gate"?

(28 Jul '11, 16:45) Volker Barth
showing 3 of 6 show all flat view
Your answer
toggle preview

Follow this question

By Email:

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 31 Oct '10, 12:16

question was seen: 3,344 times

last updated: 28 Jul '11, 16:45