I'm running SQL2005 SP1 on win 2003 SP1.
I've noticed that when I'm running large updates, SQL is not using all
processors. When I run an update statement like:

UPDATE TestTable SET Price = Price + 1

all the processing is done on 1 processor. The box has 2 hypherthreaded
processors, so OS and SQL see 4 processors. Total CPU time is on 25% and
that means that only 1 thread is used. Testtable has over 10 million records
so it takes more than 30 minutes to update entire table that way.
Maximum degree of parallelism is set to 0, if I run a large select on a
table it uses all processors and CPU time is on 100%. Log file is also on
separate drive and there is no queue on it.
Can I force UPDATE statement to use all processors and speed up update that
way?


Tom.

You also need to consider the possibility that the process is actually
I/O bound, not CPU bound. The update you describe really doesn't have
a lot of complex processing, it mostly has to move a lot of data from
the disk and back again.

Have you tried running two such tests at the same time? Against
different tables, of course, but tables in the same database. If the
elapsed time is constant and the CPU activity doubles then the disk
activity has doubled, and a parallel plan might use some of that
reserve disk throughput. But to the degree that the elapsed time
increases it would tend to reflect an I/O bottleneck that can not be
addressed by parellel processing.

Roy Harvey
Beacon Falls, CT

On Wed, 13 Sep 2006 13:47:46 +0200, "Tom" <mcseman@hotmail.com> wrote:

>I'm running SQL2005 SP1 on win 2003 SP1.
>I've noticed that when I'm running large updates, SQL is not using all
>processors. When I run an update statement like:
>
>UPDATE TestTable SET Price = Price + 1
>
>all the processing is done on 1 processor. The box has 2 hypherthreaded
>processors, so OS and SQL see 4 processors. Total CPU time is on 25% and
>that means that only 1 thread is used. Testtable has over 10 million records
>so it takes more than 30 minutes to update entire table that way.
>Maximum degree of parallelism is set to 0, if I run a large select on a
>table it uses all processors and CPU time is on 100%. Log file is also on
>separate drive and there is no queue on it.
>Can I force UPDATE statement to use all processors and speed up update that
>way?
>
>
>Tom.
>

I tried running two tests at the same time and elapsed time stayed the same.
CPU time jumped to 50% and disk activity also doubled, specialy on the disk
where log file is located due to heavy logging (disk queue still remains on
0 most of the time).Data disk has almost no activity except when checkpoints
are acouring since both test tables fit in RAM.
So, IO subsystem is not a botleneck here and there is still a lot of CPU
power left, SQL is just not using all CPU's for such a large UPDATE and I
don't know why!!!

Tom



"Roy Harvey" <roy_harvey@snet.net> wrote in message
news:rctfg2l6q65fqtbt1j4k526cjvk820e5gu@4ax.com...
> You also need to consider the possibility that the process is actually
> I/O bound, not CPU bound. The update you describe really doesn't have
> a lot of complex processing, it mostly has to move a lot of data from
> the disk and back again.
>
> Have you tried running two such tests at the same time? Against
> different tables, of course, but tables in the same database. If the
> elapsed time is constant and the CPU activity doubles then the disk
> activity has doubled, and a parallel plan might use some of that
> reserve disk throughput. But to the degree that the elapsed time
> increases it would tend to reflect an I/O bottleneck that can not be
> addressed by parellel processing.
>
> Roy Harvey
> Beacon Falls, CT
>
> On Wed, 13 Sep 2006 13:47:46 +0200, "Tom" <mcseman@hotmail.com> wrote:
>
>>I'm running SQL2005 SP1 on win 2003 SP1.
>>I've noticed that when I'm running large updates, SQL is not using all
>>processors. When I run an update statement like:
>>
>>UPDATE TestTable SET Price = Price + 1
>>
>>all the processing is done on 1 processor. The box has 2 hypherthreaded
>>processors, so OS and SQL see 4 processors. Total CPU time is on 25% and
>>that means that only 1 thread is used. Testtable has over 10 million
>>records
>>so it takes more than 30 minutes to update entire table that way.
>>Maximum degree of parallelism is set to 0, if I run a large select on a
>>table it uses all processors and CPU time is on 100%. Log file is also on
>>separate drive and there is no queue on it.
>>Can I force UPDATE statement to use all processors and speed up update
>>that
>>way?
>>
>>
>>Tom.
>>

It is easy enough to limit parallelization, but I don't know of any
way to force it. Perhaps one of the real gurus will know some tricks
that I do not.

Roy

On Wed, 13 Sep 2006 16:47:02 +0200, "Tom" <mcseman@hotmail.com> wrote:

>I tried running two tests at the same time and elapsed time stayed the same.
>CPU time jumped to 50% and disk activity also doubled, specialy on the disk
>where log file is located due to heavy logging (disk queue still remains on
>0 most of the time).Data disk has almost no activity except when checkpoints
>are acouring since both test tables fit in RAM.
>So, IO subsystem is not a botleneck here and there is still a lot of CPU
>power left, SQL is just not using all CPU's for such a large UPDATE and I
>don't know why!!!
>
>Tom

I also tryed MAXDOP option and it works nice with SELECT but UPDATE ignores
it.

Tom


"Roy Harvey" <roy_harvey@snet.net> wrote in message
news:rctfg2l6q65fqtbt1j4k526cjvk820e5gu@4ax.com...
> You also need to consider the possibility that the process is actually
> I/O bound, not CPU bound. The update you describe really doesn't have
> a lot of complex processing, it mostly has to move a lot of data from
> the disk and back again.
>
> Have you tried running two such tests at the same time? Against
> different tables, of course, but tables in the same database. If the
> elapsed time is constant and the CPU activity doubles then the disk
> activity has doubled, and a parallel plan might use some of that
> reserve disk throughput. But to the degree that the elapsed time
> increases it would tend to reflect an I/O bottleneck that can not be
> addressed by parellel processing.
>
> Roy Harvey
> Beacon Falls, CT
>
> On Wed, 13 Sep 2006 13:47:46 +0200, "Tom" <mcseman@hotmail.com> wrote:
>
>>I'm running SQL2005 SP1 on win 2003 SP1.
>>I've noticed that when I'm running large updates, SQL is not using all
>>processors. When I run an update statement like:
>>
>>UPDATE TestTable SET Price = Price + 1
>>
>>all the processing is done on 1 processor. The box has 2 hypherthreaded
>>processors, so OS and SQL see 4 processors. Total CPU time is on 25% and
>>that means that only 1 thread is used. Testtable has over 10 million
>>records
>>so it takes more than 30 minutes to update entire table that way.
>>Maximum degree of parallelism is set to 0, if I run a large select on a
>>table it uses all processors and CPU time is on 100%. Log file is also on
>>separate drive and there is no queue on it.
>>Can I force UPDATE statement to use all processors and speed up update
>>that
>>way?
>>
>>
>>Tom.
>>

Is there an index on the column you are updating? Updates are always single
threaded. That is the act of updating the row. You can't have two threads
update one row. The WHERE clause can use parallelism. In this case you are
updating the entire table so there is nothing to search per say. If there is
an index on the column you have to update that as well and it may be easier
to do that single threaded. One thing to try is specify a tablock on the
update and remove any indexes on that column.

--
Andrew J. Kelly SQL MVP

"Tom" <mcseman@hotmail.com> wrote in message
news:%23j247py1GHA.4908@TK2MSFTNGP02.phx.gbl...
> I'm running SQL2005 SP1 on win 2003 SP1.
> I've noticed that when I'm running large updates, SQL is not using all
> processors. When I run an update statement like:
>
> UPDATE TestTable SET Price = Price + 1
>
> all the processing is done on 1 processor. The box has 2 hypherthreaded
> processors, so OS and SQL see 4 processors. Total CPU time is on 25% and
> that means that only 1 thread is used. Testtable has over 10 million
> records so it takes more than 30 minutes to update entire table that way.
> Maximum degree of parallelism is set to 0, if I run a large select on a
> table it uses all processors and CPU time is on 100%. Log file is also on
> separate drive and there is no queue on it.
> Can I force UPDATE statement to use all processors and speed up update
> that way?
>
>
> Tom.
>
>

How do you know that the update is only using one processor.
I like to look into this on our system too.
Thanks