>> In the last demonstration I showed
you in monitoring performance,
we saw an application that was bottlenecked on CPU resources.
We saw that through Query Store,
Azure Monitor through our DMVs.
Now let's see how we can maybe scale and
accelerate performance for that application
very easily in Azure SQL.
Now in front of me here, again, is a familiar tool,
a SQL Server Management Studio connected to my Azure SQL Database.
Now let's take a look through T-SQL,
how I can see what I will only see through the portal.
I can see the service tier that I deployed.
We call that service tier sometimes
service-level objective or SLO.
By running a query against dm_user_db_resource_governance,
I mentioned this in a previous video to you,
I can see the configuration.
I can see here that I've got a general-purpose, Gen5 hardware,
two vCore deployment, and see if your limit shows,
I've only got two CPUs allocated to this Azure SQL Database.
I can even use this DATABASEPROPERTYEX function and
also see the service level tier I've deployed.
If I want to scale this application very easily,
what if I could just like bump up my CPU?
That will be cool. I'm not going do that through the portal,
I can do it through SQL.
Notice here that the altered database command
in Azure SQL Database has been
changed and enhanced to
support changing the service objective or the service tier.
In this case, I'll kick off this command
in an asynchronous fashion.
I'm scaling now my deployment behind the scenes from
two vCores to eight vCores to get better performance.
I can see the progress of this scaling operation
by using a DMV or even looking at the Azure portal.
I'll go over here,
you can tell that I've already scaled.
It's that quick sometimes of an operation.
That already Azure SQL Database has moved
my Azure SQL deployment to
another infrastructure that supports eight vCores.
Let's look and see how that can maybe improve our performance.
Back over here, remember our workload we ran using OStress,
I'm simply going to run that workload again.
Then I'm going to go in Management Studio and run
the same queries that I ran before.
For example, I'll run dm_exec_requests,
and you can see here that I've got
more running queries now than before.
Still some SOS_SCHEDULER_ YIELD waits,
but more running queries.
If I go look at another DMV we looked at before, resource stats,
I can see here that even though
it thinks I've got a hundred percent CPU utilization.
It's because of the time frame that I'm
actually running this application,
that over time it's actually,
in the period time that it's going to run,
it takes less CPU percentage.
Remember before, we had three rows of 99 percent.
If I go back over here and look at my workload,
notice it's already finished.
It took a minute and 40 seconds before.
Now that takes some 29 seconds to complete the workload.
A significant performance improvement just
by an easy scale operation in Azure.
If we go back over, look at Azure SQL Database at Query Store,
we will see the difference in comparison.
If I look at my top resource performing queries,
this was the previous query in this duration,
here is the new query.
You can tell, if I hover over here,
that the average duration time,
which was like five seconds before,
is down to a second.
If I even go look at my CPU wait
statistics through Query Store as well,
and I click on it, I can see that the new query is
significantly waiting on less CPU resources.
But you can look here in the report and see that
the average wait time and CPU is even less than a second,
less than a millisecond is a trickier case.
You can see that a significant improvement I have
gained by just doing a scale operation in Azure.
Another way I can look at the scale of
this operation and the speed improvement
is going back to the portal.
You look in the Azure portal,
you can tell when I ran this workload before,
the duration of the CPU utilization 100 percent.
Here I can tell that it did peak almost 100 percent,
but the duration was much tinier.
You can even see that it's possible
for medium to scale this further out
by even adding more CPUs to my environment. Guess what?
If I pick Serverless as my deploy option,
Azure SQL would've automatically scaled my number of CPUs up to
my max number automatically for
this application without me even doing any manual operations.
Here is an example of putting it all together.
In the first demonstration I've done previously,
I managed my application,
solved its bottleneck on CPU resources.
Second demonstration, I was able to easily go in to the T-SQL,
up my number of vCores,
in a short period of time I was back up and running,
ran the same workload,
got a significant performance increase,
a much shorter duration of runing these queries because I'm
not bottlenecked as much in CPUs.
But again, Serverless can be a great option to
avoid even having to do this manually.
Check out our next videos.
We talk more about performance for Azure SQL.