Saturday, July 25, 2009

How to: Stress Test / Load Test databases

Well, we are often in a situation when we are near a release or adding more functionality or exposing more users to our application. One of the things we need to take care of is to make sure our infrastructure and our databases will live up to the extra load/stress. In this article we will see how we can stress/load test our database. To make it more organized it is desirable to create tests that relate to one or more usage scenarios. That way, we can pin-point what usage scenario has to be fixed for performance. It also in a way tells us which usage scenarios will work under the increased stress/load.
The simple idea is like this:

  • Run your application and record the database trace calls via SQL Profiler for a particular usage scenario.
  • We can break up the whole application into small usage scenarios so we can test and monitor them separately.
  • Perform operations on our application for one single user.
  • Take the SQLTrace/Profile output which tells us all the db operations that happen on a specific case for a single user.
  • Write a wrapper SQL script using all the above sql calls and run that in a loop for 100, 200, 300, 400 users with other varying parameters.
Well, what do you. I found a tool that does exactly that. Introducing SQL Load Test Tool.
SQL Load Test 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 user can start with using the application to generate a trace of the SQL statements which typically represent a usage scenario. They can then turn the trace into equivalent ADO.NET code program that can be used in a load test.
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 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.
Installation and setup
1. Make sure SQL Server 2005 SDK is installed.
2. Make sure you have SQL Server 2005 Management Tools installed.
3. Install SQL Load Test tool.
4. You will need 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 (from SQL Load Test site)
  • SQL Server 2005 only (others not tested).
  • ADO.NET 2.0 (others not tested).
  • Most data types.
  • Named input, output and input-output parameters.
  • Positional input parameters to stored procedures.
  • Operation timers generated for each database call.
Other helpful tools and utilities regarding stress and load testing databases
Read more about SQL Load Test tool at its Codeplex site. The site also includes a SQL Server 2008 Profiler template file for download to used with SQL Server 2008 databases. The tool is licensed under Ms-PL.

Update (06/18/2013): See the Spanish translation of this page by Maria Ramos.

No comments:

Post a Comment