>> Learn about the recommended VM-Series for SQL Server
on Azure Virtual Machines this week on Data Exposed.
[MUSIC]
>> Hi, I'm Anna Hoffman,
and welcome to this episode of Data Exposed.
Today I'm joined by Pam Lahoud,
a Program Manager on the Azure SQL team.
Pam, thanks so much for joining us again today.
>> Thanks for having me, Anna.
Always excited to do Data Exposed.
>> Always excited to have you on the show.
Today we're going to be continuing our Azure SQL VM series.
We're going to be talking about a certain VM size.
But before we get into that,
I wanted to ask Pam,
what are some considerations that go into selecting a VM size?
>> That's a great question Anna,
that's something that many of
our customers struggle with because there are
literally hundreds of different VM sizes that you can choose from.
The best way to pick is to either use one of our tools like
the Data Migration Assistant
or one of our services, Migration Service,
that can help do this work for you.
But if you're doing this on your own,
what you need to do is monitor your On-prem workload,
look for things like how much memory is SQL using?
How much IO is it generating and
there's perf counters you can use to get that.
You can look at things like page life expectancy,
target server memory, total server memory.
Those will tell you how much memory SQL Server is
actually using and how much it needs.
Then for IO, you can look at things like disk reads per second,
disk writes per second,
that gives you IOPS.
Then for throughput, you can look at disk read
bytes per second and disk write bytes per second.
Monitoring those counters allow you to see
how much your SQL Server workload is consuming.
Then you can take those specs and
match them to the specs of the VMs.
That's the best way to do it.
But obviously, it's a bit of work upfront.
>> Yeah. It does seem like a bit of work.
What if I'm in this scenario where I
either don't have that data or it's
a new workload or maybe I'm already running
SQL Server in an Azure VM and having some performance issues,
do you have any guidance or maybe you have
a favorite or a go-to that you use as far as VM sizes.
>> Wow, so amazing that you would ask me that question,
Anna [LAUGHING] because what I
would love to talk to you about is our favorite VM-Series.
Over the past year or so,
we've been doing a lot of our own internal testing and we've
been looking at what customer workloads are generating.
We've arrived at our favorite VM-Series which is the Edsv4-series.
This series just seems to have
everything in that sweet spot for most SQL Server workloads.
Probably the easiest way to show you that
is to go to the documentation for
the Edsv4-series and walk through
the specs of this series and give you
some insight into why we love it.
>> Cool.
>> Let me show you the documentation.
This is just our regular Azure documentation
online available to everyone.
They outlined many of the different VM sizes here.
This is Edv4 and Edsv4-series.
The Edsv4-series is the one that we love.
It's got its Intel Xeon platinum,
everything that you would expect in a VM,
most of our VMs have great processor architectures
and memory and an IO throughput.
But this one, in particular,
has a couple of things.
One is, it's got
the right core-to-memory ratio for most SQL Server workloads.
What we're finding is that 1-8 is the sweet spot for SQL Server.
If you've got a data warehouse workload,
you might need more memory.
If you've got a very lightweight SQL Server, you might need less.
But for the vast majority of SQL Server workloads,
1-8 is the right core to memory ratio.
If you're not sure what you need,
this is the place to start,
1-8 and the Edsv4-series has
a 1-8 core to memory ratio across the board.
All of the sizes have that, so it's great.
Anywhere you are within the CPU spectrum,
you'll have the right core to memory ratio.
That's a great place to start.
Then you can choose however much CPU power
you need and you can move up and down as needed.
The other thing that's great is
it's got really good IO throughput.
Obviously, database workloads are reading and writing data,
that's what they do so they have a much higher need for
good IO throughput than many other workloads in Azure VM.
The great thing about this size is it's got
really good IO throughput.
There's two different kinds of IO throughput which if
you've seen our past episodes on Data Exposed,
you probably already know there's local and remote.
Remote throughput is the data disks that you attach to the VM.
For those, that's usually where you'll be putting
your SQL Server data files and your transaction log files.
That's this uncached disk throughput.
The first number is IOPS,
IO operations per second,
and the second number is megabytes
per second, that's your throughput.
This is if you were monitoring,
this is the numbers that you'd be looking
for from your On-prem workload and you want to make sure that
this is at those numbers or better, so higher.
This gives you a large number of IFS and a decent amount
of throughput across all the different VMs,
especially when you start getting up into these higher end.
That's where your remote throughput.
The other reason why we love
this series and the reason why it really stands out from
other VM-Series that have a large amount of memory
is this max cached and temp storage throughput.
This is your local throughput.
These VMs have this temp storage,
that's your local driver,
your d drive where we recommend you put TempDB.
They've got a pretty decent-sized local drive,
and they also have this cache which
allows you to turn on read cashing,
and read caching can be a huge win for your data files.
You can turn on read caching for free.
That shifts some of the IO from
the remote throughput into
the local throughput because it goes into a local cache.
That can improve your performance as far as latency goes.
But what it also does is it
preserves some of that remote bandwidth
for things like the transaction log
that they can't leverage caching.
Then if you put your TempDB on that local drive,
again, that's only going to come out of
this local throughput limit.
That's not going to hit your remote throughput limit.
It's a really great way to
get more performance without increasing your cost at all.
If you're comparing VMs and you're trying to figure out,
okay, these have similar specs;
they have the same CPU,
they have the same memory,
but you're not sure which one to choose?
I would definitely go for the Edsv4.
If you don't know where to start,
this is the VM-Series to start with because like I said,
for most SQL Server workloads,
this is going to have everything you need.
>> Awesome. No, this has been really helpful and it's
nice to see the threads that we've
been talking about in other series or
other episodes of this series around caching,
around IO and throughput kind of see them
all come together with this one series.
Pam, this has been really useful for me,
I think for our viewers as well.
Do you have any final words of advice when it comes to selecting
these series or choosing to go
with this series or not this series?
>> Yeah, sure. I guess
a couple of thoughts I would leave with is one;
obviously the best thing for you to do
is to understand your On-prem workload.
If you have the ability to do any kind of monitoring,
please by all means do that.
Choosing the Edsv4 blindly is fine if you don't have the numbers,
but if you do have the numbers,
you want to verify that this is right.
The other thing is,
you're never going to match exactly what you have On-prem.
I would say, don't worry so much about,
this has less memory than I have On-prem
or I'm a little bit concerned about the IO throughput,
it's different than what I have On-prem.
It's probably okay.
Most times you're buying more hardware
On-prem than you actually need so when you
come up into Azure and you're renting
hardware which you're paying for it by the minute,
you want to get as close as you can to the edge.
You really want to match what you
have today and then you can size up later.
I would say, don't worry too much about the numbers.
Pick a VM that has good throughput,
that has a large local drive,
lots of local throughput,
and go for something that has that 1-8 core to memory ratio,
and that's going to be a sweet spot
for almost all your SQL Server workloads.
>> Awesome, cool. That's a great piece of advice around
monitoring and not trying to
over-provision because you're going to pay for that.
Pam, thanks so much for coming
on the show, this is really useful.
For our viewers, thanks so much for joining us today.
We put some links in the description for you to learn more
about this Edsv4-series virtual machine.
We hope to see you next time on Data Exposed.
[MUSIC]