>> Learn about the crucial metrics you need to monitor in
Azure Monitor to keep your VMs healthy and monitored,
this week on Data Exposed.
[MUSIC]
>> Hi, I'm Anna Hoffman
Welcome to this episode of Data Exposed.
Today I'm joined by David Pless,
a Program Manager on the Azure SQL team.
David, thanks so much for joining us today.
>> Hello, Anna. Happy to be here.
>> We are happy to have you here.
Now, in previous sessions we have talked about
virtual machines uncached versus cached performance.
But today we want to examine
the Azure Monitor and metrics that can help
us track resource heath
and virtual machine and some of its levels.
To get into this, David,
what is the purpose for tracking VM cache health for SQL VMs?
>> Basically, the remote storage that we have for
our virtual machines should be
measured in terms of IOPS and throughput,
IOPS being the number of requests to
storage or the virtual machine,
and throughput being the amount of data that's transferred.
Our disk in the virtual machines
have limits for IOPS and throughput,
and when we hit those performance limits,
that's when we're getting capped at those levels.
But there's two limits,
and we talked about this in our previous Data Exposed sessions.
The max uncached disk throughput is
the default storage maximum limit
that the virtual machine can handle,
and the max cached storage limit is
a separate limit that only applies when you enable host caching.
As we've discussed, we definitely want to
enable host caching for the data disk.
Host caching works by bringing storage closer to
the virtual machine that can be
written to or read from very quickly,
and the amount of storage that's
available to the virtual machine for
host caching is going to differ between the VM series and size.
The machine that we're going to look at today,
and you'll see that I named it DPS or DP SQL general 1.
That is a DAS D3 that comes with 200 gigs of cache storage,
16,000 IOPS, and a 128 megs of throughput.
But these are the uncached limits.
When we enable caching,
we're going to get about a 30 percent bump in
most workloads when we enable recaching for the data disk,
and it's read-only for the data disk
and no caching for the [inaudible] ,
last thing to say here, with tempdb
being placed on the ephemeral D drive in most cases.
That's the best practice scenario, and
it's where our storage configuration leans us towards.
>> Cool. Do you have something? Can we take a look?
>> Absolutely. Yes, I have two tabs
that I've set up, and I'm doing this so we
can look at the metrics separately
and how to find the metrics and pin the metrics,
and then what a dashboard looks like
that I've already had some metrics pinned to.
The first thing I want to show, though,
down here there's a grouping
for monitoring, where we have Insights, Alerts and Metrics.
I'm doing this specifically in the VM.
I already went to the virtual machine blade,
clicked into the ''Virtual Machine'',
that's something that's really important to
understand that I can get to the metrics here.
I can look at it holistically through Azure Monitor,
and I think, Anna, you have that link that you've shared?
I think we had a couple of links.
One is for Azure Monitor and the other is to
talk about some of the metrics that we're going to look at.
Here I want to start though at,
because sometimes this can get overlooked, at the Overview.
We have a tab here for monitoring.
So when you click this Monitoring tab,
there's a couple of things I want to point out.
One is any time you see this pin,
this means that I can click this pin, and pin it to
an existing dashboard or a new dashboard.
I get this question all the time.
I show this view, and folks are, like, how did you do that?
This looks great. I would love to have something customed too.
This is how I did it.
Essentially what you can do is take
a number of apps and tiles and pin it to a dashboard.
It just refreshed. It doesn't auto refresh.
You can take your metrics and pin those to this view.
I'll talk a little bit about what we're seeing here.
You can see that this is already populating.
I already have a workload running.
But let's start with how do I pin these things?
So all you have to do is click these.
If you notice we have the metrics of CPU, network,
disk bytes, disk operations per second,
that's my IOPS and my throughput,
and then my memory health.
Now if I were to click into any of these,
if I was to click into Memory Bytes (Preview),
I'm going into Metrics here, where I could start adding
additional metrics and continuing
to modify the line chart that I have expressed here.
I can change it to an area view.
I can do a few things here to
get to the data and look at it in a particular way.
All I had to do is go into the Overview and then
click into this Monitoring section.
That's great. It's a really quick start.
It's really nice to be able to see this up front.
I think we have that Azure storage metrics reference.
It's specific to the metrics that helps diagnose disk IO capping
and that helps you diagnose VM IO capping.
I just need to see this myself.
I just want to populate these as I wish.
What's nice is that
the search is really easy to get to.
If I say data disk IOPS,
that's my data disk IOPS.
I could add a metric for,
let's say, my data disk bandwidth,
and I'm grabbing this.
What I would recommend doing is grouping these by purpose.
In this case, my purpose is my data disks.
These are like, let's say, your data file, your log file,
and if I had tempdb placed on the remote disk,
that would be my data disk.
But my OS disks,
let's grab these too, OS disk IOPS,
it's really nice that I can be able to search.
>> Yeah. I noticed, David,
you are adding all of these metrics on the consumed percentage.
Is that an important thing for folks look at?
>> Yeah. These are the key ones.
It's a really important point that you're bringing up.
Let me grab OS data bandwidth.
I think it is what I was looking at.
OS data disk bandwidth, there we go.
It's a really important point that you're bringing up.
Here's all you need to know.
We have essentially eight metrics,
data disk, OS disk,
IOPS, and bandwidth,
bandwidth being the throughput that's at the storage level,
VM cached IOPS, cached bandwidth,
uncached IOPS, and uncached bandwidth, eight counters.
I'll show you this on the other dashboard.
If you get to a 100 percent on any of those,
you know that you've reached the limit for
that metric. It's that simple.
Yes, that's a great question.
The consumed percentage is very important and those are
why we specifically target those eight counters.
Speaking of those eight counters,
and I have been running disk speed in
various shapes and sizes to
punish this VM and push it against those limits.
Let's take a look here.
If you see, these are those eight counters.
I'm going to scroll down just a tad.
I have these eight counters, and I group these,
as I said, by purpose.
This is my virtual machine set,
and this is my data disk set.
We can see here that, I'm going to scroll down just a tad,
I'm highlighting on data disk consumed percentage.
I'm hitting around 66, 67 percent.
But what is the cap?
What is the limit?
I'm being limited at the VM, in this case,
because I can see that my cache bandwidth is 100 percent,
and so is this uncached bandwidth.
You'll see that it reaches 100 percent as well.
So in this case, better storage does not help me.
I either need to tune my workload or resize the VM.
You're getting all of that just by
looking at Azure Monitor Metrics.
>> Awesome. This is a great simple way to diagnose,
is it at the VM level or the data disk level?
These are really useful, and I think
everyone probably using SQL Server
virtual machines should be pinning
these or monitoring these in some shape or form.
David, this has been really useful.
Any final words of advice for
users who are starting to monitor their VMs?
>> Start simple, group by purpose,
keep your VM counter set
separate from your data disk set,
separate from your OS set.
Remember that if you're looking at VM cache health,
you have to [inaudible] from the Azure Monitor Metrics perspective.
I absolutely still recommend leveraging perfmon on the VM,
especially if you've already grabbed
those source counters that we talked
about that in that Data Exposed session.
Grabbing your source metrics, targeting those metrics,
and continuing that monitoring approach
even after you've migrated to the Cloud
because it's consistent, and you'll actually get
a good idea of the shape of your performance over time.
But again, if you want to look at
the health of the VM itself, especially the cache,
you really need to do it through Azure Monitor Metrics.
>> Awesome. That's great advice, great tips.
Thank you so much, David, for coming on the show today.
For our viewers, thanks for joining as well.
We've put some links in the description for you to learn more
about these different metrics and how to get them set up.
If you liked this video,
go ahead and give it a like,
subscribe to our channel.
We hope to see you next time on Data Exposed.
[MUSIC]