It depends on the game. In CPU intensive games like Starcraft 2, you'll notice a huge difference probably >30%. In FPS games, probably not much. It also depends on your GPU.
That is absolutely correct CPU intensive games additionally like Microsoft's Flight Simulator 10 do show FPS increases from overclocking the CPU, non CPU intensive games show almost zero FPS gains from a CPU overclock, as those games are reliant on GPU performance and overclocking the CPU does not increase GPU performance in those games.