Multi processor OS restriction?

[DMAC]

Hi, Ive been told this, but I hope it is NOT true. I have an sql
server2000 installation running on a server that has four processors.
It is on a active network but is not the domain controller so
essentially it is fully dedicated to servicing the needs of sql
server, (a bit of browsing, a bit of ms Office, but almost wholly
dedicated to sqlserver. Now, the big question, why, when the server
properties have been set to utilize all four processors, can any one
job never get more than 25% of cpu time? I can launch multiple
instance of QA and run the same job on each one and that will utilise
more and more cpu time, but if you launch multile QA windows from
within one insance of QA, you can NEVER get more than 25% CPU
utilisation. Now i have to run a job (FTS is a good example,
re-indexing lots of db's another, or even a huge query with multiple
ufd's on computed cols which I hoped would grab lots of CPU time that
they need, but no. So do I have to live with this or can I tell either
windows or sql server to grab more cpu when it want to ie use my spare
CPU capacity more efficiently or am i working on a misguided premise
and 25% per job is your lot?

DMAC

[louis]

Re: Multi processor OS restriction?

>>Hi, Ive been told this, but I hope it is NOT true. I have an sql[color=blue][color=green]
>>server2000 installation running on a server that has four processors.
>>It is on a active network but is not the domain controller so
>>essentially it is fully dedicated to servicing the needs of sql
>>server, (a bit of browsing, a bit of ms Office, but almost wholly
>>dedicated to sqlserver. Now, the big question, why, when the server
>>properties have been set to utilize all four processors, can any one
>>job never get more than 25% of cpu time?[/color][/color]
MSSQL can utilize 'parallelism' to make use of multi processors. But
it doesn't work half the time. Unless you have a single user, it is
better for MSSQL to save those other processors for other SPIDS.
[color=blue][color=green]
>>I can launch multiple
>>instance of QA and run the same job on each one and that will utilise
>>more and more cpu time, but if you launch multile QA windows from
>>within one insance of QA, you can NEVER get more than 25% CPU
>>utilisation . Now i have to run a job (FTS is a good example,
>>re-indexing lots of db's another, or even a huge query with multiple
>>ufd's on computed cols which I hoped would grab lots of CPU time that
>>they need, but no. So do I have to live with this or can I tell[/color][/color]
either[color=blue][color=green]
>>windows or sql server to grab more cpu when it want to ie use my[/color][/color]
spare[color=blue][color=green]
>>CPU capacity more efficiently or am i working on a misguided premise
>>and 25% per job is your lot?[/color][/color]
What's FTS & UFDs? User defined function?

[Dan Guzman]

Re: Multi processor OS restriction?

The SQL Server optimizer will generate a parallel plan only when it makes
sense to do so and the current server workload permits it. Many queries
will not benefit from a parallel plan. In practice, parallelism can be a
symptom of needed indexes or poorly formulated query.
[color=blue]
> I can launch multiple
> instance of QA and run the same job on each one and that will utilise
> more and more cpu time, but if you launch multile QA windows from
> within one insance of QA, you can NEVER get more than 25% CPU
> utilisation.[/color]

This is not consistent with my experience. For example, I see both
processors fully used on my dual-cpu box by running the following query from
within the same QA instance. Do you get similar results?

USE master
SELECT COUNT(*)
FROM sysobjects a
CROSS JOIN sysobjects b
CROSS JOIN sysobjects c
CROSS JOIN sysobjects d
GO


--
Hope this helps.

Dan Guzman
SQL Server MVP

"DMAC" <drmcl@drmcl.fr ee-online.co.uk> wrote in message
news:462ac9a0.0 501180705.4f2c5 be7@posting.goo gle.com...[color=blue]
> Hi, Ive been told this, but I hope it is NOT true. I have an sql
> server2000 installation running on a server that has four processors.
> It is on a active network but is not the domain controller so
> essentially it is fully dedicated to servicing the needs of sql
> server, (a bit of browsing, a bit of ms Office, but almost wholly
> dedicated to sqlserver. Now, the big question, why, when the server
> properties have been set to utilize all four processors, can any one
> job never get more than 25% of cpu time? I can launch multiple
> instance of QA and run the same job on each one and that will utilise
> more and more cpu time, but if you launch multile QA windows from
> within one insance of QA, you can NEVER get more than 25% CPU
> utilisation. Now i have to run a job (FTS is a good example,
> re-indexing lots of db's another, or even a huge query with multiple
> ufd's on computed cols which I hoped would grab lots of CPU time that
> they need, but no. So do I have to live with this or can I tell either
> windows or sql server to grab more cpu when it want to ie use my spare
> CPU capacity more efficiently or am i working on a misguided premise
> and 25% per job is your lot?
>
> DMAC[/color]

[DMAC@devdex.com]

Re: Multi processor OS restriction?

Thanks Dan,

Your query did indeed put all four processors into overdrive, (took me
a while to cancel it cos I could not get my mouse click over the wire)
so my problem probably lies with your first suggestion about poorly
formed queries. Is there any mechanism to influence the optimiser or
thread selection to have my one really bad query utilise its own
processor( or just generally so that I can keep developement to its own
cpu/thread combo), leaving everthing else to utilize the other
processors, ie why did sql server immediately grap all the cpu time from
your query?

Cheers


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Dan Guzman]

Re: Multi processor OS restriction?

> Is there any mechanism to influence the optimiser or[color=blue]
> thread selection to have my one really bad query utilise its own
> processor( or just generally so that I can keep developement to its own
> cpu/thread combo), leaving everthing else to utilize the other
> processors,[/color]

You can specify a MAXDOP hint to limit parallelism to the specified number
of processors: for a particular query:

USE master
SELECT COUNT(*)
FROM sysobjects a
CROSS JOIN sysobjects b
CROSS JOIN sysobjects c
CROSS JOIN sysobjects d
OPTION (MAXDOP 1)
GO

You can also adjust the server-wide setting with the 'max degree of
parallelism' configuration option. On a server with 4 or more processors, I
usually use this option to specify fewer processors than are available (e.g.
3) so that a single query won't monopolize CPU resources.
[color=blue]
> ie why did sql server immediately grap all the cpu time from
> your query?[/color]

When SQL Server determines a query can benefit from parallelism, it
considers the current server workload and adjusts the number of parallel
threads accordingly. The optimizer may choose to use a single thread or
fewer processors when the machine is busy and a more aggressive plan when
not currently busy.

It's a good practice to segregate development and production on different
servers when possible.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<DMAC@devdex.co m> wrote in message news:41ef7c7f$1 _2@127.0.0.1...[color=blue]
> Thanks Dan,
>
> Your query did indeed put all four processors into overdrive, (took me
> a while to cancel it cos I could not get my mouse click over the wire)
> so my problem probably lies with your first suggestion about poorly
> formed queries. Is there any mechanism to influence the optimiser or
> thread selection to have my one really bad query utilise its own
> processor( or just generally so that I can keep developement to its own
> cpu/thread combo), leaving everthing else to utilize the other
> processors, ie why did sql server immediately grap all the cpu time from
> your query?
>
> Cheers
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]