asked
Database performance hitting 98% percent CPU
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?
You should look at statistics and your indexing structure first.
As @TRAD mentioned, look at the statistics, that they are up to date. Probably when you put the DB on your laptop, statistics were updated and then queries run more smoothly. Not up to date statistics and/or inappropriate indexes are 90 % of such problems.
What is its current configuration. Is there an app server that is connecting to the database server? Is it using TCP/IP or Named Pipes, what type of disk is the database server using? When you restored to your laptop did you update stats after the restore? Does your laptop have the typical 5200 RPM drive or a SSD?
Depends on how the database was moved to the laptop. If it was just a restore the statistics will be the same. It's either a whole bunch of problems, or there's something else running on the system using up all the CPU.
When you're spiking like that, I would suggest running [Adam Machanic's sp_whoIsActive][1] 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. [1]:
http://sqlblog.com/blogs/adam_machanic/archive/2011/04/27/who-is-active-v11-00-a-month-of-activity-monitoring-part-27-of-30.aspx
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.
did you delete or create any indexes in any of the user defined databases.
