Modifying max degree of parallelism to 1 for azure sql server

0
votes

I'm able to query "max degree of parallelism(maxdop)" value from sys.configurations but unable update the same for an azure-sql server. I'm not very sure if there is an alternative for sp_configure stored procedure in azure-sql.

SELECT
    name,
    value_in_use
FROM
    sys.configurations(nolock)
WHERE
    description LIKE '%parallelism%'

update  sys.configurations set value_in_use=1 where name='max degree of parallelism'

I get an error that: Ad hoc updates to system catalogs are not allowed. I think it's expected since we are supposed to update configuration values using sp_configure.

Throw some light on setting maxdop at the server level for azure-sql. I can set this at each sp level explicitly but It would be great to set this at server level itself.

How can we set "max degree of parallelism" on sql-azure without using sp_configure? Guide me on ways of updating values in sys.configurations. I do not see any properties for "max degree of parallelism" from the azure portal also.

2
sql-serverazureparallel-processingazure-sql-database

2 Answers

1
votes

You will not be able to change Max DOP in SQL DB and it is equivalent to 0 (for SQL DB v12) which is a different behavior in SQL DB V11 (which is 1) always. If you see your query plans impacting the performance and need to reset it to 1, please open a support ticket to MSFT and this will be fixed.

0
votes

Hmm.. I'm pretty sure that SQL Azure actively ignores any MAXDOP query hint passed to it anyway, which may render your question a moot point perhaps?

AFAIK it's just set to 1 by default, and you get an error if you try to change it up, as you are finding.