Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to "benchmark"/simulate parallell execution of a query Expand / Collapse
Author
Message
Posted Wednesday, August 27, 2008 1:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 14, 2009 7:33 AM
Points: 32, Visits: 29
Hi

I have a web site running a database query against a sql 2005 DB. The query is fast when executing in query analyzer, but for some reason it doesnt scal when multiple users are running the same query from the external web. I strongly suspect that the SQL server is in some way a bottleneck...but I need to find a way to "stress test" my query in some way. I want to find a way to, internally inside SQL Server, simulate this one sql query running in several parallell instances...

From this test, I would like to see/verify IF this parallell execution makes each individual query to go slower...

Please advice how to proceed to find the bottleneck. Joins and indexes etc..have been analyzed and verified by a lot of "sql people"...

Help mostly appreciated

br
Helmut
Post #559917
Posted Wednesday, August 27, 2008 2:05 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
If you need to stay within SQL Server alone, then the easiest way is probably to use the SQL Agent Job system.

Write a stored procedure that will call your query(s)/proc(s) in a loop, possibly with a Delay, if you want that.

Then write two or more Agent Jobs that call your stored procedure. Now start your Jobs and use the Job Monitor and the Activity Monitor (or sp_Who(2) or Profiler) to confirm that all (N) of your jobs are running.

Now you can do your tests/observations. when you are done just stop the Jobs or their Processes.

This can also be done with Service Broker (bit more trouble to set up, but easy to change the number of running copies) and of course from your client as well (either multiple Query windows or your own VB/C# client).


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #559923
Posted Wednesday, August 27, 2008 2:59 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, May 4, 2014 10:21 PM
Points: 199, Visits: 307
Why can't you run a SQL profiler replay trace, capture it for a brief period of time and run the replay in a test environment?

If you're concerned about the Profiler overhead you can script it out as a server side trace.

Also, go grab a copy of Itzak Ben-Gan's book "Inside Microsoft SQL Server 2005: T-SQL Querying". Chapter 3 actually has a pretty Chuck T. Awesome way for capturing and performance tuning queries based on wait times.

In fact, you can go grab the sample code here, but don't tell anyone... its our little secret.

http://www.sql.co.il/books/insidetsql2005/



Post #559965
Posted Thursday, August 28, 2008 2:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 14, 2009 7:33 AM
Points: 32, Visits: 29
Thanks for the reply..and sorry about the cross post...

Could you tell me a littel bit more in detail how I can set up thsi parallell execution (for example through SQL Agent jobs)...can I really simulate many "users" accessing/executing the query at exactelly the same time..will this setup not give a sequential execution..could you explain a little bit more ?

Also, if I do not want to involve any stored procedures...let's say I only have an extremelly simple query as an example, how could I implement these jobs without sp for this purpose.....

..and also, how can I monitor the execution more exactelly..let's say i manage to understand how to start 10 parallell executions of this same simple query..how can I practically verify how long the execution time of all queries to finsish is related to the number of simultaneous queries running.....

I have some thoughts that the execution time for - let's say 10 simultaneous executions will not take even close to 10 second, even though one execution in query analyzer takes 1 second...just want to verify in some way that my thought are correct and that 10 executions take like 20 seconds, and 20 executions perhaps a minute or so

Very greatful for some more guidence on this one....

Thanks
Helmut
Post #560211
Posted Thursday, August 28, 2008 6:44 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
Helmut Nielsen (8/28/2008)
Could you tell me a littel bit more in detail how I can set up thsi parallell execution (for example through SQL Agent jobs)...can I really simulate many "users" accessing/executing the query at exactelly the same time..will this setup not give a sequential execution..could you explain a little bit more ?


It is true that each SQL Agent Job is limited to sequential execution, that is why you have to make multiple different Jobs. Each job would contain exactly the same definition, only the Job Name would be different. Then if you create 5 different Jobs (called, say "Test Job 1" through "Test Job 5") you can have up to 5 simultaneous executions. This may not sound like a lot, but automated procedures in a loop are much faster than real users (who are human and have pauses/waits in-between server requests) so this would easily simulate the intensity of 20 to 100 users.

If you are unfamiliar with the Job system and need a walkthrough of how to create these Jobs, let us know.


Also, if I do not want to involve any stored procedures...let's say I only have an extremelly simple query as an example, how could I implement these jobs without sp for this purpose.....

Whether you are going to test or your are going to use the Job system, you want stored procedures. Bare commands, no matter how simple, are way to much trouble to manage in these more abstract situations. Plus a stored procedure can be as simple or as complex as you need it to be.

In this case you need not just you SQL query statement, but a way to loop also: that means at least two SQL commands, so you definitely want a stored procedure. If you are unfamiliar with how to setup or use stored procedures, let us know and we can help you with that too.


..and also, how can I monitor the execution more exactelly..let's say i manage to understand how to start 10 parallell executions of this same simple query..how can I practically verify how long the execution time of all queries to finsish is related to the number of simultaneous queries running.....

There are a number of ways to do this, but that starts to get more complicated. I have go earn a living right now (:)) so hopefully someone else can chime in (I'll be back later today). What would help is if you could give us some example commands and table defintions to start working with. See this link for details on how to provide us with this information: http://www.sqlservercentral.com/articles/Best+Practices/61537/


I have some thoughts that the execution time for - let's say 10 simultaneous executions will not take even close to 10 second, even though one execution in query analyzer takes 1 second...just want to verify in some way that my thought are correct and that 10 executions take like 20 seconds, and 20 executions perhaps a minute or so

It depends, if they are all hitting the same data, then they will either be unrealistically fast (for Selects) or unrealistically slow (for modifications). One of the reasons that we need to see your actual commands is so that we can show you how to randomize this somewhat.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #560348
Posted Thursday, August 28, 2008 7:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 13,872, Visits: 9,600
In a job, you can use "bare naked SQL", without procs, if you want. An SQL task in a job will do any SQL you want. It's generally better to execute your queries as procs, but if you need to test something else, you can do it this way.

If you need run time for each, you can add a logging command to each proc/script that you run. This will, of course, add a little overhead to the process, but it should be relatively minimal if you keep the log simple.

A better way to monitor performance is to run a trace on the server and see how much CPU, how much time, IO counters, etc., for your procs. You'll get a lot more data that way and it's quite often very eye-opening.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #560454
Posted Thursday, August 28, 2008 8:32 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
Let us know if you need anything else, Helmut.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #560961
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse