Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Large(ish) Dataset Timing Out - CXPacket Suspensions - Power BI Datamart setup w/Query Issues

Hi all;

 

Setting up a DataMart with an incremental refresh from power bi(same errors occuring as a Dataflow option)

 

Advanced option -- has a query referencing multiple tables, with some Case statements and Where clauses. No parameters, no CTE, no functions. 

 

Should return 20-50m rows of data, then incrementally refresh from there.

 

Linking to on prem SQL server via a gateway. 

 

Continues to time out. 

Checking the SQL server status, the request keeps getting suspended for CXpackets. 

So I change the setting on COTP from 5 to 50(on the sql server instance);

Added Max Degrees of Par to 8(on the sql server instance);

 

Same errors. Timeout. Suspended query. 

 

Any thoughts on how to get around this, force the sql instance to place this query as a higher priority, or any other thoughts? 

 

Your help would be greatly appreciated!

Dataflow
Need Help
Tips and Tricks

2 Replies

  • GilbertQ's avatar
    GilbertQ
    Icon for Super User rankSuper User
    3 years ago

    Hi Anonymous 

     

    Firstly please know that datamarts is currently in preview and is a very new product with changes coming down the line and improvements. Right now I personally would not use it for production workloads. It will get there over time but not right now!

     

    With that being said it sounds like the error is within the datamarts because your SQL Server appears to be running well.

     

    You could make sure that the gateway has enough memory, CPU and bandwidth as that might be where the issue is?

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi , Anonymous 

    As far as I know, for SQL Server data sources, each incoming query is processed at the same time according to the order, there should be no forcing the SQL instance to take this query as a higher priority of this setting, if it is convenient you can try to schedule one or several incremental data refreshes of DataMart in this SQL instance is relatively idle (such as early morning or midnight), In order to ensure that the SQL instance is relatively idle time period can make your datamart incremental data refresh complete, if this is not possible, I think the only solution is to continue to optimize your datamart data transformation and SQL multiple table query solutions.

    For more information, you can refer to:
    10 Data Mart Best Practices - CLIMB (climbtheladder.com)

     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

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