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 Maxed out by sql server 2k sp3a

Hi all,
I'm having a problem, that curiously enough, a few people
are experiencing but so far I haven't found an answer for
it:
I have two different servers running sql server 2000 sp3:
- IBM P III 800 Mhz 1 Gb RAM, RAID 5 win 2k advanced
server.
- DELL pe 2650 Xeon 2.0 Ghz 512 Mb RAM, RAID 5 win 2k
advanced
The same stored procedure that returns a 100 record page
out of an ~27000 set takes a fraction of a second in the
first system and around 4 min in the second!. The CPU
usage in the second system goes to full during the whole
time whereas in the first it shows just a small peak ~30%.
In both systems, memory usage remains minimum.
I've used the performance monitor for both systems and so
far have not found big differences.
Any suggestions, help will be really appreciated.
Thanks
JorgeA fill factor of 20%? Or do you mean 80%? 20% full is an awful lot of
room to leave empty on a page. In either case I suspect it was not the
fact that you reindexed the table but more that the statistics were updated
as a result of the reindexing. When ever you move or restore data you
should always update the statistics. See sp_updatestats and UPDATE
STATISTICS in BooksOnLine.
--
Andrew J. Kelly
SQL Server MVP
"Jorge" <jflores@.savvydata.com> wrote in message
news:0a3301c34d18$45a968b0$a501280a@.phx.gbl...
> Hello Andrew, Thank you for your response.
> I had not looked at the execution tree of both machines
> before because everything was the same on them... my
> mistake! Now, after a recreation of my clustered index
> with a fill factor of 20% everything is back to normal.
> Again thank you, you saved me a trip to the server
> collocation space an a few gray hairs!
> >--Original Message--
> >Have you looked at the query plan for both to see what is
> different? I
> >suspect the second is doing full table scans where as the
> first is using an
> >index seek. Have you updated the statistics on the
> second machine?
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Jorge" <jflores@.savvydata.com> wrote in message
> >news:06d301c34c3a$adb48950$a501280a@.phx.gbl...
> >> Hi all,
> >> I'm having a problem, that curiously enough, a few
> people
> >> are experiencing but so far I haven't found an answer
> for
> >> it:
> >> I have two different servers running sql server 2000
> sp3:
> >> - IBM P III 800 Mhz 1 Gb RAM, RAID 5 win 2k advanced
> >> server.
> >> - DELL pe 2650 Xeon 2.0 Ghz 512 Mb RAM, RAID 5 win 2k
> >> advanced
> >>
> >> The same stored procedure that returns a 100 record page
> >> out of an ~27000 set takes a fraction of a second in the
> >> first system and around 4 min in the second!. The CPU
> >> usage in the second system goes to full during the whole
> >> time whereas in the first it shows just a small peak
> ~30%.
> >> In both systems, memory usage remains minimum.
> >> I've used the performance monitor for both systems and
> so
> >> far have not found big differences.
> >> Any suggestions, help will be really appreciated.
> >> Thanks
> >>
> >> Jorge
> >
> >
> >.
> >|||Again, you're right. And 80% is what I ment.
Thanks
>--Original Message--
>A fill factor of 20%? Or do you mean 80%? 20% full is
an awful lot of
>room to leave empty on a page. In either case I suspect
it was not the
>fact that you reindexed the table but more that the
statistics were updated
>as a result of the reindexing. When ever you move or
restore data you
>should always update the statistics. See sp_updatestats
and UPDATE
>STATISTICS in BooksOnLine.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Jorge" <jflores@.savvydata.com> wrote in message
>news:0a3301c34d18$45a968b0$a501280a@.phx.gbl...
>> Hello Andrew, Thank you for your response.
>> I had not looked at the execution tree of both machines
>> before because everything was the same on them... my
>> mistake! Now, after a recreation of my clustered index
>> with a fill factor of 20% everything is back to normal.
>> Again thank you, you saved me a trip to the server
>> collocation space an a few gray hairs!
>> >--Original Message--
>> >Have you looked at the query plan for both to see what
is
>> different? I
>> >suspect the second is doing full table scans where as
the
>> first is using an
>> >index seek. Have you updated the statistics on the
>> second machine?
>> >
>> >--
>> >
>> >Andrew J. Kelly
>> >SQL Server MVP
>> >
>> >
>> >"Jorge" <jflores@.savvydata.com> wrote in message
>> >news:06d301c34c3a$adb48950$a501280a@.phx.gbl...
>> >> Hi all,
>> >> I'm having a problem, that curiously enough, a few
>> people
>> >> are experiencing but so far I haven't found an answer
>> for
>> >> it:
>> >> I have two different servers running sql server 2000
>> sp3:
>> >> - IBM P III 800 Mhz 1 Gb RAM, RAID 5 win 2k advanced
>> >> server.
>> >> - DELL pe 2650 Xeon 2.0 Ghz 512 Mb RAM, RAID 5 win 2k
>> >> advanced
>> >>
>> >> The same stored procedure that returns a 100 record
page
>> >> out of an ~27000 set takes a fraction of a second in
the
>> >> first system and around 4 min in the second!. The CPU
>> >> usage in the second system goes to full during the
whole
>> >> time whereas in the first it shows just a small peak
>> ~30%.
>> >> In both systems, memory usage remains minimum.
>> >> I've used the performance monitor for both systems
and
>> so
>> >> far have not found big differences.
>> >> Any suggestions, help will be really appreciated.
>> >> Thanks
>> >>
>> >> Jorge
>> >
>> >
>> >.
>> >
>
>.
>|||Brian,
If you have read the other threads you probably know by
now that I solved my problem. Nevertheless, in the
process, while I suspected my Dell server, I found out
that there were a few BIOS and controller updates Dell had
come up with in a relatively short time, and I didn't know
about them. If you haven't by now, take a look at any
updates for your server. Just wanted to give you the heads
up, hope this helps.
Good Luck!
>--Original Message--
>Jorge,
> This is going to sound strange, but I am haveing the
>same problem. I have 2 servers running with the same
data
>on them sql server 2000 sp3 win2k, and the older one is
>running fine but the new Dell 2600 2G Ram, Xeon processor
>has a bad memory leak. The systems should be exactly the
>same, but the one server will stop responding at various
>times through the day, and the only way to get it back up
>is to restart the machine. I am guessing they are not
>related just because of the simularities of the machines,
>but maybe there is something to it.
>.
>