It's personal preference. I believe I have my Logitech G500 set to somewhere between 2800 and 3200 at the default setting for both desktop and games. It's been a while since it last checked the DPI. I drop down to 800 DPI for sniping.
to be honest theres no need to go over 1600-2400 dpi even 2400 is a bit twitchy when it comes to fine adjustments...
most C.S. gamers still use 400 and 800dpi mice although the vast majority have moved on to 1600+ having higher dpi will reduce prediction on mice that have a lot of it. setting the higher dpi say 4000+ will mean you have to slow the mouse down by reducing its tracking speed.
this is the easiest way to counter a mouse that has heavy prediction at low dpi settings. you mouse will track 4 dpi for every pixel on screen which means you should get smooth tracking... problem is i say should... some mice dont track smoothly regardless of how high the dpi is and how slow you force the mouse speed. reason being is some use a math function to calculate very 4th or 5th pixel the mouse tracks instead of the 4 it should at 4000dpi en result is even more prediction and seriously poor response...
so in theory it should be a good thing to have massive dpi often the reverse is true. very few mice handle high dpi correctly in a way that reduces to almost zero levels of prediction. there are a few but not many...
brands i would say handle it well are logitech coolermaster, corsair and oddly cyborg rat (refreshed versions). they use different brands of sensor but have managed to implement a tracking system that is smooth and accurate... brands to stay clear of. razor a lot of there mice are suffering with z axis tracking and tend to have much more prevalent angle snapping and prediction levels... even when they use the same sensor as cyborg there firmware lets it down.
in all honestly there is only 1 mouse from razor that is worth the money and thats the deathadder and only if its the mark 1 or 3.5G versions.
1 of the best basic mice (basic in that it doesnt offer lots of gaming functions) are the zowie am mice. there 3 and 5 button mice are some of the best you can get but you will pay a good price for them.
logitech and many others use the avango 9500... 3600(or there abouts )dpi sensor. any mouse that uses this is considered a good mouse, and there are a lot of them. but like i say even though they have all that dpi i prefer to run my mice at my screen rez or as close as i can get it... if i cant get it bang on but i can get it to 1600/900 i will use that instead. you loose very little speed and gain a whole lot of control.
so in all my years thats the conclusion i have come to. its better to have a mouse that can match the dpi of your screen rather than huge amounts of dpi with badly written firmware.