SQL Query Stress Tool

By:   |   Comments (16)   |   Related: 1 | 2 | 3 | > Testing


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?


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

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.

 Configure Database Connectivity

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 --

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.

Execute Stored Procedure using SQLQueryStress

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.

You can also see valuable information for actual seconds, CPU, Logical reads and elapsed time

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
AND cp.objtype = 'proc'
AND OBJECT_NAME(st.objectid, dbid) LIKE 'uspGetEmployeeManagers'
GROUP BY cp.plan_handle, DB_NAME(st.dbid),
ORDER BY MAX(cp.usecounts) DESC

View Total executions via T-SQL

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.

Using SQLQueryStress to add additional stress with multiple threads

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.

The iterations completed is twenty-five because we used five threads and had five iterations that were executed for each thread

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.

rerun our T-Sql script from step #5

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.

Use Random values for Parameters with SQLQueryStress

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]

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.

Once you added the T-SQL script you would want to select the column

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.

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.

using the  SQLQueryStress tool
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author John Sterrett 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

Comments For This Article

Wednesday, March 2, 2022 - 11:32:12 AM - Irawan Soetomo Back To Top (89846)
Using Parameter Substitution, I have trouble having a script containing a table variable that doesn't need substitution. Is there a workaround besides converting it into a temporary table?

Saturday, May 9, 2020 - 2:10:05 AM - Ade Back To Top (85609)

Hi John,

Thanks for the write up. However where can I download the SQL stress testing tool? 

Monday, September 16, 2019 - 2:06:46 AM - Grťgory Nail Back To Top (82464)


Is it possible to have a Excel files or SQL tables in source of query to push a load. Today, I paste my load but is not possible to write "GO" and it's necessary to rewrite my code logged.

Friday, September 21, 2018 - 3:50:34 PM - qwerty Back To Top (77696)


 I have issue withn connecting to database. When i click on database icon -> localhost -> connection failed.

Help me.

Thursday, April 13, 2017 - 2:08:24 PM - Suresh Padmanabhan Back To Top (54811)

 Hi John

Well written documentation on the tool.  I have a question on the interpretation of Client Seconds /Iteration and Actual Seconds/Iteration.

When I ran my Stored procedure for a single iteration I have the above 2 output parameters as 0.5349 / 0.9530.  The same when I ran for 1000 iterations the 2 output parameters were 20.8750 / 41.6336.

How do I compare these two?  When these two parameters are displaying averages, why my multi-thread values show more values than the single thread values.

What should I be considering as the measure for Performance improvement?





Saturday, April 8, 2017 - 5:26:08 AM - Jignesh Raiyani Back To Top (54455)



It’s very helpful tool for load test and we can analyse performance. I got much help to check performance on new servers using this tool.

Monday, August 25, 2014 - 6:37:03 AM - Sakshi Back To Top (34246)

very nice article

Monday, June 2, 2014 - 5:29:37 AM - Vinit Back To Top (32027)


Can you tell, is it possible to use this tool for Oracle11g. If yes, can u tell steps to do so?

Thanks in advance

Monday, June 24, 2013 - 2:53:51 AM - mahdy Back To Top (25532)

pretty cool!! thanks for that!!!!!!!!

Thursday, July 19, 2012 - 10:48:21 AM - Sasi Back To Top (18654)

Nice article...however I have a different scenario...I have multiple entity framework generated scritps that run alot of time on our production databases. We are still in the process of removing EF scripts and writing our own stored procs. When the EF code runs it uses SP_EXECUTESQL followed by the code. Can I use this tool to identify how much stress the EF scripts are adding? For that in the query section should I use the scripts as is? like sp_executesql 'script'...

Thursday, July 19, 2012 - 1:49:12 AM - Yadava Back To Top (18643)

Great Tip...! Thanks a lot... :-) Keep going....!

Wednesday, July 18, 2012 - 2:46:06 PM - John Sterrett Back To Top (18615)


Great point about sp_recompile. I didn't want to take too much focus away from SQLQueryStress on this tip. I am actually working on my next tip which will go over various ways to remove an plan from cache.

Simon and Junior,

Thank you for the feedback. I am glad you enjoyed the tip.


Wednesday, July 18, 2012 - 1:23:42 PM - Ameena Back To Top (18609)

John, Great artilce. I downloaded sqlstress and had fun with it. Thanks for introducing it to us.

Aaron, I agree using sp_recompile is a better approach when you are testing in production.

Wednesday, July 18, 2012 - 9:52:05 AM - Aaron Sentell Back To Top (18607)

Great overview and exactly what I needed for a task today. One tip: Rather than executing DBCC FREEPROCCACHE which clears the procedure cache for the entire SQL instance you can clear it for a single stored procedure by using sp_recompile.

sp_recompile uspGetEmployeeManagers


Wednesday, July 18, 2012 - 8:58:42 AM - Simon Doubt Back To Top (18606)

I've also used this tool for my stress-testing - it's great.

Wednesday, July 18, 2012 - 8:19:38 AM - Junior Galv„o - MVP Back To Top (18605)

, God Morning.


Congratulations this posts, very good.



get free sql tips
agree to terms