x

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?
more ▼

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

Katie 1 gravatar image

Katie 1
1.4k 132 163 202

(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

Tim gravatar image

Tim
36.4k 36 41 139

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][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
more ▼

answered Aug 23, 2011 at 10:34 AM

Kevin Feasel gravatar image

Kevin Feasel
6.1k 3 5 11

(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

WilliamD gravatar image

WilliamD
25.9k 17 19 41

(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

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

(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

ramesh 1 gravatar image

ramesh 1
2.2k 63 67 69

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1840
x249
x13

asked: Aug 23, 2011 at 09:57 AM

Seen: 1614 times

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