Data Gateway refresh causing huge CPU spikes on SQL server (Dynamics 365)

[jdubs]

I have a couple of Power BI reports which query Dynamics 365 SQL databases via a scheduled Data Gateway refresh. When run these refreshes cause huge CPU spikes on the SQL server which in turn causes SQL timeouts on the Dynamics web front-end and major performance issues. Is there a way to throttle the queries so that they don't cause such impact on the SQL server?

[jdubs]

It's almost like the refresh is locking the tables/view that it is querying. In this case, it is the Contacts table in a Dynamics 365 database. When the refresh is running, you can't access these records in dynamics. The loading wheel just spins and eventually you get a SQL timeout. I've read other threads about using NOLOCK on a SELECT query but what if you're just retrieiving the whole table using this syntax?

let
Source = Sql.Database("sqlserver", "sql_database", [CommandTimeout=#duration(0,0,120,0)]),
dbo_FilteredTable = Source{[Schema="dbo",Item="FilteredTable"]}[Data],

[v-janeyg-msft]

Hi, @jdubs 

There seems to be no good way. Please try to schedule the refresh of large data during non-working time to ensure that other tasks can be performed without being affected.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jdubs]

Unfortunately, disabling this did not make any noticeable difference.

[parry2k]

Yes you do need to republish the report, yes that setting is correct.

Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[jdubs]

I just want to confirm that this is the setting you are referring to?

And I assume I need to re-publish the report once I turn it off?

[parry2k]

@jdubs I assume it will not 100% sure but I'm confident it will, can you please test.

Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@jdubs may be in your power bi report, turn off parallel processing under options and then check.

Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[jdubs]

Thanks for the reply! Would this affect a scheduled data refresh via the data gateway?