Question :
In SQL server the default setting for cost threshold for parallelism is 5.
I know that this setting can’t be modified in Azure SQL server (as mentioned here).
My question is whether Azure SQL server updates that value depending on the needs of that database
Answer :
As far as I know the answer is no.
Try to run:
SELECT * FROM sys.configurations
WHERE name = 'cost threshold for parallelism'
while your Azure SQL Database is under heavy load, I’m quite sure you will not see that value change. (I haven’t tried)
Is well knows that cost threshold for parallelism is set to 5 by default even on the latest versions of SQL Server.
My assumption is that nowadays this is part of that legacy code that has been ported to Azure.
It stays there in a corner, forgot, and no one notice it.
And god only knows how Azure handles cost threshold for parallelism behind the curtains in the cloud.
But sure you have no vision of it and you cannot tune it.