When does hook_field_storage_details_alter() fire?
The function that invokes both hook_field_storage_details() and hook_field_storage_details_alter() is _field_info_prepare_field(), which is indirectly called from the following functions:


field_info_cache_clear()
field_info_extra_fields()
field_info_field()
field_info_fields()
field_info_field_by_id()
field_info_field_by_ids()
field_info_instance()
field_info_instances()


In other words, all times information about a field is required, that hook is used.
As Clive points out, _field_info_collate_fields(), the function that directly calls  _field_info_prepare_field(), caches the information returned by those hooks, which means they are not always invoked; they are invoked when the cached data don't exist, or have been removed. This makes hook_field_storage_details_alter() used in a similar way to hook_menu_alter(), which is called only when the menu definitions need to be rebuild, and not all times Drupal is retrieving the menu definitions given by all the enabled modules.