0

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.

seesharpconcepts
  • 180
  • 2
  • 4
  • 13

2 Answers2

1

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.

Satya_MSFT
  • 1,024
  • 6
  • 10
  • Setting `option (maxdop 1)` for each impacted query seems to be working well for us. We have done much of testing after changing these queries and have not run into anything unexpected. I wanted to check if this can be done at the server level. – seesharpconcepts Oct 14 '15 at 06:04
  • Right now there is no support for modifying the setting but are working on providing this ability for each database (no ETA yet). – Satya_MSFT Oct 14 '15 at 17:13
  • 3
    This can be changed now in SQL DB with database scoped configuration setting. http://www.sqlindepth.com/changing-max_dop-setting-in-sql-azure-db-v12/ – Sirisha Chamarthi Jun 23 '16 at 04:31
  • i have a question here, thanks https://dba.stackexchange.com/questions/274411/update-large-table-with-millions-of-rows-in-sql-server-in-parallel –  Aug 26 '20 at 16:21
0

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.

m1nkeh
  • 1,337
  • 23
  • 45
  • 1
    I'm afraid with the newer version of Azure Sql which is V12 MAXDOP value is not defaulted to 1 instead it is 0. I queried sys.configurations and saw maxdop as 0. I read [here](http://sqlmag.com/sql-server/introducing-azure-sql-database-v12) that parellelism support is available from this version for Premium tier. I might have missed something from the change log. – seesharpconcepts Oct 13 '15 at 17:20
  • that's ok, no need to be afraid ;) i guess this will start to happen more and more with the rampant release cycle of azure, i've not read anything about v12 yet at all! in fact, this "what's new" doesn't even list the change - https://azure.microsoft.com/en-gb/documentation/articles/sql-database-v12-whats-new/ – m1nkeh Oct 14 '15 at 08:55