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.
CTFP, no, but check this out: SQL 2016 brought database-scoped parameters, and one of those is MAXDOP. You can control it with ALTER DATABASE like this:
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1;
That works in Azure SQL DB.
LikeLiked by 1 person
Hey Brent. Totally forgot about that command will update the post and play around with it. Thanks for the update
LikeLiked by 1 person
Pingback: Dew Drop - March 19, 2018 (#2686) - Morning Dew