Databases & DPA 12.0: Tune Tables Too! – SolarWinds Lab Episode #70

Databases & DPA 12.0: Tune Tables Too! – SolarWinds Lab Episode #70


[techno music]>>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–>>Data!>>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.
>>Exactly.>>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.
>>Right.>>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.>>Exactly.>>Exactly.>>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.>>Yeah.>>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.>>Okay.>>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.>>That’s right.>>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.>>Right.>>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.>>Right.>>So the caching issue.>>Right, exactly, so.
>>Maybe.>>Maybe.>>Maybe.>>Right.>>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.>>Yes.>>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.>>Exactly.>>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.>>Yep.>>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.>>Okay.>>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.>>Supporting data?>>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?>>Sure.>>Was there anything underneath the statistics at the bottom?>>There is, yes.>>Blocking, plans, no no. Then the resources?>>Yes.>>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.>>Okay.>>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. [techno music]

Daniel Ostrander

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *