>> You've seen a few videos on connectivity in Azure SQL.
But how can connectivity and
the different options you select affect performance?
Learn more in this episode of Data Exposed.
[MUSIC]
>> Hi, I'm Anna Hoffman. Welcome to this episode of Data Exposed.
Today, I'm joined once again by Silvano,
a Program Manager on the Azure SQL team.
Thanks for joining us again.
>> Yeah, thanks for having me, Anna.
>> In the past few videos,
and even we've done some videos with Rohit,
and I've done some videos myself,
we've talked about connectivity,
and we'll link to those videos in the description.
But I wanted to take a step back and talk about at a high level,
what are some tips and tricks.
Silvano, I know you have a lot of experience working
with Azure SQL database and Managed Instance.
What are some tips and tricks for
maximizing performance when it comes to connectivity?
>> Yeah, over the last years actually,
Azure SQL started to introduce a number
of flavors and capabilities around connectivity.
Some of these options actually do
also have an impact on performance,
especially when we are talking
about highly transaction OLTP applications that
runs literally hundreds of thousands of
database transactions in a relatively small amount of time.
Some of these tips and tricks that I learn over the course of
the years may seems basic and good old common sense.
But they actually are making
a big difference when it comes to overall application performance.
People may not realize that in
OLTP applications actually latency matters a lot.
If you can run your application code
as close as possible to your database from a latency perspective,
this will make a lot of difference,
especially if your application is very chatty.
If some of your business logic requires a lot of
round trips and a lot of database codes in order
to process your application information,
it's very important that you can run,
again, your code as close as possible.
This means that from a Cloud perspective, for example,
that your application hosts should
be in the same Azure region, for example.
Avoiding Cross Region connectivity,
which can again introduce tens
of milliseconds in terms of latency.
From Azure SQL perspective,
you can leverage some of the capabilities like
for example, the connection policies.
Connection policies is effectively defining how
clients are connecting to your back-end database in Azure SQL.
There are actually two different connection policies available.
One is called proxy,
which means that you go through
the Azure SQL gateway
every time you are connecting to your own database,
and this gateway is effectively adding a little latency
on your end-to-end round-trip time for applications.
We also have this redirect connection policy, which is instead,
after the first connection happens with the gateway,
it's basically giving back to
the connection to the application client.
It's given back the back-end IP address of the database node,
which is hosting your database.
This means that all the subsequent requests coming through
that particular database connection
are going to go directly to your back-end database node.
This means, again, when there
are tens of thousands of database transactions,
this means a lot of time will be saved and
a lot more performance would be given to your application.
Some very trivial recommendations will be to filter,
to make sure that from your application code,
you're actually only retrieving
the columns and rows that your application actually need.
Again, in a low traffic type of application,
this may not make a lot of difference.
But again, when you have potentially thousands of
concurrent users that are interacting with your database,
objects, tables, and views and such,
making sure that you're only retrieving what
you're needing can save a lot of
kilobytes or actually megabytes of traffic between
your application and your database,
and again, can make the difference between
very high-performing application and
a poorly performing application.
You can also find some benefits by
batching operations that are
basically going between your application code and your database.
These operations can be both read operations, like for example,
you can request the multiple result set as part of
a single round trip by
batching multiple select operations, for example.
This can be done in your database command,
in your application code,
or it can be done in a server-side stored procedure.
By this means that your client applications
will then consume multiple result sets
and will be able to process
more data in a single database round-trip.
Again, there are a number of good reasons to apply
this trick to your database access code,
because you can actually reduce the number
of round trips and make your application much more efficient.
The same happens also when you are
inserting data into Azure SQL database.
You can find here in the slide and in the video command,
so you can find this link
to an article that we wrote a long time ago,
which is still relevant about how to use
batching to improve application performance,
especially when you are loading data into Azure SQL database.
You can effectively improve
your application performance by several orders
of magnitude by using this approach rather
than going with a row by row type of approach.
Connection pooling is also
very important aspect to
consider when you are creating your applications.
Most of the client libraries that we have for
Azure SQL are offering connection pulling out of the box.
Some of them are not.
But it's important for developers to
always understand how connection pooling can effectively
help in reducing this initial connection time
whenever you are opening a new connection.
Instead, by leveraging connection pooling,
you can just pick up an existing connection from
your connection pool in your local application space,
and then save is additional overhead in opening up a new one.
Last but not least,
we just announced recently a new hardware options for Azure SQL,
including for example, M-series and
F-series type of hardware also.
These new hardware options are
offering accelerated networking out of the box,
so you don't need to configure anything on your database instance.
You can leverage accelerated networking
from all the client VMs and
services that can enable that option on the networking side.
This is reducing to literally fractions of a millisecond,
the round-trip time that it takes
to connect from within
your application code to your database code,
and it's making again,
a lot of difference in your OLTP applications.
>> Awesome. Wow, Silvano.
That was a lot of things,
but it was also a lot of things that I
think you could implement today pretty easily.
You can take a look at your T-SQL queries that you're
writing and make sure you're leveraging things like batching,
things like you can set up accelerated networking
pretty easily sometimes.
The connection pooling is a new one for me.
I'm not actually really familiar with that one,
so I'll have to go look that one up.
>> Yeah. Connection pooling is, again,
definitely not a new option.
Basically, most of these client libraries,
what they could do is,
the moment you are opening up a new connection with the database,
then you're done with your database worker,
and from an application standpoint,
you're just closing it by calling
some of the libraries method that we have,
instead of closing immediately that connection.
We are basically keeping the connection already opened
in the pool in the application memory space.
So that next time the application is doing open,
is calling an open method on a connection,
which has similar characteristics in terms of server name,
database name and such,
it's basically picking up that connection from the pool instead of
requiring to open a brand
new TCP socket with your back-end database.
That guide option is already saving you a lot of time.
>> Got you. Cool. So it's interesting,
these subtle things that can really save a lot of time.
Thank you, Silvano, for joining us
today on Data Exposed to learn more
about some ways you can improve
performance via the way you connect.
If you liked this video,
please like this video, and comment,
and let us know what you learned today
and what you're doing as far as connectivity goes,
and we hope to see you next time on Data Exposed.
[MUSIC]