All, a sql server instance with 3GB ram with just few gb data, and currently with the process only inserting and reading around 240K records at a time, is hitting around 98% of the cpu. I have restarted the server and restarted the services. but still hitting 98% . Any quick solution, is increasing a ram a immediate solution?
When you're spiking like that, I would suggest running [Adam Machanic's sp_whoIsActive] as a start to see if anything else is running that is taking up those resources. But if it is just the one process, I would then start with @TRAD's idea of checking out statistics and indexes. 3GB of RAM isn't very much, but it's not totally inadequate, either, depending upon the circumstances--we comfortably run certain instances with less RAM than that. And based on how it's doing the inserts and reads, a nice index or two (depending upon the complexity of your query) might turn a lot of table scans into index seeks, which reduces resource requirements as well. :
You need to look at the execution plan of that query. Excessive CPU usage sounds like you are sorting you data. A well placed index, or re-write of the query can work wonders. Maybe you can provide us with the query, table structure, test data and the execution plan for that query (that is the most important one one the list actually). We'll be able to offer some advice going on that information.
I agree with everything said so far, especially checking the statistics and indexes. But also look at the query and see if you have something inherently expensive there. UDF's, especially complex mathematical ones, can both substantially slow down a query and can eat up CPU resources. RBAR queries can do the same. Of course, as pointed out by Pavel, if the same query is running on substantially the same database without issue, then statistics are the most likely (though definitely not the only possible) culprit.