phoon's old website used to have a great explanation for this. Essentially if you use a generally high sensitivity, it's useful to have your dpi set to a higher number. otherwise, let's say you have a 6" 360, you could potentially notice that your cursor will skip a number of pixels even when moving as little as possible. However, the usefulness of using high dpi to avoid pixel problems ends when your sensitivity is generally pretty low to begin with, especially because a high dpi and a low sensitivity is a recipe for negative mouse acceleration. I'm pretty sure raw input makes this a nonissue, however. Before raw input was released, ~12" 360 and higher with something like 1800 dpi would have negative acceleration issues.
so general rule of thumb for me would be if you use a generally low sens, don't bother with raising dpi. if your sensitivity is pretty high, however, it's useful to use a higher dpi to minimize problems with pixels.