100 % CPU Usage

[Paul]

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

[Erland Sommarskog]

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

[Paul]

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

[Paul]

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

[Erland Sommarskog]

> 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

[Paul]

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

[angsql]

Hi Paul,

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

..AS

[Paul]

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

[Erland Sommarskog]

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

[Paul]

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

[Brian Cryer]

"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

[Paul]

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

[Paul]

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

[Erland Sommarskog]

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

[Paul]

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

[Brian Cryer]

"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

[Paul]

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

[Brian Cryer]

"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

[Paul]

Well thankyou to everybody who has posted. I will post back when (if) I
do find a resolution to this problem.

Paul

[Paul]

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

[Erland Sommarskog]

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

[Ian Murphy]

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 ***

[Erland Sommarskog]

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