>> If you're using geo-replication for Azure SQL database.
There are some performance considerations
you'll want to take into account.
Learn more in this episode of Data Exposed MVP addition.
[MUSIC]
>> Hi, I'm Anna Hoffman,
and welcome to this episode of Data Exposed MVP edition.
Today, I'm joined by Monica,
a member of the MVP community.
Monica, thanks so much for joining us today.
Can you tell viewers a little bit about yourself and what you do?
>> Hey, Anna, it's great to see you.
I'm Monica Rathbun.
I'm a long-time consultant with Denny Cherry and Associates.
I've been doing SQL Server and
specializing in performance tuning for about 20, some odd years.
I'm not going to tell you my age, but it's around there.
I'm a Microsoft MVP and I am specializing
in performance tuning and absolutely love it.
It is my bread and butter.
I'm really excited to talk about this topic with you today.
>> Awesome. Well, we're super excited to have you on the show.
Today, we're going to be talking about managing performance when
it comes to your geo-replicated Azure SQL databases.
This is a really interesting topic to me because I
actually haven't heard anyone really talked about it before.
With that, I'm just going to pass it to you, Monica, and ask,
what are some considerations
people need to think of when they think
about performance for these readable secondaries?
>> I really wanted to talk about and
show you in the portal what it
looks like when you've chosen to offload your workloads,
whether it be for reporting or
just heavy hitter queries that need to happen by your end-users.
When we go into the portal,
it really gives us some great tools to analyze your workload,
especially when we're looking at DTU consumption,
resource consumption, when it's your IO, things like that.
What I like to do when I go into environments like this is
go directly into intelligent performance.
There's some really great tools in
here that give us, like it says,
insight into these queries that could be
performing not so great for our environment.
First, let's look at our primary here.
If I go to "Query Performance Insights",
you can see I've got a workload here.
I'm going to go ahead and get us some more real estate
here and close that clasp a little bit.
We can see I've got some queries
running on our database right here.
This yellow one may be a little
concerning to me and I might want to dive in and
say what is actually using 30 percent of
my resources on a regular basis here
it could be the reason why I have 80 percent
here CPU ongoing on currently.
The yellow bar here will correspond
with my query ID listed in yellow.
Very, very simple. If I click on this,
[inaudible] it actually gives me the statement that's running.
I can actually see the time and duration
for that query and it breaks down all my runtime stats.
This is fantastic when I'm going into an environment to tune.
It makes it really quick, really easy,
and I can pinpoint exactly what's going on.
Now, let's look at the exact same thing on our DR workload.
The common scenario is you have a report that's running really
long or your users are complaining that a report is running long,
that's normally not that way.
I'm going to go ahead and look at our readable secondary.
Again, this is geo-replicated exactly the same database.
I'm going to my DR version here.
When I click on that database,
again, I'm going to close this.
I'm going to go back and look where I just looked on my primary,
which is back down to the Intelligent Performance.
We'll look at "Query Performance Insights" because again,
that's my go-to when I want to see
the queries that are running and how it's
making a effect on my performance, CPU DTU consumption.
That's where I go. When I go to "Query Performance Insight",
look at this, the picture looks exactly the same.
If I look, I've got that same yellow bar and I've got "808".
Guess what? If I click on "808" It is that exact same query.
I'm going to see the exact same runtime statistics.
The reason why this is is because Query Store is
actually replicating information from the primary database.
It reminds you right up here,
it's telling you that all the workload is coming from the primary,
which really cuts off
my options on a quick at glance into the system of
what queries are causing my performance issue within
the portal that I can get to and
remedy really quickly and identify.
I need everybody to keep in mind and what I
like to tell them is when you're
deciding to offload your reporting from production to a secondary,
which is a really great option.
Keep in mind when you're using Azure SQL DB is that
everything is replicated from the primary to that secondary.
It does not have its own Query Store.
If we were in SSMS Management Studio,
you would see that Query Store is read-only.
It just brings the information for the primary.
Any automatic tuning recommendations that might happen,
any performance recommendations that are sitting here under
intelligent performance will all be listed
and given by the workload of the primary,
which you need to keep in mind
again when making these types of decisions.
Anna, does a lot of people
actually realize that this is what's going on?
I find when I'm talking to them that they're like,
"I can't find what I need,"
and they think they're able to identify it,
but it's really the primary's workload.
>> Yeah, I mean, I think we see this a lot and I
think a lot of people don't realize that this is what's happening.
I, myself tend to forget that this
is what's happening and this is
what we're doing behind the scenes.
This is really useful, Monica.
Are there things we can do like can we index a secondary?
Can we monitor still,
how can we manage performance for these secondaries?
Because of course, they're still super
important for our reporting side.
>> Reporting has different needs.
The data usage in your workload
needs to have different statistics associated with it.
Therefore, you can get better plans for
that workload and not exactly the primary's workload.
When it comes to indexing,
there may be reporting
index needs for specific reports that you need to do.
Here's another caveat that people don't think about.
When you are actually using a geo-replicated environment,
all of that, again,
replication is still coming from your
primary and that includes your indexes.
But what you can do is you can actually dive
into your query plans using DMVs or using SP,
who's active, and get the plans of what's currently
running and decide what you're tuning needs actually are.
You could create those indexes on the primary side.
Those indexes would be replicated over to the secondary.
There are still ways to get the indexing, but unfortunately,
because it's a read-only,
I cannot actually create the index on the secondary only.
I end up with
a little bit extra workload on that
primary for maintaining that index,
when that's not the index set I might not use.
It's the index for the reporting needs,
that's what statistics do.
There's no actual statistics on your secondary.
There are some temporary statistics that
it will create inside "TempDB" for you,
for the optimizer to be able to use,
but those are only temporary statistics.
It really just brings the statistics over from the primary.
You know how you can right-click and
create statistics when you're doing
some development and really fine tune
with the optimizer sees statistic-wise?
You can't do that on the secondary, you lose that as well.
Again, because it is a read-only.
When deciding to do these things,
you really have to look at your reporting workload.
It might be an ETL situation that you need to do instead.
Therefore, you can get some more
granular performance optimization options
available to you instead of using a readable secondary.
You've got to actually think about your workload.
Well, you're going to be doing with it
because unless you are a diehard DBA that
knows how to tune using
the DMVs and using scripts that the community uses,
such as Glynn berries,
diagnostic scripts, things like that,
you're really not going to be able to use the portal to
identify and tune like
you can with the primary on Azure SQL database.
>> Awesome. Thanks, Monica. That's really interesting.
One thing that I do find interesting
when we do things like this and we talk about topics like this,
is that if you have
those performance skills that you were talking about,
they're going to be super valuable here.
It's not like you move to
Azure SQL database and you don't need your performance.
>> It goes like that.
>> We're giving you some insights for your primary,
but like you mentioned, for your secondary,
you're going to have to use DMVs,
other tools, just to clarify,
do all those tools still work?
>> They do. However, when you go to Azure SQL DB,
you have a shared TempDB.
When you want to dive into what's consuming
TempDB and what's consuming some other things,
because your database is
shared resources with other databases out there,
there are some things that you can't get into.
You can still look at weight stats,
you can still look at your query plans.
There's a list of DMVs that are available for Azure SQL DB,
but you may run into a roadblock or two with
your good old favorites that you can no longer get to.
There's still some tweaking.
I hope maybe things will change in the end.
This is still an evolving workspace for all of us.
I'm looking forward to what's coming to make
my life a little bit easier when it comes to tuning.
>> Awesome, thanks so much, Monica.
As people think about this and we close out the topic,
are there any final tips and tricks or
general advice for people who are working with secondaries,
are getting started with performance in SQL database?
>> Query Store is your friend when it comes to any tuning,
I absolutely love Query Storing and
having it on your primary and being
able to dig into it and forcing
a plan is your quickest route to performance tuning for me.
When you're actually thinking about having a secondary readable,
my biggest tip is to think about it.
What is your workload look like?
Do you get a lot of calls as you have
your environment now before you move to this,
and what kind of tuning are you doing
with your users to make it faster?
Is there other options you can use?
It's not so much of a tip on how to tune on the other side,
it's knowing what you can tune
and what you won't be able to have quick access to,
I think is the number one question.
The second thing is that you don't know that your DR is actually
being used just from
the portal because all the metrics are coming from the primary.
You have to actually dive into "Sp_who",
"Sp_who's active" to see who's active to see
the workload coming through inside SSMS.
That's a big thing you could say
my report writers are actually using the secondary.
If you never check on it,
they could be hitting your primary database to get their data.
You have to keep an eye on things like that.
>> Awesome. Those are great tips and overall,
Monica, this has been a really insightful session for me.
I personally learned a lot and I
think our viewers probably did do so.
Thanks so much for joining us on the show.
To our viewers, we put some links in the description for you to
dive deeper into this topic and learn more.
We'd love to hear your feedback.
If you want to leave a comment, let us know what you think.
How are you thinking about using your secondaries and
accessing them and monitoring performance and tuning performance?
If you'd like this episode,
go ahead and give it a like subscribe to our channel and
we hope to see you next time on Data Exposed.
[MUSIC]