Using Visual Studio 2005 to Perform Load Testing on a SQL Server 2005 Reporting Services Report Server

Artículo
10/08/2009

Runying Mao
Heidi Steen
Microsoft Corporation

Lukasz Pawlowski
Donovan Smith
Tudor Trufinescu
Dave Wickert
Microsoft Corporation

September 2006

Applies to:
Microsoft SQL Server 2005 Reporting Services
Microsoft Visual Studio 2005 Team System

Summary: Microsoft Visual Studio 2005 Team System includes a load-test tool that you can use for performance and stress testing of a Microsoft SQL Server 2005 Reporting Services deployment. The load-test tool runs the tests that you create, and optionally logs the data into a SQL Server 2005 database. You can monitor tests as they progress, review performance data after the tests are completed, and precisely determine what the threshold is for a specific report-server deployment. This article contains step-by-step instructions for creating a Web page load test, and sample code and instructions for creating a unit test. Also, instructions are provided for setting up the load test that you use to specify load patterns. The article assumes that you have the AdventureWorks sample database and reports, so that you can try these steps on your computer. (25 printed pages)

Click here to download the Word document version of the article, UsingVSforLoadTestingonSQLServer.doc.

About Project REAL
Introduction
Setting Up
Creating a Web Test
Creating a Unit Test
Extending Web Tests and Unit Tests
Creating a Load Test
Creating a Test Results Database
Checking Results
Conclusion

About Project REAL

Project REAL is an effort to discover best practices for creating business intelligence (BI) applications that are based on SQL Server 2005, by creating reference implementations that are based on actual customer scenarios. This means that customer data is brought in-house and is used to work through the same issues that customers face during deployment.

These issues include the following:

Design of schemas for relational databases and for Analysis Services
Implementation of a data extraction, transformation, and loading (ETL) process
Design and deployment of client front-end systems, for both reporting and interactive analysis
Sizing of systems for production
Management and maintenance of those systems on an ongoing basis, including incremental updates to the data

By working with real deployment scenarios, we gain a better understanding of how to work with the tools. Our goal is to address the full gamut of concerns that a large company would face during its own real-world deployment.

This white paper offers a detailed technical discussion on how to perform performance analysis for SQL Server 2005 Reporting Services using Visual Studio 2005 Team Edition for Software Testers. The infrastructure discussed in this white paper refers to how the Reporting Services performance and throughput tests were implemented in Project REAL.

For an overview of Project REAL, see the Project REAL: Technical Overview white paper. A number of papers, tools, and samples will be produced over the lifetime of Project REAL. To find the latest information, visit the Project REAL Web site.

Introduction

This article describes how to use Visual Studio 2005 Team Edition for Software Testers to run performance characterization tests for SQL Server 2005 Reporting Services. You can use this article as a guideline for capacity planning or to assess performance before rolling out reports on a production server.

This article contains step-by-step instructions for setting up a project, creating Web page and unit tests, creating and configuring a load test, running the test, and evaluating the results. After you create the tests, you can run them on different server configurations to quantify the improvement in performance when you change hardware components or modify a report definition or query, or specify different rendering formats.

Choosing Reports

This article uses the AdventureWorks sample reports and database to illustrate key concepts. You can use the sample reports if you want to use the sample code and steps provided, or you can work with your own reports and modify the code and steps accordingly. When you perform load tests, the reports must be able to run with no user interaction required. If the report prompts for data-source credentials or parameter values, you must temporarily modify the report to use stored or integrated credentials and default parameters for the purpose of running the tests.

Requirements

This article assumes that you have the following software, samples, and permissions installed on a test server:

Visual Studio 2005 Team Edition for Software Testers.
SQL Server 2005 Reporting Services.
SQL Server Management Studio.
AdventureWorks sample database.
AdventureWorks sample reports.
Permission to access the Database Engine, create databases, and retrieve data from the AdventureWorks database. You must also have role assignments that grant access to the reports.

Visual Studio 2005 Team Edition for Software Testers

You can install a subset of the Visual Studio 2005 components. The following screen shot shows the Team Developer and Tester tools that are used in this exercise. The tools that you will use include Performance Tools, Code Analysis Tools, and Testing Tools.

You must also have a language project installed. The sample code provided in this article is in Microsoft Visual C# 2005, but you can use another language if you want to use your own code.

Figure 1. Visual Studio 2005 setup (Click on the image for a larger picture)

AdventureWorks Sample Database and Reports

AdventureWorks is a sample relational database that is included with SQL Server 2005. If you want to use the AdventureWorks sample reports, first make sure that the Reporting Services samples are installed. By default, they are located at <drive>:\Program Files\Microsoft SQL Server\90\Samples. If not they are not installed, you must install them. For instructions on how to install and uninstall the samples, see Installing Samples in SQL Server 2005 Books Online. You can also download the samples from the Microsoft Download Center.

In this exercise, we will use the following single-page and multipage reports:

Company Sales
Product Catalog
Employee Sales Summary

Employee Sales Summary prompts for a parameter value. When you create a unit test, you will specify a parameter value to pass to the report at run time. This allows the report to run unattended.

All of these reports retrieve data from the AdventureWorks sample database, using Microsoft Windows authentication and your credentials to connect to SQL Server 2005.

Before you start, verify that you can access the AdventureWorks sample database and run the reports by starting Report Manager and opening each report.

Setting Up

Firstly, install Visual Studio 2005 Team Edition for Software Testers. Secondly, create a test project, as follows:

Click Start, select Programs, and then select Microsoft Visual Studio 2005.
On the File menu, click New, and then click Project to open the New Project dialog box.
In the New Project dialog box, expand the Visual C# node, and select Test.
Select Test Project, accept the default name TestProject1, and then click OK.

Figure 2. Select the Test Project template in Visual C#. (Click on the image for a larger picture)

By default, the newly created test project contains an empty unit test. You can safely ignore it for now; you will add sample code to it later.

Creating a Web Test

A Web test is used to test the functionality of Web applications and to test Web applications under load. Although you can build Web tests manually, it is easier to create them by recording your activities in a browser session.

In Solution Explorer, right-click TestProject1, select Add, and then select Web Test.

Figure 3. Add a Web test to TestProject1. (Click on the image for a larger picture)

A browser window pops up automatically. It will look similar to Figure 4. You will use this browser window to add URLs for each report that you want to include in the test.

Figure 4. Browser window (Click on the image for a larger picture)
In the Address bar, replace about:blank with the URL address of a report that has been deployed to a report server. Open the report through a direct connection to the report server, and then copy the URL.
Add the &rc:Toolbar=False parameter to the report URL to hide the toolbar at run time. Suppressing the toolbar is necessary to avoid an rsExecutionError error. This error will occur if the session identifier that was current when the test was created differs from the session identifier that is created when the report runs. The toolbar uses session information to track page navigation. By hiding the toolbar, you eliminate the need to retrieve session information when the report is run.

A report URL to Company Sales that includes the &rc:Toolbar=False parameter looks like the following:

https://localhost/ReportServer/Pages/ReportViewer.aspx?%2fAdventureWorks+Sample+Reports%2fCompany+Sales&rs:Command=Render&rc:Toolbar=False
Press the Enter key. Keyboard actions and user input are recorded and stored in the test for subsequent playback. When you press Enter, the report is processed and rendered in the browser window. The actions are recorded for subsequent playback when you run the test.

Figure 5. Company Sales sample report (Click on the image for a larger picture)

You can add multiple reports if you want to test several at the same time. To do this, replace the URL with a different report URL and press Enter to record the action.

In the Address bar, enter the report URL for a Product Catalog report, and press the Enter key. You can repeat these steps to add other reports.
After you have added all the reports you want to test, click Stop to stop recording and return to the project.

You now have a Web test that contains a list of recorded actions.

Figure 6. Recorded actions in the Web test (Click on the image for a larger picture)

Next, you will run the test. Visual Studio 2005 provides a Run button in the toolbar. On the tab named WebTest1.LoadTest, it is the first button with a green arrow on it.

Figure 7. Run the test. (Click on the image for a larger picture)
Switch to the WebTest1.webtest tab, click the Run button, and then select Run Test to replay the reports. The Test Results window will show whether or not the test was successful.

If you do not see the Test Results window, on the Test menu, select Windows, and then select Test Results.

Figure 8. View test results. (Click on the image for a larger picture)

If an error occurs, you can click the Run button again and select Debug Test to diagnose the error.

Creating a Unit Test

Unit tests are programmatic tests that are written in Visual C# (or other programming languages) and used to exercise other source code by directly calling the methods of a class, passing appropriate parameters, and then (if you include Assert statements) testing the values that are produced against expected values.

In this section, you will learn how to create a unit test that calls a single report on a local report server, and run the report with a specific parameter value.

In Solution Explorer, double-click Unit Test1.cs. Delete the existing code, so that the file is empty. The following lines of code should be deleted.

using System;
using System.Text;
using System.Collections.Generic;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace TestProject
{
/// <summary>
/// Summary description for UnitTest1
/// </summary>
[TestClass]
public class UnitTest1
{
public UnitTest1()
{
            //
            // TODO: Add constructor logic here
            //
}

#region Additional test attributes
//
// You can use the following additional attributes as you write your tests:
//
// Use ClassInitialize to run code before running the first test in the class
// [ClassInitialize()]
// public static void MyClassInitialize(TestContext testContext) { }
//
// Use ClassCleanup to run code after all tests in a class have run
// [ClassCleanup()]
// public static void MyClassCleanup() { }
//
// Use TestInitialize to run code before running each test 
// [TestInitialize()]
// public void MyTestInitialize() { }
//
// Use TestCleanup to run code after each test has run
// [TestCleanup()]
// public void MyTestCleanup() { }
//
#endregion

[TestMethod]
public void TestMethod1()
{
            //
            // TODO: Add test logic   here
            //
}
}
}

Copy the following sample code and paste it into the unit test. You can use the following code as a template and update it to assign different values to the parameter, render multiple reports, and so on.

This sample code displays the Employee Sales Summary report. The report has a parameter named EmpID, and the code sets the value to 275.

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.VisualStudio.TestTools.WebTesting;

namespace RSLoadTest
{
public class Report : WebTest
{
protected const string REPORTSERVER = "https://localhost/Reportserver";

// Report's URL address.
protected string m_urlName;
// EmpID is a Report parameter.
protected int m_EmpID;
// ThinkTime between each report rendering. 
protected int m_thinkTime;

public Report()
{
            this.PreAuthenticate = true;

            // Set Report Name.
            m_urlName = "%2fAdventureWorks+Sample+Reports%2fEmployee+Sales+Summary";
            // Set value to Parameter EmpID.
            m_EmpID = 275;
            // Set think time to 35 seconds. 
            m_thinkTime = 35;
}

public override IEnumerator<WebTestRequest> GetRequestEnumerator()
{
            WebTestRequest request = new WebTestRequest(REPORTSERVER);
            WebTestRequestHeaderCollection headers = request.Headers;
            // A lot properties are set by default
            // Example: 
            // For each request: Verb = "GET", HTTPVersion = "HTTP/1.1"
            // For each header: Accept, Accept-Language, User-Agent, Host are set by default
            // We only modify properties that have non-defaults. 

            // First remove all existing headers.
            headers.Clear();

            headers.Add("Cookie", "(automatic)");

            // Before each request, clear all existing parameters.
            request.QueryStringParameters.Clear();

            request.QueryStringParameters.Add("", m_urlName, false, false);

            request.QueryStringParameters.Add("rc:Toolbar", "False");


            request.QueryStringParameters.Add("EmpID", m_EmpID.ToString());

            request.QueryStringParameters.Add("rs:Command", "Render");

            // Choose to set Think Time to 30 seconds. 
            request.ThinkTime = m_thinkTime;

            yield return request;
}
}
}

Extending Web Tests and Unit Tests

In the previous section, you used the sample code in a unit test to learn how to set parameter values programmatically. Although the sample code illustrates basic principles that can help you get started, it has two limitations that you might want to address before you create similar tests for an actual test environment. Namely, the value that is passed to the EmpID parameter is hard-coded, and the output format is always the default HTML-rendering extension. To perform realistic load tests on your reports, you should try a variety of rendering extensions and run reports with different parameter values to get a complete picture of how the report performs when you vary the query parameters.

Testing for Rendering Formats

To specify different rendering formats, consider incorporating URL access into your tests. In Reporting Services, each report can be accessed through its URL. You can specify parameter values on a report URL to vary the rendering extension, test device configuration settings, or specify a data source. The URL must be a fully qualified path to the report. For more information about the URL parameters that are used for accessing SQL Server 2005 reports, see Using URL Access Parameters.

The following code snippets show you how to specify rendering extensions, so that you can run tests for different rendering formats.

To render a report in Microsoft Office Excel, use the following code snippet.
```
request.QueryStringParameters.Add("rs:Format", "EXCEL");
```
To render a report in PDF, use the following code snippet.
```
request.QueryStringParameters.Add("rs:Format", "PDF");
```

The code snippet should be placed in function.

public override IEnumerator<WebTestRequest> GetRequestEnumerator()

The following code provides a complete example.

public override IEnumerator<WebTestRequest> GetRequestEnumerator()
{
            WebTestRequest request = new WebTestRequest(REPORTSERVER);
            WebTestRequestHeaderCollection headers = request.Headers;
            // A lot properties are set by default
            // Example: 
            // For each request: Verb = "GET", HTTPVersion = "HTTP/1.1"
            // For each header: Accept, Accept-Language, User-Agent, Host are set by default
            // We only modify properties that have non-defaults. 

            // First remove all existing headers.
            headers.Clear();

            headers.Add("Cookie", "(automatic)");

            // Before each request, clear all existing parameters.
            request.QueryStringParameters.Clear();

            request.QueryStringParameters.Add("", m_urlName, false, false);

            request.QueryStringParameters.Add("rc:Toolbar", "False");

            request.QueryStringParameters.Add("EmpID", m_EmpID.ToString());
       
            request.QueryStringParameters.Add("rs:Format", "EXCEL");
 
            request.QueryStringParameters.Add("rs:Command", "Render");

            // Choose to set Think Time to 30 seconds. 
            request.ThinkTime = m_thinkTime;

            yield return request;
}

Testing with Dynamic Data

To work with dynamic data, use the data-binding features in Visual Studio 2005 to pass query-parameter values to a report. In the sample for the unit test, the Employee Sales Summary sample report has a parameter named EmpID that is set to an m_EmpID member variable. In most cases, parameter values are stored in a database table (in this example, values for parameter EmpID are from table [AdventureWorks].[HumanResources].[Employee], from the column EmployeeID). To pull a value for parameter EmpID dynamically from that database table and assign it to parameter EmpID, you can create a data source that connects to table [AdventureWorks].[HumanResources].[Employee] and then bind column EmployeeID to parameter EmpID. For detailed instructions on how to set up data binding in Visual Studio 2005 Team System, see How to: Add Data Binding to a Web Test.

Creating a Load Test

To run the unit test that you just created, you must define a load test that sets the load pattern that you want to use. In this exercise, the primary goal of the load test is to simulate multiple users accessing a server simultaneously. By adding a Web page test or a unit test to a load test, you can simulate multiple users opening connections and making multiple HTTP requests.

In a previous section, you created a unit test. In this section, you will create a load test, and then add the unit test that you created.

In Solution Explorer, right-click TestProject1, select Add, and then select Load Test. This starts the New Load Test Wizard.

Figure 9. Create a load test. (Click on the image for a larger picture)
Click Next on the Welcome page, and then click Next on the Scenario page.
On Load Pattern, in User Count, enter 25 to simulate 25 simultaneous connections to the report server, and then click Next.

Figure 10. Enter the number of simultaneous connections. (Click on the image for a larger picture)
On the Test Mix page, click Add to add the unit test (Report) to the load test.
Select Report (this is the name of the unit test), click the > arrow to add it to the Selected Tests area, and then click OK.

Figure 11. Add the unit test to the load test. (Click on the image for a larger picture)
Click Next to accept the default values on the Browser Mix and Network Mix pages.

Optionally, on the Counter Sets page, you can specify custom performance counters to use during the test run. This is useful if the reports or queries are run on a remote computer. If they are, you can add counters from the remote computer and monitor them locally when the test runs. If all processing is local, you do not have to specify a counter set. The load-test tool provides access to local performance counters by default.
Click Next to continue to the next page.

Figure 12. Add performance counters. (Click on the image for a larger picture)
On the Run Settings page, enter a value in the Run Duration box and, if applicable, a value in the Warm-up Duration box also.

Figure 13 shows a run duration of 30 minutes, but you can specify a shorter or longer duration.
Click Finish.

Figure 13. Add run settings. (Click on the image for a larger picture)

Creating a Test Results Database

Before running the load test, you must create a load-test result repository. It stores the result data that is collected during the load-test run.

Start SQL Server Management Studio and connect to the Database Engine.
On the File menu, select Open, and then select File.
Open the loadtestresultsrepository.sql file that is located at %ProgramFiles%\Microsoft Visual Studio 8\Common7\IDE.
Click Execute. This creates the database on the current Database Engine instance.
Close SQL Server Management Studio.
In Visual Studio 2005, on the Test menu, click Administer Test Controllers. The Administer Test Controller dialog box appears.
In the Load Test Results Store connection string, click the ... button to edit the Connection Properties dialog box.
Enter the server name that will host the database, and select the database. By default, the database name is LoadTest.
Click OK.

Figure 14. Select the Database Engine instance that has the LoadTest database. (Click on the image for a larger picture)

Now, you can run the load test exactly as you would if it were a Web test.
Click the Run button and select Run Test.

Figure 15. Run the load test. (Click on the image for a larger picture)

Checking Results

While the load test is running, you will see the results displayed in the Test Results window as In Progress. You can click In Progress to view information about the test as it runs.

Figure 16. Monitor the test. (Click on the image for a larger picture)

To monitor server performance, use the Requests/Sec and Avg. Response Time counters. If you added other counters when you create the load test, you can find them in the Counters pane. Double-click them to add them to the graph.

Conclusion

Now that you have a basic understanding of how to use the load-test tool with reports, you can build upon that knowledge by creating and running tests on configurations that are used in your organization.

In most cases, you will want to run a load test on a computer different from the one used to run the report server. Additionally, to mimic actual user activity, configure multiple user sessions across multiple computers. This configuration is called controller-agent configuration, and the computer that runs the load test is the controller. The computers that host the user sessions are created as agents. Controller-agent configurations are beyond the scope of this white paper, but if you want to learn more about controllers, agents, and Visual Studio 2005 Team Edition for Software Testers, see the following links on the MSDN Web site:

For more information about how to set up controller and agent functionality, see Setting Up the Controller and Agent Functionality.
To learn more about controllers and agents, see Working with Controllers, Agents, and Rigs.
For more information about Visual Studio 2005 Team Edition for Software Testers, see Team Edition for Testers.
For more information on report-server performance and how to report on load-test results, so that you can compare results over time and on different configurations, see Planning for Scalability and Performance with Reporting Services and Share the Load: Report Visual Studio Team System Load Test Results via a Configurable Web Site.
For more information, see SQL Server 2005 Reporting Services.

Compartir a través de