How to run a certain query from 20-30 connections simultaneously ? 1

[royc75]

Hello,

I need to simulate a ceratin query on the Database.
I need to do it from 20-30 Connections at the same time in order to test it's locking on the Databse.
Can anyone show me such Stored Procedure which run this query simultaneously or can direct me to a freewere tool which will do that?

 

[mrdenny]

There are lots of Load Test software out there which will handle this. Most of them are fairly pricy however. A stored procedure won't be able to do this by it self.

This can be done fairly easily with the native SQL tools.

Create a stored procedure with this code.

create procedure test_CauseDelay as
waitfor time '14:00:00.000' --(or what ever time is about 2 minutes in the future)
go

Open a bunch of Query Analyzer windows (or SQL Management Studio is SQL 2005). At the top put in to execute the test_CauseDelay procedure. Update the procedure to wait until about 2 minutes after the current time (be sure to give your self enough time to execute all the windows).

Now start all the windows. They will all wait until the specified time that you specified in the procedure and then they will run the rest of the code.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[royc75]

Thank you Denny, looks like a creative idea...
But aren't there any freeware tools for that? Suppose I would like to run it over and over during the night for example...

 

[mrdenny]

To do that I'd modify my process a little bit.

Create a table with a single entry in it.

create table dbo.NextRun
(NextRun datetime)
go
insert into dbo.NextRun
select getdate()

Now change the procedure to this.

create procedure test_CauseDelay as
declare @WaitTime as datetime
select @WaitTime = NextRun
from NextRun

waitfor time @NextRun
go

Now setup a window to run this.

while 1=1
begin
    update dbo.NextRun
    set NextRun = dateadd(mi, 10, getdate())

    waitfor delay '00:00:30.000'
end

Now put all your code with a while loop and run this a bunch of times.

while 1=1
begin
   exec test_CauseDelay

   Do your code here
end

This would cause every connection to run the code every 20 minutes. You could set it to a lower number of minutes, but be sure that it's long enough to let everything complete or you could end up with processes all pissed off trying to wait for a date that has passed.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com