Did you know: MAXDOP 1 will not always limit CPU utilization to 1

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:

use tempdb go create table t0  (    c1 int   ,c2 nchar(200) not null default '#'  ) go insert t0(c1)   select top(500000)          row_number() over(order by current_timestamp)     from sys.trace_event_bindings as b1         ,sys.trace_event_bindings as b2 go

Our test table consists of two columns and contains 500000 rows.

Now, let’s create a second table this way:

create table t1  (   c1 int not null  ,c2 nchar(300) not null default '#'  ) go

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   for insert as   declare @p1 int  select @p1 = t0.c1     from t0,inserted     where t0.c1 between inserted.c1 and 30000      order by t0.c1 go

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.