How to make SQL Server fully consume CPUs on a dedicated box

SQL Server 2000 on windows 2000 never seems to consume 100% of the cpu no
matter what I do -- indexing & heavy queries at the same time. Is there
something I need to set to get/allow SQL Server to be greedier with the
CPUs?
Thx,
TomYou probably have I/O contention... Run performance monitor and watch your
disc drives.
"Tom Ryan" <tryan@.comscore.com> wrote in message
news:O75ELsJpDHA.2416@.TK2MSFTNGP10.phx.gbl...
> SQL Server 2000 on windows 2000 never seems to consume 100% of the cpu no
> matter what I do -- indexing & heavy queries at the same time. Is there
> something I need to set to get/allow SQL Server to be greedier with the
> CPUs?
> Thx,
> Tom
>|||Don't worry, if SqlServer needs the CPU it will get it(if it's not 100%
used). I'm not sure why you want SQL to use all the CPU. Do you have
performance complaints? In every system there is always a'bottleneck' in
yours it's not CPU appearently.
--
regards,
Mario
http://www.sqlinternals.com
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:eWTZ3vJpDHA.1496@.TK2MSFTNGP11.phx.gbl...
> You probably have I/O contention... Run performance monitor and watch your
> disc drives.
>
> "Tom Ryan" <tryan@.comscore.com> wrote in message
> news:O75ELsJpDHA.2416@.TK2MSFTNGP10.phx.gbl...
> > SQL Server 2000 on windows 2000 never seems to consume 100% of the cpu
no
> > matter what I do -- indexing & heavy queries at the same time. Is there
> > something I need to set to get/allow SQL Server to be greedier with the
> > CPUs?
> >
> > Thx,
> > Tom
> >
> >
>|||Well, I have ASE running on a similar box, with a similar disk set up.
Performance is good in ASE, but you definately see the cpu's jump when
processes (especially concurrently running) are running. Performance isn't
great w/the SQL server and the database is fairly small (especially compared
to the ASE box). Since the disk arrays are comparable, my conclusion is that
SQL server isn't using the CPUs aggressively enough.
Had the problem one time in ASE. There was a config setting to tweak how
dedicated the box is for the data server. It was set too low, so ASE
frequently gave up the CPUs for general use by the OS.
Why I'm running a couple large queries and indexing at the same time, seems
like the CPU usage should be greater than 25% on a four way box.
"Mario" <nospam@.sqlinternals.com> wrote in message
news:3fab54ca$0$58706$e4fe514c@.news.xs4all.nl...
> Don't worry, if SqlServer needs the CPU it will get it(if it's not 100%
> used). I'm not sure why you want SQL to use all the CPU. Do you have
> performance complaints? In every system there is always a'bottleneck' in
> yours it's not CPU appearently.
> --
> regards,
> Mario
> http://www.sqlinternals.com
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
message
> news:eWTZ3vJpDHA.1496@.TK2MSFTNGP11.phx.gbl...
> > You probably have I/O contention... Run performance monitor and watch
your
> > disc drives.
> >
> >
> > "Tom Ryan" <tryan@.comscore.com> wrote in message
> > news:O75ELsJpDHA.2416@.TK2MSFTNGP10.phx.gbl...
> > > SQL Server 2000 on windows 2000 never seems to consume 100% of the cpu
> no
> > > matter what I do -- indexing & heavy queries at the same time. Is
there
> > > something I need to set to get/allow SQL Server to be greedier with
the
> > > CPUs?
> > >
> > > Thx,
> > > Tom
> > >
> > >
> >
> >
>|||It's nice that you're drawing conclusions, but the only way to find out
what's REALLY happening is to run perfmon and find out.
I can tell you that the SQL Servers I administrate certainly have no problem
consuming CPU cycles.
"Tom Ryan" <tryan@.comscore.com> wrote in message
news:eJXrKDVpDHA.3688@.TK2MSFTNGP11.phx.gbl...
> Well, I have ASE running on a similar box, with a similar disk set up.
> Performance is good in ASE, but you definately see the cpu's jump when
> processes (especially concurrently running) are running. Performance isn't
> great w/the SQL server and the database is fairly small (especially
compared
> to the ASE box). Since the disk arrays are comparable, my conclusion is
that
> SQL server isn't using the CPUs aggressively enough.
> Had the problem one time in ASE. There was a config setting to tweak how
> dedicated the box is for the data server. It was set too low, so ASE
> frequently gave up the CPUs for general use by the OS.
> Why I'm running a couple large queries and indexing at the same time,
seems
> like the CPU usage should be greater than 25% on a four way box.|||By the way, indexing, especially building clustered indexes, is very IO
intensive. It will pin most disc drives long before the CPU.
"Tom Ryan" <tryan@.comscore.com> wrote in message
news:eJXrKDVpDHA.3688@.TK2MSFTNGP11.phx.gbl...
> Well, I have ASE running on a similar box, with a similar disk set up.
> Performance is good in ASE, but you definately see the cpu's jump when
> processes (especially concurrently running) are running. Performance isn't
> great w/the SQL server and the database is fairly small (especially
compared
> to the ASE box). Since the disk arrays are comparable, my conclusion is
that
> SQL server isn't using the CPUs aggressively enough.
> Had the problem one time in ASE. There was a config setting to tweak how
> dedicated the box is for the data server. It was set too low, so ASE
> frequently gave up the CPUs for general use by the OS.
> Why I'm running a couple large queries and indexing at the same time,
seems
> like the CPU usage should be greater than 25% on a four way box.|||Ah I see. You mean 'control panel'->system->advanced->performance options
and then click 'background services'. If I remember correctly that will
increase the time quantum threads get on the cpu. There's another one
somnewhere in network settings/file&printer sharing where you can specify
'optimize for network apps' which will trim workingsets less aggresively.
These will help somehow, probably/ But still if there are cpu available and
SQLserver wants them, it'll get them.
Even if there was such a thing as 'general use by the OS', and there are
cycles left: there are still cycles to grab for sqlserver, so I don't think
it makes sense to look at cpu.
If sqlserver is not using cpu 100% it's waiting on something(or simply
waiting for work to come): check the wait stuff in sysprocesses, and you'll
find out what it's waiting on/for. Also check dbcc sqlperf(Waitstats): if
you see high pageio times (compared to elapsed and cpu used times) it's
probably I/O bound. If you see high networkio times (again compared etc)
there is a lot of roundtrips (or slow depending on the count) between
clients and server.
Or you can also rely on perfmon as Adam suggest, which is probably the
'easy' and most used way to go.
Apart from that I fully agree with Adam: don't guess: measure!
good luck!
--
regards,
Mario
http://www.sqlinternals.com
"Tom Ryan" <tryan@.comscore.com> wrote in message
news:eJXrKDVpDHA.3688@.TK2MSFTNGP11.phx.gbl...
> Well, I have ASE running on a similar box, with a similar disk set up.
> Performance is good in ASE, but you definately see the cpu's jump when
> processes (especially concurrently running) are running. Performance isn't
> great w/the SQL server and the database is fairly small (especially
compared
> to the ASE box). Since the disk arrays are comparable, my conclusion is
that
> SQL server isn't using the CPUs aggressively enough.
> Had the problem one time in ASE. There was a config setting to tweak how
> dedicated the box is for the data server. It was set too low, so ASE
> frequently gave up the CPUs for general use by the OS.
> Why I'm running a couple large queries and indexing at the same time,
seems
> like the CPU usage should be greater than 25% on a four way box.
> "Mario" <nospam@.sqlinternals.com> wrote in message
> news:3fab54ca$0$58706$e4fe514c@.news.xs4all.nl...
> > Don't worry, if SqlServer needs the CPU it will get it(if it's not 100%
> > used). I'm not sure why you want SQL to use all the CPU. Do you have
> > performance complaints? In every system there is always a'bottleneck' in
> > yours it's not CPU appearently.
> >
> > --
> > regards,
> > Mario
> >
> > http://www.sqlinternals.com
> >
> > "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
> message
> > news:eWTZ3vJpDHA.1496@.TK2MSFTNGP11.phx.gbl...
> > > You probably have I/O contention... Run performance monitor and watch
> your
> > > disc drives.
> > >
> > >
> > > "Tom Ryan" <tryan@.comscore.com> wrote in message
> > > news:O75ELsJpDHA.2416@.TK2MSFTNGP10.phx.gbl...
> > > > SQL Server 2000 on windows 2000 never seems to consume 100% of the
cpu
> > no
> > > > matter what I do -- indexing & heavy queries at the same time. Is
> there
> > > > something I need to set to get/allow SQL Server to be greedier with
> the
> > > > CPUs?
> > > >
> > > > Thx,
> > > > Tom
> > > >
> > > >
> > >
> > >
> >
> >
>