It depends.
First, it depends on how much you move the mouse. If you move your whole hand and arm around, you won't need high DPI. Good tracking, yes, but not high DPI. If you move with your fingers, you can never have enough DPI.
Then, it will be game dependent. Some games have odd behaviors tied to movement amount and speed (such as acceleration tied to movement, allowing you to aim a turret or similar faster with high DPI), and some have exceptionally coarse angular movement (Gamebryo, FI), which begs for very high DPI and low in-game sensitivity, and/or dynamically selectable sensitivities. In general, you want mouse DPI and game sensitivity set so that you can effortlessly make single pixel aim changes (match both sensitivities up to a comfortable physical amount of mouse movement).