I needed to generate some load for a demo and wasn’t looking forward to doing it. I was in a hurry, and didn’t want to deal with a lot of setup. Fortunately, I’d seen a technique for doing this in one of Brent Ozar’s classes. I’d taken a couple, and they’re good. One of the items I took away was the need to simulate a load that might help me analyze my system.
Brent’s written about his technique, and I decided to adapt this to my own demo database. I took his code, and then added a few items.
Examining Parameters
I was happy with a load of ten items, so that makes things simple. I could reuse most of Brent’s code, but unlike his system, I have different parameters in use for different types of calls. In examining the types of procedures that might generate load, I found I had these types of parameters
The basic code uses RAND() to generate a number, but what about the other items? I didn’t want scalars, so I needed random other items.
For dates, I still used a similar random integer, but then used that with DATEADD() to alter an existing date. My code would generate a separate random number and then use that to go backward in time a certain number of days.
SELECT @off = RAND() * 1000;
SELECT @dt = DATEADD(DAY, 0 - @off, SYSDATETIME());
For strings, it was different. In this case, it was search items and while there can be a (seemingly) infinite number of possibilities, I can simulate this. I can select from the table and use a random ordering to get different terms. If this isn’t efficient, that’s fine. I want a load on the system.
DECLARE @term VARCHAR(100);
SELECT @term = SearchTerm
FROM dbo.SearchTerms
ORDER BY NEWID();
SELECT @term;
Once I had these techniques, I built a stored procedure just like Brent’s, and then replaced his procs with my own. I built parameters in front of the CASE statement, and then use the appropriate random parameter for the procs as needed.
Running the Load
I used the same SQLQueryStress tool that Brent did, setting the connection credentials, threads, and iterations as needed to generate load. This gives me control over the load I need to generate.
I had 10 possible procedures, each of which runs with different parameters each time the proc is called. For 100 iterations of 4 threads, I generate 400 calls to my load procedure, which generate 400 random calls to 10 other procs with different parameters
A quick and dirty load on my server. Not representative of any production load, but good enough to stress the system and let me look for places to tune code. And, a way that I might get some of the weird random things users do.
Voice of the DBA 