- Hosts: Steve Stedman / Mitchell Glasscock
- Listen on Spotify!
- Topic: Using the Query Loading Ramp in Database Health Monitor for Load Testing
Load Testing with Query Loading Ramp Stedman SQL Podcast SQL Podcast Sn 3 Ep 12
Steve Stedman and Mitchell Glasscock discuss the Query Loading Ramp feature of Database Health Monitor, a tool for load testing SQL queries. They demonstrate running a load test with 400 sessions, simulating real-world traffic, and identify performance issues. The test reveals that 3 out of 17,000 queries failed, with an average query time of 46 milliseconds. Adding an index significantly improves performance, increasing throughput to 14,000 queries per second. They also show how to run load tests in headless mode and emphasize the importance of regular monitoring to maintain optimal SQL Server performance.
Podcast Transcript
Steve Stedman 00:16
Welcome to the Stedman SQL Podcast. This is Podcast Season Three, Episode Number 12, and as part of Database Health Monitor Month, we’re talking about one of the Database Health Monitor features. Today’s topic is load testing with the query loading ramp. Joining me is Mitchell Glascock. Mitchell, good to have you here.
Mitchell Glasscock 00:34
Hey, Steve, glad to be here. What are we breaking today?
Steve Stedman 00:38
Oh, today we get to break queries and intentionally. So we’ve got query loading ramp open, and we’re going to run a real load test live and walk through everything, everything that it shows us, and everything we can learn on that. And we’re going to go in and break queries on purpose and figure out exactly where they broke because of load, and then figure out how to deal with it.
Mitchell Glasscock 01:01
Always fun.
Steve Stedman 01:03
Yep, we’re going to see live load tests, live charts, and a feature called the Breaking Point Finder. So, if you need help with SQL Server load testing, you can use the query loading ramp, or you can just reach out to us. We’re here to help. Are you interested in being a guest on the Stedman SQL podcast. If you are, just reach out to us, and we can talk about what topics, things you want to cover or talk about, anything relating to SQL Server, Database Health Monitor, or about our company. You can reach out to us at https:StedmanSolutions.com/guests, or go to the podcast page and click on the guest link, fill out the form and find out if you’re going to be a guest on one of our future episodes. Okay, so what is Query Loading Ramp?
Mitchell Glasscock 01:50
Yeah, so Query Loading Ramp is a new tool that we’ve rolled out with Database Health Monitor. Currently, it is released alongside Database Health Monitor. So if you get the latest version, you’ll get the latest version of the query loading ramp as well. But a little bit of a background on it is: we’ve been doing a few load tests. We’ve been involved in a few load tests for a few clients here and there, and they all use special tools for whatever fits their current situation. But none of those tools have really scratched the itch of raking specific queries that we’re looking for. So we came up with this standalone tool that we’ve released, and we use it to well, like we’ve said a few times, break queries and figure out how we can fix them even better.
Steve Stedman 02:40
Yep. So one of the things we can do with this is we can run one or more SQL queries across n concurrent sessions simultaneously, where n is up to 1000s of open sessions. This is very similar to like if you had web server or a web farm that was hitting your SQL database, which kind of we see a lot of that, and that’s where the kind of the foundation of this came from is that you might have a web server that has 1000 connections on it. Well, rather than doing that, we’ve got an application that’s going to simulate those 1000 sessions doing different queries throughout the system. So, who is it for, really? I mean, who is the optimal client on this?
Mitchell Glasscock 03:21
There’s a few DBAs. If you’re looking, if you have a development team that’s coming to you and being like, “Hey, why is the SQL Server side of this slow? Maybe they’re not as familiar with all the ins and outs of SQL Server. It’s a really good way to put their queries or their stored procedures up to the test. Any database developers, of course, are going to find this handy. Or really, anyone who wants to ask the question: Is this query going to hold up to real traffic beyond me pressing f5 and Management Studio and firing it off once to see how it it goes? You can fire this off over and over and see how it goes?
Steve Stedman 04:01
Okay, so it’s not just a stress tester; it’s really there to produce actionable diagnostics. Yeah, and the thing is, we want to be able to run this and learn as much as we can about what’s going on, and it makes me think. Like years ago, well, I’m gosh, over the years we’ve worked with a lot of different clients, and I remember one client that they would have like a quarterly product release where they were selling something through their store, and every quarter they would have a new release of new products. And what would happen is they would send out a notification that these new things are available in their store, and as soon as that happened, their website would go down because they would hit all these users would hit the site logging in trying to figure out oh what’s the new product can I buy it today right and we did another one where long time ago they were doing some kind of events where they would have something where 10s of 1000s of people would all hit their system at once and well their SQL server crumbled at that point because they hadn’t load test. That are stressed it enough to understand how it was going to behave, and a tool like this can help get you in shape so that you won’t be squeezed as much when that kind of thing happens. I think another thing to add to it, kind of big picture, is it’s not just for breaking the query; it also provides actionable items that you can, if even if you
Mitchell Glasscock 05:25
aren’t doing a separate monitoring on what’s happening to the SQL Server, the query load tester, our query loading ramp will provide action items for you after each load test.
Steve Stedman 05:39
Yep. All right. So on that, shall we jump into the first load?
Mitchell Glasscock 05:43
Yeah.
Steve Stedman 05:44
So what we’ve got here is the query loading ramp application. If you’re used to using Database Health Monitor and you’ve just started up Database Health Monitor and you wonder, well, how do I get to Query Loading Ramp? You would go under Tools and Query Loading Ramp shows up right here. One of the things that I like to run when we’re running query loading ramp is the SQL Performance Viewer. That’s another component of Database Health Monitor that tracks some information that we can use. So we’re just going to have this Performance Viewer running in the background, so we can go and check on it and see some stats a little bit later. That’s a handy tool whenever we’re doing any kind of load testing. So we’ll put that aside for a minute, and then to set the stage here, we have SQL Server Management Studio, and we’re for this test we’re running on SQL Server 2025 Enterprise, and we’re running against a database called Load Test, and in that database there’s a handful of tables that simulate kind of a little business where you’ve had orders and products and customers and stuff like that, and it’s something that I used AI to just generate to have it so we could have a bunch of problematic test queries. And the first one we’re going to look at is this one right here, where it’s getting orders by customer, and this shows that this customer has that had that many orders. So what I want to do is just make sure that I drop the index that I added yesterday when I was testing this before we start our test. Yes, so it is not there. So what we’re going to do is we’re going to grab this query, get orders by customer ID, and we’re going to paste it into Query Loading Ramp. And when we look at that, here it is. And what we can do is we can go and run a load test with that. Let’s say you’ve got a select statement like here’s one. I can just grab that and paste that in here, and we could do both of them as part of it, or we could even put in multiple. And as a little bit later in the demo, we’ll show you how to do multiple queries and have them be weighted up here, as to say like run one of these for every two of the other one that’s being run, things like that. So yeah, you can put any SQL in here that you can you can execute.
So what we’re going to do is just jump right into a quick load test, and we’re not going to start with 2000 because this that could be a problem here. We’re going to start with 400 sessions. The reason I say it could be a problem is because this is a known problematic query on a test bad database that was built in order to show performance issues and simulate real live customer load. So in here we have 400 sessions. Ramp up time is one minute, so it’s going to over one minute slowly ramp up to that 400 sessions. We’re going to let the query run for one minute. We’re doing this really briefly here because we’re on a podcast. You don’t want to sit and watch and wait for this to spin for 20 minutes while we let it run. Think time is time between queries being executed on each thread, and then think time jitter 5% What that means is adjust that think time a little bit so that we’re not on a cycle where every single query is hitting on the exact same millisecond when it gets executed. So it kind of shakes up the load there a little bit. Max SQL Server CPU of 90% Let’s set it to 90-five, and if what that’s saying is that if the CPU on the SQL Server goes over 90-5% assume that it’s not going to be able to handle much load, and any sessions above that will be aborted. So before I put that setting in, and it would hit 100% and it would just keep throwing stuff at it, and it would just crumble the SQL Server at that point. So we don’t want to take it to the point that it completely locks up and takes down the whole SQL Server.
Steve Stedman 09:26
We just want to take it to the brink of that happening. And then command timeout in seconds. This, if any query takes longer than 30 seconds to run, consider it a fail. So then we hit run load, and what it’s doing at this point is it’s going out and spinning up sessions, and if you look at the active sessions panel in the middle, you can see that that’s climbing. We’re up as well as the green bar at the bottom that’s showing it climbing. We’re up to 120, 130 out of 400 sessions that are going to be run. Now, what you’re going to see if you put in numbers that are more than your SQL Server can handle is that at some point that active sessions will stop climbing before it gets to the goal of 400 or 2,000 or whatever it is you’re shooting for there. The other thing we’re seeing here, well, Mitch, what else are you seeing as we look at this that stands out as interesting?
Mitchell Glasscock 10:23
Well, there’s a few different ways to that this can show where you’re going to start getting squeezed. Like you said, a general one is active sessions. It will peak out and then eventually start decreasing again. Each chart.
Steve Stedman 10:40
Just to jump in, right as soon as you said it’ll peak out, we hit the end of our ramp up time, and now it’s going into the sustained load where you can see that active sessions is staying at 400 at this point.
Mitchell Glasscock 10:52
Yeah, yeah. So each chart kind of has a different. I don’t. I would say peak like where you’re going to see. Oh, the SQL service had too much. If you’re looking, if your query is hammering the page life expectancy, it’s throwing too much out of memory and eventually back into disk. You’re going to see that that that PLE chart in the bottom left is going to collapse, and that’s going to be another issue, or the throughput you’re locked out on that. The throughput is going to decrease, or your CPU is going to pin out to 100 or 95 like we’ve said it to. So each chart is going to show a slightly different error, you could almost say, as far as what the failures could be.
Steve Stedman 11:43
Yep. So at this point, we’ve just come up to the end of the load test. We had three errors along the way. We can see what those errors were. So three out of 17,000 queries failed for some reason. We don’t know why. We’d need to look into why on that. We can look at the results down here, and just here’s the overall chart. And what’s going to happen as we run this test, different test, multiple times here, you’re going to be able to compare the results. Things like 17,000 queries completed in that minute of ramp up and minute of runtime, with 46 millisecond as our P 95 or 95th percentile. 95% of the time, queries are finishing quicker than 46 milliseconds. And on that previous screen, we show a few different of the P numbers. You’ll have your P 99 which is 99% of the time queries are finishing. I can see 27,000 milliseconds, so about 2.7 seconds, which is not good by any means.
Steve Stedman 13:01
Yeah, and what’s interesting, you say it’s not good. Well, it’s good that we’ve found this. It’s good that we understand our baseline now, but it’s not good because what we’re missing is, I mean, we can’t handle load. And the key here is overall, the sustained throughput was about 120-five to 150 connections per second. So if you’ve got that big website sale and your website is opening up to more than 125 queries per second, and keeping in mind this is queries just for this one query being run. If there’s five other queries being run on each page load, you can have those kind of things to consider too. But what that’s showing is that that’s where it’s going to max out. And frankly, 125 a second is not very good for SQL Server. I mean,
Mitchell Glasscock 13:48
all of these charts kind of paint a picture too. You have to you have to look at everything in one. You have to encompass everything. So I mean, a single user waiting 2.7 seconds for their orders. If if this is someone using web app, that’s slow. I don’t want to wait 2. Almost three seconds for that to happen on a on a web store.
Steve Stedman 14:13
Yep, for sure. So let’s take a look at another option here, and then what we’re going to do is run another load test. So the thing is that one of the things that you may look at here, if you know how queries work, that running the same query over and over again for customer ID of 42 might skew the results. Meaning that if the orders table is really big and all of the orders for customer 42 were five years ago and they’re all in one section of the table, well, it might not be really simulating real load. So what we’re going to do is I just ran this query: select distinct customer ID from orders. I’m going to go in here and copy with headers. What I want to do is copy that entire 99,994 customer IDs. And then I’m going to go back to Query Loading Ramp and paste those in as parameters. They were already here before for my testing, but I’m just going to clear it out and then paste the new ones in. And what it’s saying here is you can use this tag, which is customer ID in a curly bracket, and in place of the actual number that’s there. So instead of a customer ID 42 we’re going to use dynamic customer IDs, and then we’re going to say down below here it says there’s 990 9994 rows in the column customer ID. So I’m going to test the query, and here it goes. It tested it with five different customer IDs, and it showed that, yeah, the first time it was run, it took 165 milliseconds, and then it took about 20 milliseconds, 15 to 20 milliseconds on each one after that. But let’s run this one more time like that, and let’s go. Let’s go for two minutes this time, and we’ll run the load. So at this point, it’s same test as before, but the only difference is instead of always looking up the same customer ID of 42 it’s passing in a different customer ID every time that query is being run.
Mitchell Glasscock 16:25
So in this exact example, I think it would be pretty safe to say that the entire table can be stored in memory. So we’re not going to be pushing out of memory at this point into TempDB, adding those parameters. could, on a bigger table or a larger query, definitely could start pushing stuff out of memory, right?
Steve Stedman 16:51
Yeah, I mean, let’s say you had, I don’t know, 20 gigs of RAM. Now, let’s say you’re on a small SQL Server with just like a couple gigs of RAM. Don’t do that. But let’s say you were, and your table was four gigs, and you only had three, two gigs of RAM. Well, in order to scan that whole table, it’s going to cause some things to be chucked out of memory and have to be loaded in from disk, unless you’ve got the right indexing in place, of course. But that’s where your page life expectancy would tank at that point. So, looking at this load, we’re seeing about a sustained load of about 150, which is just a hair higher than we saw last time. I think we’re in the 125 range, so not really that different. Our CPU has jumped a little bit higher this time than we saw last time, and this test is going to run for a little bit longer. And the reason I’m letting this one run just a little bit longer is that there is additional reporting that we can show in here once the load test is complete. The other thing is we’re seeing down below on the query latency that 95% of the time the queries are finishing in about two and a half seconds or less, but what’s interesting here is we’re starting to see errors now, and our active sessions is starting to drop off. So something has happened that’s maxed out the capacity here. Timeouts-we’re getting timeouts. So there are some queries that are timing out when this is trying to run, and what we want to do is let this finish. I’ll make this a little bit bigger here so we can see all the results. So there’s about 20 seconds or 15 seconds, I guess, left in this load test. We’ll let it finish, and then we’ll take a look at the results and see how that compared to the previous one.
Mitchell Glasscock 18:39
So overall, from the the average query time to our P 50 or P 99 are you seeing anything that would be really concerning? Or they’re all kind of they’re all fairly similar. I think to me, it’s it’s just not good in general across averages.
Steve Stedman 18:59
Right, it’s not good. I mean, on average, we’re in two and a half to three seconds for the query to run, and that means that every page view on the site is going to be two and a half to three seconds slowed down because of that. And that might be just one out of 50 queries on a web page, right? Yeah. So let’s look at the results, and we can compare the results here between the two runs. Both of them, we ran 400. The runtime was a little bit longer on the second one. The average queries per second pretty close. I mean, the first one was better. The second one a little bit worse. Average of about 2.3, 2.2, 2.3 seconds per run, and yeah, just not very good numbers overall. So let’s go ahead and close that, and let’s take a look at a different tool in here, and then we’re going to look at how we can make this query faster. So we have a tool called the. Breaking point finder. So the question is: We just ran 400 sessions. Is 400 sessions enough that that would have maxed out or broken the server? Well, I don’t know, but let’s run a test here to see what will break it. So we’re going to go at max 1,024 sessions. Just happens to be a power of two, but it doesn’t have to be. Seconds per step is 20, meaning we’re going to run each step for 20 seconds. Warm up time for each step will be five seconds, and then the think time will be two milliseconds. So we’re going to be doubling. We’re going to run one session, see what our whole throughput is. Two sessions, four, 816, 30-two, and so on. So then, what’s going to happen is the query is going to run, and it’s going to run one thread for 20 seconds and see how many, how what the throughput is going to be, and then it’s going to run two and see what the throughput is, and three and four and five. No, not that’s wrong. It’s going to run one and two and four and eight and 1630-two powers of two going forward. Here, with one thread, it completed 487 queries in with an average of 14 milliseconds.
Okay, now it’s running two in parallel, and what it’s going to do is it’s going to compare these, and if you look at the charts down below, the orange is the P 95 latency, meaning the first one was 22 seconds or 22 milliseconds, and it jumped to 25 and we should see that jump over time as there’s more of them being run in parallel. The blue is the actual throughput of how many were run, and with four of them in parallel, it was able to complete 15 120-one in the 20-second runtime.
Mitchell Glasscock 21:52
So it’s almost doubling how much it can’t do, but it’s dropping off each time by a little bit.
Steve Stedman 21:59
Yes, until this. this one, it wasn’t almost doubling. It went from 1500 to 2000 which isn’t even close to double. Interesting. As soon as you said that, Mitch, the pattern changed.
Mitchell Glasscock 22:13
Well, we might be seeing the end of this test pretty soon then.
Steve Stedman 22:16
Yeah. So there’s the the term the knee is where you get the absolute best performance before it breaks, and that’s a load testing term. So what it’s showing here is that on this query, it plateaued out at 16 in parallel, and that the throughput we were getting the absolute best throughput at eight. So that’s not really very good overall. So let’s go back and see what we can do to fix this query. So back to Management Studio. What we did is we went and looked at the query, examined the plan, and all that kind of stuff, and we came up with this suggested index that be added. This one here. We will add that index, and then what I want to do is I want to run the same breaking point finder again after adding the index, and see how it changes. But before we do that, I’m going to save this report out to temp directory, and then we’re going to run the breaking point finder again.
Mitchell Glasscock 23:27
So one of the settings that I saw on there, it was doubling or fixed. What’s going to be the difference between those?
Steve Stedman 23:37
So if you set it to fixed, it would like increase by whatever your fixed value was, like five, five sessions, 1015, 2020-530, By doubling, you’re going powers of 2248, 1630-260-four, 120-eight so on like that. And so you’re able to. I mean, it might be if you were doing a fixed amount that you might run for hours before you found that breaking point. But with a small fixed amount, you’re going to be more exact on that breaking point, whereas if you’re doubling, it’s just going to give you kind of an overall estimate of where that breaking point
Mitchell Glasscock 24:06
is. Right. So once you get in those higher numbers with a doubling breaking point, it’s a rough estimate of how much can be done at that point.
Steve Stedman 24:16
Yep. So what I’ve done is I’ve brought over the previous report that I saved as an HTML page, and just to compare them side by side as we’re running here. So the previous run, we got to eight. That was the knee. 16 was the plateau, and then it gave up at that point. So we’re letting this run. Looking at the throughput before. before adding the index, we were getting about 30-two queries per second, then 50-nine, then 101 and three 130-six and so on down there. Well, look at our completed in that. Let’s see, queries per second: 50-five, 102 160-five 412 So we’re much higher now. We’re up to 780 queries per second, much higher than we saw before. Interesting. Something’s jumping around a little bit on latency there. I hope there’s, gosh, it’s a test server. There could be someone else writing something on it too. And that’s just a side note. You want to make sure that there’s not a lot of other stuff going on in your test server when you do this. They could throw off the test. But in that 22nd test, it’s showing it completed 11,000 Now it completed 20,000 in that next test, and so the queries per second were at 13 150-nine and it’s continuing to climb here, way better than what we were seeing before. I mean, before the charts peaked at about 140-seven almost 150 and now we’re up at 3000 per second queries per second. Way more throughput than we were able to see before. We were able to see that adding that index had enough of an impact on the system or on the query in order to be able to know that it was helping.
Mitchell Glasscock 25:59
So we had since we built this database, we had the actionable item on what to fix with that specific query. If this was a a server where we hadn’t built it or not a test database, how would one find those actionable items if it’s an index or some code changes, where can they look for that kind of stuff?
Steve Stedman 26:26
Well, you could use the query performance program here, and we go look at like long running queries. But high CPU is what I was looking at, and under load test, well, it’s not formatted very well here. But if we right clicked and we don’t have the execution plan on that one. That’s some blocking tests that we’ve got. Yeah, there’s other stuff going on here. So that’s where we would go and look at the execution plan. And when this finishes, we’ll go take a look at another way to do that. So this time, our knee was at 512 sessions instead of eight, so that’s a significant improvement. Our plateau was at 1024, and our max that we were getting throughput was about 14,000 queries per second, pretty good compared to the 136 queries per second that it maxed out before we added that index. Okay, so let’s go back and let’s run this load test again, and instead of 400 let’s go to 2000 Whoops, 2000 because I think that this can be more of a load than what we saw before. I know if we’d done 2000 with the previous query without the index being added, it would have just peaked out and crashed at that point. But let’s see what happens now. So it’s a ramp up time of one minute, and then after that, it is running for two minutes.
Mitchell Glasscock 28:10
It’s already above 30,000 queries completed, so that’s way better than what we were seeing the first time.
Steve Stedman 28:19
Yeah, for sure. And look at our throughput. Our throughput’s up to almost 5000 And what did we say we saw on the breaking point finder? We saw the throughput was around 14,000 So let’s see if we can get above 14,000 with this load test here. This might be throwing more of a load at it than it can handle. And where we would see that if it’s throwing too much of a load at it is that we would see the active sessions peak out and then start to drop again.
Mitchell Glasscock 28:46
Another kind of fun one to see is all the P 99 and the P 50 numbers compared to 2.7 seconds. We’re looking at 50 milliseconds, 99% of the time.
Steve Stedman 28:59
Yeah, 50 milliseconds versus almost three three seconds. Yeah, that’s a significant increase. So right now it looks like we’re starting to see a peak on the throughput on queries per second. Now that peak could be just because of how many sessions we have thrown at it now, but yeah, we’re somewhere around 8000 It looks like per second, and we’ve got approaching 600,000 queries that have been completed so far in this load test. Oh, something just happened. The throughput just dropped massively. Okay, and it’s starting to come back up. So that’s one of those things that I don’t know what else we’ve got going on the server. I hope not that much, but we did hit some errors along the way there.
Mitchell Glasscock 29:56
Where would you recommend running the load tests? From would you say right on the SQL Server itself, or where your web host server is, or run the application from say your remote desktop somewhere?
Steve Stedman 30:14
Yep. So that’s a good question. It really depends on what it is you want to be testing. I mean, right here I’m running it on my desktop, connected to a SQL Server that’s on the same network in my in my home office. Here should be a pretty good network connection. Although I am running over Wi-Fi on my laptop running this, so that could impact it. I think that if you really want to test the absolute overall throughput of the SQL Server, run this on the SQL Server or as close to the SQL Server as you can get, and then that will eliminate things like local network traffic or other things like that. If you’re trying to simulate what’s the maximum load that could be coming from your web server, well, you could run this app on your web server and see how things compare at that point. If you’re looking for, well, here’s what you shouldn’t do. I was working on this remotely, and I had a Starlink connection that wasn’t in the best overall Starlink speed. Although I love Starlink, it’s great speed usually most of the time. But I was in a kind of a slow location at that point in time, and I ran this, and the throughput was significantly lower over my VPN going across Starlink than it was being in the same office connected or data center connected to the SQL Server. So don’t do it over a slow remote connection, because that won’t really slam your server.
Mitchell Glasscock 31:40
There’s other bottlenecks beyond SQL Server that you just have to be mindful of while running this.
Steve Stedman 31:45
Yep, yep. So I forgot to show this earlier on the other when we ran it earlier, but there’s this peak plans button down here. Once the report is done, if we go and look at peak plans, well, because this one was so efficient, there weren’t very many showing up, so let’s do something here. Let’s add another query, and then then run the load test again and see what we can what we can do here. So we’re going to go back to this prepared query. Get order items by order, rather than having an order ID hard coded in there. I know that there are orders between zero and a million in this table. So, what I’m going to do is use this absolute value of the checksum of a new ID mod 1000 to generate random numbers between, sorry, not 1000 a million between zero and a million, and then feed that into the get order items by order, and let me just show you an example. Every time we run this, we get a different set of results.
Mitchell Glasscock 32:49
So it’s more or less another way to to generate parameters without pasting a parameter table into there.
Steve Stedman 32:56
Correct. Yep. Yep. So then what I’m going to go here is I’m going to say add. I’m going to add another query in here. There’s my query, and then I’m going to test it. There we go. It took 173 milliseconds to test it. So then, if I leave these both enabled and weighted the same, I’m going to run the load test again, and it’s going to do 50% of them are going to run one query, and 50% are going to run the other query. Let’s not go 2000 because I know this one’s going to break. Let’s just go 1000 and see where it breaks, and then we’ll run the load again and watch and see what happens on this test. So here, three minute overall load test. In reality, I like to run them longer. If we’re doing like real customer stuff, I mean, five to 10 minutes will give you better details. But the podcast would have gone really long if we made you wait for eight different load tests that each took five minutes to 10 minutes.
Mitchell Glasscock 33:57
So we’ve been running pretty quick ones just for the sake of the podcast. Could I set this to run for five hours and just leave it overnight?
Steve Stedman 34:08
Yes, and not to jump the gun here, but we do have a demo of how you can run it and like on a scheduled task, you can kick it off in headless mode, which we’ll get to that in just a moment. But I want to see this load test finish first, but what we’ve got now on the latency down below is we can see there’s two different latencies that we’re getting there. There’s query one and query two in the blue and the red, and you can see that query two is much more poorly performing versus query one, and our overall throughput combining both of these queries has dropped down to about 150, rather than up around 8000 like we were seeing before.
Mitchell Glasscock 34:44
We can see the active sessions dropping off there too.
Steve Stedman 34:48
Exactly. I was about to say that as well. We we hit our peak and it’s been dropping ever since, and now we’re starting to see errors where things are timing out. Yeah, because. Connections are being used up, and they’re not able to. Yeah, they’re timing out. So we’re going to let this finish, and when this finishes, I want to go look and see what we can learn about this query, pretending we don’t know what index it needs.
Mitchell Glasscock 35:15
Beyond, so we do know we’ve kind of prefaced this one. We do know that this query specifically is missing an index, and it would benefit from that. Are there other recommendations that the load tests can make beyond indexes?
Steve Stedman 35:32
Yes, and part of that we’re going to see in just a moment with our plan analysis. So we’re going to be able to look at the execution plan and learn from the execution plan, and I was doing this yesterday with a client that they were having a really long running query, and we went and looked at our execution plan analysis, and it just completely blew his mind with being able to show all of the reasons that his query was running really slow.
Mitchell Glasscock 35:58
And I guess for some clarity, if you’re this isn’t a execution plan like you would see in Management Studio, this is an execution plan put into plan language, right?
Steve Stedman 36:10
So here’s the deal: execution SQL Server generates execution plans, and it’s XML that tells you everything about the query. Oh, our plan button is gone this time. Maybe if we got too many errors, let’s run this one more time. Let’s just go with like 400 sessions for one minute each. That might be a bug we’ve discovered there that we need to fix, Mitch. So this will run for two minutes. But back to your question about well, is it the same plan we see in SQL Server Management Studio? It’s the same data behind it, but we choose to represent it and show things differently compared to what Management Studio chooses to show
Mitchell Glasscock 37:01
it’s a bit more digestible than crawling through management studio plan. Of course, there’s nothing wrong with knowing how to go through that execution plan, and you should be able to. A good DVA will know how to read through that, but putting it into plain language to show others is also a very handy tool. Is here’s another question: Is that kind of plain language execution plan that we show in this program limited to this program only? Or say I have an execution plan myself that I pulled off of my own testing in Management Studio, and I’ve saved it. What can I do with that?
Steve Stedman 37:46
Well, that’s a great question. Let me show you. If I start up our while, we’re waiting for this query to run. We got another minute here. I can do a quick plan viewer demo from the start menu. I went to Database Health Monitor and chose Plan Viewer, and here is a plan that was sent from a client running Database Health Monitor, and it was something that they found in Database Health Monitor that wasn’t behaving well. So you can take any SQL plan that you save out of SQL Server Management Studio and open it up in our plan viewer, and get all kinds of information here about what’s going on with it, and recommendations, problems, clustered index, scalar UDF, function applied in the column and the predicate, all those kind of things. And so, yes, this is available there. It’s also available in Database Health Monitor. There are many places that we will now show you the plan on the query. Okay, the load test finished. Good commercial interlude there. Let’s go look at the peak plans and see what we saw. So we can see the query that keeps showing up is select all this stuff from order items, and if we double click on it, there is our plan analysis. And what’s interesting is this is the test, so there’s also the fix for it we had in our comments here from earlier. But we’re going to pretend that that comment is not there telling us what the fix is. But it’s get order items by order number, and it’s doing this select statement to do that. The reason that that’s slow, well, 100% of the total cost is associated with that, and it’s equivalent to almost 4,000 simple index lookups with the amount of work that’s being done there. And the clustered index scan, clustered in well, this table only has a clustered index on it right now, which is not recommended, and then here it is. It’s recommending that we add this index from the plan analysis. We have analyzed what’s in there and present this index as an option to add to improve the overall throughput. Shall we just take that index as it suggested there mentioned?
Mitchell Glasscock 40:01
To try, I would say no. I mean, I know that index is going to work. I would say don’t always do that. You got to know what you’re doing, what the index is. Look at look at it before you do it. But
Steve Stedman 40:14
this one is good, Dad. Okay, so let’s go look at it then. We have the table order items, order items. items indexes. Oh, there’s only the primary key on it. Okay, so the primary key is probably on the order ID or order item ID. Yep, there it is. And so this is on orders ID. So I would rename this to like order items order ID, and I think looking at understanding the structure of this table, that’s probably not only a safe but a smart index to add. So never just blindly grab it and run it, though. That’s a good point.
Mitchell Glasscock 40:53
Instead of looking in the Object Explorer at that table too, is there another way?
Steve Stedman 40:59
Oh yeah, we can go to order items here and hit Alt F1, and that gives us information on the table, and we can go down and see that here’s all the indexes and what keys are them in them, and we can see also that the one we just added is listed there as well.
Mitchell Glasscock 41:17
That’s one of my favorite magic hot keys that nobody really knows about.
Steve Stedman 41:23
Yeah. Okay. So the index is added. You can see that this at peak runtime, these are all the queries that we’re taking time, and they’re all showing the same plan. So what we’re going to do is we’re going to close this and just run the load test again. Let’s do it one more time with the exact same settings, and it’s one minute ramp up and one minute runtime, and we’re going to see how it looks. Well, the thing I’m seeing right off the bat was that our throughput on the last test? I think was maxing out around 150, and now we’re already up close to or approaching 750. So yeah, things are running a lot better this time, from what I can see. Same thing with latency. Yep, yep. Latency. I guess I should size the window a little bit larger so we can see all the reports.
Mitchell Glasscock 42:35
And that CPU, the CPU is staying below 25%
Steve Stedman 42:41
Yeah, and that’s the thing that we see a lot of the times with load testing is that we either run out of CPU or we hit a lot of blocking. One or the other, usually. And considering that the price of your SQL Server is often tied to the number of CPUs cores that you have being able to reduce CPU on your load overall reduces could reduce your cost of your SQL Server if you reduce the cores, or it would prevent you from having to grow to that next level. So many times I’ve seen people see, well, we’re 100 CPU. Well, let’s just upgrade to that next more expensive server, or we’ll have we’ll upgrade the number of cores on the SQL Server, thus upgrading your SQL Server licensing at the same time. Well, it’d be a lot cheaper to throw an index in the right place or to find what’s wrong with a bad query and fix it than it would be to double the price of your cores in your SQL Server. Yeah, I think it’s not often weighted enough is the cost of the physical hardware, or if you’re on something like Azure, the cost of these resources isn’t cheap. You can’t just max out a SQL Server and then say, “Oh, let’s just double the size. It’s oftentimes not cost effective,
Mitchell Glasscock 44:00
especially with the cost of everything going up too, so having someone on your team or knowing who to call to say, “Hey, we need to reduce these costs. It’s not just a “Hey, we’ve made the server faster. It’s an actual tangible cost benefit that you can reap by making things faster.
Steve Stedman 44:20
Let me give an example of that. One of the clients that we work with regularly, in the first six months of working with them, they had on their primary SQL server 96 cores on two processors, and that was quite spendy. Let’s just say on what it cost for their what is it called SPLA licensing or whatever for that server, and after working with them for about six months, we had reduced the load so much by performance tuning that they were able. And this was a physical server; they were actually able to go into that data center on a maintenance window, open up the server, and physically pluck out one of the two CPUs, reducing the overall number of cores. To 40-eight, almost cutting the cost of their SQL Enterprise in half. That’s the kind of win you can get with the right load testing and performance work. So let’s look at the peak plans now. So there are no plans showing up in this case that are problems. Well, there’s these two other ones on the blocking test database that I know about because that’s another test that’s running. But if we wanted to look at the plan, you go and see. Well, here’s what the plan looks like on that one too. But what that shows is that the index we added fixed the overall performance issues that were bogging things down there. Okay, so let’s take a look at another option. I’m going to just run a Windows command prompt, and we’re going to run in headless mode, as it’s called, or command line mode. If you go to the menu, you can choose command line mode. It will pop up this help file that tells you what you need to run it. You of course need the full path to run the executable if it’s not in your path. Oh, the QLR file. That is, if we go here and we save our configuration, and I’m just going to save this over podcast. QLR, and it’s going to save all of our queries and settings that we have here into that query load ramp file. Then we’re going to pass in sessions, ramp up, runtime, think time, jitter, and an output file. That’s all the stuff that we would put in through the dialogs if we were starting to load test. Somewhere here, I have the prepared earlier, so we don’t have to actually type all of that. Copy that, paste it over here. Runtime one minute, ramp up one minute. So this is where if you wanted to run for like an hour, you could put in a runtime of like 60 minutes and let it run for an hour, and then come check on this tomorrow morning or something like that, or even run it for six hours overnight. But we’re going to run ramp up a one minute runtime of one minute just so we can get through this podcast without you having to watch through six hours of load testing, and we’re just going to 200 sessions. But what we’re seeing here as this runs is the output is showing we’re at 54 out of the 274 out of the 200 But we’re seeing that it’s already run 31,000 queries, with queries per second of being 4050 600 queries per second. So we can see there’s a lot going on here. The overall CPU is at 5% and there’s no errors yet.
Mitchell Glasscock 47:48
It’s a good thing we put that index in place.
Steve Stedman 47:52
Yes. So what you can do with this, I mean, imagine if there’s some queries that you’re responsible for, if you’re the DBA or the developer, and you know you’ve got these three or four problem queries. Like this is what happens when users are logging in, or where they’re looking up a product, or whatever that thing is that’s the bottleneck on your server. You could put those into this load test, save it off as the QLR query load runner file, and then schedule that to run every night as a scheduled task. When it runs, it finishes and it saves off an HTML file that’s time-stamped, which is what we’re going to see right here with this ctemp podcast.html. And you could then have a way that if that was running every night for let’s say 20 minutes, you could go back and look over time and see are things getting better or are things getting worse with this query, or these queries that are the problem areas? So, you kind of give an example of you know a developer knows what the problems are and they’re running tests as they continually change stuff. What if there’s complaints of stuff being slow, but you don’t know what is actually the root cause of that being slow. Where would you recommend the first place to look? Say, hey, I know I have problems with this SQL Server, but I don’t know where these problems are coming from. That’s where I would start with Database Health Monitor, and I would go look for things that we normally look at, like blocking queries, like long running queries, like queries that use a lot of tempdb like queries, I that’s what database health monitor is built for is how to go and find that kind of stuff.
Mitchell Glasscock 49:28
Yeah, I guess that’s a good place to plug. We have plenty of other podcasts that cover the topics of how to performance tune with database health monitor specifically.
Steve Stedman 49:38
Yep. So what I did is I just copied that path to that HTML file. We’re gonna open up here, and here are the results of that load test. It was run command prompt in headless mode. You could put that in a scheduled task nightly. It was run two queries, 50% each on the waiting, and total elapsed time was. Two minutes, 790-one queries completed. Average query time nine milliseconds. Well, if you run this every day and you realize suddenly that that nine milliseconds jumped back up to three seconds, you know something went wrong that needs fixing. Comparison with the queries on the average milliseconds and all that. Here’s the latency. Here’s the throughput on queries per second, and yeah, that’s good information to have there, so you can go back and compare over time. The whole thing about load testing or any type of perform any type of performance work like this is being able to have really a baseline, and to be able to establish that baseline and compare it over time. And if your baseline is your queries are taking three seconds, and you can get them down to 15 milliseconds, well, then you’re the performance hero at that point. Okay, I think that Mitch, I think that covers everything we wanted to cover today, doesn’t it? Is there anything else that we that I missed that we should cover?
Mitchell Glasscock 51:03
I think so. I’ve the only other thing I’d throw out there is if you’re not sure exactly where to start, we’re always happy to help. If you’re if you’re running the issues with your SQL Server and you know that you could benefit from a load test, you know you have queries that need to be checked, but you’re not sure. You want some outside eyes? Contact us to look at it.
Steve Stedman 51:27
Yep. And back to your question about how do you find these? Well, database health monitor. Here’s a quick word from our sponsor: Is your SQL Server slowing down your business? Slow queries, bottlenecks, and unexpected issues eating up your time. Introducing Database Health Monitor, the powerful tool built by our team at Stedman Solutions. Get real-time insights into performance with over 100 built-in reports, index analysis, weight stats tracking, and proactive alerts. Quickly diagnose and find problems before they impact your operations. with an easy-to-use interface for DBAs and developers monitor unlimited servers, tune queries, and keep your databases running healthy and fast. Download a free trial today at databasehealth.com and take control of your SQL Server performance. Okay, so if you’re watching this live, well, it’s Database Health Monitor Month, and we’re offering annual subscriptions of Database Health Monitor at 25 off for that annual subscription. And with Database Health Monitor, with that 25% off discount, you get the query loading ramp in order to be able to do anything that we’ve shown you here on the demo today. Anyway, finding out what the breaking point is is really critical in this type of load testing, and figuring out where that breaking point is and being able to push it further is always a good thing.
Mitchell Glasscock 52:58
It’s another tool to throw in a DBA or developer’s arsenal of tools, especially when the arsenal comes from the database health monitor development team, we’re wrapping up tools that we use. That’s the biggest point I think is we use these tools as SQL Server experts, and that’s why we put such a golden endorsement on them. It’s not just pumping software out there.
Steve Stedman 53:24
Yeah, and that’s the thing. When we first built this, I built this not as like a gee, let’s build something and see if people need it. I built it as I need this. I need this now to be able to test and performance test and figure out what’s going on with the client environment. So let’s build it and figure out how to use it. And everything here is built because we needed it. It’s not purely speculative, hoping someone’s going to use it.
Mitchell Glasscock 53:49
Yeah, for sure. And that’s the that’s the same rubber stamp that we put on every every tool that we have. It’s because we use it. It’s also shaped by the community too. So if you have ideas for database health monitor, a really cool feature you’d like to see inquiry loading ramp, reach out to us. We we love hearing from the community and how it’s both helps and how we can help more.
Steve Stedman 54:14
Yep, you know it’s interesting because I get to see how excited Mitchell gets when we get an actual request for yes feature in database health monitor that a client needs, so it’s always fun to do those. Okay, so I think that wraps it up. If anyone needs any help with load testing or performance or anything with your SQL Server, you can always reach out to Stedman Solutions. Can help. And thanks everyone for watching and thanks for joining me, Mitch.
Mitchell Glasscock 54:37
Thank you.
Steve Stedman 54:54
Thanks for watching our video. I’m Steve, and I hope you’ve enjoyed this. Please click the thumbs up if you liked. And if you want more information, more videos like this, click the subscribe button and hit the bell icon so that you can get notified of future videos that we create.