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?