Process only running at 25%

Anyone know why the process on our SQL server only ever gets as high as 25%?
It is a default SQL server 2000 install so Im not sure why it is being
restricted?
Thanks,
GrantA single query will use no more than one CPU unless it involves a parallel
plan. On a quad processor box (or dual hypterthreaded), 25% total CPU is
expected for a CPU-bound query.
SQL Server will use all processors (including virtual ones) by default.
Multiple processors can be used by concurrent queries and/or parallel plans.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Grant" <gpsnett@.hotmail.com> wrote in message
news:%23dHcrdD9DHA.2316@.TK2MSFTNGP11.phx.gbl...
> Anyone know why the process on our SQL server only ever gets as high as
25%?
> It is a default SQL server 2000 install so Im not sure why it is being
> restricted?
> Thanks,
> Grant
>|||Dan - That SQl server is running on a dual hyperthreaded machine, does that
mean the 25% maximum usage is normal behaviour even for large queries? Would
it ever get larger than that? What explanation do I give to a user that is
worried that the system is only running at 25% capacity?
If there was one hyperthreaded CPU then maximum CPU usage would be 50%
because it is splitting usage bewtween actual CPU and virtual CPU? Is that
right? If it is right then that would mean 2 hyperthreaded CPU's at maximum
use would also push out 50% (2 real CPUs and 2 virtual ones).
Thanks Dan,
Grant
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:u$8T7lD9DHA.2168@.TK2MSFTNGP12.phx.gbl...
> A single query will use no more than one CPU unless it involves a parallel
> plan. On a quad processor box (or dual hypterthreaded), 25% total CPU is
> expected for a CPU-bound query.
> SQL Server will use all processors (including virtual ones) by default.
> Multiple processors can be used by concurrent queries and/or parallel
plans.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Grant" <gpsnett@.hotmail.com> wrote in message
> news:%23dHcrdD9DHA.2316@.TK2MSFTNGP11.phx.gbl...
> > Anyone know why the process on our SQL server only ever gets as high as
> 25%?
> > It is a default SQL server 2000 install so Im not sure why it is being
> > restricted?
> >
> > Thanks,
> > Grant
> >
> >
>|||> Dan - That SQl server is running on a dual hyperthreaded machine, does
that
> mean the 25% maximum usage is normal behaviour even for large queries?
Yes.
> Would it ever get larger than that?
Yes. If you have 4 concurrent cpu-bound queries running, I'd expect you to
average around 100%. You may also exceed 25 with a fewer number of queries
and parallel plans.
> What explanation do I give to a user that is
> worried that the system is only running at 25% capacity?
Ask the user how many lanes they believe a single car on a 4-lane highway
should use. Would the car arrive at the destination faster by using more
than one lane? This does not mean that the additional capacity is wasted.
There may be other times when all lanes are needed and used.
> If there was one hyperthreaded CPU then maximum CPU usage would be 50%
> because it is splitting usage bewtween actual CPU and virtual CPU? Is that
> right? If it is right then that would mean 2 hyperthreaded CPU's at
maximum
> use would also push out 50% (2 real CPUs and 2 virtual ones).
A single thread (e.g. non-oparallel query) can use only one processor
(virtual or physical) - period. A multi-threaded application like SQL
Server can take advantage of multiple (logical or physical) CPUs by doing
concurrent work when appropriate. So it you have a single HT CPU, one
thread can use up to 50% and 2 threads can use up to 100%. In the case of
dual HT CPUs, 1=25%, 2=50%, 75%, 4 = 100%.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Grant" <gpsnett@.hotmail.com> wrote in message
news:%23EwRu2D9DHA.2404@.TK2MSFTNGP11.phx.gbl...
> Dan - That SQl server is running on a dual hyperthreaded machine, does
that
> mean the 25% maximum usage is normal behaviour even for large queries?
Would
> it ever get larger than that? What explanation do I give to a user that is
> worried that the system is only running at 25% capacity?
> If there was one hyperthreaded CPU then maximum CPU usage would be 50%
> because it is splitting usage bewtween actual CPU and virtual CPU? Is that
> right? If it is right then that would mean 2 hyperthreaded CPU's at
maximum
> use would also push out 50% (2 real CPUs and 2 virtual ones).
> Thanks Dan,
> Grant
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:u$8T7lD9DHA.2168@.TK2MSFTNGP12.phx.gbl...
> > A single query will use no more than one CPU unless it involves a
parallel
> > plan. On a quad processor box (or dual hypterthreaded), 25% total CPU
is
> > expected for a CPU-bound query.
> >
> > SQL Server will use all processors (including virtual ones) by default.
> > Multiple processors can be used by concurrent queries and/or parallel
> plans.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Grant" <gpsnett@.hotmail.com> wrote in message
> > news:%23dHcrdD9DHA.2316@.TK2MSFTNGP11.phx.gbl...
> > > Anyone know why the process on our SQL server only ever gets as high
as
> > 25%?
> > > It is a default SQL server 2000 install so Im not sure why it is being
> > > restricted?
> > >
> > > Thanks,
> > > Grant
> > >
> > >
> >
> >
>