1) You are running an AGP based computer. This severely limits your video card choices... Most 'New' video boards are made for PCI-Express
2) You BOUGHT Counter Strike Source? I assume you don't have 'Half Life 2', because if you do you paid for the same game twice!
2) HalfLife 2 works just fine with a 128MB (or even 64MB) ATI or NVidia board - I ran it on my GeForce4 based PC and my ATI 9600 based laptop.
4) Other, newer games (HL2/CSS is about 2 years old) will run the above system into the ground, regardless of video card...
PCs have finally reached the point where clock speed (Ghz) is irrelevant WRT system speed. AMD and Intel have both by-and-large switched to a 'Model Number' system to denote their CPUs, because each 'revision' of the Pentium4 or Athlon64 has barely increased it's clock speed, but significantly increased it's performance. In short, 2 'Pentium 4' CPUs may both run at 3ghz, but the second one may be significantly faster...
It has gotten to the point where in order to increase performance, both companies are selling multiple-CPU chips (aka 'Dual Core'), sometimes running at slower clock rates than their single-CPU counterparts (but actually running the system faster)...
So, it may not seem that big of a jump to 3ghz+, but the current crop of CPUs are well ahead (Logically, Intel should be on 'Pentium 10' by now, but neither Intel nor AMD has actually changed the names of their CPUs to indicate the level of development that has occurred)....