PowerBI Report Server Performance Tuning Best Practices (PBIRS)

In this article, we are going to highlight the PowerBI Report Server performance tuning best practices. Since the PBI report server is an enterprise reporting solution, it is imperative to optimize the performance of the overall server.

Microsoft has done their own benchmarking where less than 5% of the users are consuming the same report at the same time. The results are below: As you can see from the results, the PBIX reports (PowerBI Desktop) consumes more resources and offers significantly less performance on an 8 CPU, 32 GB memory server. On the contrary, when you double the resources (16 CPU & 64 Gigs of RAM), the performance gap considerably reduces.

Workload Bench	8 Core & 32 GB RAM	16 Core & 64 GB RAM
Power BI Report Heavy (PBIX) (>60%)	1,000 users	3,000 users
Paginated (RDL) Report Heavy (SSRS) (>60%)	2,000 users	3,200 users
PBIX Heavy Reports	50 Concurrent user loads	150 Concurrent user loads
SSRS Heavy Reports	100 Concurrent users	160 Concurrent users
PBIX Heavy Reports (Total User base)	1000	3000
SSRS Heavy Reports (Total User Base)	2000	3200
View the Report uses as a reference	8 core (PBIX) 8 core (SSRS)	16 core (PBIX) 16 core (SSRS)

As you can see from the official tests, the performance of the report server significantly increases when you increase the allocated memory and RAM. However, do note that this is a 3-tier server and comprises of the following:

1. AD Domain Controller (2 Cores, 7 GB RAM – DS2_v2)
2. Database Server (15 Cores, 56 GB RAM – DS5_v2)
3. SQL Server Analysis Services (15 Cores, 56 GB RAM – DS5_v2)
4. PowerBI Report Server (Standard_D8S_v3 for 8C/16 – Standard_D8S_v3, GB & 16C/64GB, vStandard_D16S_v3)

Now, these are all Azure Virtual Machines, and PowerBI report server is primarily meant to be run on-premises. So, for on-premises deployment, let’s find out what best practices you need.

PowerBI Report Server Performance Tuning Best Practices

Just like the reference system that Microsoft used for the tests, we recommend isolating the AD, SQL Server Database, Analysis Services (if applicable), & Power BI Report Server. Here is a sample reference architecture below:

 

For the Database Server, IOPS should be the key metric to look for. Since the database servers are transactionally heavy, investing in a high-performance storage device is recommended. Here are the storage recommendations:

• Less than 10 ms – very good and ideal for both Database & Log files (MDF & LDF)
• Between 10 – 20 ms – okay (Suitable for DB Files (MDF), not for log files)
• Between 20 – 50 ms – slow, needs attention
• Greater than 50 ms – Serious I/O bottleneck – Storage Upgrade might be necessary if the behavior is consistent.

Here are some of the Performance monitor counters (perfmon.exe) to capture disk specific parameters are as follows:

• Physical Disk—% Disk Time  –     Should average below 50%.
• Physical Disk—Avg. Disk Queue Length Should average below 2 per disk. For example, for an array of 5 drives, this figure should average below 10.
• Physical Disk—Avg. Disk Reads/sec    –    Used to size the disk and CPU. Should be below 85% of the capacity of the drive.
• Physical Disk—Avg. Disk Writes/ sec   –     Used to size the disk and CPU. Should be below 85% of the capacity of the drive.

Ensure that the SQL Server: Buffer Manager— Buffer Cache Hit Ratio should exceed 90% (and ideally approach 99%).

For the Report Server, since it is only servicing stateless HTTP requests, under high load, the CPU and memory come under pressure, but IOPS – not so much. Monitor the following perfmon performance counters to ensure that your CPU and Memory usage is under control.

• Memory—Pages/sec     Should average below 20 (and preferably below 15).
• Memory—Available Bytes           Should remain above 50 MB.
• Memory – Free System Page Table Entries
• Memory – Pages Input/Sec
• Processor: % Processor Time  Should average below 75%, avoiding spikes (and preferably below 50%)

Disk Configuration Best Practices

• Use separate disk drives for different purposes. (Log File, Data Files, Backup & TempDB)
• Maintain strict Disk latencies. For Database Files (MDF) 15-25 ms. For Log files, between 2-5 ms. It applies to both standard & tempDB data & log files.
• A minimum of RAID 5, RAID 10 is recommended for Reporting Queries (SSRS Paginated Reports)
• Format all the disk drivers (that will contain Data & Log files) with 64 KB (Extents) cluster allocation.
• If you are running any corporate anti-virus programs, put *.mdf, *.ndf & *LDF under exemption list.
• TempDB should be planned accordingly based on the optimizations defined by Microsoft.

TempDB Best Practices (On the Catalog Server)

• Set the recovery model of TempDB to SIMPLE. This model automatically reclaims log space to keep space requirements small.
• Disable to default allow settings for TempDB files to grow automatically. It reduces the CPU overhead of managing a dynamic file-growth.
• Create multiple data files for TempDB. How Many? Total Number of TempDB Primary Data File = SQL Processor Count not exceeding eight files. Which means, if you have 16 cores for SQL Server, create only eight files.
• Each data file should be of equal size.
• Try to keep each data files on separate disk drives for achieving high IO Parallelism.
• TempDB Data and Log files should be maintained on faster disk drives (Preferably RAID 1 if possible)
• Use RAID-10 or SSD Disks.
• Pre-size TempDB files
• 25% of largest DB size.
• Set Auto Growth to fixed size < 200 MB
• You should have the same number of data files as the number of CPUs up to a maximum of 8.

Conclusion

We hope this article helped assist you with PowerBI Report Server Performance Tuning Best Practices. Questions and suggestions are welcome in the comments section below. Thanks for visiting!