How can INDEX rebuilds be going parallel when MAXDOP is set to 1

Question

I am periodically running into a THREADPOOL wait issue with a SharePoint data store of hundreds of databases using a SQL Server 2008R2 Standard Edition instance (recently migrated to an Azure VM). It is running a stored procedure named proc_DefragmentIndices in many (possibly all) of these databases at a time.

The stored procedure rebuilds every index in the database unconditionally. Of course, they are head blockers (because it is Standard Edition, each ALTER INDEX command runs with ONLINE=OFF). Because there are so many of them running at a time (each in a different database), and they are going parallel (which ties up even more workers), everything piles up. Just for extra noise, Azure Backup is taking backups of many of the databases while all this is going on, eating up even more workers. Activity Monitor shows 106 waiting tasks, and multiple instances of the same session Id for many of the ALTER INDEX commands (which is why I say they are going parallel).

What I find puzzling is that these ALTER INDEX statements are going parallel even though MAXDOP is set to 1 in the instance, as is recommended for SharePoint databases, and the ALTER INDEX statements executed by the stored procedure are not using a MAXDOP option to override it.

Q1: How can INDEX rebuilds be going parallel when MAXDOP is set to 1?

Q2: Activity Monitor shows the ALTER INDEX commands but sp_WhoIsActive does not. Does anyone know why?

Answer 1

It is running a stored procedure named proc_DefragmentIndices in many (possibly all) of these databases at a time.

The stored procedure rebuilds every index in the database unconditionally.

I assume you don't have any control of this, but I'd be remiss if I didn't suggest that you make this stop happening as soon as possible 😀 Use Ola Hallengren's script, which will at list rebuild or reorg only at specific fragmentation thresholds. Or considering avoiding index rebuilds in favor of statistics updates.

How can INDEX rebuilds be going parallel when MAXDOP is set to 1?

They shouldn't be. And they really shouldn't be because you're using Standard Edition - and parallel index operations are an Enterprise Edition feature. I wonder if there is just something weird about the way this is showing up in Activity Monitor.

Activity Monitor shows the ALTER INDEX commands but sp_WhoIsActive does not. Does anyone know why?

That's another red flag that something is off here. Index rebuilds should show up just fine in sp_WhoIsActive. Here's me rebuilding the Posts table (from the StackOverflow2010 sample database) on my laptop:

USE StackOverflow2010;

ALTER INDEX PK_Posts__Id ON dbo.Posts REBUILD;

As a further point of investigation, you could query sys.dm_os_tasks directly when you see one of these ALTER INDEX sessions that appears to have gone parallel, to be extra sure whether it's serial as it should be, or parallel.