How would you generate realistic SQL Server activity?

As a tester working on the ground-breaking new version of SQL Response I’m haunted by the following question: “How do you performance test a product like SQL Response?”

Yes, I am literally haunted by voices in my head asking me this question. After all, this is a product that needs to exist on a customer’s network with the lowest possible impact while monitoring potentially a couple of hundred highly active servers for dozens of symptoms that could imply problems. Compounding this problem is the fact that the performance metrics have to cover not just the installed application host machine but also the monitored servers, domain controllers and the network itself. So it’s not an easy problem. It’s going to take a LOT of discussion, meticulous planning and technical inspiration.

A lot of Red Gate employees have SQL Server installed on their machines, so it would be simple for us to ask for volunteers and have our wonderful new product happily monitoring 150 SQL Servers…. However, many of these SQL Servers are doing precisely nothing. We could build a product that could monitor them for eternity without the slightest performance hiccup. However, we’re a bit more informed than that. Red Gate makes a lot of effort to communicate with SQL Server DBAs and one thing we never hear is “Our SQL Servers just  sit on our desktop machines ready for the occasional time when we need to do a quick bit of testing. Then we all go home for the weekend and don’t have to worry”.

There are some decent tools out there, notably the free Microsoft tools SQLIO and SQLIOSim. Although these definitely stress a system they don’t actually involve SQL Server in any way so wouldn’t necessarily create the kind of realistic activity we’re interested in seeing. We’ve evaluated a tool called SQL Stress which seems very nice. One problem – it doesn’t have command line support so isn’t great for an automated test system. We could always roll our own SQL Scripts and write a very basic multi-threaded C# app to run them. Easy to do but again not likely to create realistic activity. The most promising idea we have is to create a clone of the server that runs the hugely popular (and Red Gate sponsored) community site SQL Server Central. Having this we could then use a trace replay tool to continuously run trace data collected from the site itself. This would be quite an undertaking but we feel it would be worth it to create a realistic performance scenario.

So.. those are our ideas. But what we’d love to know is – what ideas do you experienced DBAs have about this kind of thing? Maybe what we’re trying to achieve sounds similar to a problem that you’ve encountered in the past? Maybe you’ve already thought of an inspired solution to this problem, have packaged it, released it and are reading this from a luxury yacht somewhere off the coast of Hawaii? Or maybe there’s a well known solution that has become standard DBA practice? We’d love to hear those ideas.

This entry was posted in Testing and tagged . Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

8 Comments

  1. Posted November 23, 2009 at 4:24 pm | Permalink

    How about using automated UI test scripts for SQL Server Central and use those? Not only do you see real queries being executed, but you also see real path the application takes, including waits etc

    You could then take the UI test scripts and expand them for multiple concurrent users…

    If you have a good framework in place, ideally you could drop in more test automation scripts for other applications (Simple Talk, Red Gate.com) – could also produce some ‘interesting’ sample results for marketing purposes…

    • Chris.Spencer
      Posted November 23, 2009 at 6:55 pm | Permalink

      Thanks Ben.

      I think your idea about UI test scripts is an interesting one that has definitely got me thinking. It may depend on how far we go in the ‘cloning’ of SQL Server Central. Initially we’ll be attempting to mimic the back-end set up and assessing the impact of our application around that scenario but this may change and we’re still at the planning stage…

      And yes we will probably be trying this with Simple Talk and Red Gate.com too. These sites have a lot of activity and will be very useful.

  2. Posted November 23, 2009 at 5:07 pm | Permalink

    ostress can be used as part of the RDL Utilities from MS.

    • Chris.Spencer
      Posted November 23, 2009 at 7:02 pm | Permalink

      Hi Barry

      It seems that I missed this tool in my initial research but a quick google seems to suggest it’s a tool well worth investigating.

      Thanks for the excellent feedback

  3. Posted November 24, 2009 at 5:43 am | Permalink

    Using sqlservercentral is a good idea, but its a web app, ideally you would want some client server apps. I worked at allrecipes which is a huge site and they really don’t hit the database very much.. In the insurance industry we have apps that runs 1500 concurrent connections a day and are full of cursors, dynamic sql etc. As you are doing agentless monitoring you really need to get some test servers under heavy pressure to make sure your monitoring isn’t going to contribute to poor performance. A traditional client server monitoring tool can queue up and cache, then upload on a less frequent basis till pressure slows down. Sorry i can’t offer you a trace but we do stress test by spinning up a bunch of virtual desktops – 30-90 and run scripts to simulate activity, forcing threading etc, if you have VMware or hyperv and some hardware that might be an alternative, i can send you some details if your interested.

    • Chris.Spencer
      Posted November 24, 2009 at 12:55 pm | Permalink

      Thanks for the feedback Iain. We won’t be limiting our tests to just the SQL Server Central scenario and will also be looking to create scenarios similar to those you’ve described. We’re very interested in getting further details of your stress testing set up so I’ll email you directly. Thanks again.

  4. barry
    Posted November 24, 2009 at 10:12 am | Permalink

    The ostress command line too I mentioned is part of rml utilities. This is a suite of tools from Ms which basically allow you to trace real activity then ramp up that activity simulating multiple spids etc. We used it to test sql 2008 full text during the ctp. I am not if this is a perfect fit for your requirements but its definitely worth a look.

    • Chris.Spencer
      Posted November 24, 2009 at 1:08 pm | Permalink

      Short of writing our own tool I don’t think we’re ever going to get a perfect fit for our requirements. The rml utilities look very promising and we’ll be investigating these along with several of the other ideas that this post has generated. Thanks again

Post a Comment

Required fields are marked *

Add an Image

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>