What kinds of operations on a db server cause heavy cpu usage as opposed to IO?
Could sorts or group bys use cpu heavily?
tia
MG
On Thu, 21 Feb 2008 09:29:01 -0800, Hurme <michael.geles@.thomson.com>
wrote:
>What kinds of operations on a db server cause heavy cpu usage as opposed to IO?
>Could sorts or group bys use cpu heavily?
>tia
An operation on a lot of rows, where the rows are already in memory
will be CPU intensive. GROUP BY and ORDER BY can fall into that
category.
Roy Harvey
Beacon Falls, CT
|||Almost any SQL operation can cause heavy cpu usage as long as the operation
is applied to data already in cache. So in addition to what Roy mentioned,
your simple SELECT or any of the aggregate functions usch as COUNT and SUM
can all drive up cpu usage, if they are run against a small set of data and
are run repeatedly. 'Small' in this case is relative to the SQL instance
buffer pool size.
Linchi
"Hurme" wrote:
> What kinds of operations on a db server cause heavy cpu usage as opposed to IO?
> Could sorts or group bys use cpu heavily?
> tia
> --
> MG
|||Optimization is one area. Certain kinds of data conversion and comparisons
can also drive CPU usage. SQL will try and trade CPU and memory usage to
drive down I/O requirements as a way to speed up the overall system.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Hurme" <michael.geles@.thomson.com> wrote in message
news:0394DA83-9905-4B54-8A52-4BC73EDAE326@.microsoft.com...
> What kinds of operations on a db server cause heavy cpu usage as opposed
> to IO?
> Could sorts or group bys use cpu heavily?
> tia
> --
> MG
Showing posts with label cause. Show all posts
Showing posts with label cause. Show all posts
Thursday, March 8, 2012
CPU Usage
What kinds of operations on a db server cause heavy cpu usage as opposed to IO?
Could sorts or group bys use cpu heavily?
tia
--
MGOn Thu, 21 Feb 2008 09:29:01 -0800, Hurme <michael.geles@.thomson.com>
wrote:
>What kinds of operations on a db server cause heavy cpu usage as opposed to IO?
>Could sorts or group bys use cpu heavily?
>tia
An operation on a lot of rows, where the rows are already in memory
will be CPU intensive. GROUP BY and ORDER BY can fall into that
category.
Roy Harvey
Beacon Falls, CT|||Almost any SQL operation can cause heavy cpu usage as long as the operation
is applied to data already in cache. So in addition to what Roy mentioned,
your simple SELECT or any of the aggregate functions usch as COUNT and SUM
can all drive up cpu usage, if they are run against a small set of data and
are run repeatedly. 'Small' in this case is relative to the SQL instance
buffer pool size.
Linchi
"Hurme" wrote:
> What kinds of operations on a db server cause heavy cpu usage as opposed to IO?
> Could sorts or group bys use cpu heavily?
> tia
> --
> MG|||Optimization is one area. Certain kinds of data conversion and comparisons
can also drive CPU usage. SQL will try and trade CPU and memory usage to
drive down I/O requirements as a way to speed up the overall system.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Hurme" <michael.geles@.thomson.com> wrote in message
news:0394DA83-9905-4B54-8A52-4BC73EDAE326@.microsoft.com...
> What kinds of operations on a db server cause heavy cpu usage as opposed
> to IO?
> Could sorts or group bys use cpu heavily?
> tia
> --
> MG
Could sorts or group bys use cpu heavily?
tia
--
MGOn Thu, 21 Feb 2008 09:29:01 -0800, Hurme <michael.geles@.thomson.com>
wrote:
>What kinds of operations on a db server cause heavy cpu usage as opposed to IO?
>Could sorts or group bys use cpu heavily?
>tia
An operation on a lot of rows, where the rows are already in memory
will be CPU intensive. GROUP BY and ORDER BY can fall into that
category.
Roy Harvey
Beacon Falls, CT|||Almost any SQL operation can cause heavy cpu usage as long as the operation
is applied to data already in cache. So in addition to what Roy mentioned,
your simple SELECT or any of the aggregate functions usch as COUNT and SUM
can all drive up cpu usage, if they are run against a small set of data and
are run repeatedly. 'Small' in this case is relative to the SQL instance
buffer pool size.
Linchi
"Hurme" wrote:
> What kinds of operations on a db server cause heavy cpu usage as opposed to IO?
> Could sorts or group bys use cpu heavily?
> tia
> --
> MG|||Optimization is one area. Certain kinds of data conversion and comparisons
can also drive CPU usage. SQL will try and trade CPU and memory usage to
drive down I/O requirements as a way to speed up the overall system.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Hurme" <michael.geles@.thomson.com> wrote in message
news:0394DA83-9905-4B54-8A52-4BC73EDAE326@.microsoft.com...
> What kinds of operations on a db server cause heavy cpu usage as opposed
> to IO?
> Could sorts or group bys use cpu heavily?
> tia
> --
> MG
Wednesday, March 7, 2012
CPU Ruuning at 100%
We have an instance of SQLRS on a virtual server under W2003.
Recently we had cause to reset the sever due to a dramatic slowdown in
response times. Upon checking we found the CPU usage was constantly around 95
to 100%.
Stopping the RS and SQL services made no difference and it was only resolved
by restarting the server itself.
This coincided with a user accessing an RS report with an Excel output,
which failed to respond 'fast enough' so he closed the window prior to the
dialogue box appearing (open-save-cancel).
We then set up a second server, and emulated exactly the users actions and
found the same problems with the CPU running out of control, requiring a
reset.
What might be the cause of this problem? I understand the user closing the
window initially caused the problem, but I am at a loss as to why stopping
the RS and SQL Services failed to resolve the problem. What other process
could RS have initiated that went out of control?
ANY pointers would be of use as this functionality is essential to some of
our processes, but we will need to restrict it's until this gets resolved.
Note: We have not, as yet, installed SP2 on either of the RS Servers.
Thanks in advance,We've ran into the same issues with high CPU usage. Support was never able
to point out the exact cause of the CPU spikes.
We have since installed all SPs, and the latest HotFix. So far, we haven't
seen the issue popup again. Maybe the SP2 helped with that, but we dont
know. It'll be good to know what causes it.
"Logicalman" <tony9scott45us@.com7cast.net34> wrote in message
news:5DE74C28-B866-4F79-9E74-0539C6514E78@.microsoft.com...
> We have an instance of SQLRS on a virtual server under W2003.
> Recently we had cause to reset the sever due to a dramatic slowdown in
> response times. Upon checking we found the CPU usage was constantly around
> 95
> to 100%.
> Stopping the RS and SQL services made no difference and it was only
> resolved
> by restarting the server itself.
> This coincided with a user accessing an RS report with an Excel output,
> which failed to respond 'fast enough' so he closed the window prior to the
> dialogue box appearing (open-save-cancel).
> We then set up a second server, and emulated exactly the users actions and
> found the same problems with the CPU running out of control, requiring a
> reset.
> What might be the cause of this problem? I understand the user closing the
> window initially caused the problem, but I am at a loss as to why stopping
> the RS and SQL Services failed to resolve the problem. What other process
> could RS have initiated that went out of control?
> ANY pointers would be of use as this functionality is essential to some of
> our processes, but we will need to restrict it's until this gets resolved.
> Note: We have not, as yet, installed SP2 on either of the RS Servers.
> Thanks in advance,
>
>|||TechnoSpyke,
Thanks for the info. I intend setting up a test scenario and have the Server
guys run every trace they can to pinpoint what
process/subProcess/Application/Service is kicked off as a result of the said
actions.
I will post what results, if we get anything, even semi-conclusive, back to
this thread.
If anyone else has anything to chip in for me to check whilst testing this,
please feel free..
Thanks again
"TechnoSpyke" wrote:
> We've ran into the same issues with high CPU usage. Support was never able
> to point out the exact cause of the CPU spikes.
> We have since installed all SPs, and the latest HotFix. So far, we haven't
> seen the issue popup again. Maybe the SP2 helped with that, but we dont
> know. It'll be good to know what causes it.
>
> "Logicalman" <tony9scott45us@.com7cast.net34> wrote in message
> news:5DE74C28-B866-4F79-9E74-0539C6514E78@.microsoft.com...
> > We have an instance of SQLRS on a virtual server under W2003.
> > Recently we had cause to reset the sever due to a dramatic slowdown in
> > response times. Upon checking we found the CPU usage was constantly around
> > 95
> > to 100%.
> > Stopping the RS and SQL services made no difference and it was only
> > resolved
> > by restarting the server itself.
> > This coincided with a user accessing an RS report with an Excel output,
> > which failed to respond 'fast enough' so he closed the window prior to the
> > dialogue box appearing (open-save-cancel).
> > We then set up a second server, and emulated exactly the users actions and
> > found the same problems with the CPU running out of control, requiring a
> > reset.
> > What might be the cause of this problem? I understand the user closing the
> > window initially caused the problem, but I am at a loss as to why stopping
> > the RS and SQL Services failed to resolve the problem. What other process
> > could RS have initiated that went out of control?
> > ANY pointers would be of use as this functionality is essential to some of
> > our processes, but we will need to restrict it's until this gets resolved.
> > Note: We have not, as yet, installed SP2 on either of the RS Servers.
> >
> > Thanks in advance,
> >
> >
> >
>
>
Recently we had cause to reset the sever due to a dramatic slowdown in
response times. Upon checking we found the CPU usage was constantly around 95
to 100%.
Stopping the RS and SQL services made no difference and it was only resolved
by restarting the server itself.
This coincided with a user accessing an RS report with an Excel output,
which failed to respond 'fast enough' so he closed the window prior to the
dialogue box appearing (open-save-cancel).
We then set up a second server, and emulated exactly the users actions and
found the same problems with the CPU running out of control, requiring a
reset.
What might be the cause of this problem? I understand the user closing the
window initially caused the problem, but I am at a loss as to why stopping
the RS and SQL Services failed to resolve the problem. What other process
could RS have initiated that went out of control?
ANY pointers would be of use as this functionality is essential to some of
our processes, but we will need to restrict it's until this gets resolved.
Note: We have not, as yet, installed SP2 on either of the RS Servers.
Thanks in advance,We've ran into the same issues with high CPU usage. Support was never able
to point out the exact cause of the CPU spikes.
We have since installed all SPs, and the latest HotFix. So far, we haven't
seen the issue popup again. Maybe the SP2 helped with that, but we dont
know. It'll be good to know what causes it.
"Logicalman" <tony9scott45us@.com7cast.net34> wrote in message
news:5DE74C28-B866-4F79-9E74-0539C6514E78@.microsoft.com...
> We have an instance of SQLRS on a virtual server under W2003.
> Recently we had cause to reset the sever due to a dramatic slowdown in
> response times. Upon checking we found the CPU usage was constantly around
> 95
> to 100%.
> Stopping the RS and SQL services made no difference and it was only
> resolved
> by restarting the server itself.
> This coincided with a user accessing an RS report with an Excel output,
> which failed to respond 'fast enough' so he closed the window prior to the
> dialogue box appearing (open-save-cancel).
> We then set up a second server, and emulated exactly the users actions and
> found the same problems with the CPU running out of control, requiring a
> reset.
> What might be the cause of this problem? I understand the user closing the
> window initially caused the problem, but I am at a loss as to why stopping
> the RS and SQL Services failed to resolve the problem. What other process
> could RS have initiated that went out of control?
> ANY pointers would be of use as this functionality is essential to some of
> our processes, but we will need to restrict it's until this gets resolved.
> Note: We have not, as yet, installed SP2 on either of the RS Servers.
> Thanks in advance,
>
>|||TechnoSpyke,
Thanks for the info. I intend setting up a test scenario and have the Server
guys run every trace they can to pinpoint what
process/subProcess/Application/Service is kicked off as a result of the said
actions.
I will post what results, if we get anything, even semi-conclusive, back to
this thread.
If anyone else has anything to chip in for me to check whilst testing this,
please feel free..
Thanks again
"TechnoSpyke" wrote:
> We've ran into the same issues with high CPU usage. Support was never able
> to point out the exact cause of the CPU spikes.
> We have since installed all SPs, and the latest HotFix. So far, we haven't
> seen the issue popup again. Maybe the SP2 helped with that, but we dont
> know. It'll be good to know what causes it.
>
> "Logicalman" <tony9scott45us@.com7cast.net34> wrote in message
> news:5DE74C28-B866-4F79-9E74-0539C6514E78@.microsoft.com...
> > We have an instance of SQLRS on a virtual server under W2003.
> > Recently we had cause to reset the sever due to a dramatic slowdown in
> > response times. Upon checking we found the CPU usage was constantly around
> > 95
> > to 100%.
> > Stopping the RS and SQL services made no difference and it was only
> > resolved
> > by restarting the server itself.
> > This coincided with a user accessing an RS report with an Excel output,
> > which failed to respond 'fast enough' so he closed the window prior to the
> > dialogue box appearing (open-save-cancel).
> > We then set up a second server, and emulated exactly the users actions and
> > found the same problems with the CPU running out of control, requiring a
> > reset.
> > What might be the cause of this problem? I understand the user closing the
> > window initially caused the problem, but I am at a loss as to why stopping
> > the RS and SQL Services failed to resolve the problem. What other process
> > could RS have initiated that went out of control?
> > ANY pointers would be of use as this functionality is essential to some of
> > our processes, but we will need to restrict it's until this gets resolved.
> > Note: We have not, as yet, installed SP2 on either of the RS Servers.
> >
> > Thanks in advance,
> >
> >
> >
>
>
Subscribe to:
Posts (Atom)