Last week I came across a special behavior regarding the MAXDOP query hint. When trying to limit the number of CPUs for a particular query to 1, I discovered a peculiarity.
See the following example for an illustration of the problem.
In the first step, we create a test table:
Our test table consists of two columns and contains 500000 rows.
Now, let’s create a second table this way:
create table t1
We’ll leave this table empty for the moment. Later on we’ll insert some rows. But first we also create an insert trigger for table t1:
create trigger ti_t1 on t1
This trigger doesn’t do anything useful but we need it for the effect I’d like to demonstrate.
We’re now ready to start the experiment. Let’s insert 1 row into table t1:
insert t1(c1) values (100)
As expected, the insert will also invoke our trigger and the execution plan looks like this:
Apparently the select statement inside the trigger is executed in parallel. If we’d like to avoid this, we may specify the MAXDOP query hint and limit the CPU utilization to 1:
insert t1(c1) values (100) option (maxdop 1)
Surprisingly or not, the MAXDOP query hint does not affect the trigger code. The SQL inside our trigger is still executed in parallel. There’s no change in the execution plan.