SqlLS 2.2: SQL Load Simulator

Mitch van Huuksloot

How many times have you been asked to predict performance of a SQL Server-based application? Experience helps, to be sure, but so does the latest version of Microsoft's SQL Load Simulator. In this exclusive article, Mitch van Huuksloot explains how to use the program he wrote.

Have you ever been asked whether that application you and your team just spent a year developing will actually scale to the required number of users? Did you have a good answer?

Let's face it -- during application development and testing, assembling enough machines to test SQL Server with a large number of clients is often impractical. The alternative is to use a few machines to simulate a large number of users. Although running multiple instances of an application on a single machine often simply isn't possible due to resource constraints, simulating only the client/server interaction of multiple sessions usually is a possibility. That's where SQL Load Simulator (SqlLS) comes in.

SqlLS was born out of necessity. A client asked me whether his company's server would scale to hundreds of users. The number of users that SQL Server can support varies widely, based not only on the hardware, but also on such factors as the design of tables, queries, indexes, and the application itself. The client's question was impossible to answer without testing the server under user load, and his company only had 10 development machines and a few servers available for the test. The simulation tools available lacked the flexibility required, so I wrote SqlLS. With SqlLS, I simulated the required number of users, but I had to use several servers on the same 100BaseT segment.

SqlLS is a multi-threaded MDI application written in Visual C++ using the Microsoft Foundation Classes (MFC). The application uses the ODBC API directly to reduce thread overhead. (Because SqlLS is based on ODBC, you could theoretically use it to test other database engines, but I haven't used it for that purpose, nor was that a design goal.) SqlLS Version 2.2 was designed to support Microsoft SQL Server Version 6.5 and Sphinx Beta 2 (SQL7).

SqlLS processes text files containing SQL script using multiple threads. Each thread establishes a database connection and runs the script independently of the other threads. Each row of the document's MDI child window shows the execution status and statistics for each thread. Figure 1 shows 10 threads executing the test2.sql document (you can read detailed information about the column meanings and the status field values in the SqlLS.hlp file that ships with the product).

When opening a script document, SqlLS prompts for ODBC connection information and simulation parameters -- whether to use a trusted connection or standard security, and how many threads to execute. Another parameter, "Delay Between Executions," allows you to build in simulated user "think" times into the script execution.

SqlLS allows you to view selected performance statistics from the ODBC drivers' SQLPERF structure (see Figure 2). The SQLPERF structure was introduced in the ODBC Version 3 specification to expose internal driver statistics. Unfortunately, there's only a single SQLPERF structure for the process, which means that the statistics in the structure are a combined total of all ODBC activity for the process.

Figure 1. Executing the test2.sql document. (See Load Simulator's SqlLS.hlp file for the information about the column meanings and Status Field values.)

Script files can contain one or more SQL batches separated by the word "GO". For example, here's a simple, two-batch sample script for the pubs database:

select p.pub_name, t.title, t.price, t.pubdate, 

   t.ytd_sales

from titles t, publishers p

where t.pub_id = p.pub_id

order by p.pub_name

GO

select o.stor_name, t.title, s.ord_date, s.qty 

from sales s, titles t, stores o

where s.title_id = t.title_id

and s.stor_id = o.stor_id

order by o.stor_name

GO

The challenge is to develop scripts that simulate realistic user interaction. Since each thread executes the same script, blocking, deadlocking, hot pages, and unrealistically high cache hit ratios are some common problems. There are a variety of tricks to get around these problems, including the use of the Transact SQL RAND function, but the result is rarely optimal. To help with this problem, SqlLS Version 2 adds ActiveX Scripting to the SQL script interpretation.

SqlLS uses ActiveX Scripting in the same way that Active Server Pages uses it. Instead of generating dynamic HTML pages, SqlLS uses the embedded Visual Basic or Java script code to generate dynamic SQL, allowing each thread to execute a different SQL command. The ActiveX script is re-interpreted in each SQL script iteration; therefore, the SQL commands issued can change for each iteration of each thread. ActiveX Scripting and the objects that SqlLS expose give the script developer almost unlimited flexibility, such as changing the "where" clause of SQL statements for each thread, delaying script execution within the script to better simulate user interaction, disconnecting and re-connecting each iteration, and changing a SQL statement based on the result of another statement. ActiveX Scripting also has the ability to call methods and manipulate properties of any COM (including MTS) object. (By the way, a product similar to SqlLS that runs ActiveX scripts in multiple threads and collects statistics, is in testing. This product is targeted for testing COM and MTS components.) By developing COM objects, you can extend the capabilities of SqlLS.

Figure 2. SQL Load Simulator's ODBC performance staticstics window.

ActiveX script is marked in the SQL script using a start tag of <% and an end tag of %>. The default language is VBScript, but this can be changed using <%SCRIPT LANGUAGE="<EngineProgID>"%>. The language selection statement must be on its own line in the script file. Only the first language selection statement is processed, since SqlLS only supports one script language per script. I've tested SqlLS with the VBScript and JScript engines that ship with Internet Explorer 3.02 and 4.0. Here's an example:

<%SCRIPT LANGUAGE="VBScript"%>

<%

Sub ChgConnection

  DBCon.DSN = "Pubs"

  DBCon.Userid = "test" & Simulation.Thread

  DBCon.Open

End Sub

If Simulation.Iteration < 11 then

  ChgConnection

Elseif Simulation.Iteration = 50 then

       Simulation.ExitThread

End if

If  Simulation.Thread < 6 then

  Script.WriteLine "exec reptq1"

  Timer.Wait 500

Else

  Script.WriteLine "exec reptq2"

  Timer.Wait 1000

End if


%>

The initial language selection in the sample code is redundant, but it demonstrates the syntax. As the script runs, each thread will disconnect (implied by the Open) and connect to the pubs datasource as the test<n> userid, for the first 10 iterations of the script. At the 50th iteration, all the threads will exit. The SQL that actually gets executed and the delay are different, depending on the thread number. Although somewhat contrived, the sample demonstrates the flexibility added by ActiveX Scripting.

The sample also shows several objects that SqlLS adds to the ActiveX Scripting name space. The objects include DBCon, Simulation, Script, SQL, and Timer. These objects and the methods and properties they expose are documented in the Help file that ships with the product.

When running repeated simulations involving many client machines, it often takes longer to start the simulation than to actually run it. Collecting the statistics from the client machines when the simulation is complete can also be a challenge. The addition of dual OLE automation interfaces to SqlLS Version 2 (a "character-building" exercise) solves these problems. The automation interfaces allow an automation controller to control the SqlLS application on the same machine or on a different machine using DCOM. Both client and server machines need to have SqlLS Registry entries before automation will work. SqlLS will write the required entries into the Registry by running SqlLS with the /RegServer parameter on the command line.

The following Visual Basic code demonstrates how to control SqlLS using automation:

Dim app As SqlLS.Application

Dim WithEvents doc As SqlLS.Document

Dim thrd As SqlLS.Thread

Dim TotalIterations as Integer

Private Sub Form_Load

Set app = CreateObject("SqlLS.Application")

Set doc = app.Documents.Add("test.sql", "Pubs", _

   "sa", "", False, 10, 0)

doc.Threads.Start

<wait...>

For each thrd in doc.Threads

  TotIterations = TotalIterations + thrd.Iteration

Next thrd

Set doc = Nothing

Set app = Nothing

End Sub

Private Sub doc_ScriptEvent(ByVal nParam As Long)

...

End Sub

Private Sub doc_ThreadError(ByVal nThread As Long)

...

End Sub

The preceding sample code instantiates SqlLS, opens the test.sql document, starts its 10 threads, and then iterates through the threads, calculating the total number of iterations. The sample also shows the document object's support of connection points, enabling the automation application to be notified when certain events occur. All of the statistics that SqlLS collects are available to the automation controller through the "Threads" and "PerfDoc" objects. The automation object model is described in the SqlLS's Help file, but I've also included a Visio diagram of the interfaces in the accompanying Download file.

When you use SqlLS, note the following:

• Opening too many scripts and/or threads on a single machine will cause a bottleneck on the client. The best indicator of client bottlenecking is 100 percent CPU usage, but the NIC (network interface card) can also be a bottleneck.
  
  
• SqlLS is meant to stress SQL Server, not test client performance. That said, SqlLS can give you a rough idea of how different simulations compare to one another. Be sure to take into account such factors as differences in network traffic between simulations. The timing statistics are only accurate to 1/18th of a second -- therefore, use the average time of a lot of iterations.
  
  
• SqlLS simulates client result set processing by iterating through result set column names and processing all rows of all result sets. SqlLS doesn't use variable binding in result set processing to reduce the per-thread memory requirement. This means that the application could be more efficient in result set processing, but it's still reasonably fast since it uses the ODBC API directly. 

In other words, SqlLS is a tool that lets you test how your SQL Server will react to user load, but it's up to you to ensure that the scripts you use to load the server are representative of user activity. By using the ActiveX scripting features in the SQL scripts, you should be able to accurately model user sessions. If you're performing a large simulation involving many machines on a ongoing basis, spending some time writing an automation controller for SqlLS can save you a lot of time.

SqlLS Version 1 is available on the BackOffice Resource Kit Volume 2. Version 2 is available from http://backoffice.microsoft.com/downtrial/moreinfo/sqldrk.asp.

Download sample code for this article here.

Mitch van Huuksloot is a senior consultant with Microsoft Consulting Services. He specializes in SQL Server, development, and infrastructure engagements. He holds both an MCP SE and SD designation. mvanhuuk@microsoft.com.