So you wanna run a load test against SQL Server. One thing I need you to understand first: you have to provide the database and the queries. Almost all of the tools in this post, except the last one, are designed to help you run queries, but they don't include the queries. The whole idea…
When I first look at a server, I want to know how busy it is, where its bottlenecks are, what is SQL Server waiting on and many other things. Batch Requests/sec is one of the data points that is used to measure how busy a server is.
WHAT IS BATCH REQUESTS/SEC?
This question comes up a lot
Especially during Office Hours, and the answer is usually... not great. You can spend a lot of money on people and complicated software to design, run, and monitor workloads against test environments, or you can throw together tests with some free tools like SQL Query Stress or Microsoft's RML Utilities.
I Like Testing Things Especially Brent's patience. And I like watching people test things. SQL Server is sort of perfect for that. You make some changes, you hit f5, and you wait for something to finish. If it's faster, you ship it. Right? I mean, we all know you just added NOLOCK hints anyway. A…
In preparation for a recent load testing engagement, we started talking about the company's goals. The DBA said something really insightful:
"We want to get to 1,000 page requests per second, which is around 5,000 batch requests per second. We're testing with all of our caching turned off, because we want to simulate what might happen on Black Friday if our caching layer fails - we still want to be able to sell stuff."
One of my favorite questions is, "How can I generate workloads to run against SQL Server for testing?"
Step 1: get the StackOverflow.com database. This database has a relatively simple schema, just a few tables, real-world data distributions, and enough rows that you can generate seriously slow queries. You can use any size, small medium or large, and they'll all work.
Sometimes you need garbage
Not because DBAs are the IT equivalent of Oscar the Grouch, but maybe you want to post a question on a forum, and you don't want to use your own data. At least, you probably shouldn't just post your own data without masking it. But masking data is annoying, and by the time you get everything in order, someone's breathing down your neck for an answer.
Load testing - real, serious load testing - is hard.
In a perfect world, you want to exactly simulate the kinds of queries that the end users will be throwing at your SQL Server. However, in the words of a timeless philosopher, ain't nobody got time for that.
We've got a client that does big batch jobs every day, loading hundreds of gigabytes of data or more in short bursts. They were frustrated with slow performance on the batch jobs, and after we performed our SQL Critical Care® with 'em, it was really clear that their hardware was the bottleneck. They were using…
Do you have a SQL Server that’s not getting enough action?
Maybe you have a new piece of production or lab hardware that you’d like to benchmark, to see how it compares with other gear in your environment.
Our QA department uses Mercury TestDirector to do load testing of our .NET web apps. The basic idea is that TestDirector runs through a set of URLs to load test an application, and it can gradually scale up the number of simultaneous sessions until the datacenter is filled with smoke. When the fire alarms go off, subtract one from the number of sessions, and that's the maximum load the application can sustain. (Okay, not really, but you get the idea.)
