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