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