For SQL2K5, memory usage per connection is roughly (3 * network_packet_size + 94) KB. So for a 4096 byte network packet size (the default), that's around 106KB per connection, more if MARS is used.

See Memory Used by SQL Server Objects Specifications

But, first things first. (It really doesn't sound at all likely that memory is your problem).

If there are anything like ten runnable worker threads over a period of time on that dual-core server, that's almost certainly the place to start. See Monitoring CPU Usage for the counters to monitor. You can also query DMVs like sys.dm_os_schedulers, but a performance graph is probably going to be easier to work with in this case.

Often, very high CPU usage is due to high levels of parallelism caused by a lack a good indexes. Once you have confirmed the CPU bottleneck, follow Jeff's advice and run Profiler looking for high-CPU operations. Normally some good gains can be achieved just by tuning the top 5 or 10 CPU-using objects.

You may also want to consider reducing parallelism - either by increasing the cost for parallelism from the default (and way way too low) value of 5; or by turning off parallelism at the server level and just enabling it for individual queries using the MAXDOP query hint. Of course it may be simply that the server simply requires more horsepower. If you have the option to transfer to a machine with more/faster cores that might be worth considering before doing the tuning. The relative priority depends entirely on your business.

When your CPU problems are sorted, I would encourage you to become familiar with the wealth of information available from the dynamic management views (DMVs). You can find some good examples for costly queries here.

Finally, probably the best all-round performance guide for 2005 is on TechNet. You could do a lot worse than follow the advice given there when troubleshooting your current problem.

Fix the CPU issue first!

Paul