CPU, Physical IO, & Memory Usage

I was wondering about what the values in Management->Current
Activity->Process Info represent. Are the values for CPU, Physical IO, &
Memory Usage cumulative or do they represent a max or a current value?
The reason I'm wondering is that I saw a large value for Memory Usage by a
process recently. The value was in the millions. Most other processes had
values < 100 and some went up to 4000 or so.
Should I be worrying about such a large value for any of these parameters?
Thanks
MG
They are cumulative values from the time the user logged on. For the most
part they are pretty useless.
Andrew J. Kelly SQL MVP
"MGeles" <michael.geles@.thomson.com> wrote in message
news:A0B603E2-0C4A-4A5A-839F-DE3CB94FCE42@.microsoft.com...
>I was wondering about what the values in Management->Current
> Activity->Process Info represent. Are the values for CPU, Physical IO, &
> Memory Usage cumulative or do they represent a max or a current value?
> The reason I'm wondering is that I saw a large value for Memory Usage by a
> process recently. The value was in the millions. Most other processes
> had
> values < 100 and some went up to 4000 or so.
> Should I be worrying about such a large value for any of these parameters?
> Thanks
> --
> MG

CPU, Physical IO, & Memory Usage

I was wondering about what the values in Management->Current
Activity->Process Info represent. Are the values for CPU, Physical IO, &
Memory Usage cumulative or do they represent a max or a current value?
The reason I'm wondering is that I saw a large value for Memory Usage by a
process recently. The value was in the millions. Most other processes had
values < 100 and some went up to 4000 or so.
Should I be worrying about such a large value for any of these parameters?
Thanks
--
MGThey are cumulative values from the time the user logged on. For the most
part they are pretty useless.
Andrew J. Kelly SQL MVP
"MGeles" <michael.geles@.thomson.com> wrote in message
news:A0B603E2-0C4A-4A5A-839F-DE3CB94FCE42@.microsoft.com...
>I was wondering about what the values in Management->Current
> Activity->Process Info represent. Are the values for CPU, Physical IO, &
> Memory Usage cumulative or do they represent a max or a current value?
> The reason I'm wondering is that I saw a large value for Memory Usage by a
> process recently. The value was in the millions. Most other processes
> had
> values < 100 and some went up to 4000 or so.
> Should I be worrying about such a large value for any of these parameters?
> Thanks
> --
> MG

CPU, Physical IO, & Memory Usage

I was wondering about what the values in Management->Current
Activity->Process Info represent. Are the values for CPU, Physical IO, &
Memory Usage cumulative or do they represent a max or a current value?
The reason I'm wondering is that I saw a large value for Memory Usage by a
process recently. The value was in the millions. Most other processes had
values < 100 and some went up to 4000 or so.
Should I be worrying about such a large value for any of these parameters?
Thanks
--
MGThey are cumulative values from the time the user logged on. For the most
part they are pretty useless.
--
Andrew J. Kelly SQL MVP
"MGeles" <michael.geles@.thomson.com> wrote in message
news:A0B603E2-0C4A-4A5A-839F-DE3CB94FCE42@.microsoft.com...
>I was wondering about what the values in Management->Current
> Activity->Process Info represent. Are the values for CPU, Physical IO, &
> Memory Usage cumulative or do they represent a max or a current value?
> The reason I'm wondering is that I saw a large value for Memory Usage by a
> process recently. The value was in the millions. Most other processes
> had
> values < 100 and some went up to 4000 or so.
> Should I be worrying about such a large value for any of these parameters?
> Thanks
> --
> MG

CPU Use Rising

Hello Group,
what is a good starting point setting a trace to troubleshoot CPU usage? My
network admin asked me to look at one of the servers: it seems after our
weekly restarts, the CPU usage keeps rising. What can I begin to look at to
determine what is causing this?Rich,
Start with Task Manager to determine what app is causing the issue. If SQL
Server, then drill down using System Monitor and Profiler.
HTH
Jerry
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:B55F66D8-C41D-4C04-9BC6-87F176891311@.microsoft.com...
> Hello Group,
> what is a good starting point setting a trace to troubleshoot CPU usage?
> My
> network admin asked me to look at one of the servers: it seems after our
> weekly restarts, the CPU usage keeps rising. What can I begin to look at
> to
> determine what is causing this?

CPU Use Rising

Hello Group,
what is a good starting point setting a trace to troubleshoot CPU usage? My
network admin asked me to look at one of the servers: it seems after our
weekly restarts, the CPU usage keeps rising. What can I begin to look at to
determine what is causing this?
Rich,
Start with Task Manager to determine what app is causing the issue. If SQL
Server, then drill down using System Monitor and Profiler.
HTH
Jerry
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:B55F66D8-C41D-4C04-9BC6-87F176891311@.microsoft.com...
> Hello Group,
> what is a good starting point setting a trace to troubleshoot CPU usage?
> My
> network admin asked me to look at one of the servers: it seems after our
> weekly restarts, the CPU usage keeps rising. What can I begin to look at
> to
> determine what is causing this?

CPU Use Rising

Hello Group,
what is a good starting point setting a trace to troubleshoot CPU usage? My
network admin asked me to look at one of the servers: it seems after our
weekly restarts, the CPU usage keeps rising. What can I begin to look at to
determine what is causing this?Rich,
Start with Task Manager to determine what app is causing the issue. If SQL
Server, then drill down using System Monitor and Profiler.
HTH
Jerry
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:B55F66D8-C41D-4C04-9BC6-87F176891311@.microsoft.com...
> Hello Group,
> what is a good starting point setting a trace to troubleshoot CPU usage?
> My
> network admin asked me to look at one of the servers: it seems after our
> weekly restarts, the CPU usage keeps rising. What can I begin to look at
> to
> determine what is causing this?

CPU Usage(%), Logical IO Performed (%) Usage for Adhoc Queries is 90%

Hello, When I am seeing SQL Server 2005 Management studio Server Dashboard> I am seeing my(USERS) databases and msdb database usage is very small % of in CPU Usage(%), Logical IO Performed (%) Usage pie chart.

90% of Total cpu usage is showing for Adhoc Queries. what excatly this means in Dashboard? if application uses more than it would have shown in Database level or not?

sicerely this dashboard is good, if any one is watching daily, please advice their experiences here.

Thanks in advance. Hail SQL Server!

This means that of all the CPU and I/O performed 90% is coming from Adhoc Queries, it does not mean 90% CPU usage.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

CPU Usage vs Catalog Population

I have an SQL account that uses FTS on SQL 2000.
I originally used Incremental Population to index the work done during the
day.
However, the indexing process was carrying over into the next work day.
Therefore, last week I changed the method to Change Tracking with Update
Index in Background (with John Kane's urging) to try to alleviate the loss
of performance being experienced during the day by the system's users.
It is my understanding that CT w/UIB would only perform indexing when the
CPU is idle and resources are available. By switching to this method, I
thought the overall performance would improve.
This morning I checked the SQL Server, CPU usage is averaging 60% and the
catalog status reports Population in Progress.
Consequently, all users are unable to perform logins, searches, ... because
of timeouts against the database.
I am remote to the application and finally got the menu up to issue the
command to stop population.
Q1:
I thought the Change Tracking with Update Index in Background option is to
prevent high CPU utilization.
Should I be seeing such high utilization with this option selected to the
point that other applications are timing out when trying to make a
connection to the database? Enterprise manager also times out when trying to
connect.
Q2:
The stop command has finally executed and the catalog status now reports
idle.
It has been over an hour and the CPU usage is still very high (50 - 60%).
Is this continued usage the result of the indexing process?
Is there residual processing that could go on for hours after the status
reports idle?
Q3:
Related to Q1, since the population status reports idle but I still see high
CPU utilization, how do I isolate what in particular is keeping the usage so
high? How to prove it is mssearch and not something else going on with the
database?
I see the sqlserver and mssearch processes listed in task mgr with high cpu
& memory usage.
Update index in background does not necessarily index when the CPU is idle
or resources are available. Rather it polls a table every second and indexed
rows which are marked to be re-indexed.
You might want to try a scheduled Update Index at intervals that work for
your requirements. For instance update the index every 1/2 hour.
If your table you are indexing is heavily updated you may run into these
problems with relatively high CPU utilization, you will also run into these
problems if your content is in German or Far East languages.
High CPU utilization just means your processor is busy, it isn't by itself a
bad thing. When it starts to cause execessive paging or create queueing is
when it can degrade overall system performance.
To isolate particular processes using high cpu you can use Performance
Monitor or simply sort the processes you find in Task Manager by CPU. You
can also run sp_who2 to get an idea of cumulative CPU and disk IO.
"RGondzur" <rgondzur@.NO_SPAM_aicsoft.com> wrote in message
news:es8K7UqFEHA.3336@.TK2MSFTNGP12.phx.gbl...
> I have an SQL account that uses FTS on SQL 2000.
> I originally used Incremental Population to index the work done during the
> day.
> However, the indexing process was carrying over into the next work day.
> Therefore, last week I changed the method to Change Tracking with Update
> Index in Background (with John Kane's urging) to try to alleviate the loss
> of performance being experienced during the day by the system's users.
> It is my understanding that CT w/UIB would only perform indexing when the
> CPU is idle and resources are available. By switching to this method, I
> thought the overall performance would improve.
> This morning I checked the SQL Server, CPU usage is averaging 60% and the
> catalog status reports Population in Progress.
> Consequently, all users are unable to perform logins, searches, ...
because
> of timeouts against the database.
> I am remote to the application and finally got the menu up to issue the
> command to stop population.
>
> Q1:
> I thought the Change Tracking with Update Index in Background option is to
> prevent high CPU utilization.
> Should I be seeing such high utilization with this option selected to the
> point that other applications are timing out when trying to make a
> connection to the database? Enterprise manager also times out when trying
to
> connect.
>
> Q2:
> The stop command has finally executed and the catalog status now reports
> idle.
> It has been over an hour and the CPU usage is still very high (50 - 60%).
> Is this continued usage the result of the indexing process?
> Is there residual processing that could go on for hours after the status
> reports idle?
>
> Q3:
> Related to Q1, since the population status reports idle but I still see
high
> CPU utilization, how do I isolate what in particular is keeping the usage
so
> high? How to prove it is mssearch and not something else going on with the
> database?
> I see the sqlserver and mssearch processes listed in task mgr with high
cpu
> & memory usage.
>
>

CPU Usage pegged at 100%

Last night we had reports that our website was not responding. After some
investigation we found that the issue was SQL Server had all of the CPU's
pegged at 100% on a server that usually runs no higher than 10%. Our site
was not exceptionally busy, nor have we installed anything new that accesses
the database recently. In the sQL Server Error Log I see alot of "buffer
latch errors", but they appear to have occurred after CPU usage had already
hit 100%.
No one here is really a SQL Server troubleshooting or tuning expert. What
should we do to try to determine the cause?
Thank youEvan,
This can be difficult to track down because you really do not have any idea
what was occuring at the time. You might consider creating an automated
proactive solution to tracking these kinds of issues by using Profiler,
jobs, alerts (performance condition alerts included), SysMon, MOM 2005
and/or a related third-party product.
HTH
Jerry
"Evan Nelson" <EvanNelson@.discussions.microsoft.com> wrote in message
news:CB0D7602-453D-4D35-A09C-9672D7B47E29@.microsoft.com...
> Last night we had reports that our website was not responding. After some
> investigation we found that the issue was SQL Server had all of the CPU's
> pegged at 100% on a server that usually runs no higher than 10%. Our site
> was not exceptionally busy, nor have we installed anything new that
> accesses
> the database recently. In the sQL Server Error Log I see alot of "buffer
> latch errors", but they appear to have occurred after CPU usage had
> already
> hit 100%.
> No one here is really a SQL Server troubleshooting or tuning expert. What
> should we do to try to determine the cause?
> Thank you
>|||Try this product eval:
http://www.teratrax.com/tpm/index.html

CPU Usage pegged at 100%

Last night we had reports that our website was not responding. After some
investigation we found that the issue was SQL Server had all of the CPU's
pegged at 100% on a server that usually runs no higher than 10%. Our site
was not exceptionally busy, nor have we installed anything new that accesses
the database recently. In the sQL Server Error Log I see alot of "buffer
latch errors", but they appear to have occurred after CPU usage had already
hit 100%.
No one here is really a SQL Server troubleshooting or tuning expert. What
should we do to try to determine the cause?
Thank you
Evan,
This can be difficult to track down because you really do not have any idea
what was occuring at the time. You might consider creating an automated
proactive solution to tracking these kinds of issues by using Profiler,
jobs, alerts (performance condition alerts included), SysMon, MOM 2005
and/or a related third-party product.
HTH
Jerry
"Evan Nelson" <EvanNelson@.discussions.microsoft.com> wrote in message
news:CB0D7602-453D-4D35-A09C-9672D7B47E29@.microsoft.com...
> Last night we had reports that our website was not responding. After some
> investigation we found that the issue was SQL Server had all of the CPU's
> pegged at 100% on a server that usually runs no higher than 10%. Our site
> was not exceptionally busy, nor have we installed anything new that
> accesses
> the database recently. In the sQL Server Error Log I see alot of "buffer
> latch errors", but they appear to have occurred after CPU usage had
> already
> hit 100%.
> No one here is really a SQL Server troubleshooting or tuning expert. What
> should we do to try to determine the cause?
> Thank you
>
|||Try this product eval:
http://www.teratrax.com/tpm/index.html

CPU Usage pegged at 100%

Last night we had reports that our website was not responding. After some
investigation we found that the issue was SQL Server had all of the CPU's
pegged at 100% on a server that usually runs no higher than 10%. Our site
was not exceptionally busy, nor have we installed anything new that accesses
the database recently. In the sQL Server Error Log I see alot of "buffer
latch errors", but they appear to have occurred after CPU usage had already
hit 100%.
No one here is really a SQL Server troubleshooting or tuning expert. What
should we do to try to determine the cause?
Thank youEvan,
This can be difficult to track down because you really do not have any idea
what was occuring at the time. You might consider creating an automated
proactive solution to tracking these kinds of issues by using Profiler,
jobs, alerts (performance condition alerts included), SysMon, MOM 2005
and/or a related third-party product.
HTH
Jerry
"Evan Nelson" <EvanNelson@.discussions.microsoft.com> wrote in message
news:CB0D7602-453D-4D35-A09C-9672D7B47E29@.microsoft.com...
> Last night we had reports that our website was not responding. After some
> investigation we found that the issue was SQL Server had all of the CPU's
> pegged at 100% on a server that usually runs no higher than 10%. Our site
> was not exceptionally busy, nor have we installed anything new that
> accesses
> the database recently. In the sQL Server Error Log I see alot of "buffer
> latch errors", but they appear to have occurred after CPU usage had
> already
> hit 100%.
> No one here is really a SQL Server troubleshooting or tuning expert. What
> should we do to try to determine the cause?
> Thank you
>|||Try this product eval:
http://www.teratrax.com/tpm/index.html

CPU usage on SQL server

Hi,
I have a quad processor clustered database server that is experiencing
significant performance problems. There are multiple user databases on ther
server, and the issues manifest themselves in different ways. We have had
users unable to save to a database to complete slow down of the whole server
.
The issues seem to be more prevalent in the morning, but we have had them
throughout the day as well. In monitoring the CPU usage we noticed that mos
t
of the activity is going through only 1 of the CPU's. CPU 0 can peak at 100
%
utilisation, and the other 3 have little or no activity at all. There has
been no changes to any settings and the SQL Server install is pretty
standard. Does anyone have any ideas?Meg wrote:
> Hi,
> I have a quad processor clustered database server that is experiencing
> significant performance problems. There are multiple user databases
> on ther server, and the issues manifest themselves in different ways.
> We have had users unable to save to a database to complete slow down
> of the whole server. The issues seem to be more prevalent in the
> morning, but we have had them throughout the day as well. In
> monitoring the CPU usage we noticed that most of the activity is
> going through only 1 of the CPU's. CPU 0 can peak at 100%
> utilisation, and the other 3 have little or no activity at all.
> There has been no changes to any settings and the SQL Server install
> is pretty standard. Does anyone have any ideas?
Have you assigned SQL Server all CPUs? Are you using MAXDOP 1 option in
your queries or have you set the "max degree of parallelism" option on
the server? I would think SQL Server would use all CPUs even if MAXDOP 1
were used when running multiple queries. It's probably some bad query
that is running on a single CPU that is causing the CPU spike. Many
query operations run on a single CPU. And blocking is probably the
problem, not the CPU per se. Although the high CPU is probably causing
extended locking and blocking problems.
You need to profile your database and see how your queries are
performing and figure out which ones are running for long periods and
locking resources required by other SPIDs.
David Gugick
Imceda Software
www.imceda.com

CPU usage on SQL server

Hi,
I have a quad processor clustered database server that is experiencing
significant performance problems. There are multiple user databases on ther
server, and the issues manifest themselves in different ways. We have had
users unable to save to a database to complete slow down of the whole server.
The issues seem to be more prevalent in the morning, but we have had them
throughout the day as well. In monitoring the CPU usage we noticed that most
of the activity is going through only 1 of the CPU's. CPU 0 can peak at 100%
utilisation, and the other 3 have little or no activity at all. There has
been no changes to any settings and the SQL Server install is pretty
standard. Does anyone have any ideas?Meg wrote:
> Hi,
> I have a quad processor clustered database server that is experiencing
> significant performance problems. There are multiple user databases
> on ther server, and the issues manifest themselves in different ways.
> We have had users unable to save to a database to complete slow down
> of the whole server. The issues seem to be more prevalent in the
> morning, but we have had them throughout the day as well. In
> monitoring the CPU usage we noticed that most of the activity is
> going through only 1 of the CPU's. CPU 0 can peak at 100%
> utilisation, and the other 3 have little or no activity at all.
> There has been no changes to any settings and the SQL Server install
> is pretty standard. Does anyone have any ideas?
Have you assigned SQL Server all CPUs? Are you using MAXDOP 1 option in
your queries or have you set the "max degree of parallelism" option on
the server? I would think SQL Server would use all CPUs even if MAXDOP 1
were used when running multiple queries. It's probably some bad query
that is running on a single CPU that is causing the CPU spike. Many
query operations run on a single CPU. And blocking is probably the
problem, not the CPU per se. Although the high CPU is probably causing
extended locking and blocking problems.
You need to profile your database and see how your queries are
performing and figure out which ones are running for long periods and
locking resources required by other SPIDs.
David Gugick
Imceda Software
www.imceda.com

CPU usage on SQL server

Hi,
I have a quad processor clustered database server that is experiencing
significant performance problems. There are multiple user databases on ther
server, and the issues manifest themselves in different ways. We have had
users unable to save to a database to complete slow down of the whole server.
The issues seem to be more prevalent in the morning, but we have had them
throughout the day as well. In monitoring the CPU usage we noticed that most
of the activity is going through only 1 of the CPU's. CPU 0 can peak at 100%
utilisation, and the other 3 have little or no activity at all. There has
been no changes to any settings and the SQL Server install is pretty
standard. Does anyone have any ideas?
Meg wrote:
> Hi,
> I have a quad processor clustered database server that is experiencing
> significant performance problems. There are multiple user databases
> on ther server, and the issues manifest themselves in different ways.
> We have had users unable to save to a database to complete slow down
> of the whole server. The issues seem to be more prevalent in the
> morning, but we have had them throughout the day as well. In
> monitoring the CPU usage we noticed that most of the activity is
> going through only 1 of the CPU's. CPU 0 can peak at 100%
> utilisation, and the other 3 have little or no activity at all.
> There has been no changes to any settings and the SQL Server install
> is pretty standard. Does anyone have any ideas?
Have you assigned SQL Server all CPUs? Are you using MAXDOP 1 option in
your queries or have you set the "max degree of parallelism" option on
the server? I would think SQL Server would use all CPUs even if MAXDOP 1
were used when running multiple queries. It's probably some bad query
that is running on a single CPU that is causing the CPU spike. Many
query operations run on a single CPU. And blocking is probably the
problem, not the CPU per se. Although the high CPU is probably causing
extended locking and blocking problems.
You need to profile your database and see how your queries are
performing and figure out which ones are running for long periods and
locking resources required by other SPIDs.
David Gugick
Imceda Software
www.imceda.com

CPU Usage on AS 2005 Server

I have 5 cubes with 15 dimensions and one cube (like Virtual cube before) with 5 linked cubes.

My client is Excel 2003 Pivot table.

From Excel/Pivot table if I run a query which is taking long time. I press "Esc" key to cancel my query. However if I look into my Server "CPU Usage" even after canceling my query is very high. Looks it doesn't cancel my query on server. I am the only user using this server.

If I restart my Analysis Services 2005 the CPU Usage drops in 90 degrees to almost 0%.

Where is the problem any idea?

Thank you - Ashok

Looks like you have a classic case of "runaway query". It is always hard to get to the bottom of who sent the query and why the query is running that long.

Oten the one of the better recommendations for improving query performance and therefore improving responcivness of your cube is to desing aggregations. Aggregations are the key to Analysis Services performance.
Try and run Aggregation Design wizard and create aggregations for your cube. On top of that you can capture queries sent in the query log and then use Aggregation Manager sample application to desing aggregations supporting exact set of queries.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

In other words how to cancel/kill these "runaway query" without restarting server?

I do have aggregation setup. In SQL Server database we can kill any running query... can we do same in Analysis Services?

|||I had the same problem, if you run a query and you close the tool executing it then the query will still keep on running and slowing down the system. I don't think you can kill it. Maybe you can try to set the timeout much lower so that it will kill any query running longer than 30 seconds. I'm not sure if this will work but it should sure prevent the query of running longer than 30 seconds protecting the server from a resource eating *** query from hell.

|||

you can use the MSAS Activity Viewer tool and you can kill sessions.

|||

What is MSAS Activity Viewer tool ?

CPU Usage on AS 2005 Server

I have 5 cubes with 15 dimensions and one cube (like Virtual cube before) with 5 linked cubes.

My client is Excel 2003 Pivot table.

From Excel/Pivot table if I run a query which is taking long time. I press "Esc" key to cancel my query. However if I look into my Server "CPU Usage" even after canceling my query is very high. Looks it doesn't cancel my query on server. I am the only user using this server.

If I restart my Analysis Services 2005 the CPU Usage drops in 90 degrees to almost 0%.

Where is the problem any idea?

Thank you - Ashok

Looks like you have a classic case of "runaway query". It is always hard to get to the bottom of who sent the query and why the query is running that long.

Oten the one of the better recommendations for improving query performance and therefore improving responcivness of your cube is to desing aggregations. Aggregations are the key to Analysis Services performance.
Try and run Aggregation Design wizard and create aggregations for your cube. On top of that you can capture queries sent in the query log and then use Aggregation Manager sample application to desing aggregations supporting exact set of queries.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

In other words how to cancel/kill these "runaway query" without restarting server?

I do have aggregation setup. In SQL Server database we can kill any running query... can we do same in Analysis Services?

|||I had the same problem, if you run a query and you close the tool executing it then the query will still keep on running and slowing down the system. I don't think you can kill it. Maybe you can try to set the timeout much lower so that it will kill any query running longer than 30 seconds. I'm not sure if this will work but it should sure prevent the query of running longer than 30 seconds protecting the server from a resource eating *** query from hell.

|||

you can use the MSAS Activity Viewer tool and you can kill sessions.

|||

What is MSAS Activity Viewer tool ?

CPU Usage Management and SQL

Right now SQL Server accounts for 80% of my CPU Usage
which is being maxed out and causing my sites to run
slow. I fear this may be related to me not properly
closing connections (I am lazy) and me just hosting too
many databases on one server. Regardless, I need insight
on how to manage this and reset CPU Usage from a SQL
perspective. Also, is there a way to see all connections
opened and individually close them? Will that help me
you think? Thanks in advance.if you execute
sp_who
or
sp_who2
from Query Analyzer, you will be shown a listing of the open =connections.
Generally, open connections would not cause CPU usage, but they do use =memory. If there are lots of open (unused) connections then your memory =usage might be higher than it needs to be. ADO should be using =connection pooling, so I would not expect that you lots of extra =connections, but I could be wrong.
The best tool to see what is going on within the database server is =Profiler (within the SQL Server program group). It will show you what =sql commands are being sent to the database.
If you can identify which stored procedures/select statements cause =problems (take a long time to run, use gobs of CPU) you might be able to =re-write them or add indexes so that the system does not have to work as =hard to retrieve the information that the application is asking for.
-- Keith, SQL Server MVP
"ASP Dev" <dmicheli@.cmiti.com> wrote in message =news:030201c34b03$bead2830$a301280a@.phx.gbl...
> Right now SQL Server accounts for 80% of my CPU Usage > which is being maxed out and causing my sites to run > slow. I fear this may be related to me not properly > closing connections (I am lazy) and me just hosting too > many databases on one server. Regardless, I need insight > on how to manage this and reset CPU Usage from a SQL > perspective. Also, is there a way to see all connections > opened and individually close them? Will that help me > you think? Thanks in advance.|||Thank You. I appreciate your help.
>--Original Message--
>if you execute
>sp_who
>or
>sp_who2
>from Query Analyzer, you will be shown a listing of the
open connections.
>Generally, open connections would not cause CPU usage,
but they do use memory. If there are lots of open
(unused) connections then your memory usage might be
higher than it needs to be. ADO should be using
connection pooling, so I would not expect that you lots
of extra connections, but I could be wrong.
>The best tool to see what is going on within the
database server is Profiler (within the SQL Server
program group). It will show you what sql commands are
being sent to the database.
>If you can identify which stored procedures/select
statements cause problems (take a long time to run, use
gobs of CPU) you might be able to re-write them or add
indexes so that the system does not have to work as hard
to retrieve the information that the application is
asking for.
>--
>Keith, SQL Server MVP
>"ASP Dev" <dmicheli@.cmiti.com> wrote in message
news:030201c34b03$bead2830$a301280a@.phx.gbl...
>> Right now SQL Server accounts for 80% of my CPU Usage
>> which is being maxed out and causing my sites to run
>> slow. I fear this may be related to me not properly
>> closing connections (I am lazy) and me just hosting
too
>> many databases on one server. Regardless, I need
insight
>> on how to manage this and reset CPU Usage from a SQL
>> perspective. Also, is there a way to see all
connections
>> opened and individually close them? Will that help me
>> you think? Thanks in advance.
>.
>|||we had a history of CPU usage here. At peak time the usage would stay up to
100% staying there for 10, 15 minutes. Last few weeks it stayed above 95%
for couple hrs. The problem was poor SQL statements and the management
reports. All queries were pure SELECT or UPDATE, no stored procedures used.
During business hrs the big bosses just wanted to see how well the business
so far for the day so they just clicked whenever they want to produce an
up-to-date online report!!!. I've warned the bosses and developers about
this but it was kind of they prefered to upgrade h/w than rewriting the
codes and kept running reports ... whenever they want. After a meeting with
the big bosses they agreed it was a big problem and promised not to run
report during peak hrs. And just next day we saw them running report like
crazy again! Until last month the web site got timed-out then they took it
seriously. Spending 50-100K for both s/w and h/w wasn't an easy thing so
the developres rewrote the main interface for the site using pure stored
procedures. They also locked out the report tool, kept it from runinng b/w
10am and 2pm. Result: it rocked. Using Insight Manager I see the avg. CPU
usage is b/w 10 and 15%. Everyone is happy with it.:-).
"ASP Dev" <dmicheli@.cmiti.com> wrote in message
news:030201c34b03$bead2830$a301280a@.phx.gbl...
> Right now SQL Server accounts for 80% of my CPU Usage
> which is being maxed out and causing my sites to run
> slow. I fear this may be related to me not properly
> closing connections (I am lazy) and me just hosting too
> many databases on one server. Regardless, I need insight
> on how to manage this and reset CPU Usage from a SQL
> perspective. Also, is there a way to see all connections
> opened and individually close them? Will that help me
> you think? Thanks in advance.|||On Tue, 15 Jul 2003 14:19:06 -0700, "Flicker"
<hthan@.superioraccess.com> wrote:
>They also locked out the report tool, kept it from runinng b/w
>10am and 2pm. Result: it rocked.
How about you replicate the data out to a mart/warehouse so they can
click all they want?
J.|||there is no need for the report to run every few minutes. The chairman does
it; the VPs do it; the CEO does it. This is kind of more a habit than a
business need, just like you are checking stocks every hour. After we
locked them out, they do other things to kill time.:) Hey, they are also
the OWNERS. So long ...
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:6fuahv4s12omn7l7k3c1jfp3ivs2up3h8h@.4ax.com...
> On Tue, 15 Jul 2003 14:19:06 -0700, "Flicker"
> <hthan@.superioraccess.com> wrote:
> >They also locked out the report tool, kept it from runinng b/w
> >10am and 2pm. Result: it rocked.
> How about you replicate the data out to a mart/warehouse so they can
> click all they want?
> J.
>

Cpu Usage High.

Hi,
can we check cpu usage of our server by query analyzer,I dont have direct ac
cess to server and simply i've registered it by entr manager.
Site is too slow..there are no locks no processesing..but still its moving l
ike dead..
i want to check by query analyzer..
1) cpu usage
2) buffer cache ratio
3) ram utilised and free on server.
regards
sunnyHi,
The easiest way is that, you can connect to Performace monitor remotely.
Open Performance monitor and click the ADD counter butter,
where you can mention the SQL server name (\\Servername). But you should
have required OS level previlages in the SQL Server machine.
Buffer hit ratio can verified using the below DBCC command
DBCC PERFMON
Note:
Incase if you found all the hardware resouces usage is below limit than
using profiler identify the TSQL/ Procedure causing
the bottle neck. After that try to tune using SQL Serevr Query execution
plan and Index tuning wizard.
Thanks
Hari
MCDBA
"sunny" <anonymous@.discussions.microsoft.com> wrote in message
news:7EDA9B30-F184-40DD-8FEE-FE144D43D9B8@.microsoft.com...
> Hi,
> can we check cpu usage of our server by query analyzer,I dont have direct
access to server and simply i've registered it by entr manager.
> Site is too slow..there are no locks no processesing..but still its moving
like dead..
> i want to check by query analyzer..
> 1) cpu usage
> 2) buffer cache ratio
> 3) ram utilised and free on server.
> regards
> sunny
>|||can use sp_monitor to check cpu activity since sql was started. . .cpu_busy
column tells you how much time cpu has spent servicing sql server requests

Cpu Usage High.

Hi,
can we check cpu usage of our server by query analyzer,I dont have direct access to server and simply i've registered it by entr manager.
Site is too slow..there are no locks no processesing..but still its moving like dead..
i want to check by query analyzer..
1) cpu usage
2) buffer cache ratio
3) ram utilised and free on server.
regards
sunnyHi,
The easiest way is that, you can connect to Performace monitor remotely.
Open Performance monitor and click the ADD counter butter,
where you can mention the SQL server name (\\Servername). But you should
have required OS level previlages in the SQL Server machine.
Buffer hit ratio can verified using the below DBCC command
DBCC PERFMON
Note:
Incase if you found all the hardware resouces usage is below limit than
using profiler identify the TSQL/ Procedure causing
the bottle neck. After that try to tune using SQL Serevr Query execution
plan and Index tuning wizard.
Thanks
Hari
MCDBA
"sunny" <anonymous@.discussions.microsoft.com> wrote in message
news:7EDA9B30-F184-40DD-8FEE-FE144D43D9B8@.microsoft.com...
> Hi,
> can we check cpu usage of our server by query analyzer,I dont have direct
access to server and simply i've registered it by entr manager.
> Site is too slow..there are no locks no processesing..but still its moving
like dead..
> i want to check by query analyzer..
> 1) cpu usage
> 2) buffer cache ratio
> 3) ram utilised and free on server.
> regards
> sunny
>|||can use sp_monitor to check cpu activity since sql was started. . .cpu_busy column tells you how much time cpu has spent servicing sql server requests

CPU usage for SQLserver.exe increased!

hi,
I have sql server 2k with sp3 on win2k advanced server.
the sqlserver.exe is consuming 95 to 100% of cpu and the cpu remains at 100% percent most of the times.
I have checked with the profiler but could not find anything fishy.
any help would be highly appreciated.
Regards,
Harshal.Is there anything running at all? Perhaps a block/lock? I would suggest contacting PSS if this problem persists.|||what is pps?|||Originally posted by harshal_in
hi,
I have sql server 2k with sp3 on win2k advanced server.
the sqlserver.exe is consuming 95 to 100% of cpu and the cpu remains at 100% percent most of the times.
I have checked with the profiler but could not find anything fishy.
any help would be highly appreciated.
Regards,
Harshal.

I am currently running windows XP and having the same problem. The only way to fix it is to shut the program down through program manager and it does not affect my operating system. Have you found anything further on this issue>
?|||I could be wrong, but this sounds like the SQl Slammer worm. Microsoft have published details how to deal with it:
http://www.microsoft.com/security/slammer.asp

--Original Message--
I am currently running windows XP and having the same problem. The only way to fix it is to shut the program down through program manager and it does not affect my operating system. Have you found anything further on this issue