performance question - high cpu usage

10 Dec 2008 8:53 PM

SarahG

Hi,
I have sql 2000 server, standard edition, 4GB, 8 processors, I only one
tempdb data file and 1 log. I found lately, one of my sp running, it caused
very high cpu usage, because of the sql standard edition, is there anything
that i can do to have the process to use cpus parallel or does "enable awe"
work?

Thanks,
Sarah

10 Dec 2008 9:33 PM

Andrew J. Kelly

First off Standard edition does allow parallel queries. It is just things
like creating or rebuilding indexes, backups etc that do not make use of
parallelism in Standard edition. Second AWE has nothing what so ever to do
with parallel processing. It is a memory feature only and SQL 2000 std
edition does not support AWE. Also tempdb has absolutely no effect on the
number of processors used or the degree of parallelism either. It will be
more of a factor of what your sp is doing and how along with the indexes on
the tables used. It sounds like you need to tune that sp.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors

Show quoteHide quote

"SarahG" <Sar***@discussions.microsoft.com> wrote in message
news:3E4327FB-9D29-434D-80D0-2043A477FEA4@microsoft.com...
> Hi,
> I have sql 2000 server, standard edition, 4GB, 8 processors, I only one
> tempdb data file and 1 log. I found lately, one of my sp running, it
> caused
> very high cpu usage, because of the sql standard edition, is there
> anything
> that i can do to have the process to use cpus parallel or does "enable
> awe"
> work?
>
> Thanks,
> Sarah

11 Dec 2008 2:22 PM

SarahG

Hi Andrew,

Thanks for your reply.
Does standard edition allow to use multiple processors?

Thanks,
Sarah

Show quoteHide quote

"Andrew J. Kelly" wrote:

> First off Standard edition does allow parallel queries. It is just things
> like creating or rebuilding indexes, backups etc that do not make use of
> parallelism in Standard edition. Second AWE has nothing what so ever to do
> with parallel processing. It is a memory feature only and SQL 2000 std
> edition does not support AWE. Also tempdb has absolutely no effect on the
> number of processors used or the degree of parallelism either. It will be
> more of a factor of what your sp is doing and how along with the indexes on
> the tables used. It sounds like you need to tune that sp.
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
>
> "SarahG" <Sar***@discussions.microsoft.com> wrote in message
> news:3E4327FB-9D29-434D-80D0-2043A477FEA4@microsoft.com...
> > Hi,
> > I have sql 2000 server, standard edition, 4GB, 8 processors, I only one
> > tempdb data file and 1 log. I found lately, one of my sp running, it
> > caused
> > very high cpu usage, because of the sql standard edition, is there
> > anything
> > that i can do to have the process to use cpus parallel or does "enable
> > awe"
> > work?
> >
> > Thanks,
> > Sarah
>
>

11 Dec 2008 2:39 PM

Andrew J. Kelly

Yes it allows for up to 4 physical processors, regardless of the number of
cores.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors

Show quoteHide quote

"SarahG" <Sar***@discussions.microsoft.com> wrote in message
news:E6C8002E-6E25-4716-806E-FF385C530579@microsoft.com...
> Hi Andrew,
>
> Thanks for your reply.
> Does standard edition allow to use multiple processors?
>
> Thanks,
> Sarah
>
> "Andrew J. Kelly" wrote:
>
>> First off Standard edition does allow parallel queries. It is just things
>> like creating or rebuilding indexes, backups etc that do not make use of
>> parallelism in Standard edition. Second AWE has nothing what so ever to
>> do
>> with parallel processing. It is a memory feature only and SQL 2000 std
>> edition does not support AWE. Also tempdb has absolutely no effect on
>> the
>> number of processors used or the degree of parallelism either. It will
>> be
>> more of a factor of what your sp is doing and how along with the indexes
>> on
>> the tables used. It sounds like you need to tune that sp.
>>
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>>
>> "SarahG" <Sar***@discussions.microsoft.com> wrote in message
>> news:3E4327FB-9D29-434D-80D0-2043A477FEA4@microsoft.com...
>> > Hi,
>> > I have sql 2000 server, standard edition, 4GB, 8 processors, I only one
>> > tempdb data file and 1 log. I found lately, one of my sp running, it
>> > caused
>> > very high cpu usage, because of the sql standard edition, is there
>> > anything
>> > that i can do to have the process to use cpus parallel or does "enable
>> > awe"
>> > work?
>> >
>> > Thanks,
>> > Sarah
>>
>>