SQL and the like

Dave Ballantyne's blog. Freelance SQL Server database designer and developer at Clear Sky SQL

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

Dew Drop – March 4, 2010 | Alvin Ashcraft's Morning Dew said:

Pingback from  Dew Drop – March 4, 2010 | Alvin Ashcraft's Morning Dew

# March 4, 2010 1:52 PM

GrumpyOldDBA said:

It's interesting but i have to ask which plan would actually be the most efficient? Table scans tend to always generate parallelism but it doesn't mean it's actually the most efficient. But very interesting, I will maybe examine some of my queries more closely in that direction in future.

# March 12, 2010 8:04 AM