Load testing a new SQL Server

  • Jimmy M

    Default port

    Points: 1481

    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

  • Grant Fritchey

    SSC Guru

    Points: 395580

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • colin.Leversuch-Roberts

    SSC Guru

    Points: 52551

    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/

  • Grant Fritchey

    SSC Guru

    Points: 395580

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Jimmy M

    Default port

    Points: 1481

    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

  • colin.Leversuch-Roberts

    SSC Guru

    Points: 52551

    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/

  • Grant Fritchey

    SSC Guru

    Points: 395580

    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=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

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • SQAPro

    Right there with Babe

    Points: 742

    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.

  • John Langston

    SSCommitted

    Points: 1860

    SQLIO and SQLIOSim are not perfect but have gotten us by.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply