>> We've talked a little bit about how to
monitor performance for Azure SQL.
Let's look at an example.
In this example, I have an Azure SQL database already
deployed as a general purpose service tier with two vCores.
I'm showing in front of you a very familiar tool to all of you,
that is SQL Server Management Studio.
I've got one of those familiar DNV queries up in front of me right
here to show you how to
monitor active requests for an application.
Let's take a look at an example of how to monitor performance for
an application running as
Azure SQL and see if we can spot some possible problems with it.
From my command line,
I'm going to kick off my workload and I'll tell you
a little bit about how I'm running this workload.
Let me start it off and run it,
and let's go monitor it with Management Studio.
You run this DNV set of queries,
and you're going to see something very familiar to you.
You may have seen before,
things like runnable and things like
SOS scheduler yield wait types when you're
running queries and monitoring activity against the SQL server,
in this case, Azure SQL database.
If you look at this and you want to
go look at a different query to see resource usage,
we can use the new dm db resource stats.
This is going to show me,
on an every 15 seconds snapshot against my Azure SQL database,
what is my CPU, IO,
memory consumption, law consumption against my Azure SQL database.
If I click this a couple of times,
you're going to see something interesting about this pattern.
Notice here in both these intervals,
I'm using almost 100 percent of the CPU for my Azure SQL database.
You click back again here,
for the exact request, you can see,
it is still running and chewing up those CPU resources.
Let's look at what the workload actually is.
This is an example of using the ostress
to log users in so many demonstrations over the years.
Open source tool you can download to run a series of queries.
In this case, I'm connecting to my Azure SQL database server,
running this query workload,
logged in with 10 users,
each running it in two iterations or cycles,
so 10 users concurrently running this query.
Here's what the actual query looks like.
It's not that sophisticated.
I'm actually using, in this case,
I'm assigning a value to a variable,
avoid any latencies from result-set processing.
I'm really pushing any CPU resources that this query needs
onto the back-end of SQL server running this in a loop.
I come back over here and I can see the query is still executing,
but it's finished now.
If you look at this point,
you can see this query has run in a minute and 43 seconds.
Well, is that bad or good?
If you go back and look at the monitoring,
the results I had, remember,
we had a lot of runnable queries and waits
on SOS scheduler yield. What does that really mean?
Let's go look at the Query Store.
In Management Studio, you've probably
seen this tool before from SQL server,
and I'll look at my top resource performing queries.
In this case, this query ID
represents that workload I just showed you.
Now, what does the statistics of this query look like?
Let's hover over here and look at the plan for a second,
and we'll see some interesting statistics for us.
Number one, it's been run quite a few times.
That's our number one running query on this database.
The average duration is almost like five milliseconds.
Well, is that a big deal?
Well, as it turns out, for this workload,
that's too slow for me.
Based on the fact that I had a lot of
runnable status queries with SOS scheduler yield wait types,
I'm suspicious that CPU might be a bottleneck for my application.
Query Store can help you further.
I can look at Query Wait Statistics and see that sure enough,
the top wait type for this application is indeed CPU.
This is really an example of
a running scenario where I'm
seeing that I've got a lot of running queries,
but I'm bottlenecked on CPU resources.
I can further prove that by clicking here and seeing that indeed,
the query waiting on CPU is my workload query.
In fact, I can tell that out of the almost five milliseconds,
almost four of it is waiting on CPU.
Another way to monitor this workload
besides DNV queries that we're used to,
using Query Store through
our Management Studio is looking at the Azure portal.
Remember, I mentioned in a previous video that
Azure Monitor can be used
to look at your workload, and sure enough,
you can see it from Azure Monitor that
the SQL workload for CPU is like
at 100 percent during this time-frame.
This is really awesome. This is
Azure Metrics as part of Azure Monitor,
automatically integrate my Azure SQL deployment,
showing me almost like a PerfMon graph
of compute utilization for my Azure SQL database deployment.
One other thing I wanted to show you is that,
let's say you want to see a history
of compute utilization over time.
I can go down here in the portal and click on
"Logs," and I'm going to, automatically,
by deploying Azure SQL database,
get a series of log information
that's collected in Azure Monitor behind the scenes.
Then I can do something called Kusto queries.
These are queries that allow me to look at metrics,
things like CPU percentage against my Azure database over time.
I can see here that yesterday,
I was running a query and I was getting
a similar type behavior for compute utilization.
I can use Azure metrics to look at things
a little bit more real-time over maybe in the last week,
last hour, last 30 minutes,
or use something like Kusto
with logging for Azure Monitor to look at
the compute percentage or CPU percentage on
my application for Azure database over a long period of time.
Here's an example of monitoring with Azure SQL.
Looks familiar to you?
Management Studio, DNV queries,
Query Store, all what you're used to.
In this particular case, you can see here,
we don't have enough CPU resources for this application.
I can look at it through DNVs,
through Management Studio, or through Azure Monitor and Logging.
Stay tuned for our next video,
where we'll see how to scale
our application and improve performance.