Attention: We are retiring the ASP.NET Community Blogs. Learn more >

SQL Load Test

Project Description

This tool takes a SQL Profiler trace file and generates a unit test that replays the same sequence of database calls found in the trace file. The unit test is designed to be used in a Visual Studio Load Test. The code generated is easily modifiable so that data variation can be introduced for the purpose of doing performance testing.
The tool generates code for both Visual Studio 2005 and Visual Studio 2008. The source code is a Visual Studio 2005 project.
This is a V1 release with some known limitations which are specified in project description.

Why Load Test SQL?


There is a lot of code out there that makes load testing the application very difficult. The most common type is a client application that contains direct database calls. The application however is not structured to allow the business logic to be exercised without the GUI. Another common scenario is a legacy application written in a language such as Visual Basic 6. In cases like these, it can be difficult to load test the application without a GUI test tool, which introduces other disadvantages. GUI test tools are notoriously difficult to use, generally lead to brittle tests and also have limitations in scaling the load injectors sufficiently to exercise the servers.
This tool offers one possible alternative. You start with using the client application to generate a trace of the SQL statements which typically represent a usage scenario. You can then turn the trace into equivalent ADO.NET code program that can be used in a load test.

Tool Description


The tool generates a Visual Studio 2005/2008 Unit Test from a SQL Server Profiler trace. It extracts all the SQL statements and stored procedure calls from the trace and turns them into a single Visual Studio Unit Test, which can then be configured as a Visual Studio Load Test. The tool does not interact with the database itself when it analyzes the trace and generates the test code. It can therefore be used in “offline” scenarios.
The generated code needs to be customized to include a connection string to the database under test. That code also includes hooks to allow the user to customize the parameters that are passed to the SQL statements and stored procedures, so that variability can be introduced into the data to prevent caching from producing artificially high performance figures.
The tool is intended to be used in conjunction with a client program that runs against a database. A trace of the client’s SQL Server activity is captured using the SQL Server Profiler. This represents a test scenario, which is then processed by the tool to produce a Unit Test that replays the scenario.

Installing


Make sure SQL Server 2005 SDK is installed.
Copy the packaged assemblies to a convenient location.
To use the generated code in a load test you need to have one of the following installed:
  • Visual Studio 2005 Team Edition for Testers
  • Visual Studio 2005 Team Suite
  • Visual Studio Team System 2008 Test Edition
  • Visual Studio Team System 2008 Team Suite

Supported Functionality

  1. SQL Server 2005 only (others not tested).
  2. ADO.NET 2.0 (others not tested).
  3. Most data types.
  4. Named input, output and input-output parameters.
  5. Positional input parameters to stored procedures.
  6. Operation timers generated for each database call.

Limitations

  1. Only supports code that operates against a single database with a single connection.
  2. Prepared statements are not supported.
  3. The tool cannot (yet) configure and capture the trace for you, it requires you to use the SQL Profiler yourself to get a trace. With SQL 2005 it is possible to automate this.
  4. Not (yet) integrated into Visual Studio or the DB Pro edition.
  5. The tool has only been tested in an English environment, it is unlikely to work correctly in other environments, particularly with dates. In a future version this could be resolved by requiring the ExistingConnection event class, which lists language and date format.
  6. The tool does not support the XML data types.
  7. InputOutput parameters that are initialised with a NULL cannot be distinguished from Output-only parameters, so they are treated as Output only.
  8. When the trace passes a NULL to a binary parameter (e.g. image, binary, varbinary) the generated code will generate a runtime error. The generated code needs to be modified to pass SqlBinary.Null. The custom code generation extensibility point can be used to work around this.
  9. Positional parameters passed to SQL statements (as opposed to stored procedure calls) are not supported.

 

Check project page at CodePlex Here

2 Comments

Comments have been disabled for this content.