Sunday, February 14, 2010

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

table t0
   c1 int
  ,c2 nchar(200) not null default '#'

  select top(500000)
         row_number() over(order by current_timestamp)
    from sys.trace_event_bindings as b1
        ,sys.trace_event_bindings as b2

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 '#'

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

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.

1 comment: