Esat Erkec

Load testing for SQL Server Reporting Services (SSRS)

September 25, 2018 by

Performance monitoring is a very significant and essential topic for database or system administrators. This is no different with SSRS load testing. In some cases, performance problems can lead to significant system downtime. Most of the time in the background of the problem is having insufficient resources or improper settings. Therefore, before experiencing performance issues, we have to take some time to review preventive measures related to current system configurations or new installations. The load test (or stress test) is the most commonly used approach to tune systems before real-life staging conditions. Now we will discuss the details of load test, and in particular SSRS load testing.

Load Testing

The intent of the load test is to determine and analyze the system performance metrics with some simulations closest to real-time usage scenarios. Stress tests can measure the system performance capacity and give more details about the performance metrics under the pressure. With load tests, we can detect performance problems earlier and can take precautions about it. Especially in peak periods, the system must preserve its own stability and in order to ensure we can achieve this, we to need test for maximum stress. At this point, we have to clarify one thing about load testing. The aim of this type of testing is not to test the system for “bugs”. The main idea of load test is to analyze system performance behavior under maximum simulated workloads. The thinking being is that if the system can manage very high, simulated workloads, then it should be able to handle production workloads that are as high or lower. If the system has failure points, then they will become readily apparent in high/higher than normal workloads, so they can be caught early before they are experienced in production.

Load testing in SSRS

Apart from SQL Server, SSRS test and performance monitoring tools are very limited. In this article, we will use an open source project for load test which is Reporting-Services-LoadTest. With this project, we can generate a synthetic workload in SQL Server Reporting Service and can run maximum workload scenarios. The advantage of this project is; its publication as an open source and also you can add additional features or can customize to project for your reporting service environment. In addition, it is documented well. Before starting the demonstration I want to add some notes, you must have some knowledge and experience about SSRS and C# development because we will skip some steps about report development in SSRS but this articles ( Reporting Services (SSRS) ) will help details about reporting service and report development. Another requirement is that you need to look at the SSRS Load Testing documents and How to onboard a new Paginated Reports Scenario tutorial. We will proceed to the next section of the article based on this tutorial.

Demo

In the first step for creating our SSRS testing system, we have to download Reporting-Services-LoadTest-master project. To open the project, we can use Visual Studio 2015 or above version but you have to ensure Web performance and testing tools installed to your Visual Studio IDE (Integrated development environment). We will use a Product Catalog report which is placed in the “Reporting-Services-LoadTest-master\src\RSLoad\ContentManager\RuntimeResources\Paginated\General” project folder.

We will create a folder in the project and copy Product Catalog report to this folder.

We will comment out all unnecessary data sources in the datasource.xml file. The Product Catalog report only requires the AdventureWorks data source and other data sources are needless.

Set the proper configuration for reporting service and dataset connections in the RSTest.Common.ReportServer.dll.Config file. Because when we run the test these credentials will be used by AdventureWorks data source.

We will edit the code and run the test. You have to ensure that your test method has passed.

After the execution of test method, it will publish the Product Catalog report and AdventureWorks data source to report server. We can see these files in the Reporting Service web portal.

We need to ensure Product Catalog report works successfully because all virtual users execute this report in the test scenario.

We will add a new test to RSLoad project. With this test, we will create our workload scenario and then test it.

We will select the On-Premise Load Test option and click next.

We will set duration of test.

We will give a name to load test scenario. The test scenario name has to be same as report folder which we created and copied to the Product Catalog report.

In this screen, we can type two types of load patterns. These are constant user or step load. In the constant load option, user count does not change during the load test. In the step load option, we can increase the user count step by step and we can limit the maximum number of user values.

We will add this test to load scenario. For this test, we will select only FullReportRenderHTML5 and click OK. If you want, you can add another test to load test scenario but you need to determine distribution ratio of tests.

We will skip previous screens with Next button and click Finish.

We will run the test.

During test you can monitor the system performance in the analysis screen and also you can find out which resources are insufficient or can be a cause to bottlenecks.

In my point of view, these screens are very complex and sophisticated, for this reason, we will use ApexSQL BI Monitor, a tool for SSRS performance monitoring. ApexSQL BI Monitor offers very efficient, simple and detailed performance monitoring screens than the default graphs and screens. When I launch to ApexSQL BI Monitor, I can find out all bottlenecks in the Report Server during the test.

The above image indicates that we have two big problems in the system that hosts SSRS because it is colored with red. When I click the red box, it will show me which resources can cause problem. These are:

  • Utilization of processor time
  • Pages input per second

When I click the Utilization of processor time it offers the detail graph of processor utilization. It means that if I face this type of workload I have to add many processors. Because processor utilization reaches the maximum.

When we navigate the Web Service details. We can find out our load test reached the maximum users, step by step, in the Active connections graph.

Summary

In this article, we mentioned SSRS load testing and how to analyze the result of a load test. Analyzing load testing results is crucial process and ApexSQL BI Monitor offers excellent features to detect performance problems and bottlenecks. In particular, the master warning indicators help to find out the troubled performance metrics easily.

See more

Check out ApexSQL Plan to view and analyze SQL Server query execution plans, for free, including comparing plans, stored procedure performance profiling, missing index details, lazy profiling, wait times, plan execution history




 

Esat Erkec

Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.

View all posts by Esat Erkec
Esat Erkec

Latest posts by Esat Erkec (see all)

261 Views