SQLStress - A SQL Server Stress Test Tool

Overview

Here you can find a description of the functions that are presented on different tabs in the main window. It is possible to change tabs while workloads are running in the background.

Connect

Use this tab to add a database to the list of databases considered for a workload. <Failover Partner> is meant to be the other node in a mirroring configuration. If <Encrypt> is checked, SSL is used for the communication between SQLStress and SQL Server. Check <Trust Server> if SQLStress is running in an environment without trust for the certificate authority of the certificate that SQL Server is using. The checkmark for <Kerberos> determines if "Windows Authentication" or "SQL Authentication" is used to connect to the SQL Server. After the click on <Add Database> the database will appear in the list on the "Databases" tab page.

Databases

On this tab, all databases are listed with a specified weight and an "enabled" checkmark. When a workload is created, the weight represents the relative number of users for this database of the total number of users specified in the workload. Databases that are not enabled are ignored.

Certain tasks can be performed on the selected databases using the context menu.

• Instance

  This menu item contains methods that are specific to the selected instance

  • Sign In - Sign Out

    This performs a "sign in" and a "sign out" on all selected databases to verify communication capability.

  • Checkpoint

    Sends a CHECKPOINT command to the selected SQL Server instances.

  • Free System Cache

    DBCC FREESYSTEMCACHE

  • Drop Clean Buffers

    DBCC DROPCLEANBUFFERS

  • Free Procedure Cache

    DBCC FREEPROCCACHE

• Database

  This menu item contains methods that are specific to the selected database.

  • Prepare

    Will create the SQLStress schema in the selected databases, all tables, indices and stored procedures necessary to run a SQLStress workload.

  • Cleanup

    Will cleanup all modifications performed to the selected databases; this also includes the potentially customized stored procedure SQLStress.SP!

  • Truncate Logfile

    Will backup the log file with the TRUNCATE_ONLY options (Don't do this in a production environment with a working backup schedule). To get back into your backup schedule you will have to do a full backup.

• Table

  This menu item contains methods that work on the SQLStress.Data table.

  • Truncate

    This will truncate the SQLStress table in order to make room for more insert operations.

  • Delete Rows

    In case the database is replicated, tables can not be truncated, therefor rows have to be deleted.

  • Refresh Cardinality

    This will do a SELECT COUNT(*) on SQLStress.Data and refresh the "Cardinality" column of the selected database.

Workload

This tab allows workloads to be created according to a workload profile that can be specified in percentages of insert, update, select and delete operations. Also a stored procedure (SQLStress.SP), which can be customized, can be executed. The "Roundtrip" operation is used for testing the network efficiency using SQL Server and can be very useful to find the proper affinity settings. The "Insert" and "Roundtrip" operation will use the document size parameter.

"Users/Workload specifies the numbers of virtual users. Each user is represented by a connection to the database server. No connection pooling is used. Once a connection fails it will be reestablished and processing continues.

If "Ave Bytes/Document" is greater than zero, an image field of the requested size will be stored on insert operations in addition to the record. The size is specified as an average and will be determined randomly.

Transactions/User specifies the number of transactions each user will perform. If this number is specified as zero, the workload has to be stopped with the <Pause> or the <Done> button.

If "Txn/Second" is specified as zero, the transactions will be fired without a wait time in between. If specified greater than zero, a transaction scheduler will be enabled that will schedule on average the requested number of transactions per second. Transactions will not be scheduled in an exact manner but with a random skew.

The groupbox labeled "Workloads" has a drop down list with all existing workloads. The buttons underneath operate on the current workload.

Underneath there is a tabbed page control that is used to specify the characteristics of the workload that will be created with the "Create" button. On the "SQL Workload" tab, each statement (delete, insert, update, select, round trip and execute SP) will have a certain weight assigned that will be taken into consideration when the next transaction is determined randomly. In the first column, the load is displayed as a percentage that corresponds to the specified weight. If a checkmark is set under the MSDTC label, every time this transaction type is selected, the virtual user will rendezvous with another virtual user that is trying to execute an operation with the MSDTC checkmark set, and engage in a distributed transaction with that user. Of course the first user will not execute his operations at the scheduled time, instead he will wait for the second user so that the two operations will be executed with no delay between them.

The "SMTP Workload" tab allows to stress test an SMTP server. "Send Mail" specifies the number of emails to be send per user. "Recipient Names" will define the username and fqdn of the recipient, the recipient number is passed and can be used in the formatting string. Finally the number of different recipients is specified in the next field and the last field defines the SMTP server that will be used to send the emails. 

Journal

On this tab, the virtual users will report errors and other events.

Performance

Use the context menu to look at SQL performance related tables. If SQLStress has been installed by "Click Once", the performance object "SQLStress" has not been created. To be able to use the Windows Performance Monitor choose <Performance Object → Create> on the context menu.

Affinity

Used to affinitize SQLStress to certain CPUs in a NUMA system. Can also be used as a calculator to specify affinities within a SQL Server installation.

The checkmark next to NUMA label will toggle all CPUs. The checkmark next to a specific NUMA node will toggle all CPUs on that NUMA node. CPUs can also be enabled or disabled on an individual basis. After selecting the desired CPU configuration, the processor affinity mask can be selected in the lower right corner. SQLStress can also be affinitized to the selected CPUs by clicking on the "Apply" button.