Guidance on how to take Performance Capacity Metrics and apply them to a Premium Gen2 P1 Capacity Se

[tctrout]

I am looking on guidance on how to take Performance Capacity Metrics and apply them to a Premium Gen2 P1 Capacity Settings. 

The Scenario:
The Premium Capacity Utilization and Metrics Report indicates that the capacity is peaking over 80% of the available Capacity CPU. The TimePoint Detail drill through indicates a list of Artifacts within the Interactive Operations which pinpoints which Artifacts / Queries are being executed at that TimePoint. The details provided are all in CPU Seconds. I know that since this is a P1 SKU that we are allowed 120 CPU Seconds (4 backend cores x 30 second intervals). This TimePoint Detail is great at identifying which solutions could benefit from optimization by reviewing Total CPU’s & % of Capacity. As an administrator, I informed the user that their solution could benefit from performance tuning and the user will perform this work. 
https://learn.microsoft.com/en-us/power-bi/enterprise/service-premium-gen2-metrics-app#timepoint


In the meantime, I want to configure the Premium Gen2 Capacity Workloads > Datasets Query Memory Limit %  to prevent capacity spikes. Current this setting is set to 0, which is the default setting resulting in a P1 SKU getting an automatic Query Memory Limit of 6 GB. 
https://learn.microsoft.com/en-us/power-bi/enterprise/service-admin-premium-workloads?tabs=gen2#quer...

The Question:
How do I know what % to apply to the Query Memory Limit % since the Premium Capacity Utilization and Metrics Report uses CPU Seconds as a measurement but the Query Memory Limit % is a percent of total GB memory allocated? The P1 SKU memory and computing power outlines that the Power BI Premium Utilization and Metrics App doesn’t currently expose max memory per query statistics (outlined in footnote 1, see link below). My confusion is that the Query Memory Limit % default setting of 0 = 6GB of a P1 SKU. A P1 SKU has a max memory per dataset of 25 GB. Does this translate to ~25% (6 divided by 25) by the default setting of 6GB ? If I want to reduce capacity spikes is my starting point ~25% and I should go down from there?

https://learn.microsoft.com/en-us/power-bi/developer/embedded/embedded-capacity?tabs=gen2#sku-memory...

[lbendlin]

Are you at any point considering / allowing elastic capacity?

[tctrout]

Not at this time.  We want to ensure we are using what resources we have effeciently first then explore expanding resources.

I do believe though I an attempting to apply Memory Capacity settings to solve a CPU spike issue.  I realize now this will not help our scenario.  In Gen2 Premium Capacities CPUs are important to monitor and to ensure to performance tune solutions to not over utilize CPU.  However, I am looking for Capacity settings to help throttle instances of poorly designed reports.  I am looking for Capacity setting to limit the amount CPU any solution / query can utilize.  

[lbendlin]

We face similar issues but we solve them with a different approach. We extract the telemetry on "long running queries" and then work with the developers who are top offenders to improve the design of their ETL or Direct Queries.  Rinse and repeat.

[tctrout]

Thank you for the insights.  This is exactly what we want to adopt.  However, we want to 'throttle' the solution that is still published while they work on a local development copy.  We are trying to prevent an approach where we have to take a solution completely down from the capacity while performance tuning is performed.  

[lbendlin]

Do you only have that single P1 or do you have the option to move workspaces across capacities?

[tctrout]

We have a P1 Capacity that is considered Development

We then have a P2 Capacity considered Production.

In this scenario, our Development Capacity spikes 100% mainly attributed to a single solution.  We want to prevent a single solution consumping 50% of our production capacity so we have engaged the development team outlining performance tuning steps.  In the meantime, we want to minimize the 100% spike on our devlopment capacity by throttling, if possible, while they work out the performance tuning.

[lbendlin]

I think you want to consider moving them to the P2 so they can sort out their issue while not impacting the other P1 workspaces.  

Dividing your capacities into Development and Production is, is, ehm.  How to say it.   Laudable? Admirable?   Let me say it differently.  In our setup we treat all capacities as equal rights resources. We have seen better performance overall when upscaling (merging two P2 into a P3, for example) rather than downscaling (splitting a P2 into "isolated" P1s.  YMMV.

[tctrout]

Thank you for the insights.  

Yes, existing company policies and procedures written for On Prem SQL Servers etc was applied to PowerBI where there must be 'physical' separation of Production and Lower Environments.  Some of these decisions were outside the realm of our influence.