Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favourite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved in your SSC briefcase from the favorites tab.
Download now (direct download link)

Execute SQL Scripts in paralel for real performance tests

By Gustavo Maia,

Ever tried to simulate a production enviromnent in a development one but realized that you couln't easily start 500 executions simultaneously ?

 

The Problem:

In a development environment we cetainly dont have the tools to simulate a production environment, that's why some companies prepare a pré-production ( or staging ) machine just for a "as close as possible" test environment.

One hard task is to simulate real paralellism between executions of the same script, checking how SQL server will handle transaction isolation levels, locks ( deadlocks ), performance issues or even hardware bottlenecks.

There are stress tests scripts and tools that can put your CPU at 100%, memory and disk IO at maximum, but this doesn't test how your code will react with multiple execution of your own code.

 

The Solution:

A simple line can provide you with paralelism and data required for your testing environment:

C:\For /L %i in (1,1,100) do start /b osql -Syourserver -Uyouruser -Pyourpass -q"yourSQLscripts" < C:\EXIT

 

I encapsulated it within this procedure:

PROCEDURE sp_multiple_execute(
@executions INT -- times your script will be executed
, @sql VARCHAR(8000) -- the script to be executed
, @server VARCHAR(255) -- connection info: server name of IP address
, @user VARCHAR(255) -- connection info: username
, @pass VARCHAR(255) -- connection info: password
, @debug INT = 0 -- turns extra prints and resultset display
)

 

 

Particularities:

  • I used Shell version of FOR to make it simple for the SO to handle.
  • I used the Start command with /b to generate the desired paralellism but on a single "window".
  • I used the osql command to execute the sql script desired with -S -U -P parameters
  • i had to use the "< C:\EXIT" because for some reason, even when returning the prompt after execution, on some tests the cmd sessions never closed by its own, and i had to kill them manually on the server.
  • I dind't use any 2005/2008 stuff like varchar(MAX) so that this script could be used under sql server 2000, but obviously that the script you're trying to run is what determine the server requirements. This can cause problems with large resultsets because of the limit of 8000 of the #temp tables used, but fell free to change it to MAX to get enough space for your resultset.
  • You will need permission to read/write C:\ or any other directory, change this path on lines 20, 32, 43 to the final destination of the temp file named "EXIT"
  • You will also need permission to run master..xp_cmdshell, check sp_configure options to learn how to enable this.

 

Execution example:

This code starts 100 almost simultaneously executions of a script that, insert its start time at a test table, waits 5 seconds, and update that row with the finishing time.

 

CREATE TABLE master..test ( id INT IDENTITY(1,1), start DATETIME, finish DATETIME )

DECLARE @sql varchar(1024)
SET @sql = ' insert into master..test(start) values ( getdate() )' + char(13)

+ ' waitfor delay ''00:00:05''' + char(13)
+ ' update master..test set finish = getdate() where id = @@identity'

EXEC sp_multiple_execute 100, @sql, 'smonreal', 'sa', '', 0

SELECT * FROM MASTER..TEST ORDER BY 1
DROP TABLE MASTER..TEST

 

Table Master..Test should contain values like this one:
ID Start Finish
1 2009-09-16 12:12:00.373 2009-09-16 12:12:05.420
2 2009-09-16 12:12:00.390 2009-09-16 12:12:05.420
3 2009-09-16 12:12:00.390 2009-09-16 12:12:05.437
4 2009-09-16 12:12:00.390 2009-09-16 12:12:05.437
... ... ...
100 2009-09-16 12:12:03.030 2009-09-16 12:12:08.047

 

If @debug parameter is set to 0, then only the total duration time is recorded, by total i mean the moment the last of the 100 sessions returned the propmt to the main window.

Otherwise the executed script along with all resultsets and the duration is returned.

 

 

Thanks,

and happy testings.

 

 

 

Total article views: 1826 | Views in the last 30 days: 12
 
Related Articles
FORUM

by mistake one Script on Master

by mistake one Script on Master

FORUM

Script system stored procedures in master?

Script my own sp's that are running as system stored procedures in master?

FORUM

Connditional executing of script

Connditional executing of script

FORUM

URGENT: Error executing scripts......

URGENT: Error executing scripts......

Tags
development    
palalelism    
stored procedures    
stress test    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones