>> [MUSIC]
>> Hello everyone. I'm Bob Ward from Microsoft.
Welcome to a special edition of
Azure SQL for beginners on performance.
A guide to performance troubleshooting with Azure SQL.
In today's episode, we're going to start
diving in to those scenarios we talked
about in the introduction and the first scenario is
a high CPU problem due to a missing index.
Now you might remember that we
had this technique we talked about in
the introduction about how to attack
a performance problem. It's either running or waiting.
It's either a high CPU problem or low CPU problem,
which could be awaiting issue.
Today, we're going to look at a high CPU problem.
If you remember the different scenarios we talked
about that we're going to cover in this series.
Today's series going to focus on
that high CPU problem that is related
to or due to a missing index.
Now, what we're going to do in this series for
each scenario is give you a troubleshooting process.
We're going to talk about a step-by-step process
that we're going to do for each scenario.
Then we will show you a demonstration of that process.
But don't forget, anytime you're looking through these videos,
you can go to the Azure SQL workshop and
see and try the scripts yourself for each scenario.
For this scenario, what we're going to do
is start off and say, yes,
we've identified a high CPU problem
and we're going to show you how to do that.
Then we're going to go and find what are
the top resource-consuming queries
or queries by CPU because it's a high CPU problem.
Now, it's possible that what we're
going to do is look at a query plan
for one of these queries that has a top resource
consuming by CPU and we're going to see,
is there an index missing which
we're going to love is in some cases,
SQL Server can make recommendations based on
your query performance pattern that
an index could be used to solve your problem.
What we'll do then is we'll go apply
that recommended index and then we'll
go see if performance has
been improved. Let's go see it in action.
We're going to start here with a database we've created in
Azure SQL database and you can see here on the left,
and we're going to create a table for customer data.
We're going to have a unique key.
We're going to have a customer ID field
and detailed information about the customer.
We're going to populate some data. You can see here.
Then what we're going to do is create a stored procedure to
select and go get data by this unique ID.
You can see here there's a select statement that will take
that parameter value and go look up a specific customer.
Let's go use the technique that we
talked about to see if a problem is running or
waiting and we'll start with
the Azure portal and we talked about the fact that
you're going to use something called compute utilization
to go see if you have a performance problem.
We talked about the way to start with a performance problem,
running versus waiting is to use
the Azure portal to look at something called compute utilization.
You can see here in the Azure portal,
here is my Azure SQL database I've deployed.
What I'll do in this overview page is click on
"Monitoring" and go down below and look at compute utilization.
You can see on this chart right here, for CPU percentage,
we have this window of time where we have a sustain
100 percent CPU consumption on our database.
This is a key signature to
a high CPU problem that we're going to narrow down.
What's our next step we talked about in our process?
Let's go identify queries that might match that.
I'm back over here in management studio,
and this is where we're going to use the Query Store.
In Management Studio for my database,
I will go to the Query Store and use a report called Top
Resource Consuming Queries to
look at which queries consume the with CPU.
You can see here several different panes on this query,
and the lower pane it shows the query that's involved
you can see here in the plan at
the bottom it's using a table scan.
I already know it's not using an index.
But also, I can do different types of metric data.
I can look at an average duration for
that query and you can see here it's
almost about two seconds to run.
But what's is really nice, I can flip over,
look at the average CPU time for this query.
I can hover here to the right and
look at for the plan and see that it's almost like
a second of CPU time running for the specific query.
Now we already know by looking at the bottom here,
that SQL Server has recommended
a missing index that could help our performance. Let's do this.
Let's right-click on this in this window right
here and see if there's a recommendation that can help us.
I can see now in a query window,
SQL Server has recommended a syntax to
create an index that might help the performance of our query.
I'd like to go implement that.
What I've done is I've built a script that
mirrors what that looks like and in this script,
I'll give a name,
like a name that makes sense for my index.
You can see that right here on
the table called customers on the tab key column,
because that's what we're going to search on that tab key column.
Now I've added a few syntax details.
I've added online and resumable,
which are options,
so when you create your index,
you keep your application as highly available as possible.
Let's go create this index and let's
see if performance has been improved any
and what we'll do is we'll go look at maybe
down at our workload to see if performance has changed.
With this index applied,
I'm going to go down here and in
these scenarios we use a tool called
OStress to do these workloads and you can
see when I ran it before it took
like 10 minutes to run this workload.
Let's go see what it looks like again,
for like five users, you can see right here.
Let's go see what looks like a game with the index applied.
I'll run the workload again and wow,
look at that under a second,
we were 10 minutes.
Now we're under a second to run
the same workload and all we did is apply the index itself.
Let's go back here and see the Query Store
reports and refresh it to see what's going on.
Now you can see at the bottom down here for
this query that we have an index seek going on for our query.
Instead of the table scan which is more expensive,
use a lot of CPU for that same query,
we're now using an index and we can go
over here and see what was the average CPU.
While look at that, you can see now
the average CPU is less than a millisecond now for this query,
we significantly improve the performance.
Now what I'd like to do though,
is make sure that over time,
like an extended period of time,
this performance will hold true,
so I run that workload with a bunch of executions,
same type of thing using
that index and Query Store can show me that data.
You can hover over these dots
here to the right and you can see for this index seek,
what did that performance look like?
Was it consistent using that index?
If I hover over, you can see the number of executions.
It looks like about 500,000,
so I ran 500,000 times with
the index and I got the same average CPU performance.
That's the consistent performance
I'm looking for in this scenario.
Let's go back to the portal now that
will prove that things are better.
Look at compute utilization.
You can see look at this now,
much shorter duration of time for that workload.
Much less CPU,
significantly less than before.
Now we effectively have
resolved a high CPU problem that we've had with
our workload by looking for
a missing index for the query plan recommended by SQL server,
applying it and resolving the problem.
In summary, remember problems are running versus waiting.
Find out what queries are involved,
which we did here we found
the top resource-consuming query by CPU.
We check the query plan and we saw
that SQL Server recommended a missing index.
Then we create that index and
confirm our problem has been resolved.
We want you to continue to have resources
to solve problems like this,
so at the top you can see here our index design guide.
You're going to get more details on how to build
indexes for SQL Server.
In addition, look at the other resources we have here to the left,
different resources for videos, workshops,
including the scripts for the scenario we just describe,
Microsoft Learning Path and other resources from our team,
including some books that you can dive further on Azure SQL.
I hope you've enjoyed
this episode on a special edition for Azure SQL for beginners,
where we took a look at a high CPU problem
and built an index, resolve it.
Hope to see you next time on
a special edition of Azure SQL for beginners [MUSIC]