solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips














































SQL Query Stress Tool

MSSQLTips author John Sterrett By:   |   Read Comments (8)   |   Related Tips: More > 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.

SQLQueryStress

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

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

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

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


Last Update: 7/18/2012


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.

View all my tips


print tip Print  
Become a paid author




Recommended For You








Comments and Feedback:
Wednesday, July 18, 2012 - 8:19:38 AM - Junior Galv„o - MVP Read The Tip

, God Morning.

 

Congratulations this posts, very good.

 

Regards.


Wednesday, July 18, 2012 - 8:58:42 AM - Simon Doubt Read The Tip

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


Wednesday, July 18, 2012 - 9:52:05 AM - Aaron Sentell Read The Tip

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

Aaron


Wednesday, July 18, 2012 - 1:23:42 PM - Ameena Read The Tip

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 - 2:46:06 PM - John Sterrett Read The Tip

Aaron,

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.

Regards,
John


Thursday, July 19, 2012 - 1:49:12 AM - Yadava Read The Tip

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


Thursday, July 19, 2012 - 10:48:21 AM - Sasi Read The Tip

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


Monday, June 24, 2013 - 2:53:51 AM - mahdy Read The Tip

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



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.