>> Recently we added some new functionality
in Azure Data Studio related to query plans.
You're going to love this episode,
so go ahead and check it out this week on Data Exposed.
[MUSIC].
>> Hi, I'm Anna Hoffman,
and welcome to this episode of Data Exposed.
We have an exciting episode plan for Azure Data Studio,
and for that, I'm bringing on Erin Stellato.
Erin, thanks so much for joining us today.
>> Thank you for having me.
>> Always a pleasure. We're actually having you back on the show.
We had you on a couple years ago as part of the NVP edition,
but now you're at Microsoft,
been at Microsoft when working on
some great things in Azure Data Studio.
We're excited to learn more about them. What's new?
>> Well, today I'm going to talk about query plan viewer.
I know you talked to me a month or two ago about table designer,
which is another feature which is new in Azure Data Studio.
Folks may not have seen these
yet because they're hidden behind a preview flag,
so I just wanted to take a note that I have these instructions in
case folks haven't seen
either table designer or query plan viewer.
It's really easy to enable that.
Even though it's in preview now,
with the next release of Azure Data Studio,
that's going to GA.
I'm in the context of the WideWorldImporters database.
We'll just make sure that I'm still in there.
You're going to notice some similarities if you've looked at
query plans before in something like management studio.
We have the option to do
either an estimated plan or the actual plan.
If I just click on this estimated plan button up here,
I will get the estimated plan down here in the bottom window.
Let me bring that up so you can see a little bit better.
This is something that folks are going to be familiar with.
We see the estimates here,
but a lot of times I know that folks want to
actually look at the actual plans.
If we turn that on,
which I've already done here,
but let me just turn it off so you can see right.
It's just a toggle button on and off.
Enable the actual plan and then we run this query again,
and we get the plan here in the output.
From here, you can say this,
share it with your friends.
We've got a right-click menu option with
a ton of different things that you can do from here.
All of those same options also exist over on the right as icons.
I can save this,
I can open up the plan in XML format for those
of you that love to dig into the XML and parse through that,
and then I can also open up the query itself in a new window.
That's not always something I need to do,
but it's nice to have that option just in case.
I want to take a look at what happens
if we run a couple of queries side-by-side.
We've got some slightly different behaviors
than folks might have seen before.
We have a properties window,
which a lot of folks love to use to understand
operations or the specific operators
and the attributes in a little bit more detail.
If I click on different operators in the plan,
I'm going to see the properties change.
You'll notice down here I've got
my other plan at the bottom of the screen. I just brought that up.
The properties window is independent,
which is cool so I can look
at the properties for one but not the other.
The team has done a lot of great work with making
these a little bit more resurface up-to-date.
You've got the ability to sort in here,
we can sort alphabetically ascending,
descending depending upon how you're used to
looking at information within
the properties window and you can also filter.
If there's something that
you're really specifically interested in,
like if I were interested in something for memory grants,
which I don't have anything going on here,
but I can search for memory,
or I can search for actual and it's just going to
collapse that list into what I'm
specifically interested in seeing at this point.
>> Nice. That's awesome.
>> Yeah, and it's also in dark mode.
Since we had that in Azure Data Studio,
so a lot of folks might also appreciate that as well.
If we look at a larger query,
so this is just a little bit bigger,
still against WideWorldImporters.
Sometimes we work with
really large plans that don't easily fit within the window.
With Azure Data Studio,
you can do control plus and control
minus to increase or decrease the size of the window.
But we also have the ability to
just do this size to fit over here.
It will just immediately size the plan to fit into the window.
Now, for those plans that have like 50 or 100 operators,
that doesn't always work,
but it's a nice option to have for certain scenarios.
Then something we've added,
which I think is really cool,
is we have this option down here,
which is to highlight the most expensive operators.
When you click on that,
you get this popup and we've given folks the option of
what attribute would you want to define as most expensive.
It defaults to cost but if I'm really
more interested in something like elapsed CPU time,
I can click on that and apply that.
You'll notice that it highlights over here this has to match.
>> Oh, wow.
>> It's the most expensive.
But then the other thing that you
can do is it gives you an option at
the bottom where I can say that this actual lapsed CPU,
that's what I always want to be like the most expensive,
so that's what this little pop-up is down here at the bottom.
I don't want it to be that, so I'm going to go ahead and say no.
Then the other cool thing that we have
is you can have a top operations pane.
You can get there just by clicking
top operations or through the menu on the right,
and this lists all of the operators that are in the plan.
From here, you can sort.
If I'm interested in sorting by cost,
for example, I can sort by that estimated rows.
Any of these columns are
sortable and I have search capabilities as well.
If I'm really interested in
understanding where the sorts are
within my plant or the nested groups,
I can do that and drill down into it.
Then I can go ahead and click on
an operator and it will hop back to that.
It's highlighted here in green in the plan.
>> Wow, this is so cool. I can see this.
I mean, for me, I'm not a performance or query
optimizer guru but I could see this being something that would
really help someone who's getting
started as your time to understand like,
hey, what are the most important metrics to think?
But also as you're an expert,
you know what you're looking for,
you could just do it that much faster.
>> We've really tried to make it accessible both for those
who are newer to plans and those who are pretty experienced.
The one other thing that I really wanted to show
you is the ability to compare plans.
I'm going to run this stored procedure just that
I've already created called GetFullProductInfo.
I go ahead and run that.
It's got two queries in it,
so I end up with two plans.
The bottom one's hidden down here just a little bit.
But what I can do from here is I right-click and I say
compare execution plan and it brings me into a new window.
Then I'm going to go grab the other plan that I have here.
Now I've got a comparison for these plans in
the same window What I really like
about this is a couple of things that the engineers did.
First of all, you'll notice we have this
dropdown up here so I can see Query 1,
or I can select Query 2.
When we've got a procedure
or a batch that has a lot of
queries in it and we end up with a lot of plans,
we can really selectively just
look at the plan that is of interest to us and
compare it to the plan from the other file that we pulled open.
Then once we have that query selected,
let's see, I switch the second one,
let's switch to the second one over here.
Then I want to do some comparison.
Obviously, I have the ability to do that visually,
but we've expanded the Properties pane as well.
There's a lot going on in here.
But what you can see is that
we've noted here if the operation is equal,
not equal to, greater than,
less than, etc.
>> Wow.
>> If I look at both the select operators here,
we can see all the things that are not equal.
I can drill down into that in more detail,
I can still sort alphabetically either direction.
This is something that they did.
This really cool to me. You can collapse
all of those and expand them.
If you're looking at everything and then you're trying to
hide something again, rather than having to scroll,
you can just go [inaudible] You have
searchability in here as well.
If I'm interested in things specific to CPU,
then I can bring those right up pretty quickly.
>> Wow, that's so cool. Can I ask a silly question, maybe?
>> Yes.
>> Blue boxes in the two queries, what is that?
>> This missing index recommendation here?
>> Yeah.
>> When there's a index recommendation
that comes from the optimizer after you've executed the query,
that shows up here in the plan.
Folks that are used to looking at that in
SSMS will know it as green.
Because I'm in dark mode here it shows up as blue,
but I can click on that.
It'll pop open a new window and list
the index recommended for that particular query.
>> Nice, cool. Folk are probably
potentially expecting that if they saw it in SSMS.
>> True. Exactly.
>> I think that's a good maybe question to
close on unless you had other things,
but this is in SSMS today,
why add it to Azure Data Studio?
>> Well, we had some level of functionality
available through a third-party extension but that
hadn't really moved forward and we recognize that there's a lot of
folks who do live in Azure Data Studio who want to do that,
who don't want to switch over to
SSMS because they don't need all the functionality that's there.
SSMS has every thing,
Azure Data Studio is a little bit more focused.
There's a lot of different users
that tend to gravitate toward that and
they need to be able to do
some basic level of troubleshooting using query plans.
It really made sense for us to add that along with table designer,
the folks that need to be able to
create objects easily who aren't familiar with T-SQL,
we give them that through the UX.
Query plans is just another thing to hopefully draw folks over to
Azure Data Studio or for those folks who've
tried it but have found it's a little bit incomplete.
This is one of the things that we're adding along
with other stuff coming down the road.
>> Awesome. Cool. Well, I'm certainly
excited to see it land in Azure Data Studio.
Sometimes I'm also on my back so that
makes my work a little bit easier.
But Erin, thanks so much for
coming on the show and sharing this with us.
I learned a lot, I think our viewers probably learned a lot.
As far as call to actions go,
we put in the description a link to learn more out
to enable this if you're accessing those prior to their release.
Any other subjects for folks as they get started?
>> Not really. I think that
the documentation should pretty much be complete.
If you find that it's not or if there is
something missing in this functionality that you'd love to see,
please go to the Azure Data Studio GitHub.
You can create issues there,
if it's in the documentation,
you can create an issue there as well,
but we'd love to hear your feedback.
>> Cool. Awesome. Well, thanks again Erin for coming on the show.
To our viewers, if you like this episode,
go ahead and give it a like, leave us a comment,
let us know what you think of this new query plan capabilities
in Azure Data Studio and we hope to
see you next time on Data Exposed.
[MUSIC]