CPU usage

I have been using SQL Server 2000 for some time and am attempting to upgrade
to SQL Server 2005 (testing the trial version).
I backed up my SQL Server 2000 database(s) and then restored them on SQL
Server 2005 which worked well.
Everything seemed to be working fine until I updated SQL Server 2005 with
service pack 2.
I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL Server
is now using 25% CPU continuously (or 100% of one of the processor
instances). The databases I converted are working as expected, however, the
SQL Server process is continuously burning CPU when nothing is happening
(this is over several days now).
Has anybody else see this problem?Ross,
I have seen this happen in the past, for many different reasons.
I assume from your comment that you know that it is sqlservr.exe that is
using the 25%. Can you run sp_who2 to identify some process that continues
to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to find
the oldest transaction in each database. (However, I assume that there is
not a blocking transaction or you would be having other problems.)
If you identify a problem spid, try to get the hostname for that spid.
Sometimes a client computer loses connectivity, but the SQL Server does not
know that, so it keeps trying (forever) to send the result set to the
client. If that is the case, logging the hostname computer off of the
domain, then logging back into the domain, often alerts SQL Server to the
problem so that it will abandon that result set.
If appropriate, you can try to KILL the spid. (But the spid may not always
be killable.)
Other software can also get into a confused state and eat up CPU. For
example, a DTS package, backup software, etc. can also get into an confused
state and never stop running. Use Windows Task Manager to check whether
another task is actually the guilty party. If it is one of those, then try
killing that Windows process.
If you cannot do anything else, you should schedule a restart of your SQL
Sever. That should (naturally) clear the problem out. But you will need to
remain alert to its returning.
But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
RLF
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
>I have been using SQL Server 2000 for some time and am attempting to
>upgrade
> to SQL Server 2005 (testing the trial version).
> I backed up my SQL Server 2000 database(s) and then restored them on SQL
> Server 2005 which worked well.
> Everything seemed to be working fine until I updated SQL Server 2005 with
> service pack 2.
> I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL Server
> is now using 25% CPU continuously (or 100% of one of the processor
> instances). The databases I converted are working as expected, however,
> the
> SQL Server process is continuously burning CPU when nothing is happening
> (this is over several days now).
> Has anybody else see this problem?
>|||Thanks for your answer.
It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I have
actually restarted SQL Server (and the machine itself) and subsequently the
SQL Server process goes right back up to 25%.
As you noted regarding the blocking transaction, I'm not experiencing any
problems with the database, just the CPU usage.
The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of 71195468 (as
it happens, the machine was restarted at 11:19PM EST last night).
Full row information from sp_who2:
SPID 3
Status BACKGROUND
Login sa
HostName .
BlkBy .
DBName NULL
Command LAZY WRITER
CPU TIME 71195468
DiskIO 0
LastBatch 03/12 23:19:34
ProgramName (blank)
SPID 3
REQUESTID 0
Any ideas?
Thanks again for your help...
Ross
"Russell Fields" wrote:
> Ross,
> I have seen this happen in the past, for many different reasons.
> I assume from your comment that you know that it is sqlservr.exe that is
> using the 25%. Can you run sp_who2 to identify some process that continues
> to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
> sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to find
> the oldest transaction in each database. (However, I assume that there is
> not a blocking transaction or you would be having other problems.)
> If you identify a problem spid, try to get the hostname for that spid.
> Sometimes a client computer loses connectivity, but the SQL Server does not
> know that, so it keeps trying (forever) to send the result set to the
> client. If that is the case, logging the hostname computer off of the
> domain, then logging back into the domain, often alerts SQL Server to the
> problem so that it will abandon that result set.
> If appropriate, you can try to KILL the spid. (But the spid may not always
> be killable.)
> Other software can also get into a confused state and eat up CPU. For
> example, a DTS package, backup software, etc. can also get into an confused
> state and never stop running. Use Windows Task Manager to check whether
> another task is actually the guilty party. If it is one of those, then try
> killing that Windows process.
> If you cannot do anything else, you should schedule a restart of your SQL
> Sever. That should (naturally) clear the problem out. But you will need to
> remain alert to its returning.
> But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
> RLF
>
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
> >I have been using SQL Server 2000 for some time and am attempting to
> >upgrade
> > to SQL Server 2005 (testing the trial version).
> >
> > I backed up my SQL Server 2000 database(s) and then restored them on SQL
> > Server 2005 which worked well.
> >
> > Everything seemed to be working fine until I updated SQL Server 2005 with
> > service pack 2.
> >
> > I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL Server
> > is now using 25% CPU continuously (or 100% of one of the processor
> > instances). The databases I converted are working as expected, however,
> > the
> > SQL Server process is continuously burning CPU when nothing is happening
> > (this is over several days now).
> >
> > Has anybody else see this problem?
> >
> >
>
>|||Ross,
Check out: http://support.microsoft.com/kb/931821
RLF
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...
> Thanks for your answer.
> It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I have
> actually restarted SQL Server (and the machine itself) and subsequently
> the
> SQL Server process goes right back up to 25%.
> As you noted regarding the blocking transaction, I'm not experiencing any
> problems with the database, just the CPU usage.
> The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of 71195468
> (as
> it happens, the machine was restarted at 11:19PM EST last night).
> Full row information from sp_who2:
> SPID 3
> Status BACKGROUND
> Login sa
> HostName .
> BlkBy .
> DBName NULL
> Command LAZY WRITER
> CPU TIME 71195468
> DiskIO 0
> LastBatch 03/12 23:19:34
> ProgramName (blank)
> SPID 3
> REQUESTID 0
> Any ideas?
> Thanks again for your help...
> Ross
>
> "Russell Fields" wrote:
>> Ross,
>> I have seen this happen in the past, for many different reasons.
>> I assume from your comment that you know that it is sqlservr.exe that is
>> using the 25%. Can you run sp_who2 to identify some process that
>> continues
>> to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
>> sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to
>> find
>> the oldest transaction in each database. (However, I assume that there
>> is
>> not a blocking transaction or you would be having other problems.)
>> If you identify a problem spid, try to get the hostname for that spid.
>> Sometimes a client computer loses connectivity, but the SQL Server does
>> not
>> know that, so it keeps trying (forever) to send the result set to the
>> client. If that is the case, logging the hostname computer off of the
>> domain, then logging back into the domain, often alerts SQL Server to the
>> problem so that it will abandon that result set.
>> If appropriate, you can try to KILL the spid. (But the spid may not
>> always
>> be killable.)
>> Other software can also get into a confused state and eat up CPU. For
>> example, a DTS package, backup software, etc. can also get into an
>> confused
>> state and never stop running. Use Windows Task Manager to check whether
>> another task is actually the guilty party. If it is one of those, then
>> try
>> killing that Windows process.
>> If you cannot do anything else, you should schedule a restart of your SQL
>> Sever. That should (naturally) clear the problem out. But you will need
>> to
>> remain alert to its returning.
>> But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
>> RLF
>>
>> "Ross" <Ross@.discussions.microsoft.com> wrote in message
>> news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
>> >I have been using SQL Server 2000 for some time and am attempting to
>> >upgrade
>> > to SQL Server 2005 (testing the trial version).
>> >
>> > I backed up my SQL Server 2000 database(s) and then restored them on
>> > SQL
>> > Server 2005 which worked well.
>> >
>> > Everything seemed to be working fine until I updated SQL Server 2005
>> > with
>> > service pack 2.
>> >
>> > I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL
>> > Server
>> > is now using 25% CPU continuously (or 100% of one of the processor
>> > instances). The databases I converted are working as expected,
>> > however,
>> > the
>> > SQL Server process is continuously burning CPU when nothing is
>> > happening
>> > (this is over several days now).
>> >
>> > Has anybody else see this problem?
>> >
>> >
>>|||Thank you very much.
I've requested the hotfix and will see what happens.
"Russell Fields" wrote:
> Ross,
> Check out: http://support.microsoft.com/kb/931821
> RLF
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...
> > Thanks for your answer.
> >
> > It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I have
> > actually restarted SQL Server (and the machine itself) and subsequently
> > the
> > SQL Server process goes right back up to 25%.
> >
> > As you noted regarding the blocking transaction, I'm not experiencing any
> > problems with the database, just the CPU usage.
> >
> > The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of 71195468
> > (as
> > it happens, the machine was restarted at 11:19PM EST last night).
> >
> > Full row information from sp_who2:
> >
> > SPID 3
> > Status BACKGROUND
> > Login sa
> > HostName .
> > BlkBy .
> > DBName NULL
> > Command LAZY WRITER
> > CPU TIME 71195468
> > DiskIO 0
> > LastBatch 03/12 23:19:34
> > ProgramName (blank)
> > SPID 3
> > REQUESTID 0
> >
> > Any ideas?
> >
> > Thanks again for your help...
> >
> > Ross
> >
> >
> >
> > "Russell Fields" wrote:
> >
> >> Ross,
> >>
> >> I have seen this happen in the past, for many different reasons.
> >>
> >> I assume from your comment that you know that it is sqlservr.exe that is
> >> using the 25%. Can you run sp_who2 to identify some process that
> >> continues
> >> to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
> >> sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to
> >> find
> >> the oldest transaction in each database. (However, I assume that there
> >> is
> >> not a blocking transaction or you would be having other problems.)
> >>
> >> If you identify a problem spid, try to get the hostname for that spid.
> >> Sometimes a client computer loses connectivity, but the SQL Server does
> >> not
> >> know that, so it keeps trying (forever) to send the result set to the
> >> client. If that is the case, logging the hostname computer off of the
> >> domain, then logging back into the domain, often alerts SQL Server to the
> >> problem so that it will abandon that result set.
> >>
> >> If appropriate, you can try to KILL the spid. (But the spid may not
> >> always
> >> be killable.)
> >>
> >> Other software can also get into a confused state and eat up CPU. For
> >> example, a DTS package, backup software, etc. can also get into an
> >> confused
> >> state and never stop running. Use Windows Task Manager to check whether
> >> another task is actually the guilty party. If it is one of those, then
> >> try
> >> killing that Windows process.
> >>
> >> If you cannot do anything else, you should schedule a restart of your SQL
> >> Sever. That should (naturally) clear the problem out. But you will need
> >> to
> >> remain alert to its returning.
> >>
> >> But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
> >>
> >> RLF
> >>
> >>
> >> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> >> news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
> >> >I have been using SQL Server 2000 for some time and am attempting to
> >> >upgrade
> >> > to SQL Server 2005 (testing the trial version).
> >> >
> >> > I backed up my SQL Server 2000 database(s) and then restored them on
> >> > SQL
> >> > Server 2005 which worked well.
> >> >
> >> > Everything seemed to be working fine until I updated SQL Server 2005
> >> > with
> >> > service pack 2.
> >> >
> >> > I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL
> >> > Server
> >> > is now using 25% CPU continuously (or 100% of one of the processor
> >> > instances). The databases I converted are working as expected,
> >> > however,
> >> > the
> >> > SQL Server process is continuously burning CPU when nothing is
> >> > happening
> >> > (this is over several days now).
> >> >
> >> > Has anybody else see this problem?
> >> >
> >> >
> >>
> >>
> >>
>
>|||Unfortunately, that didn't go very well.
I installed the hotfix which appeared to solve the CPU problem (sqlservr.exe
back at 0% when no activity). I was able to run the management software and
all looked fine, but when I attempted to run my web application, sql server
choked. A simple query returning a datareader (ASP.NET) caused SQLServer to
get a memory exception.
Needless to say, I removed the Hotfix and am now back where I started. I
guess I'll have to wait until Microsoft releases the hotfix in an update.
Ross
"Russell Fields" wrote:
> Ross,
> Check out: http://support.microsoft.com/kb/931821
> RLF
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...
> > Thanks for your answer.
> >
> > It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I have
> > actually restarted SQL Server (and the machine itself) and subsequently
> > the
> > SQL Server process goes right back up to 25%.
> >
> > As you noted regarding the blocking transaction, I'm not experiencing any
> > problems with the database, just the CPU usage.
> >
> > The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of 71195468
> > (as
> > it happens, the machine was restarted at 11:19PM EST last night).
> >
> > Full row information from sp_who2:
> >
> > SPID 3
> > Status BACKGROUND
> > Login sa
> > HostName .
> > BlkBy .
> > DBName NULL
> > Command LAZY WRITER
> > CPU TIME 71195468
> > DiskIO 0
> > LastBatch 03/12 23:19:34
> > ProgramName (blank)
> > SPID 3
> > REQUESTID 0
> >
> > Any ideas?
> >
> > Thanks again for your help...
> >
> > Ross
> >
> >
> >
> > "Russell Fields" wrote:
> >
> >> Ross,
> >>
> >> I have seen this happen in the past, for many different reasons.
> >>
> >> I assume from your comment that you know that it is sqlservr.exe that is
> >> using the 25%. Can you run sp_who2 to identify some process that
> >> continues
> >> to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
> >> sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to
> >> find
> >> the oldest transaction in each database. (However, I assume that there
> >> is
> >> not a blocking transaction or you would be having other problems.)
> >>
> >> If you identify a problem spid, try to get the hostname for that spid.
> >> Sometimes a client computer loses connectivity, but the SQL Server does
> >> not
> >> know that, so it keeps trying (forever) to send the result set to the
> >> client. If that is the case, logging the hostname computer off of the
> >> domain, then logging back into the domain, often alerts SQL Server to the
> >> problem so that it will abandon that result set.
> >>
> >> If appropriate, you can try to KILL the spid. (But the spid may not
> >> always
> >> be killable.)
> >>
> >> Other software can also get into a confused state and eat up CPU. For
> >> example, a DTS package, backup software, etc. can also get into an
> >> confused
> >> state and never stop running. Use Windows Task Manager to check whether
> >> another task is actually the guilty party. If it is one of those, then
> >> try
> >> killing that Windows process.
> >>
> >> If you cannot do anything else, you should schedule a restart of your SQL
> >> Sever. That should (naturally) clear the problem out. But you will need
> >> to
> >> remain alert to its returning.
> >>
> >> But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
> >>
> >> RLF
> >>
> >>
> >> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> >> news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
> >> >I have been using SQL Server 2000 for some time and am attempting to
> >> >upgrade
> >> > to SQL Server 2005 (testing the trial version).
> >> >
> >> > I backed up my SQL Server 2000 database(s) and then restored them on
> >> > SQL
> >> > Server 2005 which worked well.
> >> >
> >> > Everything seemed to be working fine until I updated SQL Server 2005
> >> > with
> >> > service pack 2.
> >> >
> >> > I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL
> >> > Server
> >> > is now using 25% CPU continuously (or 100% of one of the processor
> >> > instances). The databases I converted are working as expected,
> >> > however,
> >> > the
> >> > SQL Server process is continuously burning CPU when nothing is
> >> > happening
> >> > (this is over several days now).
> >> >
> >> > Has anybody else see this problem?
> >> >
> >> >
> >>
> >>
> >>
>
>|||Ross,
Too bad. Memory exceptions are bugs, so you could report it to Microsoft if
you want.
RLF
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:8498810D-A651-4F6C-9970-A6FD26FC8835@.microsoft.com...
> Unfortunately, that didn't go very well.
> I installed the hotfix which appeared to solve the CPU problem
> (sqlservr.exe
> back at 0% when no activity). I was able to run the management software
> and
> all looked fine, but when I attempted to run my web application, sql
> server
> choked. A simple query returning a datareader (ASP.NET) caused SQLServer
> to
> get a memory exception.
> Needless to say, I removed the Hotfix and am now back where I started. I
> guess I'll have to wait until Microsoft releases the hotfix in an update.
> Ross
> "Russell Fields" wrote:
>> Ross,
>> Check out: http://support.microsoft.com/kb/931821
>> RLF
>> "Ross" <Ross@.discussions.microsoft.com> wrote in message
>> news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...
>> > Thanks for your answer.
>> >
>> > It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I
>> > have
>> > actually restarted SQL Server (and the machine itself) and subsequently
>> > the
>> > SQL Server process goes right back up to 25%.
>> >
>> > As you noted regarding the blocking transaction, I'm not experiencing
>> > any
>> > problems with the database, just the CPU usage.
>> >
>> > The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of
>> > 71195468
>> > (as
>> > it happens, the machine was restarted at 11:19PM EST last night).
>> >
>> > Full row information from sp_who2:
>> >
>> > SPID 3
>> > Status BACKGROUND
>> > Login sa
>> > HostName .
>> > BlkBy .
>> > DBName NULL
>> > Command LAZY WRITER
>> > CPU TIME 71195468
>> > DiskIO 0
>> > LastBatch 03/12 23:19:34
>> > ProgramName (blank)
>> > SPID 3
>> > REQUESTID 0
>> >
>> > Any ideas?
>> >
>> > Thanks again for your help...
>> >
>> > Ross
>> >
>> >
>> >
>> > "Russell Fields" wrote:
>> >
>> >> Ross,
>> >>
>> >> I have seen this happen in the past, for many different reasons.
>> >>
>> >> I assume from your comment that you know that it is sqlservr.exe that
>> >> is
>> >> using the 25%. Can you run sp_who2 to identify some process that
>> >> continues
>> >> to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look
>> >> at
>> >> sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN
>> >> to
>> >> find
>> >> the oldest transaction in each database. (However, I assume that
>> >> there
>> >> is
>> >> not a blocking transaction or you would be having other problems.)
>> >>
>> >> If you identify a problem spid, try to get the hostname for that spid.
>> >> Sometimes a client computer loses connectivity, but the SQL Server
>> >> does
>> >> not
>> >> know that, so it keeps trying (forever) to send the result set to the
>> >> client. If that is the case, logging the hostname computer off of
>> >> the
>> >> domain, then logging back into the domain, often alerts SQL Server to
>> >> the
>> >> problem so that it will abandon that result set.
>> >>
>> >> If appropriate, you can try to KILL the spid. (But the spid may not
>> >> always
>> >> be killable.)
>> >>
>> >> Other software can also get into a confused state and eat up CPU. For
>> >> example, a DTS package, backup software, etc. can also get into an
>> >> confused
>> >> state and never stop running. Use Windows Task Manager to check
>> >> whether
>> >> another task is actually the guilty party. If it is one of those, then
>> >> try
>> >> killing that Windows process.
>> >>
>> >> If you cannot do anything else, you should schedule a restart of your
>> >> SQL
>> >> Sever. That should (naturally) clear the problem out. But you will
>> >> need
>> >> to
>> >> remain alert to its returning.
>> >>
>> >> But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
>> >>
>> >> RLF
>> >>
>> >>
>> >> "Ross" <Ross@.discussions.microsoft.com> wrote in message
>> >> news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
>> >> >I have been using SQL Server 2000 for some time and am attempting to
>> >> >upgrade
>> >> > to SQL Server 2005 (testing the trial version).
>> >> >
>> >> > I backed up my SQL Server 2000 database(s) and then restored them on
>> >> > SQL
>> >> > Server 2005 which worked well.
>> >> >
>> >> > Everything seemed to be working fine until I updated SQL Server 2005
>> >> > with
>> >> > service pack 2.
>> >> >
>> >> > I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL
>> >> > Server
>> >> > is now using 25% CPU continuously (or 100% of one of the processor
>> >> > instances). The databases I converted are working as expected,
>> >> > however,
>> >> > the
>> >> > SQL Server process is continuously burning CPU when nothing is
>> >> > happening
>> >> > (this is over several days now).
>> >> >
>> >> > Has anybody else see this problem?
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>