Hmm. Well I know certain games have bugs that are effected by the enhance pointer precision setting in the mouse options (EA's Dead Space especially if it is on you will know it is like going from 1600DPI to 200DPI when it is on), but most games aren't effected by this.
Anyways basically it really depends on the game and it seems some games implementation on how they grab mouse input is severely flawed in some way and causes erratic game breaking behavior depending on the settings of the OS.
mouse acceleration increases the rate at which your mouse moves across the screen in relation to how fast you move it physically
Without mouse acceleration if you move your mouse from point A on your mouse pad to point B on your mouse pad, the cursor will move from point A on you screen to point B on your screen regardless of how fast you actually move your mouse between those two points. Each physical translation of the mouse results in a proportional translation of the cursor regardless of any other factor.
With mouse acceleration turned on, the translation of the cursor also depends on the time taken for the mouse to travel from point A to point B. However, since there exist a near infinite number of physical points between point A and point B the software has to look at the displacement and time between every single point polled, not merely where you started and stopped your cursor. This is why it's called acceleration, it measures the translation over very small periods of time and calculates the change in translation. Higher rates of physical translation increase the rate of translation for the cursor.
The physical effect of mouse acceleration is that with a slow physical translation from A to B the cursor will translate less than it would with a faster physical translation from A to B. Mouse acceleration works both ways (there's also negative mouse acceleration) and is necessary to get really fine grained control without changing the speed of the cursor (translation scaling). With the proper settings, Sniping in video games benefits greatly from mouse acceleration. It's also necessary for counteracting the joystick emulators that developers love to throw on their crappy console ports.
Mouse acceleration is sort of like exponential increase of cursor speed:hand speed
With mouse accel. off, A to B is exactly like A to B onscreen
With mouse accel. on, A to B depends on how fast it took your hand to get there.
FPS gamers dont like accel because of the inconsistency, but I personally like it because I sort of jerk my hand little bits at a time, not in a smooth movement.