Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228

    Unanswered: Stress-Testing SQL Server

    What do you recommend for stress-testing the performance of key stored procedures (they have been identified) for our application? The parameters can be programatically selected, for example:
    Select 'exec my_proc @id = ' + Cast(id As varchar)
    From myTable
    Where foo = 'bar'
    I have the Support Tools Available For Stress Testing & Performance Analysis from Microsoft's site and they are pretty good.

    Recommendations appreciated, and thanks in advance!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My preferred method for stress testing is to setup a pure test system, with only one user on it. Use SQL Profiler to record their session, and have them put the application you wish to stress test through its paces, giving the application the kind of usage that they expect power users to give it.

    Examine the script, looking for what data is session driven (for example client number, invoice id, etc), since these things need to change for each test session. Create a large number of test scripts by editing copies of the original profiler session, changing the session dependant data... Generate WAY more scripts than you expect to need.

    Start to run the scripts in parallel with each other. Start with two, then four, etc.

    Watch for conflicts caused by "bottlenecks", where a single process (like searching for a client) might be single threaded by design, because these can kill your test. The most obvious sign of this is when all of the scripts seem to "gang pile" on a single SQL statement, which is often a stored procedure. If there are any, these single-threaded bottle necks need to be corrected before you can do the real stress test.

    Now you need to switch to testing on the proposed production configuration (hardware, software, etc). You also need to decide if caching is acceptable, or if you need to restart the server before each flight of tests.

    Once you've cleared any single threaded bottle necks, you can start real testing. Run the first script, and time it. Run two, then four, then more, timing from the start of the first script to the end of the last script in each "flight" of scripts. This gives you the total time needed for that test, even though individual sessions might finish much faster for any number of reasons.

    Increase the load (more sessions per flight) until you reach 200-300 percent of your expected production load. At the very least, I'd recommend five tests at every 25% (25, 50, 75, 100, 125, 150...300) of your expected production load. For presentation, throw out the fastest and slowest run at each testing threshold, since they'll tend to skew your test data.

    -PatP

  3. #3
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    Excellent - we will do exactly that. Thank you very much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •