Does PowerBI when connecting to SQL Server/Azure SQL override MAXDOP?

[Macknife]

Sorry if this is the wrong area to be asking this but I didn't see any that seemed relevant to this question.

I am a SQL DBA but recently became responsible for our PBI environment.

I am trying to update a query being used to populate one of the datasets, and I noticed that the number of parallel processes is way more than the MAXDOP setting for the database. Does PBI override that when connecting to SQL Server/Azure SQL, and, if so, how can I change that?

TIA
Michael MacGregor

[HughLa]

Hi Macknife

Power BI will not override the MAXDOP setting when connecting to SQL Server or Azure SQL. If the MAXDOP setting has been configured at the server level, Power BI will respect that setting when executing queries against the server. If the MAXDOP setting has not been configured at the server level, or if it has been set to 0, Power BI will use the default MAXDOP value for the server.

You can specify the MAXDOP setting for individual queries by using the MAXDOP query hint. For example, you can use the following syntax to specify a MAXDOP value of 4 for a particular query:

SELECT *
FROM table
OPTION (MAXDOP 4)

Hugh

[Macknife]

Hi Hugh,

This is not the behaviour that I am seeing though. We are using Azure SQL Database and the setting for the database is MAXDOP = 8 but the number of parallel processess far exceeds that. 

One particular query that I am trying to update does not appear to be a simple SELECT, and I am unable to see the native query as it has a slew of changes made when viewed in the advanced query editor, one of which was to limit the data to the current year, which is an issue as the users want to see data from the end of last year. 

Although it is not just that query. I see other dataset refreshes being run against the DB and they are not adhering to the MAXDOP setting, with queries having well over 100 parallel processes.

I am fully aware of being able to use the MAXDOP hint for a query, but as mentioned above, none of the queries used by PBI seem to simple queries and cannot be viewed in Desktop in native mode.

Regards,

Mike