Stress Test

[ptheriault]

Does anyone have any scripts that could emulate N number of users running queries on a sql server. I need to simulate 100 users running queries while or QA department runs through the application.

does anyone have any thoughts on how I could do this?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

Here is what I have so far.
I need to get this to open N number of sessions and loop for N number of hours.

--Declare variables
Declare @RandomTime char(8), 
        @RandomString char(1), 
        @RandomInt int,
        @loopcnt int

--SET variables
SELECT =  @RandomTime = '',
          @loopcnt = 1

--Start loop
WHILE @loopcnt < 10
-- sleep random period between 20 and 40 seconds
SELECT @RandomTime = convert(char(8), DATEADD ( ss , (rand() * 10) + 10, '20000101' ), 108)
SET @RandomString = char(rand()*26 + 65)
SET @RandomInt = rand() * 80 + 20
WAITFOR DELAY @RandomTime
SELECT 'RandomTime= ' + convert(varchar(25), @RandomTime) +' RandomString= ' + @RandomString + ' RandomInt= ' + convert(varchar(10), @RandomInt)
--poor performing query
select WFE_ID
from WF_event
order by wfe_datecreated desc

--Increment counter
SELECT @loopcnt = @loopcnt + 1

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[TallOne]

You might consider working this in with a Job on a schedule. The poor performing query appears simple. Consider adding an index to WF_event.

 

[ptheriault]

That's a good idea. If I put this in a stored procedure an execute it every 1 minute over the time of the test I should get multiple threads running. I want that query to perform poorly. (It's a long story!)

Thanks


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

I tried to place the proc in a job but I can't execute the job again if it is still executing from the previous time.

So I have this statement that I would like to execute N times over 120 Minutes.

any suggestions????

Goerge????

Declare @RandomTime char(8), 
        @RandomString char(1), 
        @RandomInt int,
        @loopcnt int

--SET variables
SELECT @RandomTime = ''
-- sleep random period between 20 and 40 seconds
SELECT @RandomTime = convert(char(8), DATEADD ( ss , (rand() * 10) + 10, '20000101' ), 108)
SET @RandomString = char(rand()*26 + 65)
SET @RandomInt = rand() * 80 + 20
WAITFOR DELAY @RandomTime
SELECT 'RandomTime= ' + convert(varchar(25), @RandomTime) +' RandomString= ' + @RandomString + ' RandomInt= ' + convert(varchar(10), @RandomInt)
EXEC sp_recompile N'uspStressTest'
EXEC uspStressTEst
GO

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[TallOne]

I've never done anything like this so my advise is just ideas. Create a vb app that calls the stored procedure. Set the vb app on a scheduled task in windows system tools.

 

[ptheriault]

I found an application that I can execute my script from. It's very nice little app. (and free!)

http://www.codeproject.com/vb/net/DBstressUtil.asp

Thanks for the ideas.

- Paul
- If at first you don't succeed, find out if the loser gets anything.