A simple VB.NET stress utility for SQL Server 2000

• Download source and executable - 22.9 Kb

Introduction

As any developer knows, the tuning phase of any data access application development implies the analysis of the database response time, possibly followed by a refinement of some database design aspects (i.e. reviewing some poorly coded stored procedures or the adopted indexing strategies).

This kind of optimization can't be unaware of high load scenarios, so it's a good habit to test the application under "stressed" conditions. For example, an ASP.NET Web Application will be typically tested with stress tools like Microsoft Application Center Test (a.k.a. ACT), that allow you to simulate multiple simultaneous users requesting the web site's ASPX pages.

Under some circumstances, however, these stress test environments are not suitable to point out performance bottlenecks on the database side. With ACT, for example, you can take some measurements about the web server response times, on a page by page basis; but what you are measuring is the web server's response time, that consists of the time spent to process/render the web page plus the time spent in accessing data from the database server. And sometimes it is not simple to understand if the poor performance cause resides in the ASPX page code or in the involved database activity.

So, a new need arises: the need to measure the pure database response time. This is true especially during the development and testing of Web Applications (where you want to measure the database response time, without the noise introduced by the web server processing), but also in many other multi-tier situations, where you want to evaluate the database performance excluding the influences of upper software layers.

Of course, this kind of measurements on the database side can be collected through tools like Microsoft SQL Server Profiler while the web application pages are being hit by the ACT-simulated workload. But, if you collect measures in this way, you need to keep in mind that the database workload comes always from the web server, and this can be sometimes not realistic in case of differences in the web infrastructure between the test environment and the real environment. To make an example: suppose that your production architecture includes a web server farm that you don't have in the test environment; after observing the test environment performance, you can perhaps imagine how your web application will scale on production by the ASP.NET point of view, but it's hard to tell how the database will behave in production, where it will be exposed to the workload generated by multiple web servers.

The tool presented in this article, named DBstressUtil, is a simple database stress utility that tries to address these problems, focusing on the database performance analysis and keeping away any other application layer (data access, business logic, user interface components) that might add noise to the database response time measurement.

Background

When I faced the problem of simulating a workload to stress a SQL Server database, someone suggested me to take a look of the Microsoft SQL Server 2000 Resource Kit. In fact, the Resource Kit contains a VB6 utility named Database Hammer, a tool to test the performance of a SQL Server 2000 instance: it creates its own work tables and then submits tons of INSERT/UPDATE statements on that tables, simulating concurrent users connected to the database. So you can observe if a particular installation of SQL Server performs well.

But my goal was different: I needed to test not a generic instance of SQL Server, but a specific database, with its indexes, stored procedures, triggers and so on. So, Database Hammer was not useful for me, but I liked the basic idea of simulating concurrent database users through a Windows Application. From there to conceive DBstressUtil, the step was short.

How the utility works

DBstressUtil is coded as a Windows Forms VB.NET application and it is designed to work on a SQL Server 2000 database (of course, using the SQL Server .NET Managed Provider classes of the System.Data.SqlClient namespace).

DBstressUtil simulates multiple database users submitting T-SQL scripts (executing stored procedures or simply sending DML commands). These scripts are taken from an input text file (that we'll call "test script" from now on), that has to be prepared in advance, with a simple text editor.

When you run DBstressUtil, on the main form, you can configure some execution options as:

• the number of simultaneous users (a.k.a. "client instances") to be simulated (see txtInstances),
• the time between the start of the activity of a user and of the subsequent user (see txtClientShift),
• the interval between each command executed by each single simulated user (see txtInterval).

When you start the stress test, a number of concurrent threads are created and each of these begins the execution of the T-SQL commands taken from the "test script". Each command execution time (in milliseconds) is measured and recorded on a CSV output file, for an easy post-processing of collected data. During the test, you can monitor the number of currently running simulated client instances (see txtRunningInstances), and the number of the total executed commands (see txtDoneCommands).

A test run has to be considered over when the number of the total executed commands equals the number of client instances multiplied by the number of commands included in the "test script". At this time, you can inspect the CSV log file produced by DBstressUtil; this output file will contain records made up of these fields:

• the starting date and time of the execution of a particular command by a particular client instance;
• the ID (ranging from 1 to txtInstances value) of the simulated client instance that executed the command;
• the ID (ranging from 1 to the total number of commands included in the "test script") of the executed command;
• the execution time (in milliseconds) related to that particular command submitted by that particular client instance.

And now, something about the code...

Each simulated client instance is an instance of the ClientProc class. The StartClientProcess method of this class is responsible for all the simulation processes of a single client, and it is invoked for each instance of ClientProc on a new thread by the main program, as shown here:

  Dim i As Integer        ' Counter of Client process instances

  Dim x() As ClientProc   ' Array of Client process instances

  ...
  x(i) = New ClientProc
  x(i).ConnectString = ...
  x(i).WaitInterval = Convert.ToInt32(txtInterval.Text)
  x(i).CommandFile = txtCommandFile.Text
  x(i).LogFile = txtLogFile.Text
  ...
  Dim t As New Thread(AddressOf x(i).StartClientProcess)
  t.Start()

Steps of the StartClientProcess method are:

• when starting, to increment the "currently running instances" counter;
• to load the "test script" in a string array (see LoadCommandFile() private method);
• to loop through the commands of the "test script", and, for each of them:
• to execute it via a SqlCommand.ExecuteNonQuery() method;
• to log its execution time on the CSV output file;
• to increment the "total executed commands" counter;
• to wait the specified pause interval before the next command execution;
• when ending, to decrement the "currently running instances" counter.

All the code accessing resources that are shared among multiple client instances is protected by a Mutex (for example: the output file access, or the update of UI elements of the MainForm).

Finally, keep in mind...

• that the "test script" must contain a valid T-SQL script on each line; so, a single line in the "test script" can be in the form of:
  • a single EXEC stored_procedure @param=value command;
  • a single SELECT / INSERT / UPDATE / DELETE command;
  • a more complicated T-SQL script, composed by multiple DML commands.
• that in the "test script" you can include comment lines, that make more readable your scripts and are ignored by DBstressUtil (also in the implicit numbering of the submitted commands); a comment line is a line beginning with "--" (two hypens); all other lines are considered as valid commands (or complete T-SQL scripts) to be submitted towards the SQL Server database;
• that DBstressUtil uses the same connection string for each simulated client instance: so, evaluating your measurements, you need to keep into account the effects of database connection pooling (if that feature is enabled);
• that DBstressUtil currently doesn't manage database errors: so, evaluating your measurements, remember that an extremely fast command might be a command that actually produces a SQL error...
• that DBstressUtil currently doesn't support the "smooth" interruption of a test run: so, be careful when you configure your "test scripts" and your execution parameters, to avoid the need to kill the application because of a too long running test.
• that the commands.txt file included in the Zip archive contains very simple commands that operate on the sample Northwind database: of course, due to those trivial commands and the database size, using the commands.txt file on the Northwind database is NOT the circumstance where you will find DBstressUtil useful! Please, try DBstressUtil on bigger databases, especially if they are full of business logic coded in complex T-SQL code... and enjoy!