>> We talked a lot about configuring
your databases and managed instance after you deploy.
But when it comes to performance,
there are other tasks involved for
configuring and maintaining Azure.
While many things are the same with SQL Server,
understanding some of the defaults and the options
that are available can help you hit the ground running.
So Bob, what are some things people
should consider when it comes to performance?
>> That's right in, there are things
specific to configure a performance for Azure SQL.
They're going to feel a little bit like SQL Server,
but a little different for the Cloud that you need know about.
One of the things first of all,
that comes to my mind is TempDB,
this is just an area of
configuration paying for customers over the years.
But in SQL Server, we've made this better.
One of things we've done is
auto-configure the number of files for TempDB
to help you place out your allocations space
with TempDB to reduce latch contention.
What we've done for TempDB in
Azure SQL is we done somewhat in SQL Server,
we're going to scale the number of
files for you depending on how you deploy.
So example in general purpose,
we'll scale them our files
depending on how many vCores you've deployed.
We're always going to keep TempDB on
local SSD drives for maximum performance,
and since this gets recreated each time,
even if he fail over doesn't necessarily matter,
they're on these local SSD drives.
Don't worry about TempDB I/O,
we've got that one figured out for you in
a lot of you do that in your deployments.
You can get 12 files with Managed Instance available,
so we don't necessarily scale that one
for you depending on vCores.
Some of the options you pick that we've done with
trace bugs over the years there through alter database options.
Those options are appropriately
a setup for you just like our in SQL Server,
but in MI you could actually change those and then finally,
the TempDB metadata optimization that's there in SQL
19 is not available quite yet for Azure SQL,
but we expect that to becoming
soon both for database and Managed Instance.
Anna talked in a previous module about loading data,
this is a very important point.
In order to meet our SLAs,
we need to use full recovery for Azure SQL databases and in
Managed Instance and so you
cannot use minimal logging for bulk operations.
It's important for you to consider that and you could run
into some transaction log rate governance scenario.
Getting those batch sizes
correctly and keeping in mind about bulk-import,
you need to keep that in mind.
But you can't bulk important attempt db though,
that is set up to do minimal logging bear.
That's an option for you,
it doesn't meet your needs for your database.
File groups are interesting.
File groups are something that's used
a lot for physical placement of
files in your SQL Server environment in many cases.
But we don't support you
going and configuring physical placement of files.
Now, we have found because of
the performance guarantees we have
around our different tiers and options that,
that doesn't really matter as long as we can
support the number of IOPS and latencies for you,
having the ability for you to move
your files were in different drives,
not having that in Azure,
we don't believe that's an issue for you,
it takes the burden off you.
It is possible that the number of files
you do and the size of the files in say
like I'm managed incident instance environment can
be important and can affect IO performance.
We get some detailed documentation on
managed instance on how you can
tweak that a little bit because we're
using Azure storage in the background and the number and size of
these files can affect and get and gain you performance.
Now MaxDegreeOfParallelism is something
that is really important for our customer
and we have all the different options you
need with SQL Server and managed Instance to go support this.
For example, alter database scope configuration,
which we introduced in SQL 16,
that is there for you to control max stop.
Managed Instance supports SP configure just like a SQL server,
so you can control max stop for
the entire instance if you need and say Managed Instance,
we allow query hints and Managed Instance also supports
Resource Governor for you to configure MaxDegreeOfParallelism.
Then finally, Resource Governor itself
as a feature behind the scenes,
we do use Resource Governor to support some of our limits.
But at Managed Instance,
we'll give you access to
user-defined workload groups and
pools for your own Resource Governor needs.
These are some interesting areas of configuration for performance,
where these are topics very familiar to use a SQL Server user,
but some differences that you need to keep in mind as you're
configuring performance for Azure SQL and Managed Instance.
Now maintenance really interesting as
I talk with Anna about building like
how do people maintain performance for Azure SQL and SQL Server?
The two things that come to mind are maintaining
indexes and statistics. Well, here's the good news.
Everything you're used to using in SQL Server is simply
there for you even automatic
statistics being used at database level.
When you think about rebuilding your indexes or
reorg indexes or using
statistics from an auto perspective
or even maintaining your own stats.
All those things are capable and available for you in
both Azure SQL database and in Managed Instance.
In this video we talked about configuring
and maintaining performance.
Feels a lot like SQL Server?
Difference is you need to be aware of
when it comes to things even like TempDB,
being able to having full recovery for loading,
using the indexes, maintaining statistics again
feels like SQL limit different for Azure SQL.
Stay tuned to next videos.
We continue to talk about how to
optimize performance for Azure SQL.