In this episode, Head Geeks™ Patrick Hubbard and Thomas LaRock will walk through the latest version of Database Performance Analyzer, as they explore advances in query performance insights, PerfStack™ integration, and the brand new table tuning advisor.
Welcome to another episode of SolarWinds Lab, Databases & DPA 12.0: Tune Tables Too! Where we’re going to talk about Tom’s favorite subject–
Okay, somebody’s just a little bit excited about this today, and stepping all over–
Yeah, you doing the intro, I know, and I know that. But ever since we released the new version of DPA, I just find myself being more and more–
Proactive, saving time and money.
Okay, so you two are kind of freaking me out just a little bit, so what you’re really trying to do is, are you trying to be proactive, or are you just–
Just finishing your sentences, yes. ‘Cause we don’t want to waste any more time on this intro. We want to get right to talking about DPA, and how it’s going to–
Cure your polio. If you had polio, which you don’t.
And see, now there’s one of the issues you have with traditional tools that claim to be proactive. You tried to finish my sentence, but you weren’t exactly right with what I was thinking. That same idea is applied to folks and tools as they spend their time on activities they think are proactive, but they’re really not. You need to know if the time you’re going to spend turning all those nerd knobs, if it’s helping or if you’re just making more work for yourself.
Right, because when you really start monitoring databases, not just sort of watching the CPU and memory for the server, for better or for worse, your blissful ignorance really disappears.
Because of the insights being collected and displayed, which results in your being aware of what’s truly happening under the covers.
And once you have that awareness, you need to decide what to do next. What action is the right action. What change can I make that will have the most impact?
And that’s why we always recommend the same three easy steps for identifying and finally resolving problems. For real, monitor it, map it, and then prove it.
Yup, and that’s why we’re here today, to show you how to use the new features in Database Performance Analyzer you can now use in the latest update, DPA 12.0, features that reinforce those three ideas.
Right, so, query performance insights, the new PerfStack integration enhancements, and my favorite new feature, the Table Tuning Advisor.
Right, and one last thing before we dive in. This is going to be a pretty dense episode with a lot to unpack, and we know that you’re going to have a ton of questions. So be sure to really come up with some good ones, and pop them over to the chat window over here to the right, and we’ll answer them live. Now, of course, if you don’t see the chat window, it’s because we’re not live for you, so swing by our home page, which is lab.solarwinds.com, check out the schedule, and be with us live next time. All right, Tom, Brian, you ready to go?
Yeah, let’s do this.
Let’s do it.
Okay, so once upon a time, I guess this is going to be story time, all right? This is in another century. When I wrote my first SQL query, I was amazed that it ran really well. The first big SQL query. Well, it didn’t run great until statistics ran that night, and then it ran great after it fixed the execution plan, right? But over time, it started to get a little bit slower, and then it got to where it was painfully slow and other dependent applications started to kind of suffer, and so that’s when I started learning about indexes. And I added indexes, and then it started running much better. And I added a few more, and it ran better and better until it didn’t.
Okay, so what did you do then?
Well, then I just started making changes to my queries again and modifying indexes and got it working again.
Did you happen to notice if your indexes made other queries run slower?
Other queries? No, no, no, no. When you do your first database, there’s just your database. You don’t care about anyone else’s query.
So, that’s the truth for a lot of SQL developers and DBAs out there as well. They will focus efforts on individual queries, right? That’s where they spend a lot of their time, but at the end of the day, you start to realize that tuning that specific query may not be where you want to be. That may not be where you want to spend your time. What you might want to think about, say, is tuning the table itself.
Okay, but how do you tune the table? That would mean you’d have to be aware of all the other queries running on that system. You’d need to know metrics for how often they were being run and a whole lot more.
That’s right. So let me show you how to get that information from DPA.
Ahhh, DPA 12.0. You know, it’s great when there’s a major version released, right?
All right, so this is DPA 12.0. We’ve logged in, and we’re looking at an individual instance. And this is the trends page. This is what you’re used to seeing. If you look up here, we’ve added this cool, new tab called Tuning, so obviously when we drill down we’re going to see some tuning information here. And what’s great about this page is it actually shows two sections. One is the Query Advisors. This is what you’re used to. You’re able to see which queries are taking a long time to run, they got a lot of wait time. You can see what’s wrong with them. But there’s this new tuning over here, the Table Tuning Advisors, and this actually shows you the tables in order, so they’re ranked, that have tuning opportunities in them.
Or based on expected performance improvement.
Exactly. And so we’re going to take a look at the orders table here. Now this has one day and six hours of wait, excluding blocking, so people are waiting that long for query set here.
Yeah, people always wait that long.
They always do, yeah. And 70 queries may benefit from tuning, so there are a lot of queries on this table that look like they’re poorly written, and it has five index recommendations, so SQL Server’s actually telling us that we should do things on this table, so let’s go ahead and drill in. All right, so this is the Table Tuning Advisor. On the left-hand side is a list of the 70 queries that need to be tuned.
Go to this one. Four inefficient steps.
Four inefficient steps, let’s take a look at that one. So here’s the SQL, we can see how much time it takes, and what we’re doing in DPA is we’re actually analyzing the steps within the plan, so we’re not just taking a look at overall time, we’re actually going in here and saying, hey, this thing pulled back 15 million rows, and it’s doing this multiple times. You can see this is super inefficient, and we can see that this is a clustered index scan, and if you don’t know what that is, you can click on here and we’ll tell you what a clustered index scan is.
Oh, that’s nice.
Now, so what should you do about this? As a DBA or developer, you really need to know more information. This is not just something you go and do. So, what we’ve done is we’ve actually augmented this with information about the indexes and the table itself, so we can see the indexes that are already here, what columns are in there, how big it is, is it fragmented or not. Those types of things. So really, the idea is that we pull together all the information that you need to start actually planning on what you’re going to do about this particular query.
So, this query says recommendation, so let’s see what that shows us.
All right, so this query actually has both a SQL Server recommendation as well as the analysis that we did. Now this is SQL Server is telling us hey you need to add this index. If we click this, we can actually see this.
Dark theme alert.
Dark theme alert. You can copy this and paste this and execute it, which is right from SQL Server, but you can also see that we’ve backed this up. So if you were concerned about, like, hey, should I really add this index? DPA is actually confirming that, hey, we see the database doing a lot of work but not returning a lot of rows.
Right, ’cause up here it says rows per execution is 12.
Right, and we’re analyzing 15 million rows.
That’s a lot of rows, 12, right. Probably need an index.
Also what you can see is would this impact any other SQLs. So, again, when we’re talking about, like hey, I’m going to make a change in this table, it’s going to impact other SQLs. Well, here’s another SQL that also has the same exact index recommendation. So, not only are we making this one better, but we’re making the other one better.
It’s a nice reminder it’s not all about you, and that you are actually using a shared set of resources and there will be impacts.
Do you find, as a part of the Beta, that that was something that customers really enjoyed was a lot of times, especially if you’re sort of a accidental DBA, a recommendation to make in index, that can actually really affect the performance of other applications, so it’s sort of helpful to know this isn’t going to bring everyone else to a screeching halt.
Absolutely. I mean, that’s the whole idea with this feature is to give you the high-level overview. Again, if you’re a developer, you’re just focused on one query at a time, you’re not really concerned about all those other queries, but this gives you insight into that. And if you’re a DBA, you can look at all of these queries that are inefficient and see what these impacts are going to be.
Maybe that’s the dividing line between Dev and Ops is being worried about more than one thing.
Yeah, I mean, I think that’s definitely true, for DBAs, it’s definitely true because they’re worried about the instance and this allows them visibility down to the table level.
That visibility, I think, is going to be critical for everybody out there that is using DPA because just, the idea right now that I could say, in this period of collection, 70 different queries are hitting this table. Just that awareness ’cause most traditional tools are going to be able to show you all sorts of queries, and they go by like, what’s the top 10 in CPU? Or what’s the top 10 IO– Right? Those are– That’s like last century’s thinking at this point. What we have right here is a way of taking the same data that has been collected in the same way, but we’ve spun it a little bit to give you just a different view and a different understanding of what that data is trying to tell you. So, I have 70 queries, and two or three of these might benefit from this one change, but I have awareness that that means 67 might have a negative impact. I should be aware of what I’m about to do.
So, what’s the way that most customers, especially during the Beta period, were walking through this? Because for a long time, to your point, they’ve been able to do the top 10, let’s say, or you look at the chart where you’ve got your wait time by query, maybe across multiple tables or even multiple databases, but in this case, these recommendations are setting off that red alert bar on the tab at the tab level.
Surfacing it up. So, are you finding that they sort of start at that level and then they drill to the table and identify the queries that are affecting that table? Or are they still sort of starting with the, hey, I have this query that’s causing an issue. Let me just sort of walk it back to the table and then sort of narrowing down all the queries that actually affect that table?
Yeah, actually, we’ve seen it both ways. It kind of depends on what problem you’re having. If you have a real problem query, you start there. Not every problem is a tuning problem. It could be blocking, it could be resources, and we can tell you that on QPA, which we’ll be showing later. But there are a lot of users that are starting here, and they’re seeing things that they never saw in DPA before ’cause we’re doing the analysis. Queries that by themselves didn’t really warrant attention, but when you roll that up to the table level, there’s a huge amount of wait that they just didn’t even know about, and so they’re starting here and going like, wow, I did not know that, hey I had six hours of wait on a query. I just didn’t even know about that.
So this is new data that’s being collected, and so reorganization of the way that it’s being presented based on feedback.
Okay, that’s awesome. Table tuning advisor, really, really helpful. What else were they talking about as part of the Beta that’s features that they like in DPA 12.0?
Yeah, the feedback’s been awesome, and the one thing we keeping hearing again and again is that this is a game changer.
That was my favorite feedback to have, just two words, game changer, and I wish they had this back when I still worked for a living. I spent a lot of time tuning specific queries with no awareness of the other activity, really. So having it summarized at that level, I wish I had been tuning the table instead.
Yeah, I can remember a few times that that would happen. I’d fix something and say awesome, I fixed my query, and about two seconds later, you’d hear somebody on the other side of the office, “Hey, my query’s not running great anymore.” Okay, so what if you’re using the Table Tuning Advisor and that still doesn’t fix it? What if it’s something else?
Oh, you mean when the database isn’t the problem.
It’s never the database.
Yeah, those times that, what, it’s resource storage. So it’s storage or networking or something else table tuning is not going to help. So, what do they need to be thinking about then?
So, that’s when I’d like to remind users and customers that there are many layers of infrastructure between them and their data. I tell them to think of their environment as kind of a triple-decker BLT sandwich.
Right, and that’s what we want to allow our customers to do with DPA data, we want to be able to show that data inside of PerfStack and let them correlate it to all the other amazing data inside of Orion.
All right, that’s cool. Well let’s see.
All right, so what we’re looking at here is, we’ve drilled down to a database census that’s being monitored by DPA, and all of this information has been there, but we added a couple of new things, the blocking and the deadlock. So this actually allows you to see either the block statements or the statements that you’re blocking, and you can see the different statement identifiers on the right, and if we highlight this, this’ll actually tell us about those queries as well.
And what’s nice too is if I want to see the actual SQL itself, I just expand it and it’s right there.
Exactly, that’s the power of having DPA data inside of PerfStack.
And I want to say that blocking, locking, right, deadlocking, but blocking specifically, that is ultimately concurrency, but that’s ultimately a root cause issue. So when somebody says, “What happened to my query right now? Everything’s bad.” And then you find out, well you’re just being blocked ’cause Joe in accounting decided to run the report at a weird time that he’d never run before, and the concurrency inside of relational database ends up causing this locking and possible blocking, and so to have this data to be able to be correlated when somebody says there’s a problem, where else in my environment– everything was running fine, but you can go back and say, I’m telling you, the disc is fine, the network is fine. The issue is right here, see? You’re blocked.
What’s the accusation workflow there? It’s the network, it’s not the network. It’s the database, no, it’s not the database, but it is a query. We fix the query, that wasn’t the root cause. There really is an issue, let’s say with storage or IOPS or something else.
Okay, well we can get that information as well inside of PerfStack, right? So we’ll head to this other screen over here.
Right, so let’s take a look at this one, and we can actually… This is another database that we have, and if we wanted to see all the related objects, we can just click this button.
So this is brilliant, right? Because one click, I’m going to see all the related entities to that particular database server.
Well, in this example of why, we keep talking about it over and over again, and you all need to spend time out on the Support portal because the Getting Started Guide for this will walk you through some of these features, like, that is one of the most powerful buttons on this page, but it doesn’t exactly have a big arrow point to it. Clippy doesn’t come up and say, “Hey! By the way, you want to find out all the things that are related to this object? Click here.” So make sure you’re checking that out at support.solarwinds.com. It’s got quick guides to remind you where all those features are on this UI.
Most people, most users would have no idea, but most DBAs wouldn’t have an idea of this either looking at all these different layers. Everything here is a potential bottleneck or root cause, right? And most people would have no idea about it. They’re focused on the query that’s inside the engine, what’s really happening there. They have to have access to this data, and that’s what PerfStack does. It gives them insights into that BLT sandwich.
You’re going to have to say bacon, don’t you? You just got to go back to bacon.
So, I was going to go one episode and not say bacon, but.
But then, you know, that just wouldn’t make any sense. Okay, so one thing I do want to talk about is for a long time, I mean– Look, I love charts that bounce up and down all over the place. They’re great. I think the human mind is amazing at averaging and making things, sort of drawing trends out, but a lot of times it can get confusing, and so one of the things that we added is a transform early was smoothing, right? So, to be able in PerfStack views, PerfStack views, say that five times fast, to be able to smooth out data. But you were asking for actual transformations, and when you start thinking about transformations, not exactly data science, but data science concepts So one of the things that we’ve been working on is the ability to do transformations on a metric-bimetric basis. So, for example, if I wanted to come over here on my CPU load and apply some transformations, pull the drop-down box. And remember, that used to basically be the outcome for smoothing.
So I’m going to click that, now I can actually apply a whole bunch. I can do change point, difference, linear regression, normalization, standardization smoothing, smoothing standardized, and standardized, and we could talk about linear regression if you want. I mean, we have someone here who knows a thing or two about math.
Oh, well, I mean, you don’t need me to tell you about the method of least squares.
I actually do.
Oh. So, I’ll try to summarize it in, you know, I’ll encapsulate it, and I’ll just say linear regression is a great way for drawing a line between all the different data points, and you want to fit the data points to the closest line you can, right? And they do that by calculating the squares of the means between the differences between the data points and the fitted line that they’re trying to draw.
Yeah, we just do this formula right here on a piece of paper.
So basically, you’re drawing a line.
Yeah, you just do that formula. Everybody knows what that is, right? You got your sigma, you know that means a sum. You got the squares, everybody understands this, right? I don’t need to go into details about that. Here’s the thing. You don’t have to worry about that formula ’cause we’re doing the math for you, right? So, when you come back here, let’s do this line.
Okay, so this one is average memory used over, what, a four day– a week.
We had three or four day periods. Just click on it, and this is going to show why linear regression is going to be very helpful for you. So what you can see here is, you see a line.
That looks like a trend line.
So that’s what linear regression does. This is where the value is. So linear regression is one of these methods that you use for predictive analytics where you say, okay, I have an idea of what the line looked like for a period of time, which gives you an idea of what it might look like in the future. So right here, I can see that my memory use is trending up. Go back to what it used to look like. So the question you’re looking at right now is usually, you’re looking at this thing, and it says, okay, I can see how much was being used over time. But if you were looking at that, you might not have an idea if it’s trending up or not. Maybe you do, but with linear regression, you’re going to be more certain about it, and it’s just one click away.
Do you find that the human mind, when you look at charts, tends to just sort of use effectively smoothing or averaging, where maybe, like, this one, you can see over time, it is trending upward, but if you had a lot of noise at the beginning, if you were really spiking, you had a lot of highs and lows at the beginning, it would be pretty easy to say, well, this is more or less consistent. I’m sort of bouncing around at 95% of average consumption, but actually being able to show that as using a linear regression transform and showing a real trend line, it pops it out. Especially for maybe people who aren’t familiar with that metric where the whole point of PerfStack, being able to take that view and hand it to another team member to say, hey we’re going to run out of space. Here’s what it’s looked like over the last two weeks, is really handy.
That’s the power, right? ‘Cause if you look at this right now and you just sort of say, is there a problem? Was there a problem at 12:00 p.m.? Well, I’m just using the memory. I have the memory– I’m using the memory that was allocated to me. That’s what you want. You want to use the resources you have. What you’re going to get with that linear regression is that, basically, that forecast. This is how you’ve been trending over time and where you’re most likely to be trending, and the further you go out, it’ll smooth, but the further you got, we gave you a better idea, so now you can do capacity planning. Two words that anybody would dread because it’s so difficult to do, but you have at your fingertips, the ability, with linear regression, to get a more accurate capacity planning forecast.
Well, that lets you put capacity planning on any metric without going to a separate capacity planning tool or dashboard or something else.
That’s right. You don’t need to buy fancy tools for capacity planning because you just need the right data and the right tool in order to get you the information where you think, hey, I can see where we’re headed with this. We’re going to need more memory, or we’re going to have to make some changes in how the resources are allocated between that host and that guest.
Well, this is just awesome. Every time that we talk about Database Performance Analyzer on the show, Brian, I feel really bad ’cause I would really like to spend more time on this because this is one of these areas that, I haven’t looked at databases in earnest, really, in 15 years. Sort of down at the query level because they’ve become a service to me, and I’ve been concerned about other stuff, but every time that we have you on, it feels like DPA just makes leaps and bounds in terms of new features that you’re adding. And these aren’t just small steps, these are like, major changes that you’re bringing to the product.
Yeah, well, don’t get too excited yet, we’re not done. We’ve got more to show you. You’ve only just seen the Table Tuning Advisor and the ability for the PerfStack integration to correlate data, but we’re going to show you something else here. We’re going to take a deeper dive into tuning queries because there are times when tuning a specific query is the right thing to be doing.
Yeah, and this release of DPA, we took all of the data and metrics that we’re collecting and brought them together into this new feature that we call Query Performance Analyzer or QPA.
Okay, so yet again, this is the same data, but another, a third new way of looking at it.
Yeah, it’s mostly the same. There’s some new stuff, too, but yeah, we really wanted to break out QPA in order to help emphasize the actions necessary for the user in tuning a query.
I love that it’s a focus on workflow and being able to get to it quickly more than we’ll just go grab more stuff and grab more stuff because DPA has been collecting, I don’t want to say everything that it possibly can, for a long time, but it has been collecting pretty much everything that you need for a really long time. And so at this point, it’s just about improving efficiency and providing new ways to quickly solve the issue.
Yeah, absolutely. That was the whole idea with this feature, is to bring all of the goodness about a single query into one place. So let me show you real quick.
All right, great. So we’ve logged in, and we’re looking at this instance again.
Remind us again how we got to this.
We logged in to the home page, and then we clicked on an instance. There’s just a big list, and this one, maybe this one was red.
So it was database, an instance, it was popped up red, and it’s going to take us to this page.
Right. Exactly. And we’re going to drill down on this big bar, this is one of the bigger bars here, that’s one of the main flows inside of DPA, and what we want to do is, we’re looking at this, there’s been a lot of wait time today, it’s kind of, looks pretty normal, but the biggest query, these are ranked, the biggest query is this one right here, so we’re just going to drill down. Now this is the new flow. This is where it takes you to QPA. And what you see here is just the one query, right, so this is the query we’re looking at. We could see all the wait time, and this is by wait type, so we can see that this is mostly waiting on memory and CPU.
And if you don’t know what the wait types are, you can get the information, right?
Exactly. So you can always click on the little “i.” It’ll tell you all about what that means and the steps that you can take to reduce that wait. One of the great features, this is all new technology, so this is using some of the new stuff that we’re building internally that we’re using across all of our platforms. It’s called Nova. But this has some really unique features. One is like, it’s like, okay, well, what if I don’t want to look at memory/CPU? I can now uncheck that and see what the other wait types are, and it makes it a lot easier to see.
And it’s going to expand the data out based on the new available real estate by changing what the previous top values were for that series.
Absolutely, yeah. So it’s a huge feature. A lot of users have asked for that. It’s like, hey, I’ve got this one wait that’s always dominating. I want to be able to hide it temporarily so I can see what else is happening.
Oh, that’s brilliant.
Now, one of the other really cool features, hold on to your hats, is when we scroll up, we keep the waits at the top ’cause we’re all about the waits, that’s what we’re about. So what we’re doing there is keeping the emphasis on wait, and if you look up high and low, then we’re actually aligning when you’re waiting with the statistics down at the bottom, and it works either way. So no matter where we go, we can always line up, hey, when did I have a lot of wait time, or a specific type of wait, to any of the statistics or metrics that are down below. So that’s a pretty nifty feature. So, we also have our advisors. So this is going to be, this is the standard stuff that’s been in DPA for a long time, but now it’s all aggregated here, so we can see that, hey, we spent a lot of time on memory/CPU, but this is also where it would say, hey, you need to add an index, or hey, you’ve got other types of things that you’ve been waiting on. And also notice the Table Tuning Advisors here. So we were looking at the orders table earlier, it also appears here and it gives me a little bit of information, it’s saying, hey, there’s not an index recommendation here, there’s one inefficient step, there are 17 other queries that also have this advice. So, really when you’re looking at this, you can quickly decide, like, okay let me go see, maybe this is a tuning problem. Not every problem’s a tuning problem, but maybe this one is, and I can jump over to the Table Tuning Advisor. But we won’t do that right now. What we’re going to do is look at what else is on this page. One of the things about this page is we made it intelligent, so what we did is before we actually display this data, we kind of take a sneak peek and say okay, what are the types of waits here? So the biggest wait here is memory/CPU, we already saw. That’s sort of the dominant wait here. And so what we do is further down, we’ll actually pick which metrics match that type of wait. So it’s really trying to help the user, trying to point the user in a direction. Now you can add whatever you want, you can customize the screen and all that kind of stuff, but we’re taking a sneak peek of that. The other thing that we do on this page that is intelligent is we actually look at different areas that we’re about to present and decide whether should we show this data or not. So if you look here, blocking and plans, these are closed. If I open them, there’s no blocking to talk about. Not either as this query’s being blocked or this query’s blocking others. Now, the whole idea with this is like, hey, we’re not going to show you stuff that doesn’t add any value. If you are going to go and look at how much blocking there is, how long would that take you to do?
So, the problem with tracking for blocking is first of all, you have to be looking for it, and not a lot of tools go looking for hey, you’re just being blocked right now. So, that’s the first thing. So for SQL Server, there’s a thing called a blocked process report. You actually have to go in, configure SQL Server, enable that to be running, that allows the capture of the information, and then you can take that and you can pump it somewhere else, and then try to correlate that information to your diagnostic tool. Let’s put it this way. It’s a lot of steps. Traditionally, it’s just a lot of steps to get that correlation of information of the query running and here is when some blocking was happening. So, how would that get that? Painfully.
Right, and so that’s the idea is that we’re tracking this over time, we make it really easy to see, and you can tell whether you’re being blocked or if you’re doing the blocking.
And drill down to those other queries. Same idea with the plans, right? We only see one plan, and if you notice it, this chart looks exactly like this chart. There’s really no value here, so we don’t show it. You can go and see it if you want to. You can click down in to the plan if you want to and see that plan and it’ll open up a new tab, but it’s not being shown because there’s really no value in showing it. What we want you to know is that we’re doing the work. We’re looking and seeing that, hey, there’s nothing here, so you don’t have to.
Right, so if there was two plans in that timeframe, we’d probably expand that out.
Exactly. If there’s more than one plan, it’ll expand it out because a lot of times when a query switches plans, that’s when the performance goes south.
And again, that type of information, how would you ever know that the query you’re looking at had two different plans in that timeframe, and that could be correlated to where the performance spike is. Again, traditionally, to go and get that information, and to get into your procedure cache, and to pull that information out, and to say, hey, and then roll it up and correlate yourself, that is a lot of extra work for most DBAs, and that’s why this QPA feature that you built, the query performance analyzer, just makes it easy. It’s the same data just being presented in a different way for consumption.
Well, you make it sound like everyone knows how to go pull performance plan history, so just that piece of it, like, I know I gave the example before about my query ran fine until it didn’t, but literally, that is a big issue. If I am an application engineer, and I know something about databases, and I’m pretty good at rolling up my sleeves and figuring things out, going and doing a history and looking at performance plan changes over time is not probably going to be at the top of the list of things that I’m looking at if I’m focused on things like indexes and table tuning. So, being able to have that where it’s available and show those changes in the view, it just even gets me to the place where that’s something I might consider. Otherwise, I would never even maybe investigate that as a root cause.
One of the great things that we heard from customers is that, hey, I now have a page that I can give my developers, ’cause we were talking about, hey, I tune a query at a time, right? This page is tremendously valuable to the developer ’cause he doesn’t have to… In the previous version of DPA you had different pages you could go to, different tabs you had to click through to get all this information. Now, everything about this single query is right here. So it makes it much easier for a developer to use the tool, and we’ve had a lot of positive feedback from them. Now, the rest of this is the metrics, and again, based on the dominant wait time being memory/CPU, we, DPA, will automatically select metrics to show you.
Hold on, go back to this one. So I see colors here. I see reds and yellows and shaded. What does this all mean?
Right, so this is actually hitting the thresholds that you’ve set inside of DPA.
I see, so when it dipped below, it’d be highlighted as reddish.
Exactly. So, page life expectancy. So, what’s page life expectancy?
How long the page lasts inside of memory before it’s ejected.
So the caching issue.
Right, exactly, so.
See? So quick to blame the database.
Yeah. So we’ve got O/S CPU utilization, instance CPU utilization, buffer cache, but if we wanted to add more metrics, we can. So, we can click here, and we’ve got all sorts of different metrics.
That looks PerfStacky.
Yeah, I was going to say, it does look, like it’s not miniStack, it’s not PerfStack, it’s something in between and injected into what really is like a entity details page.
Yeah, so you can filter, you can pick whatever you want to show here. DPA is also smart in that if you customize this page, it will actually remember those customizations for that database type and that predominant wait type. So whenever we come back, if you customize and say, hey, I always want to see metric ABC, it will actually do that the next time we come here for any SQL Server database whenever memory/CPU is the dominant wait.
And you do that because you would have similar contributing factors for any given wait state.
So instead of just defaulting to a standard view applied to all the queries, it’s like, no, I want to be a little bit smarter. This seems to be relevant for memory, so show that widget again.
Or memory in your environment, or memory with the resources configured as they are.
So, it really is intelligent.
It really is intelligent. A couple other things on here. Okay, so get ready. We’re about to show it.
Oh, that looks dark.
I made the joke about dark theme for you guys, but seriously, I’m beginning to wonder if all of the product managers are beginning to sneak more and more of these dark resources into different views to just eventually force it to happen.
Yeah, well, I can’t comment on that.
Well, when I first saw this, I thought it was a bug. [laughing]
It looked really familiar to me when I looked at it.
But the idea with this is, so, you have the SQL text here at your fingertips. We’re highlighting it, so we’re making it really easy to read, and then if this was a really long query with thousands of lines, this first section here is actually tell you the part that is this query.
And supporting data. This is all of the additional data that we have about this query. So we tell you what database it’s running against, we tell you the user that’s calling it, we actually look at the files and the machines. Now, again, this is intelligent. There’s only one database, so I’m not opening it, but since I have multiple files, or actually the files, we always show. But if there were multiple machines, multiple programs this would open up as well.
That’s just fabulous. Can you go back to intelligent analysis?
Was there anything underneath the statistics at the bottom?
There is, yes.
Blocking, plans, no no. Then the resources?
What’s below that? Is that the end?
That’s it, but I did, I skipped over statistics, which we shouldn’t do. So this is actually data that we had before, but we’ve augmented it some again. We used to show executions. Now we show wait time per execution. This is really handy if you have a planned change, and then all of a sudden, you’ll see, hey, I’m still executing at the same, but my wait time for execution goes way up. And you can kind of point at that and say–
So, we used to show these in two different graphs, and we would just tell the humans and their eyes to kind of match it up themselves. So now we’re embedding the two things together, make it a little easier to consume.
Right. And if we wanted to see other things here, rows processed, logical reads, that kind of stuff, we could do that as well. And again, we’ve got the executions, the logical reads, and the logical reads per execution, so we can see what’s happening over time. So again, this is data that would be crazy hard to get, and we’re just tracking this all over time. This is all data that we already have. We’re just displaying it differently.
That’s the key, right? It’s not that it’s necessarily difficult to get, some of the things can be a little more difficult to get, but it’s data that we’ve had, and we’ve just realized, hey, we don’t have to go collect more things. We just have to kind of twist a little bit the data we have to present in a slightly different way for consumption, and then it becomes way more powerful for our users to just get to that root cause a little bit faster.
Well, it’s also the result of how many of you guys have gotten involved in our UX program for DPA, right? Because the amount of user feedback, the amount of suggestions that we’ve had from you, from the THWACK community, has been amazing, especially what’s gone into this particular release. And so, I always feel good when I see a lot of changes that I have seen either been bumped up on the ask list or questions about in support, and so I’m seeing a lot of requests that have been closed off for request features.
Yeah, absolutely. The Table Tuning Advisor was one of our– was our number one feature, so we’ve obviously got that one done. There’s a lot of requests for making the GUI better. There wasn’t one specifically for QPA, but, again, based on the user feedback that we got, we watched users use the product, and that really led us to QPA. We get down to a query, and they’re like, okay, I’ve got to go look here, and I’ve got to look here, and then I’ve got to change this tab and that tab, and they’re putting together a story, but it’s all in their head. They can’t see it, and that’s what really led to, sort of, QPA as a feature.
And some of these changes, like switching from execution to wait time per execution. I remember Microsoft Ignite last year. I’m talking to a customer who’s, they’re not an accidental DBA. I mean, they were a SysAdmin, they’ve got a lot of experience and they have done a lot of database tuning, but he was asking the question, he said, “You guys just really seem to be preoccupied with wait. It’s wait, wait, wait, wait, wait.” And standing next to him was an admin, who maybe didn’t have as much experience with database, and he looked at him and said, “Well, what’s the number one thing that you have, in terms of an issue, when the customer says my query is slow?” Well, he says, “I log in, and I look at the CPU, and the CPU is low, but the memory is pegged. What’s it doing?” Well, it’s waiting. Everything in the database ends up being waiting. That’s the number one thing that’s reported. The thing that drives the help desk ticket is wait. You’re trying to find the root cause behind that, so a lot of those shifts toward, like, in this case, keeping that wait time at the top, that’s essentially linked to what would be driving a ticket, or at least, let’s say the ticket is slow application, which then hands down to the app team, which goes to the database team, that’s the thing that I need to resolve before I can close that.
I mean, well, just look at this, just what he highlighted. Executions are down slightly. The wait time’s up, and you can see up above, it’s a page IO latch to share a page IO latch. And we know there’s no blocking, but we know it’s going to disc, right? That’s what page IO means, so that’s powerful information to have at your fingertips.
I have a question for you, though ’cause you clicked on that hash number to get here, and usually I click on a hash number to rename that SQL, so where’s that done now?
Right, you’re exactly right. So everywhere in the product now, when you used to click this, it would have a pop-up, and it would say you can rename this, or you can go to the history, or you can do analysis. We’ve gotten rid of all those different flows, and now when you click on any query anywhere in the product, it takes you to QPA. So the idea was, hey, if you’re interested enough in this query, I mean, you need to know what it is before you rename it, and QPA has all the information that you would need to know in order to rename it, so we click on this. That’s simply just right up here now, so still got the same functionality that you had before. You can name it whatever you want, put a description, you can turn it off in the trends charts and the analysis.
So it’s still there. It’s just…
You just moved it.
We just moved it.
My cheese is over there now.
We moved the cheese a little bit.
Okay, so what else should they be thinking about? Are there any new requirements? Is it SQL Server only? Is it multiple database? What do they need to know?
So, really, not a lot. Other than sort of moving that cheese, we really didn’t make any other changes to the flow. The one thing that’s great is that, okay, QPA is for all the databases that we support now, so SQL Server, Oracle, DB2, Sybase, mySQL, and it works with all the data you already have. So, all of the data that you’ve been collecting for years is now here. One of the great features that I hadn’t shown yet was that now you have a date selector. So a lot of people get frustrated that we only show 30 days of history. You can actually show however much you want, but here we’ve got a default of 90 days, so now you can actually see 90 days of this query, and I don’t have that.
And we weren’t running.
And we weren’t running. But that’s one of the ideas, is that QPA can leverage all the data that you have there. The table tuning analysis is the same way, but it has to do analysis, so it takes a day or two for the analysis to catch up, but it leverages all the data you already have.
But that’s the same thing with baselines, recommendations, anything else, it’s going to need to collect a little bit of data so that it can do some math and make recommendations.
Absolutely. So, when you upgrade to DPA 12.0, QPA, everything is available right away. Table tuning takes a day or two just depending on how stale the date is.
This is fantastic, and this is really the DPA I wish I had back when I was a production DBA.
When you were a kid?
When I was a kid. So, we had a lot of suggestions, that went into this release, and I’m hopeful that our customers are going to be really happy and excited about what they see in DPA 12.0.
That’s the idea. At the end of the day, everyone in IT should be happy.
But isn’t that really why we got into IT in the first place? Is to make people happy? I mean, we came to technology because it lets us be really critical to our businesses, but just with no capes. I mean, that’s kind of the secret.
So I think these features, in DPA 12.0, they are going to change fundamentally how people approach performance tuning. What we’ve done is we’ve combined traditional methods with traditional collections, but we’ve presented the data in the new way, and it’s surprisingly helpful. It’s going to allow for better analysis.
And this will also lead to better use of your time, as you’ll be able to identify which actions to perform based on those that have the most impact.
Yeah, sort of the holy grail of IT, easy prioritization of the multiple overlapping performance issues that databases are so famous for.
Well, I’m not so sure that that’s what databases are famous for. For SolarWinds Lab, I’m Brian Radovich.
I’m Thomas LaRock.
And I’m Patrick Hubbard. Thanks for watching.