SQL Query Stress Tool

By: John Sterrett   |   Updated: 2012-07-18   |   Comments (16)   |   Related: 1 | 2 | 3 | > Testing

Problem

I have a stored procedure and I need to add additional stress and test the stored procedure using a random set of parameters before it can be pushed to production. I don't have a budget for stress testing tools. Can you show me how to accomplish these goals without buying a third party tool?

Solution

Yes, the SQLQueryStress tool provided by Adam Machanic can be used to apply additional stress when testing your stored procedures. This tool can also be used to apply a dataset as random parameter values when testing your stored procedures. You can also read more about SQLStressTest on the tools documentation page to find more details about how you can use the tool.

For the purpose of this tip we are going to use the uspGetEmployeeManagers stored procedure in the AdventureWorks2008R2 database. You can exchange this with your stored procedure to walk through this tip in your own environment.

Step 1

Our first step is to test the following stored procedure with a test parameter. This is done in Management Studio using the query shown below.

exec [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 8
GO

Now that we know we have a working stored procedure and a valid parameter that returns data we can get started with the SQLStressTool. Once you downloaded and installed SQLQueryStress, fire the tool up and  paste in the code that you used in Management Studio. Next, we need to click on the database button to configure our database connection.

Step 2 - Configure Database Connectivity

Now that we clicked on the database button we will want to connect to our AdventureWorks database. In this example I am using a instance named "r2" on my localhost. We will connect with windows authentication and our default database will be AdventureWorks2008R2. Once this is done we will click on Test Connection and click on the "OK" box in the popup window. We'll see the Connection Succeeded message to verify that our connection settings are connect.

Step 3 - Clear Proc Cache

Before we execute our stored procedure using SQLQueryStress we are going to clear out the procedure cache so we can track the total executions of our stored procedure. This shouldn't be done on a production system as this can cause significant performance problems. You would have to recompile all user objects to get them back into the procedure cache. We are doing this in this walkthrough tip to show you how we can count the total executions of the stored procedure.

NOTE: In SQL Server 2008 and up you can actually clear a specific plan from the buffer pool. In this example we are clearing out all plan's incase your using SQL 2005. Once again, this shouldn't be done on a production system . Please see BOL for a specific example on clearing out a single plan.

-- FREEPROCCACHE will purge all cached procedures from the procedure cache --
-- Starting in SQL Server 2008 you can purge a specific plan from plan cache --
DBCC FREEPROCCACHE
GO

Step 4 - Execute Stored Procedure Using SQLQueryStress

Now that we have established our connection and specified a default database we are going to execute our stored procedure specified in step one. You can execute the stored procedure once by making sure the number of interations and number of threads both have the value of "one." We will go over these options in more details a little later in the tip. Once those values are set correctly you can execute the stored procedure once by clicking on the "GO" button on the top right side of the SQLQueryStress tool.

Once the stored procedure execution completes you will see that statistics are generated to help give you valuable feedback towards your workload. You can see the iterations that completed. In this case we only executed the stored procedure once. You can also see valuable information for actual seconds, CPU, Logical reads and elapsed time as shown in the screen shot below.

Step 5 - View Total Executions via T-SQL

Now we will execute the following T-SQL script below, which will give us the execution count for our stored procedure. We just cleared the procedure cache so you will get an execution count of one as shown in the screen shot below.

SELECT  DB_NAME(st.dbid) DBNamee
,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName
,OBJECT_NAME(st.objectid,dbid) StoredProcedure
,MAX(cp.usecounts) Execution_count
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE DB_NAME(st.dbid) IS NOT NULL 
AND cp.objtype = 'proc'
AND OBJECT_NAME(st.objectid, dbid) LIKE 'uspGetEmployeeManagers'
GROUP BY cp.plan_handle, DB_NAME(st.dbid),
OBJECT_SCHEMA_NAME(objectid,st.dbid),
OBJECT_NAME(objectid,st.dbid)
ORDER BY MAX(cp.usecounts) DESC
GO

Step 6 - Using SQLQueryStress to Add Additional Stress with Multiple Threads.

Now that we have gone over the basics of executing a stored procedure with SQLQueryStress we will go over adding additional stress by changing the values for Number of Threads and Number of Iterations. The number of Iterations means the query will be executed this amount of times for each thread that is specified. The numbers of threads specify how many concurrent threads (SPIDS) will be used to execute the number of iterations.

Being that we changed the number of iterations to five and the number of threads to five we will expect the total number of iterations completed to be twenty-five. The iterations completed is twenty-five because we used five threads and had five iterations that were executed for each thread.  Below is a screen shot of the the workload completed after we clicked on the "GO" button with valuable average statistics during the workload.

If we rerun our T-SQL script from step 5, you will see that there is a total of twenty-six executions for the uspGetEmployeeManagers stored procedure. This includes our initial execution from step 4 and the additional stress applied in step 6.

Step 7 - Use Random Values for Parameters with SQLQueryStress

Next, we are going to cover using a dataset to randomly provide parameters to our stored procedure. Currently we use a hard coded value of eight as the value for the BusinessEntityID parameter. Now, we are going to click on the "Parameter Substitution" button to use a T-SQL script to create a pool of values that will be used during our stress testing of the uspGetEmployeeManagers stored procedure.

Once the parameter substitution window opens we will want to copy our T-SQL statement provided below that will generate the BusinessEntityID values we would want to pass into our stored procedure.

SELECT [BusinessEntityID]]
FROM [AdventureWorks2008R2].[HumanResources].[Employee]
GO

Once you added the T-SQL script, you would want to select the column you would like to map to the parameter used for your stored procedure.

Finally, the last part of this step is to drop the hard coded value assignment for the stored procedure. This way the parameter substitution will be used for the parameter value.

Step 8 - Wrap-up Results

To wrap up this tip, we have gone over controlling a workload to provide additional stress and randomly substituting parameters to be used for your workload replay. If you capture a SQL trace and replay the workload you should see a similar output as the one provided in the screen shot below. Looking at the screen shot below you will notice that each thread (SPID) has five iterations. Also, you will notice that the values for the businessentityid provided are randomly selected from our block of code provided for the parameter substitution.

Next Steps

• If you need to do some load testing, start using SQLQueryStress tool.
• Review several tips on SQL Profiler and trace
• Revew tips on working with Stored Procedures

About the author

John Sterrett is a DBA and Software Developer with expertise in data modeling, database design, administration and development.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips