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

[Juerg]

Hi There, 

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ürg

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

Introduction

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.

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.

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.

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.

Setup

Old environment

SQL Server:

SQL Server Version:                              2012

OS:                                                        Windows Server 2008 R2

CPU:                                                     24 cores

RAM:                                                    128 GB

Microsoft On Premises Data Gateway:

Hosted:                hosted on the SQL Server

Version:                3000.10.248 (October 2019(Release 2))

New environment

SQL Server:

SQL Server Version:                             2019

OS:                                                       Windows Server 2016 / Microsoft Windows Server 2022 Datacenter

CPU:                                                     8 cores

RAM:                                                    128 GB

Microsoft On Premises Data Gateway:

Hosted:                hosted on separate Server in same Domain

Version:              3000.142.14 (September 2022)

Tested Scenarios

• Report with Excel
  Building exactly the same report in Excel and connecting to the new environment does not cause any issues.
  Results are displayed within seconds
  
  
• Install Data Gateway on new SQL Server
  Result: no better performance, same CPU usage
  
  
• Use old Data Gateway installed on old SQL Server
  Result: no better performance, same CPU usage
  
  
• Increase CPU power on new SQL Server to 16 CPU
  Result: no better performance, relation of CPU Usage is the same – see screenshot below
  
  
• Exactly the same report on the old SQL Server runs within seconds (like via Excel)

SQL Server CPU Usage

Calling Report with 16 cores

Calling Report with 8 cores

as you can see, increasing the number of CPUs is not resolving the problem.

[Juerg]

Hello Neeko, 

Thank you very much for your reply and the helpful links.

In the meantime I found a workaround by identifying the combination measures and dimensions that cause the issue and modified the reports.

Of course this is not an ideal solution but it helps for the moment as only a few reports are affected.

As we face this behavior only in the combination of PowerBI Service, Data Gateway and SQL Server 2019 (where the only difference to the old environment is the SQL Server version), I guess the only way forward is (as you also suggest) to contact the SQL support.

Thanks for your help and kind regards,

Jürg

[Anonymous · Accepted solution]

Hi @Juerg ,

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

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.

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.