>> We've seen some scenarios in how we can
diagnose some common performance issues.
Now, Bob, once we know what our issue is,
what are some specific things we should consider doing in
order to tune or accelerate the perfect we're getting?
>> Thanks, Anna. Certainly, some things to
consider that are specific to Azure SQL,
but I feel a little bit like SQL Server.
First of all, you might want to scale CPU capacity.
Now, how are you going to do that on your deployment today?
Well, if you're running a bare metal machine,
you got to get another machine,
and you got to migrate your database in SQL Server.
In virtual machine environments, often,
you can stop the virtual machine and change
your CPU configuration to get more CPUs.
But what if you don't have enough capacity?
Again, you might have to actually migrate
your database, not on Azure SQL.
We're going to provide you Portal,
T-SQL and CLI capabilities to literally scale the number of
CPUs upper bound for
your scenario that you need to meet your CPU demands.
Hyperscale is really interesting
because it does it in constant time.
In either case, you're going to experience
a small set of downtime to do this.
But depending on how you're trying to scale your operations,
this downtime can be actually quite small.
Even things like serverless provide
autoscaling of these environments.
Now, I/O performance is interesting,
and we oftentimes, in terms of IOPS or I/O per second.
I felt a lot of people in SQL Server environments
really hard even measuring that today.
Make sure you understand how to measure I/Os per second,
even things like Windows Performance
Monitor in your own environment.
You can measure IOPS with
our DMVs and metrics we provide an Azure SQL.
IOPS restrictions are dependent on your deployment option,
your service tier, your vCore model,
we always documented on what IOPS we support.
You'll have to go look for the max
IOPS for your particular deployment,
and that can show up in the form of longer I/O waits.
You need to take a look at if you're getting longer I/O waits,
could it be you're actually restricted by IOPS?
But again, our DMV show you how to go look at those things.
You can scale up and
change to either Business Critical or even sometimes,
number vCores to gain higher IOP performance.
For faster I/O latency issues,
you might consider things like Business Critical or Hyperscale.
They're going to be more, they're going to give you that
higher or better or lower I/O latencies
or better I/O performance in the deployments.
For faster I/O latency for Managed Instance,
though, it's really about Business Critical.
But specifically for Managed Instance,
it could be the number of files or the size of
your file that could increase I/O performance for you.
Then, I don't know if you've been using SQL for awhile,
but a lot of times, just batching your transactions together,
instead of what we call singleton
transactions can improve I/O latency scenarios,
and that's even applicable to your SQL Server deployments.
But sometimes, I've seen people move
from on-premises deployment to an Azure SQL,
and maybe pick a lower storage tier,
and then this type of problem will crop up.
That's more of an application change
that can actually improve your performance.
Now, memory or workers are interesting so for Azure Database,
scaling for your vCores will give you higher limits for memories,
and more concurrent workers.
For Managed Instance, it's scaling
up vCores to get that more memory capabilities.
Keep in mind that if you are using In-Memory OLTP,
It could be using a portion of
that memory for Business Critical tier scenarios.
Also keep in mind, as I've talked about in previous videos,
that we use Windows jobs to limit those memory constraints.
You might be looking at some of the DMV
that's going to show you the overall memory of,
say, the virtual machine behind the scenes,
and we're not maybe going to give you all of
that capacity because of
the memory limits we've documented per your service tier,
and that using things like DMO as job objects can show you
that true memory capacity
of the SQL deployment you're doing in Azure SQL.
Then in Managed Instance today,
you can actually change max worker threads.
I should caution you that we're taking a look at that,
it's possible we may actually throttle max worker threads in
a Managed Instance environment
because you've found some customers are
just bumping up this number and
running into problems in their deployments.
Today, you can't change that,
but that is something in the future we're considering limit,
just like we do in Azure Database.
Then finally for application latency,
Anna has mentioned before from
a network connectivity point of view,
to use redirect connections and not proxy.
That's going to give you
your best application latency performance.
But I've often found that some customers are used to
their environment of building applications
that are what we call chatty.
In other words, they're sending a lot of traffic back and forth,
and not taking advantage of things like
stored procedures or limiting their results sets,
and when they get into an Azure environment,
even everything is deployed in Azure,
they're running into some latency issues they would
not see in their on-premises deployments.
Make sure you take a look at your application, and often,
if you're migrating say from SQL Server,
this is an opportunity for you to go ahead and
optimize these chatty application scenarios.
I've shown you CPU, I/O, memory, application latencies,
things that are somewhat specific to
Azure but a little bit like your SQL Server deployment,
but don't forget that tuning
your SQL queries just like
a SQL Server and Azure SQL is really important.
Getting the right indexes,
batching applications, batching queries,
using store procedures, prioritizing queries correctly,
and properly processing result sets,
those type of things I've seen across years in
SQL Server caused customer performance pains,
they are no different in Azure SQL.
Just keep in mind,
your standard SQL query tuning opportunities
and activities are still going to be
important to achieve the best performance
you can get from your Azure SQL deployment.
>> Thanks, Bob. We've seen some new things that we can consider,
but I also really like to call
out at the end is that at the end of the day,
it is just SQL,
so you're going to have to leverage
the existing skills that you have in Azure as well.
Tune in for the next video.