Load testing for SQL Server Reporting Services SSRS
Load testing for SQL Server Reporting Services SSRS
Author Recent Posts Esat ErkecEsat 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 Latest posts by Esat Erkec (see all) Five beneficial Azure Data Studio Extensions for SQL developers - July 19, 2022 How to build custom widgets on Azure Data Studio - July 7, 2022 How to obtain SQL Execution Plans using different methods - June 30, 2022
SQLShack
SQL Server training EspañolLoad testing for SQL Server Reporting Services SSRS
September 25, 2018 by Esat Erkec 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.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 historyAuthor Recent Posts Esat ErkecEsat 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 Latest posts by Esat Erkec (see all) Five beneficial Azure Data Studio Extensions for SQL developers - July 19, 2022 How to build custom widgets on Azure Data Studio - July 7, 2022 How to obtain SQL Execution Plans using different methods - June 30, 2022