Database performance hitting 98% percent CPU


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?

more ▼

asked Aug 23, 2011 at 09:57 AM in Default

avatar image

Katie 1
1.4k 132 164 205

(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

You should look at statistics and your indexing structure first.

more ▼

answered Aug 23, 2011 at 10:23 AM

avatar image

40.9k 39 92 168

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

Aug 23, 2011 at 10:32 AM Katie 1

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?

Aug 23, 2011 at 10:36 AM Tim

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.

Aug 23, 2011 at 11:28 AM Pavel Pawlowski

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.

Aug 23, 2011 at 07:54 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Aug 23, 2011 at 10:34 AM

avatar image

Kevin Feasel
6.2k 4 7 15

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Aug 23, 2011 at 10:59 AM

avatar image

26.2k 18 35 48

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Aug 23, 2011 at 11:44 AM

avatar image

15.6k 22 51 38

(comments are locked)
10|1200 characters needed characters left

did you delete or create any indexes in any of the user defined databases.

more ▼

answered Aug 23, 2011 at 11:42 PM

avatar image

ramesh 1
2.2k 66 69 73

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 23, 2011 at 09:57 AM

Seen: 1955 times

Last Updated: Aug 23, 2011 at 09:57 AM

Copyright 2017 Redgate Software. Privacy Policy