Azure SQL Database and Cost Threshold for Parallelism

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'

azureversion

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;

1core
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

P2DB

SELECT * FROM sys.dm_os_schedulers;

2online

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.

Advertisements

3 thoughts on “Azure SQL Database and Cost Threshold for Parallelism

  1. 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.

    Liked by 1 person

  2. Pingback: Dew Drop - March 19, 2018 (#2686) - Morning Dew

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s