473,380 Members | 1,346 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,380 software developers and data experts.

100 % CPU Usage

Hi

I am having a real issue with CPU usage by SQL Server, and it is not
related to a poor query.

I have a clients database that I am currently investigating some issues
with. After I perform a standard task using the application, and the
results have been returned to the application the cpu usage remains at
100%.

Even once the application has been completely closed down the cpu usage
remains at 100%. Nothing else is happening.

I am at a complete loss as to how to proceed to with investigation of
this issue (i have been looking at this for over a week using SQL
Server tools and Performance Monitor, and eliminating various other
possibilities)

I downloaded Process Explorer, and looking at the threads for
sqlservr.exe there is one in particular that is consuming all of the
cpu time:

MSVCRT.DLL

I am running SQL Server 2000 SP4 on the following machine:

Windows 2000 SP4
Pentium 4 3Ghz (Note this is seen as 2 processors so the reported cpu
usage is 50%)
1Gb Memory

I also have about 20Gig of free disk space.

One other thing, the page faults reported on the Performance tab of
Process Explorer exceeds 3 million. This is after running the
application for around 10 minutes.

Please can anybody suggest anything at all that might help? I'm sorry
there is not too much information in here but I have not been able to
find out anything useful!

Many Thanks in advance.

Paul

Nov 23 '05 #1
22 31936
Paul (pa***********@hotmail.com) writes:
I am having a real issue with CPU usage by SQL Server, and it is not
related to a poor query.

I have a clients database that I am currently investigating some issues
with. After I perform a standard task using the application, and the
results have been returned to the application the cpu usage remains at
100%.

Even once the application has been completely closed down the cpu usage
remains at 100%. Nothing else is happening.

I am at a complete loss as to how to proceed to with investigation of
this issue (i have been looking at this for over a week using SQL
Server tools and Performance Monitor, and eliminating various other
possibilities)

I downloaded Process Explorer, and looking at the threads for
sqlservr.exe there is one in particular that is consuming all of the
cpu time:

MSVCRT.DLL


Sounds bad.

I would look in the SQL Server error log, to see if there are any
messages.

If this scenario is happen repeatedly, I would consider open a case
with Microsoft, as this sounds like it could be a problem in SQL Server.
That or some hardware problem.

You should of course also make sure with sp_who2 that there are no
active processes. Particularly, watch the CPUTime column, to see if
there is any suspect.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 23 '05 #2

Hi Erland, thanks for the reply.

This problem occurs everytime. I have just reproduced it again and the
results of sp_who2 are below:

SPID Status Login HostName
BlkBy DBName Command CPUTime DiskIO LastBatch
ProgramName SPID
----- ------------------------------ ----------------------
------------- ----- ------ ---------------- ------- ------
-------------- ------------------ -----
1 BACKGROUND sa .
. NULL LAZY WRITER 246172 0 11/23 10:48:41
1
2 sleeping sa .
. NULL LOG WRITER 31 0 11/23 10:48:41
2
3 BACKGROUND sa .
. NULL LOCK MONITOR 0 0 11/23 10:48:41
3
4 BACKGROUND sa .
. master SIGNAL HANDLER 0 0 11/23 10:48:41
4
5 BACKGROUND sa .
. master TASK MANAGER 0 131 11/23 10:48:41
5
6 BACKGROUND sa .
. master TASK MANAGER 0 0 11/23 10:48:41
6
7 sleeping sa .
. NULL CHECKPOINT SLEEP 0 0 11/23 10:48:41
7
8 BACKGROUND sa .
. master TASK MANAGER 0 159 11/23 10:48:41
8
9 BACKGROUND sa .
. master TASK MANAGER 0 0 11/23 10:48:41
9
10 BACKGROUND sa .
. master TASK MANAGER 0 45 11/23 10:48:41
10
11 BACKGROUND sa .
. master TASK MANAGER 0 117 11/23 10:48:41
11
51 sleeping FWPS\PWragg
BHX-WS0292-2K . master AWAITING COMMAND 109 290 11/23
10:49:12 MS SQLEM 51
53 RUNNABLE sa
BHX-WS0292-2K . master SELECT INTO 359 824 11/23
11:05:42 SQL Query Analyzer 53

Sorry about the formatting. As you can see the only runnable process is
the sp_who2 command. And the user for the application is not in the
list of Logins so it cannot be anything related to the application. I
do have this message in the error log though from when I started up the
instance this morning:

2005-11-23 10:48:54.00 spid51 Starting up database 'msdb'.
2005-11-23 10:49:11.92 spid51 Error: 15457, Severity: 0, State: 1

However, I think this is an unrelated error as this does not appear for
any of the other times this has happened (and I have checked for this
on other newsgroup postings). Incidentally, this does occur on another
2 machines I have tried it on and so I do not think it is a hardware
problem.

Maybe I should try and raise this with Microsoft unless you can think
of anything else?

Thanks

Paul

Nov 23 '05 #3

Hi Erland, thanks for the reply.

This problem occurs everytime. I have just reproduced it again and the
results of sp_who2 are below:

SPID Status Login HostName
BlkBy DBName Command CPUTime DiskIO LastBatch
ProgramName SPID
----- ------------------------------ ----------------------
------------- ----- ------ ---------------- ------- ------
-------------- ------------------ -----
1 BACKGROUND sa .
. NULL LAZY WRITER 246172 0 11/23 10:48:41
1
2 sleeping sa .
. NULL LOG WRITER 31 0 11/23 10:48:41
2
3 BACKGROUND sa .
. NULL LOCK MONITOR 0 0 11/23 10:48:41
3
4 BACKGROUND sa .
. master SIGNAL HANDLER 0 0 11/23 10:48:41
4
5 BACKGROUND sa .
. master TASK MANAGER 0 131 11/23 10:48:41
5
6 BACKGROUND sa .
. master TASK MANAGER 0 0 11/23 10:48:41
6
7 sleeping sa .
. NULL CHECKPOINT SLEEP 0 0 11/23 10:48:41
7
8 BACKGROUND sa .
. master TASK MANAGER 0 159 11/23 10:48:41
8
9 BACKGROUND sa .
. master TASK MANAGER 0 0 11/23 10:48:41
9
10 BACKGROUND sa .
. master TASK MANAGER 0 45 11/23 10:48:41
10
11 BACKGROUND sa .
. master TASK MANAGER 0 117 11/23 10:48:41
11
51 sleeping FWPS\PWragg
BHX-WS0292-2K . master AWAITING COMMAND 109 290 11/23
10:49:12 MS SQLEM 51
53 RUNNABLE sa
BHX-WS0292-2K . master SELECT INTO 359 824 11/23
11:05:42 SQL Query Analyzer 53

Sorry about the formatting. As you can see the only runnable process is
the sp_who2 command. And the user for the application is not in the
list of Logins so it cannot be anything related to the application. I
do have this message in the error log though from when I started up the
instance this morning:

2005-11-23 10:48:54.00 spid51 Starting up database 'msdb'.
2005-11-23 10:49:11.92 spid51 Error: 15457, Severity: 0, State: 1

However, I think this is an unrelated error as this does not appear for
any of the other times this has happened (and I have checked for this
on other newsgroup postings). Incidentally, this does occur on another
2 machines I have tried it on and so I do not think it is a hardware
problem.

Maybe I should try and raise this with Microsoft unless you can think
of anything else?

Thanks

Paul

Nov 23 '05 #4
> This problem occurs everytime. I have just reproduced it again and the
results of sp_who2 are below:
...

Sorry about the formatting. As you can see the only runnable process is
the sp_who2 command. And the user for the application is not in the
list of Logins so it cannot be anything related to the application.
Hm, there is a lot of CPU on the Lazy Writer given the login time.
Could that be a token of something? (I'm just thinking aloud.)
I do have this message in the error log though from when I started up
the instance this morning:

2005-11-23 10:48:54.00 spid51 Starting up database 'msdb'.
2005-11-23 10:49:11.92 spid51 Error: 15457, Severity: 0, State: 1
15457 is change of configuration option.
Incidentally, this does occur on another 2 machines I have tried it on
and so I do not think it is a hardware problem.
Interesting indeed.
Maybe I should try and raise this with Microsoft unless you can think
of anything else?


I would definitely open a case for this. This smells very fishy to me.
If this indeed is a bug in SQL Server, you should be refunded anything
you are charged initially. Of course, if there is indeed something bad
in your environment, you will have to pay.

OK, one idiot check before you call: you are not using compressed drives
are you?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 23 '05 #5
Hi Erland

No I am not using compressed drives. I did think that the number of
page faults reported by Process Explorer was exceptionally high though,
but I am not sure if this is related in any way?

I will attempt to go through the process of raising something with
Microsoft then (if I can convince my company that this is what is
required).

Many Thanks for your help!

Paul

Nov 23 '05 #6
Hi Paul,

Could you please update the group if you have resolved it. I am curious
to know the fix.

..AS

Nov 24 '05 #7
Hi,

I have not managed to resolve this problem yet, this is not a huge
priority as this does not happen except for with this particular
database. It does not appear to happen for the customer at their site
so seems to be something that has either occured when we restored their
backup to our environment, or a specific issue with the copy of the
database we received from them (although this is the third copy we have
had that has exhibited this behaviour).

Do you have a similar problem?

Paul

Nov 24 '05 #8
Paul (pa***********@hotmail.com) writes:
I have not managed to resolve this problem yet, this is not a huge
priority as this does not happen except for with this particular
database. It does not appear to happen for the customer at their site
so seems to be something that has either occured when we restored their
backup to our environment, or a specific issue with the copy of the
database we received from them (although this is the third copy we have
had that has exhibited this behaviour).

Do you have a similar problem?


No, this is completely alien to me.

So, you say that there is a common path for all these databases where
this happens? It's always a copy from the customer's production environment
that bails out when you restore into test and you run this standard routine
there?

Hm, could there be some sort of corruption in that database that exhibits
only after a restore?

Did you run DBCC on the database?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 24 '05 #9
Hi Erland

Do you mean DBCC CHECKDB.

I have never had any problems where I have needed to check any of the
database structure before.
I note in Books Online that there are a few DBCC CHECKxxx commands. I
am guessing that you refer to the DBCC CHECKDB command?

Thanks,

Paul

Nov 24 '05 #10
"Paul" <pa***********@hotmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...
Hi Erland

Do you mean DBCC CHECKDB.

I have never had any problems where I have needed to check any of the
database structure before.
I note in Books Online that there are a few DBCC CHECKxxx commands. I
am guessing that you refer to the DBCC CHECKDB command?

Thanks,

Paul


Yes, 99.99999% sure he was referring to checkdb. That's how I read it when
reading the thread. All you want is:

dbcc checkdb('my-db-name') with no_infomsgs

That will check the database but won't do any repairs and won't flood you
with information messages.

Its worth running checkdb occasionally anyway. I got a fright recently when
running it against a database I thought was fine only to find it turned up
some problems.

Hope you resolve your issue.
--
Brian Cryer
www.cryer.co.uk/brian
Nov 24 '05 #11
Thanks, I have begun running the command against the database. I have
no idea how long this takes but I will post back here once I know
whether this has helped or not.

Thanks to all who have replied so far!

Paul

Nov 24 '05 #12
Hi

Unfortunately running DBCC DBCHECK has not made any difference. Back to
the drawing board I suppose. If anybody else who has read this posting,
or those of you who have replied have any other ideas then please let
me know.

And if I do find a solution I will let you know!

Paul

Nov 24 '05 #13
Paul (pa***********@hotmail.com) writes:
Unfortunately running DBCC DBCHECK has not made any difference. Back to
the drawing board I suppose. If anybody else who has read this posting,
or those of you who have replied have any other ideas then please let
me know.


The point with running CHECKDB is to see if it reveals any corruption
errors. It is not likely that it would fix the problem.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 24 '05 #14
OK , I have re-run with the information messages on and there were 0
allocation and 0 consistency errors so this looks OK.

I have requested the mdf and ldf files from the customer rather than
the backup in the hope that we may see different behaviour.

Again, I will post back with any progress.

Paul

Nov 24 '05 #15
"Paul" <pa***********@hotmail.com> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.com...
OK , I have re-run with the information messages on and there were 0
allocation and 0 consistency errors so this looks OK.

I have requested the mdf and ldf files from the customer rather than
the backup in the hope that we may see different behaviour.

Again, I will post back with any progress.

Paul


I don't think it will turn anything up, but try running checkdb against your
master database.

Another thought (probably equally unlikely to turn up anything), I know
you've already looked in your sql server log and didn't find anything, have
you looked in the windows system event logs? If there were a disk related
issue it would probably be reported there - although I would expect
something to appear in the sql logs and I'm not sure it would account for
your high cpu time.
--
Brian Cryer
www.cryer.co.uk/brian
Nov 24 '05 #16
Hi

I should have stated that I had checked the windows logs as well and
there is nothing unusal in there. I have also run check disk against
all my disks to ensure that they are fine. No errors were reported.

I have now run DBCC CHECKDB against the master database and no errors
were reported.

Paul

Nov 24 '05 #17
"Paul" <pa***********@hotmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
Hi

I should have stated that I had checked the windows logs as well and
there is nothing unusal in there. I have also run check disk against
all my disks to ensure that they are fine. No errors were reported.

I have now run DBCC CHECKDB against the master database and no errors
were reported.

Paul


Seems like you've covered everything I can think of.

Erland's early suggestion of opening a call with Microsoft might be your
only answer ...

--
Brian Cryer
www.cryer.co.uk/brian
Nov 24 '05 #18
Well thankyou to everybody who has posted. I will post back when (if) I
do find a resolution to this problem.

Paul

Nov 24 '05 #19

Hi

I got the MDF and LDF files from the customer and the same thing
happens, so it doesn't appear to be anything that happened during the
backup/restore process.

So I am still at a loss and I do not want to pay for support from
Microsoft if this is not a bug. I think we have somebody in the company
who is an MSDN subscriber though so I will try and get round to raising
this if I can (and I will post back).

Thanks,

Paul

Nov 25 '05 #20
Paul (pa***********@hotmail.com) writes:
I got the MDF and LDF files from the customer and the same thing
happens, so it doesn't appear to be anything that happened during the
backup/restore process.
Just to refresh my memory: the effect does not happen at the customer's
site, only when you try it on your machines in-house?

Indeed very strange. What difference could there be between your customer's
configuration and your own? Yes, I know that you tried it on three different
machines in-house, but I suspect they may have something in common, which
they don't share with the customer's machine. For instance, the customer
may be running on a cluster, but you don't? Operating system? Exact SQL
Server version? And not the least, the version of MSVCRT.DLL?

And, another check, what about anti-virus?
So I am still at a loss and I do not want to pay for support from
Microsoft if this is not a bug. I think we have somebody in the company
who is an MSDN subscriber though so I will try and get round to raising
this if I can (and I will post back).


I will have to admit that at this point I would probably be prepared to
pay to find out, just to ease my own curiousness. It's just so damn weird.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 26 '05 #21
Hi, I've just picked up on this thread. I am also having the same
problem. Configuration is a 2003svr with sql server with sp4.

I am seeing exactly the same symptoms and have tried the suggestions
posted in this thread, all no no avail.

We're a small consultancy and have a collection of db's for various
packages mounted on the server. The server behaved normally until a few
days ago. I don't know what changed but I initially attacked disk and db
fragmentation and rebuilt the indexes to see if that was the cause - no
luck.

The cpu simply sits at 100% for the sqlserver process.

If anyone wants to make any suggestions this is not a production server
so I'm open to ideas.

What does work is taking the db's offline and putting them online again
- not exactly an ideal solution.

Unfortunatly the problem only rears its head occasionally and once you
have taken 7 o 8 db's offline it goes away. I am not sure the problem is
being caused by the last one to be taken offline.

I need this to go wrong again a few more times to work out which is
causing the problem.

regards

Ian Murphy

*** Sent via Developersdex http://www.developersdex.com ***
Dec 9 '05 #22
Ian Murphy (ia*@integra-xp.com) writes:
Hi, I've just picked up on this thread. I am also having the same
problem. Configuration is a 2003svr with sql server with sp4.

I am seeing exactly the same symptoms and have tried the suggestions
posted in this thread, all no no avail.

We're a small consultancy and have a collection of db's for various
packages mounted on the server. The server behaved normally until a few
days ago. I don't know what changed but I initially attacked disk and db
fragmentation and rebuilt the indexes to see if that was the cause - no
luck.

The cpu simply sits at 100% for the sqlserver process.

If anyone wants to make any suggestions this is not a production server
so I'm open to ideas.

What does work is taking the db's offline and putting them online again
- not exactly an ideal solution.

Unfortunatly the problem only rears its head occasionally and once you
have taken 7 o 8 db's offline it goes away. I am not sure the problem is
being caused by the last one to be taken offline.


Well, in the case of Paul's databases, it happened with his in-house
servers, but not at the customer site with the same database. That was
sort of strange. Also, in Paul's case, he had exactly what operation
that caused the condition. It does not seem that you have not identified
anything similar, but it your case it just happens. Out of the blue,
I would guess on autogrow.

All that I can really suggest at this point is to identify exactly
which database you need to take off-line for the situation to resolve.
Of course setting up a Profiler trace with selected events may be an
idea.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 9 '05 #23

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: rbt | last post by:
Would a Python process consume more memory on a PC with lots of memory? For example, say I have the same Python script running on two WinXP computers that both have Python 2.4.0. One computer has...
2
by: tomvr | last post by:
Hello I have noticed some 'weird' memory usage in a vb.net windows app The situation is as follows I have an app (heavy on images) with 2 forms (actually there are more forms and on starting...
3
by: Ian Taite | last post by:
Hello, I'm exploring why one of my C# .NET apps has "high" memory usage, and whether I can reduce the memory usage. I have an app that wakes up and processes text files into a database...
26
by: Bruno Jouhier [MVP] | last post by:
I'm currently experiencing a strange phenomenon: At my Office, Visual Studio takes a very long time to compile our solution (more than 1 minute for the first project). At home, Visual Studio...
11
by: Paulo Eduardo | last post by:
Hi, All! We are developing one app for windows 95/98/Me/NT4.0/2000/XP/2003 using Visual C++ 6.0. We need to set the % of CPU Usage to app process. Is there an API to set % of CPU Usage? Can...
10
by: rdemyan via AccessMonster.com | last post by:
My app contains utility meter usage. One of the things we have to deal with is when a usage is clearly incorrect. Perhaps someone wrote the meter reading down incorrectly or made a factor of 10...
3
by: Sirisha | last post by:
I am using the following code to get the CPU usage PerformanceCounter myCounter; myCounter = new PerformanceCounter(); myCounter.CategoryName = "Processor"; myCounter.CounterName = "%...
1
by: spacecoyote | last post by:
I tried this: usage = "Something, by Spacecoyote\nusage: %prog file " parser = OptionParser(usage) test.py --help and I expected: Something, by Spacecoyote usage: test.py file
1
by: sowmya.rangineni | last post by:
Ours is a windows based application. When we open the application the CPU usage is 0% and the Memory Usage is 54,324Kb When I open a specific form in a module, the CPU usage is 0% and the...
2
by: jld | last post by:
Hi, I developed an asp.net based eCommerce Website for a client and it is hosted at discount asp. The site is quite interactive, queries a database a lot and uses ajax.asp.net to spice up...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.