Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Output = MAXDOP 1

It is widely know that data modifications on table variables do not support parallelism, Peter Larsson has a good example of that here .  Whilst tracking down a performance issue,  I saw that using the OUTPUT clause also causes parallelism to not be used.

By way of example,  first lets create two tables with a simple parent and child (one to one) relationship, and then populate them with 100,000 rows.

Drop table Parent
Drop table Child
go
create table
Parent
(
id integer identity Primary Key,
data1 char(255)
)

Create Table Child
(
id integer Primary Key
)
go
insert into
Parent(data1)
Select top 1000000 NULL
from sys.columns a cross join sys.columns b

insert into Child
Select id from Parent
go

If we then execute

update Parent 
set data1 =''
from Parent
join Child on Parent.Id = Child.Id
where Parent.Id %100 =1
and Child.id %100 =1

We should see an execution plan using parallelism such as

Parrelel1

 

However,  if the OUTPUT clause is now used

update Parent 
set data1 =''
output inserted.id
from Parent
join Child on Parent.Id = Child.Id
where Parent.Id %100 =1
and Child.id %100 =1

 

The execution plan shows that Parallelism was not used

Parrelel2

Make of that what you will, but i thought that this was a pretty unexpected outcome.

 

Update : Laurence Hoff has mailed me to note that when the OUTPUT results are captured to a temporary table using the INTO clause,  then parallelism is used.  Naturally if you use a table variable then there is still no parallelism  

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.