Forum Discussion

jdubs's avatar
jdubs
Icon for Helper V rankHelper V
5 years ago

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

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 ...
Need Help
Tips and Tricks
jdubs's avatar
jdubs
Icon for Helper V rankHelper V
5 years ago

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's avatar
v-janeyg-msft
Icon for Community Support rankCommunity Support
5 years ago

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.