>> PerfInsights can help you evaluate
the health of your Azure SQL Virtual Machines,
either in the portal or locally from the virtual machine.
Learn more in this episode of Data Exposed.
[MUSIC]
Hi, I'm Anna Hoffman
and welcome to this episode of Data Exposed.
Today, I'm joined by David Pless,
a Program Manager on the Azure Data Team.
David, thanks so much for joining us again today.
>> Hello, Anna. Thanks for having me.
>> Always a pleasure.
Now, in previous sessions of this series,
we talked about Azure storage and caching,
monitoring, and the best practices for
things like sizing and storage performance.
Now, today we're going to examine how to
properly evaluate the health of your VMs
and troubleshoot various resource issues
using something called PerfInsights.
We've got further VM,
David can you tell us a little bit more about
what PerfInsights is and how it can help us.
>> Sure. PerfInsights is a great tool for Azure Virtual Machines,
because not only does it do a great job of capturing and
giving an evaluation of performance and diagnostic information,
but it also summarizes system information,
especially your storage information,
making it a lot easier to figure out
your Azure SQL Virtual Machine key performance characteristics.
It gives you an overview of any environmental risks.
It summarizes your storage configuration.
It puts together in one place
important diagnostic data that can be
evaluated separately with familiar tools,
such as performance analysis of blogs.
But PerfInsights really does a great job of analyzing the output.
Now, there's a number of ways that PerfInsights can be executed.
We've the ability to run PerfInsights from the Azure portal,
and we're going to take a look at that.
We have some deep insights that you can analyze as well,
and this is going to be discovered by
running PerfInsights from the command line where you're
going to have control over which scenarios you'll run
PerfInsights under using several switches
that are built into the tool set.
After the collection analysis is complete,
you get a zip output of all of the diagnostic data
along with an HTML report that provides the analysis.
Let's jump in and take a look.
Let's start with the Azure portal.
As you can see,
I'm on my VM blade here.
I'm just on the overview.
What you're going to want to do to pull up
PerfInsights in the portal is go to
your Diagnose and Solve Problems,
and pulling up here.
Then you're going to get a troubleshooting tools,
and here's your performance diagnostics,
so you just click this.
Now, this can take a while to run,
I think by default it's 300 seconds.
I like to do 600 seconds pursuing deeper analysis.
But you can see that it actually is telling
me the type of analysis that I've done,
and then if I click on any one of these scenarios,
I'm going to see the breakdown of the high impact,
medium impact, and low impact.
On this scenario, I have no high impact scenarios,
but I've three medium and one low impact.
I click on this situation here,
I can see a breakdown of my high CPU usage scenario.
I didn't have a CLR performance analysis,
and then some information
on speculative control settings that are enabled.
If it's SQL Server scenarios, NOS scenarios,
these would all get populated with
the high performance risk medium and
then your informational low risk scenarios.
Now, this is essentially taking the report and producing
the output in the portal so you can analyze it this way.
If you want, you can always download the report,
and then if you have another scenario,
you can always run your own diagnostics.
Now, I'm not going to go run the scenario,
but I want to walk through a couple of the options.
If you click "Run Diagnostics" you can
see the potential analysis opportunities here.
This is the quick performance analysis I mentioned.
You can do a performance analysis,
an advanced analysis,
or your Azure file analysis.
Then you can list your symptoms here, let's say,
slow SQL VM,
just so that is documented and listed.
If you have a support request number,
you can put this here,
you just acknowledge that
you're getting this information from Microsoft,
and of course read your privacy policy and so forth.
Then if you've acknowledged everything,
you can click "Okay" and that will run the diagnostic,
everything in the background on your VM.
You don't have to restart your VM or anything,
it will just continue running and run this diagnostic for you,
and then it will produce the report as you see in the portal.
Now, this is in the portal.
If you want to do a custom diagnostic on your machine,
and we'll take a look at that now and see what that looks like.
Now, I already have a report pulled up.
If I wanted to run this and if you see here,
I'm going to go and let's
just clear this out again so you can see how I got here.
I'm going to type PerfInsights list,
and if you see,
this is going to give me a very similar list that
I saw in the portal: my quick, my benchmark,
VMslow; this is a unique scenario specifically the command line,
Azure files, and advanced.
These are the scenarios that I
mentioned earlier: expert, network,
Storport tracing, performance counter trace,
and then this speed benchmark if you like.
Of course here's some of the scenarios,
so if I want to run a benchmark test for 600 seconds
and accepting all of the disclaimer information,
I could do it this way.
Or this is my VMslow example,
so I'm saying PerfInsights run, VMslow,
my duration of 600 seconds,
300 is the default.
These are just some example runs.
Now, let's take a look at an actual report here.
Right when you pull up the PerfInsights report,
you're going to have findings, your run information,
and your machine information,
and then it breaks down all of the finding analysis information.
If I click SQL or high CPU or disk usage and so forth,
they essentially going to be going directly to one of these tabs.
I'm going to walk through a couple of these scenarios.
First of all, if you click "Run Information",
it tells you exactly the name of the machine,
the command line scenario that you ran it under,
it's going to give you a path to
the log file information, which is really nice,
that's your output information that you
have for your PerfInsights capture,
and then maybe some information about your machine itself.
It's just like assist info track.
Now, I did have some information on CPU,
so let's click here.
Then you can see how it's giving me a medium impact level,
the finding, and the resources that are found,
which is really nice,
is they're giving me references on how to address these scenarios,
write directly to Microsoft documentation.
In this way it's an assessment tool
as well as a troubleshooting tool.
>> That's awesome
>> I love it. Plus you're getting this updated information,
and every time you run PerfInsights,
it will give you an update and then you can reanalyze.
Of course, I like to be aware of any changes to the tool set.
Of course, here's all of
your detailed impacted information
based upon what was found in this particular workload.
In my case, I was running a lot of heavy SQL scenarios,
missing indexes, some statistic anomalies,
some other scenarios that were going on.
Maybe it's just some in general bad queries that were being
run just to produce some workload for this particular scenario.
I can get my information on
your speculation control settings and any of your top consumers.
It tells me the top consumers that will run,
and it breaks this out this way.
Now there's a lot here.
We're not going to be able to cover everything.
I really want to hone in to
some really specific things that you want to take a look at.
Let's take a look at memory.
Even though that there's no findings that
were an issue for memory,
I really like to be aware of my top consumers.
It's telling me my working set.
It's giving me information about what was committed,
how much is in use by your top memory reconsiders.
In this way, this is what I mean by it's
a really good documentation tool as well.
Even if it doesn't find an issue,
it's going to give you some really good information.
That is especially true for storage.
I absolutely love what they're doing for storage,
and I want to spend a little bit of time here.
Now, we did have an issue that was found on storage.
I was throwing a lot of workload at this guy.
It's breaking down.
I had some demo workloads running.
Of course, SQL is going to take quite a bit.
If you can see I was doing some queries store scenarios
here and a bunch of OStress.
Those OStress routines of doing
some scenarios where I had
some missing indexes, really Bigtable scans,
just some bad query patterns and
scenarios that I knew that were going to
produce some heavy workloads.
But in this findings,
I'm going to go backwards,
I want to start at the high resolution disk usage.
If you remember from the previous sessions that we've done,
we've been talking quite a bit about IOPS and Throughput.
These high resolution disc rules,
these CSV files have the information about
the historical disc usage and
it's balancing this against our premium storage.
It's giving me just based upon the usage from a P40 to a P60.
I'm going to take the Queue Depth stuff out,
so we can only look directly at our IOPS.
If you see here,
this is my P60 is this top line.
This is going to be my P40, my P50.
I see a spike here that goes past my P40, P50.
But I can see that most of my workload, that pink line,
that would be my P30,
I can see most of my workloads are well underneath that.
If you're looking for
your specific disc needs based upon your IOP and Throughput,
we've been talking about this quite a bit in previous sessions.
PerfInsights is the tool specifically way to
give you that and to graph it out for you.
Anna this might be my favorite aspect of why
PerfInsights gives us from a SQL perspective because
you know how much we really care about IOPS and Throughput.
This is my IOPS. I can do the exact same thing for Throughput.
The only thing I will tell you is I recommend
removing the Queue Depth pieces.
As you can see that I'm doing as I'm talking through this,
just because it provides a little bit of clarity.
You can just specifically look at only your Throughput scenarios.
You can see the same scenario here.
Here's my Throughput,
here's my top disc,
that top disc being my P60.
You can see here that I'm well underneath that pink line,
that pink line again is my P30.
That's true for both IOPS and Throughput.
I know that for the most part,
other than that spike,
I'm well below those scenarios both the IOPS and Throughput,
if I chose a P30 disk for that.
Really strong capability here,
but there's a lot more here too,
so I can get my disk consumer information.
What were my high disk usage periods?
What was being used?
SQL Server, my demo workloads,
my reads, my writes,
how much data between reads and writes?
When did this happen?
What disks were specifically hit?
What were the average IOPS throughput reads,
writes for each of those disk?
I'm going to move past some of these other things
because I want to focus on some key things here.
To me, this is huge from a documentation perspective.
The fact that I can go to the storage pool here,
and it's telling me specifically the disks that I have,
how the number of columns in each storage pool,
and remember this is going to match the number of disks,
or the number of devices
in Azure for your Virtual Machine templates.
That's the best practice from a VM perspective.
That's what we would want. It's telling
me I have a 64K interleave.
Otherwise I would have to dig through
PowerShell through the system information to get that.
I'm getting it all here in the PerfInsights documentation.
I also can see how my volume is mapped out,
telling me the number of disks,
the physical disk name,
also giving me my interleave here as well.
It lets me know what my OS disk is,
what my temporary disk is,
the volume ID and labels for each of my disks,
so I can see how those are being laid out and used.
Then of course, my disk maps
specifically on the physical disk name and its ID,
if I want to look at it at that level.
It's really nice. Anna I can't forget Host Caching.
It would have been terrible if I had missed this.
Host Caching letting me know which disk are set to read-only,
which ones are set to none.
Remember data disc should be a read-only.
Your transaction log should be set to none.
For SQL files, we don't want it to be read-write for any scenario.
I can confirm that here.
I can confirm that in the portal,
that is really nice to be able to run
a report and get that information.
Of course, if there's any network issues,
Windows issues, they're going to let me know here.
That was that low finding on my information.
The key thing here is our SQL scenarios,
and of course I ran a lot of workload or water scenarios.
It's giving me a list of
the impact level which would affect performance.
Low is going to be informational medium,
that says best practice that you would be looking at.
But all of these recommendations,
whether I break these out or not,
that gives you your details,
are going to point to where
our documentation that we've been talking about this
whole time and all of these data exposed
scenarios that we've been covering over the past several months.
It's absolutely great that it's all in one place for you.
That's what I love so much about PerfInsights.
>> Awesome. This has been a really thorough demo,
I really enjoyed getting the walkthroughs
and I like how you can get the same experience in
the portal as you did in the more local type of environment.
As I close out do you have any final tips
for folks that are just getting started with PerfInsights.
>> It's really easy. Just go out,
download the tool if you want to use it in the local machine.
They give you some advice on what switches to look at.
The scenario of VMslow or Quick Insights,
that's going to be a lesser impactful scenario.
Definitely wouldn't run the heavy benchmarks
or advanced workload on
a production virtual machine also leveraged the portal.
You can start from there from your quick win scenario.
Make sure you're using your latest versions because we're
certainly putting in more capabilities and it's an evolving tool.
>> Awesome, cool. David, thanks so much for joining us today.
It's been really useful,
I learned a lot, I got to see a lot.
I have some resources to go check out, but for our viewers,
we also put those resources in the description,
so be sure to check those out.
David, again, thanks so much for joining us today.
For our viewers if you liked this video,
go ahead and give it a like subscribe to our channel and
we hope to see you next time on Data Exposed.
[MUSIC]