question

Katie 1 avatar image
Katie 1 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?
sql-server-2008performancecpu
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image
Tim answered
You should look at statistics and your indexing structure first.
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
1 Like 1 ·
but the problem is.. that i put the same database backup and the application pointing the database on my local laptop it just takes 20% of the CPU on my machine
0 Likes 0 ·
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?
0 Likes 0 ·
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.
0 Likes 0 ·
Kevin Feasel avatar image
Kevin Feasel answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ramesh 1 avatar image
ramesh 1 answered
did you delete or create any indexes in any of the user defined databases.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.