i normally turn d-lighting off as i do not want it to change the colors of my picture. however, in situations where there is a large disparity between the dark and bright regions of a picture where i want active d-lighting to try and bring it to something that has less disparity, i would use active d-lighting.
for example, if i am taking a pic where it is on a bright day and a building is casting a large shadow, i would use active d-lighting. below is a picture where i used HIGH active d-lighting. i do not have a before and after pic to show you, but without active d-lighting, the shadow areas would become much darker.
When not to use it:
When you want to shoot silhouette.
When you want to play with light and shadows to create "mood".
When you shoot an illuminated subject against very dark background (low-key studio photography).
Otherwise, I leave mine (called Dynamic Range Optimizer in my system) on all the time.