Output = MAXDOP 1
- by Dave Ballantyne
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 ParentDrop table Childgocreate table Parent(id integer identity Primary Key,data1 char(255))Create Table Child(id integer Primary Key)goinsert into Parent(data1)Select top 1000000 NULL from sys.columns a cross join sys.columns b insert into ChildSelect id from Parentgo
If we then execute
update Parent set data1 =''from Parentjoin 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
However, if the OUTPUT clause is now used
update Parent set data1 =''output inserted.idfrom Parentjoin 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
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
