Geek City: Poor (Wo)Man's Load Testing

Kalen Delaney — Wed, 24 Oct 2007 19:28:00 GMT

Last July, I posted about a way to run a batch multiple times when you use GO as the batch separator. I usually take advantage of this technique when I am loading test data into a table.

I was mentioning this feature to my class last week in Minneapolis, and one of the students thought that this feature had another use. He thought it would be great to use it to simulate load testing. One connection could execute a procedure and terminate the batch with GO 100 and another connection could be started that did the same thing. You could open up multiple query windows using Management Studio (or Query Analyzer) and have each one run a procedure, or even a statement, over and over again. You could also have different connections run different stored procedures or statements. This will allow you to detect problems with concurrency, which most query tuning techniques do not take into account.

In the past, I had recommended using the Replay feature of SQL Server Profiler to simulate a "poor man's load testing" environment, but this great idea would make it even easier!

Of course, for the rich (wo)man, there is also very nice load testing software available. Whether rich or poor, it's crucial to make sure you test your queries and procedures with multiple users.

Have fun!

~Kalen

Did You Know? Run a batch multiple times

Kalen Delaney — Sun, 22 Jul 2007 22:08:00 GMT

At first I thought I would just add another sentence to my previous post, but then I realized that this feature is not even documented anywhere, and it might be useful to mention it in its very own post. I have no idea why it is undocumented, but it has been part of all command interfaces to TSQL that I have used for the last 20 years.

When you use GO as a batch terminator, you can follow it by an integer, which indicates that the batch should be executed N times.

I use this frequently when populating test tables. In my previous post, I ended with this suggestion:

-- You can add more rows by just rerunning the INSERT statement from above, as many times as you would like.

So, to run the INSERT 5 times, I would do this:

INSERT INTO details
(SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID,
UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate)
        SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID,
             UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate
        FROM Sales.SalesOrderDetail
GO 5

Have fun!

~Kalen

Kalen Delaney : GO

Geek City: Poor (Wo)Man's Load Testing

Did You Know? Run a batch multiple times