High CPU Usage - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

High CPU Usage

Author

Topic

Zhanna
Starting Member

4 Posts

Posted - 2007-11-14 : 22:53:15

We have a live server that has had very high CPU usage in the last few days, therefore the site is extremely slow. There are about 60,000 users per day on the average. It always had high CPU usage, but not as bad as in the last few days, in the 90s, sometimes even reaching a 100.

Any solutions? I've run SQl Profiler to check on queries that have high CPU usage. When I run the same queries on our staging server, they are very quick. For example, the same query can take 1 minute on stage, but 12 minutes on the live site. We do know that this is also related to traffic, during lower traffic times, the same query takes less time, but still never as fast as stage.

Oh yeah, it's SQL Server 2005 and asp code running IIS6 on windows 2003 server.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-14 : 23:13:31

Did you see blocking on the server? How about disk i/o? Is table fragmented or statistics out of date?

Rovastar
Starting Member

38 Posts

Posted - 2007-11-15 : 05:15:36

Do the querys on live run quickly at off peak times.

I wouild look at it over a 24 hour period in profiler to get more info.

I sort of did this a year or two he is a doc

http://www.milkdrop.co.uk/SQL/SQL%20dianotistic%20procedure%20Online.doc

I have seen stupid scheduled DTS and denormalisaztion routines run on the background for hours at a time and only picked them up this whjy.

You will need to look also at the IIS traffic too to see when you have busy times concurrent users (current user in perfmon) is an important indicitor. It is not about the users a day it is how many there are on the box at any one time doing stuff.

Is the IIS server on the same box as SQL server?

That might be problem SQL server process taking up all teh 90+% resources or some of it is the web stuff.

Maybe it is time for an upgrade.

Zhanna
Starting Member

4 Posts

Posted - 2007-11-15 : 17:13:12

HI, yes, the queries run quickly at off peak times
Yes, the IIS and SQL Server are on the same box.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-15 : 22:46:01

Did you check blocking?

Zhanna
Starting Member

4 Posts

Posted - 2007-11-16 : 12:45:21

what is blocking?

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-16 : 13:08:06

No, if there is a high CPU usage then blocking is not a problem
Blocked server has CPU=0

You need to find bad queries and then optimize them
Sounds like a good plan?

Zhanna
Starting Member

4 Posts

Posted - 2007-11-16 : 13:20:51

yes, I have been doing that, was just wondering whether there's anything else I could do on top of that?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-17 : 01:20:57

>> if there is a high CPU usage then blocking is not a problem

When there are lot of blockings, blocked queries may timed out then retry. That will send cpu usage to roof, and I fixed too many those issues.

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-17 : 05:08:47

I dont understand.

Lets say we are talking about the OLTP system with short queries spending 30-200ms CPU
So query consumes 200ms and gets blocked
Normal .Net timeout is 30 seconds and we assume that it is resarted immediately
In order to put CPU 100% for 1 core we need to have 30/0.2 = 150 connections - all participating in that blocking
On a real 4-core server we need 600 spids
It is quite a big number, on the most heavy-loaded systems there are 150-200 spids because of pooling.

If there are 150 spids participating in lock, then you would have much more serious signs of the disaster then just high CPU

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-17 : 21:37:39

How do you know queries on OP's server consume 200ms? By the way, many dbs have mixed type of processes.

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-18 : 03:12:19

Well, if usage pattern is different, then it is possible
But in OLTP systems queries are almost always 'quick': something like GetClientByID etc.
In mixed and OLAP system your scenario is possible,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources