February 4, 2009 at 1:44 am
Hi all,
We are migrating a core application from a sql 2000 server to a new sql 2005 server on new hardware. We are doing user testing but we really need to test out this server under something resembling a realistic load, which is approx 2000-3000 connections, 20-25 transactions per second, 400-500 batches per second. The sql server is dedicated to the application's 2 databases (1 of which is really just config).
A SQL profiler trace would probably be ideal, but almost all connections are made using windows-authentication, and profiler cannot replay such a trace since it cannot impersonate the windows users. Has anyone found a way around this? Perhaps a cheeky workaround that takes the trace file and manipulates it to make windows login myDomain\JoeBloggs a sql server login JoeBloggs, etc. I'm just clutching at straws!
Or are there any other tools that would fit the bill?
This is a windows app as opposed to web-based, so i cannot use a web loading tool to send off loads of URL postings that hit the db.
If there are any suitable tools out there that anyone has used with success i'd be very grateful to hear about it.
Many thanks.
James
February 4, 2009 at 6:54 am
I've used two different tools to do this. The one I've used the most is Quest Load Factory. You can combine with Quest Data Factory to create a large scale data load as well as lots of users. The other was a tool from Idera whose name escapes me at the moment.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 4, 2009 at 1:31 pm
I don't see the issue - put your logins on the new server and the replay trace will run fine.
I'm not sure you'll find a replay trace the answer however, having used this method to run through code, doing exactly what you want to do I remain unconvinced. You ideally need to replay against your original server too and then you have to run aprofile while you replay in an attempt to get caomparisions. I've always found that the replays tend to block. btw, 25 transactions/sec is very low activity, for those batches are you sure you have the figure correct - I usually find transactions exceed batches.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 4, 2009 at 6:06 pm
The only problem with Trace, and it's rather big, is that it's single threaded. So instead of multiple threads for multiple sessions, you get a single thread for all sessions. It's an OK method for testing, but the tools I referenced allow you to create a full blown multi-thread operation using multiple systems to really place a serious load on the database.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 5, 2009 at 1:52 am
Thanks all. I downloaded a trial of Quest Benchmark Factory and started to have a little play. I will carry on playing with this, but i was pleased to find that the profiler windows-authentication limitation i mentioned (as SSCrazy has pointed out) is not valid at all. I was abled to replay my trace and pretty suvccessfully.
The GUI options imply you can run this multi-threaded, and this does indeed seem to work. Maybe i should check the actual number of concurrent, active transactions, however. Certainly it is the closest thing yet to a decent workload!
Taking your points about profiler replay's little issues into account, perhaps i will now combine this trace with Benchmark Factory's features to ensure a multi-threaded replay and also to ramp up the volume further.
Yes! I have long wondered why on many of my servers TPS is the same or more than BPS. But on this server BPS is consistently 8 or more times higher than TPS. This was the same in my trace. A factor perhaps of a application and connectio layer that uses implicit transactions (this is indeed the case)? Also, we have transactional replication in place so i wonder if the replcmds counts as a batch but not as a transaction ... or something like that?! It is actually quite a busy and well-used system (in my opinion!), so i have been curious why our TPS is about a quarter of what I once read was the low threshold for a busy RDBMS (I saw something that suggested a busy database had 100+ TPS).
Anyway, thanks for your replies. I have something to work with now.
Cheers,
James
February 5, 2009 at 2:36 am
I was going to say that profiler does allow to run multi-threaded. The RML utilites will also do the job http://www.microsoft.com/Downloads/details.aspx?familyid=7EDFA95A-A32F-440F-A3A8-5160C8DBE926&displaylang=en
but I found the documentation to be too vast and never had time to read it all - perhaps another time.
Counters can be a bit odd/strange, most servers I work with tend to have sustained transactions/second over 1,000 - but it's only a measure -like many of the others
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 5, 2009 at 5:44 am
colin Leversuch-Roberts (2/5/2009)
I was going to say that profiler does allow to run multi-threaded. The RML utilites will also do the job http://www.microsoft.com/Downloads/details.aspx?familyid=7EDFA95A-A32F-440F-A3A8-5160C8DBE926&displaylang=enbut I found the documentation to be too vast and never had time to read it all - perhaps another time.
Counters can be a bit odd/strange, most servers I work with tend to have sustained transactions/second over 1,000 - but it's only a measure -like many of the others
Good one. I always forget about the RML utilities. I really need to get a better handle on them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 5, 2009 at 9:53 am
If you have developers that are using, or otherwise have access to MS Visual Studio Team System in either then "Team Suite" or "Test Edition" varieties, then you have access to a pretty wonderful loadtesting tool that can do loadtests using either HTTP protocol webtests (no good in yoru case) or code level "Unit Tests" (which would be a great way to emulate your application's access of the server)
In that case you could potentially create (or work with your developers to create) some query scenarios using unit tests, databind them to text files so that the query values differ, and then create a loadtest from one or more of those unit tests.
That tool also does a great job of monitoring the performance counters in the SQL server itself, so you'll have a much better idea of where bottlenecks might be coming from and what factors are having the biggest affect in terms of constraining performance of the system. (cpu, io, disk, etc) The folks over in the MSDN forum for VSTS Loadtesting are really knowledgeable and are a great resource if you need help or more info.
February 5, 2009 at 10:03 am
SQLIO and SQLIOSim are not perfect but have gotten us by.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply