August 27, 2008 at 1:53 pm
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
August 27, 2008 at 2:05 pm
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).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 27, 2008 at 2:59 pm
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.
August 28, 2008 at 2:13 am
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
August 28, 2008 at 6:44 am
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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 28, 2008 at 7:52 am
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
August 28, 2008 at 8:32 pm
Let us know if you need anything else, Helmut.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply