CPU Consumption issues with SQL Server 2019, Cube and PBI Live Connection

Question

Hi There,&nbsp;I am in the middle of a SQL Server upgrade project and am migrating PowerBI Reports to the new server.During the migration I noticed that some tiles of the reports are using a lot of CPU on the SQL Server and do not release it.This is a documentation of the issues, did anyone face similar issues during a migration to a new SQL Server?On the old environment (SQL Server 2012) exactly the same reports run perfectly fine.Thanks!JürgIssues with SQL Server 2019, Multi Dimensional Data Model (Cube) and Power BI Live Connection via MS Data Gateway&nbsp;Introduction&nbsp;Currently our company is running a BI system hosted on premises based on SQL Server 2012 on Windows Server 2008 R2.Due to the end of extended support of SQL 2012, the company decided to migrate the BI system “as is” on a newer version of SQL Server (SQL Server 2019) and Windows Server (Windows Server 2016, Microsoft Windows Server 2022 Datacenter).Both environments run on the same VM host.&nbsp;The SQL Server Analysis Service is running a multi dimensional model which is accessed via Excel and PowerBI via live connection.Currently both environments are running in parallel.&nbsp;During the preparation of the migration of the Power BI Reports from the old to the new environment, issues occurred with the usage of the CPU in the new environment.&nbsp;The symptoms are, that specific PowerBI reports are causing a very high CPU usage which result in very bad performance (up to 11 Minutes to display one visual) or, even worse, the reports are not displayed at all.The same report built in Excel updates within seconds.We ran tests with different sizing of CPU. Tests were executed with 4, 8 and 16 cores. None of them have shown a better performance.&nbsp;SetupOld environment&nbsp;SQL Server:SQL Server Version:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2012OS:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Windows Server 2008 R2CPU:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 24 coresRAM:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 128 GB&nbsp;Microsoft On Premises Data Gateway:Hosted:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; hosted on the SQL ServerVersion:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3000.10.248 (October 2019(Release 2))&nbsp;&nbsp;New environment&nbsp;SQL Server:SQL Server Version:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2019OS:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Windows Server 2016 / Microsoft Windows Server 2022 DatacenterCPU:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8 coresRAM:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 128 GB&nbsp;Microsoft On Premises Data Gateway:Hosted:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; hosted on separate Server in same DomainVersion:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3000.142.14 (September 2022)&nbsp;Tested Scenarios&nbsp;Report with ExcelBuilding exactly the same report in Excel and connecting to the new environment does not cause any issues.Results are displayed within secondsInstall Data Gateway on new SQL ServerResult: no better performance, same CPU usageUse old Data Gateway installed on old SQL ServerResult: no better performance, same CPU usageIncrease CPU power on new SQL Server to 16 CPUResult: no better performance, relation of CPU Usage is the same – see screenshot belowExactly the same report on the old SQL Server runs within seconds (like via Excel)&nbsp;&nbsp;SQL Server CPU Usage&nbsp;Calling Report with 16 cores&nbsp;&nbsp;&nbsp;&nbsp;Calling Report with 8 cores&nbsp;&nbsp;as you can see, increasing the number of CPUs is not resolving the problem.&nbsp;

anonymous · Accepted Answer

Hi&nbsp;Juerg&nbsp;,
&nbsp;
Please check whether your report has complex logic or applies RLS, you can refer to this post to optimize the report.
If you have a Premium license, you can use the Premium Capacity Metrics app to track and analyze your CPU.
You can check out the following posts that may be helpful to you:
Solved: Complex report dragged SQL Server down to a crawl - Microsoft Power BI Community
&nbsp;
If this does not work, then the problem does not seem to be related to Power BI and you should try contacting SQL support for assistance.
&nbsp;
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.&nbsp;
&nbsp;

Forum Discussion

CPU Consumption issues with SQL Server 2019, Cube and PBI Live Connection

Issues with SQL Server 2019, Multi Dimensional Data Model (Cube) and Power BI Live Connection via MS Data Gateway

Introduction

Setup

Old environment

New environment

Tested Scenarios

SQL Server CPU Usage

2 Replies

Related content

Memory consumption issue while refreshing the data source

Less resource consumption

Fabric Capacity Consumption

Data Gateway Connection Issue

Fabric Lakehouse connection issue