MultiProcessor Usage 
Author Message
 MultiProcessor Usage

I run a data conversion process in a server that has
4*550MHz CPUs, 4 Gig Xeon P3 processor. The conversion
process consists of 200 stored procedures executed in
sequence. It takes 40 hrs for the conversion to complete
in this server(for a 11Gig data) and the CPU usage during
this process is not 100% in all the 4 CPUs.

I tried the same process in another server that has a
single CPU GX260 1.8GHz, 1Gig P4 Processor and it takes 21
hours.

Why doesn't the process utilize all the 4 CPUs to the
maximum 100% in the first server? All the parameters such
as maximum worker threads and minimum query plan threshold
are set to default values.

Please let me know if there is a way to minimise the time
for the data conversion process in the server with 4 CPUs.



Mon, 15 Aug 2005 22:37:14 GMT
 MultiProcessor Usage

Some queries can't be executed over several processors, quite simply. There are only a class of
queries that can be executed over several processors...

--
Tibor Karaszi, SQL Server MVP
For help on TSQL, give us something we can execute in Query Analyzer


Quote:
> I run a data conversion process in a server that has
> 4*550MHz CPUs, 4 Gig Xeon P3 processor. The conversion
> process consists of 200 stored procedures executed in
> sequence. It takes 40 hrs for the conversion to complete
> in this server(for a 11Gig data) and the CPU usage during
> this process is not 100% in all the 4 CPUs.

> I tried the same process in another server that has a
> single CPU GX260 1.8GHz, 1Gig P4 Processor and it takes 21
> hours.

> Why doesn't the process utilize all the 4 CPUs to the
> maximum 100% in the first server? All the parameters such
> as maximum worker threads and minimum query plan threshold
> are set to default values.

> Please let me know if there is a way to minimise the time
> for the data conversion process in the server with 4 CPUs.



Mon, 15 Aug 2005 22:58:41 GMT
 MultiProcessor Usage
Could you please give a sample of what type of queries can
be executed over several processors.

Thanks

Quote:
>-----Original Message-----
>Some queries can't be executed over several processors,

quite simply. There are only a class of
Quote:
>queries that can be executed over several processors...

>--
>Tibor Karaszi, SQL Server MVP
>For help on TSQL, give us something we can execute in
Query Analyzer


in message

>> I run a data conversion process in a server that has
>> 4*550MHz CPUs, 4 Gig Xeon P3 processor. The conversion
>> process consists of 200 stored procedures executed in
>> sequence. It takes 40 hrs for the conversion to complete
>> in this server(for a 11Gig data) and the CPU usage
during
>> this process is not 100% in all the 4 CPUs.

>> I tried the same process in another server that has a
>> single CPU GX260 1.8GHz, 1Gig P4 Processor and it takes
21
>> hours.

>> Why doesn't the process utilize all the 4 CPUs to the
>> maximum 100% in the first server? All the parameters
such
>> as maximum worker threads and minimum query plan
threshold
>> are set to default values.

>> Please let me know if there is a way to minimise the
time
>> for the data conversion process in the server with 4
CPUs.

>.



Mon, 15 Aug 2005 23:09:07 GMT
 MultiProcessor Usage
I'm sure there's more info in BOL and perhaps also "Inside SQL Server", but as I recall a GROUP
BY is a good candidate. Perhaps other will jump in here with other examples.

--
Tibor Karaszi, SQL Server MVP
For help on TSQL, give us something we can execute in Query Analyzer


Quote:
> Could you please give a sample of what type of queries can
> be executed over several processors.

> Thanks
> >-----Original Message-----
> >Some queries can't be executed over several processors,
> quite simply. There are only a class of
> >queries that can be executed over several processors...

> >--
> >Tibor Karaszi, SQL Server MVP
> >For help on TSQL, give us something we can execute in
> Query Analyzer


> in message

> >> I run a data conversion process in a server that has
> >> 4*550MHz CPUs, 4 Gig Xeon P3 processor. The conversion
> >> process consists of 200 stored procedures executed in
> >> sequence. It takes 40 hrs for the conversion to complete
> >> in this server(for a 11Gig data) and the CPU usage
> during
> >> this process is not 100% in all the 4 CPUs.

> >> I tried the same process in another server that has a
> >> single CPU GX260 1.8GHz, 1Gig P4 Processor and it takes
> 21
> >> hours.

> >> Why doesn't the process utilize all the 4 CPUs to the
> >> maximum 100% in the first server? All the parameters
> such
> >> as maximum worker threads and minimum query plan
> threshold
> >> are set to default values.

> >> Please let me know if there is a way to minimise the
> time
> >> for the data conversion process in the server with 4
> CPUs.

> >.



Mon, 15 Aug 2005 23:13:49 GMT
 MultiProcessor Usage
Hi Gayathri - the performance differences between these two machines may not
just be limited to their CPU speeds. The speed of the disk subsystem can
play an important part in the overall performance of a system. (for each
server: what is the RAID configs? speed of the disks? throughput of the
controllers?)

It is possible that the reason the older 4-way system was not pegged at 100%
CPU utilisation because the disk i/o was your bottle-neck. Conversely, the
newer single-CPU system may have been pegged at 100% CPU since it had a more
performant disk subsystem.

To understand more about what is happening here, use PerfMon to check the
disk i/o counters, including disk queue length.

While I suspect that query parallelism is not the cause of your performance
issues, you can read more about it in BOL under the heading of "Degree of
Parallelism".
c
hitec.chm::/8_ar_sa_163x.htm

regards, Ken

--
This posting is provided "AS IS" with no warranties, and confers no rights.



Quote:
> I'm sure there's more info in BOL and perhaps also "Inside SQL Server",

but as I recall a GROUP
Quote:
> BY is a good candidate. Perhaps other will jump in here with other
examples.

> --
> Tibor Karaszi, SQL Server MVP
> For help on TSQL, give us something we can execute in Query Analyzer



> > Could you please give a sample of what type of queries can
> > be executed over several processors.

> > Thanks
> > >-----Original Message-----
> > >Some queries can't be executed over several processors,
> > quite simply. There are only a class of
> > >queries that can be executed over several processors...

> > >--
> > >Tibor Karaszi, SQL Server MVP
> > >For help on TSQL, give us something we can execute in
> > Query Analyzer


> > in message

> > >> I run a data conversion process in a server that has
> > >> 4*550MHz CPUs, 4 Gig Xeon P3 processor. The conversion
> > >> process consists of 200 stored procedures executed in
> > >> sequence. It takes 40 hrs for the conversion to complete
> > >> in this server(for a 11Gig data) and the CPU usage
> > during
> > >> this process is not 100% in all the 4 CPUs.

> > >> I tried the same process in another server that has a
> > >> single CPU GX260 1.8GHz, 1Gig P4 Processor and it takes
> > 21
> > >> hours.

> > >> Why doesn't the process utilize all the 4 CPUs to the
> > >> maximum 100% in the first server? All the parameters
> > such
> > >> as maximum worker threads and minimum query plan
> > threshold
> > >> are set to default values.

> > >> Please let me know if there is a way to minimise the
> > time
> > >> for the data conversion process in the server with 4
> > CPUs.

> > >.



Wed, 17 Aug 2005 06:43:45 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. MultiProcessor Usage

2. Server/Per-Seat CAL and Multiprocessor Usage

3. Usage Analysis / Usage Optimization Error

4. ADO Causes 100% Memory Usage/Increases in VM Usage

5. Index Space Usage more than Data Space Usage

6. SQL 7.0 Performance and MultiProcessor servers

7. multiprocessor problem!

8. Multiprocessor Constraint for SQL Standard Edition

9. Has MSOLAP multiprocessor support

10. SQL2000 and upgrade to multiprocessor environment

11. SQL2K with multiprocessor

12. MultiProcessor Licensing Question


 
Powered by phpBB® Forum Software