We all know that the magic figure for cost threshold for parallelism is 5 by default, meaning if the estimated cost of a query is greater than 5 it may very well generate a parallel plan.

Does this apply to Azure SQL Database? Let’s check.

SELECT @@VERSION

SELECT * FROM sys.configurations
WHERE name = 'cost threshold for parallelism'

Can we change it? No.

EXEC sp_configure 'cost threshold for parallelism', 10;
GO
RECONFIGURE;
GO

Msg 2812, Level 16, State 62, Line 9 Could not find stored procedure ‘sp_configure’. Msg 40510, Level 16, State 1, Line 11 Statement ‘CONFIG’ is not supported in this version of SQL Server.

No post on cost threshold for parallelism is complete without a quick mention on MAXDOP. For Azure SQL Database MAXDOP is set to 0. This means that it will parallelize a query across all available cores that are assigned to the SQL Server. From looking at the schedulers there is only 1 visible online. (Please note I was testing this on a S1 database).

SELECT * FROM sys.dm_os_schedulers;

Very interesting there are many hidden ones where they are used to process requests that are internal to the engine itself.

Being a curious cat I wondered if we would have more visible online schedulers after scaling to a premium level database?

SELECT * FROM sys.database_service_objectives
WHERE database_id = 15

SELECT * FROM sys.dm_os_schedulers;

Just as I thought, we do have an extra visible online scheduler to handle more tasks.

update – as Brent states in the comments you can issue the ALTER DATABASE SCOPED CONFIGURATION command for maxdop.