Create Checkpoint is definitely one of our more esoteric activities, and should really only be used in edge case situations. 

What it does: It's actually pretty simple, and workflow does this for you automatically most of the time - It saves the workflow instance state to the database. 

Why would you use it? - The main use case for using it is if you choose to uncheck the 'Unload when idle' performance option for the workflow in the Advanced Server Options dialog. 

Unload when idle (defaults to on) means that whenever the workflow is waiting for work from an external system it will save itself to the database and remove itself from the machine's memory. This generally makes the workflow instance reliable in the case of machine failure but comes at the small cost of loading/saving the workflow to the database (time + db transactions). It also allows the system memory (RAM) to remain generally low and consistent. If you turn this option off, Create Checkpoint allows you to indicate when you would like the workflow to save. If you do not use Create Checkpoint the workflow instance would not be generally recoverable if a disaster happens (e.g. power goes out without a UPS, etc.). 

Should you use it? Probably not.