>> Now that you've learned about some of
the tools and processes you
can use for configuring and monitoring performance,
let's take a look at some scenarios.
I think many of our viewers might
have experienced something like the following.
Let's say I get a call at 2:00 AM that there's
some performance issues of MySQL database or Managed Instance.
Now Bob, you've taken
just a few support calls in your time here at Microsoft.
Can you help me through, what should I do?
>> Right Anna. Certainly over my years in support,
I have seen performance issues come to my attention
from customers and something that they often struggle with.
Over the years, we've had this philosophy,
is a problem in SQL Server running or waiting?
You just saw in a previous demo video
something like a running type scenario.
So I want to just keep in mind for the audience here,
it's just SQL Server engine in the background,
so that same philosophy is going to apply.
Let me just walk you through how I would walk through
a problem if I was tackling a performance problem with Azure SQL.
So first of all, I'm going to be looking at resource consumption,
and I showed in a demo video how to use
dm_db_resource_stats to do that.
Server resource stats can be used for MI,
you can even be using the Azure portal
or Azure Monitor to look at that.
So you first want to see what
is resource consumption actually look
like and you could even use alerts to Azure Monitor
as well to be alerted to a resource consumption problem.
Well, let's say you look at it and it's CPU,
just like we saw in a previous demonstration video.
This is what I call the running scenario,
and typically the problems that come down to customers in running
scenarios is an executing query or compiling query.
Compiling queries have a unique characteristics,
you can see things like set statistics time to
find out is that query
compiling or even through the Query Store you can see that.
But for executing queries, what do you do?
Well, you use the things you've always used.
You use Query Store, dm_exec_requests,
you look for an actual weight type called SOS_SCHEDULER_YIELD.
In fact, that's an interesting scenario,
you're waiting but running.
So the reason it's called
a waiting scenario is because you don't have
enough CPU resources to schedule your work on your SQL Server,
same thing applies here to Azure SQL.
You click at "Query stats," or "Procedure stats," to drill
in to estimated plan information about that query.
This is going to look very familiar to you,
same type of process you might use
for a SQL Server as you are dealing with Azure SQL.
What about a waiting scenario,
this is going to be very much the same as you are
looking for a SQL Server scenario. But keep this in mind.
You setup and deploy your Azure SQL with
the appropriate tier and deployment
option that as some cases have limits,
things on I/O or memory,
or various other things that could affect these types of waits.
So keep that in mind as you're looking at I/O waits or
latch waits or different types of waits in your environment.
Even things like plan cache eviction can be affected by
the memory limits of your Azure SQL deployment.
Well, how are you going to look at that?
You're going to use Query Store or look
at these DMVs, dm_os_wait_stats,
one most popular DMVs in the history
of SQL Server is still applicable,
both at Managed Instance and Azure SQL database.
For exec_requests, just like we saw on a demonstration video,
you're going to see what is
the wait type of the query that is taking
either the most resource usage or that is
blocking the world like my blocking chain.
Yes, blocking problems are the same in
Azure SQL as they exist in SQL Server.
Now there are some specific Azure scenarios
I wanted you to keep in mind of
that are there because of the way
we deploy things inside the Azure infrastructure.
So first of all, we do not allow you to have
unlimited access to the transaction log
from a performance perspective.
So you might see a wait type called LOG_RATE_GOVERNOR show up.
This is us. As you're building
transaction logs through different operations you're doing,
as we're building up log records,
we decide whether we need to throttle the amount of
log you're generating to make sure we meet those SLAs for you.
So you might see this wait type show up and in fact,
you're going to see that before you see a
common wait log wait show up.
Other types of GOVERNOR waits can show
up as well depending on the deployment,
whether they are pool,
Azure Managed Instance, or even Hyperscale.
Now worker limits are interesting.
You're used to seeing in case of SQL Server,
THREADPOOL waits indicate maybe don't have
enough worker threads or it could be a block scenario.
That type of scenario
potentially can show up in a Managed Instance,
but for Azure SQL Database,
we put a cap on
the concurrent worker limit before you even reach those numbers,
in fact, you can't even configure max worker threads.
So you might find an error in those scenarios because
you've reached what we call a concurrent worker limit,
and to overcome that,
you would need to change your vCore or
service tier to get more concurrent workers.
Now for business critical scenarios,
you can see sometimes
the popular HADR wait types like SYNC_COMMIT,
FLOW_CONTROL, and THROTTLE_LOG_ RATE_SEND_RECV.
>> Bob, why are we seeing all these waits?
>> Yeah, it's interesting you ask that Anna,
because people are looking for screen go on.
Well, if you've got an availability group,
these are normal waits to see as
part of running a sync availability groups.
Keep in mind that a business critical deployment is
indeed behind the scenes using always on availability groups.
So I've seen customers go from
general purpose to business
critical because of their performance needs.
All of a sudden they see these waits types show up and go,
"I didn't build an availability group,
why am I seeing these?"
The end reason is that we're building one behind the scenes.
So I want to make sure and call out that these waits
are not surprising and not specific to Azure,
but something you may not recognize or be aware of that could
show up because we're using availability groups behind the scenes.
Now Anna, in addition for Hyperscale,
there are a few special waits that might show
up because of the caching infrastructure we've built,
or like things like page servers.
So DMVs and Extended Events have
been augmented and enhanced
for you to look at those type of things,
or you might see some special wait types that
indicate you are being throttled from a log perspective.
But because of the way our caching infrastructure works,
you might see different types of wait types show up.
>> Thanks Bob, you really helped me and probably a lot of people
understand how to troubleshoot some of
these common performance scenarios.
I hope you stay tuned for the next video where we dive
into accelerating and tuning performance in Azure SQL.