-: How do we measure query performance?
Well, in this lesson, we're going to find out.
So what we're going to be discussing is we're going to take
a walk through the Synapse Query Store.
And if you don't know what that is, don't worry,
we will be discussing that.
We're also going to talk about some common scenarios
for the Query Store and honestly, just some common scenarios
for why you would measure queries.
And then, that's not all,
we're also going to talk a little bit about
dynamic management views,
log analytics, and Azure Data Explorer.
So with all of that, let's get started.
So first, let's take a walk through the Synapse Query Store.
What is the Query Store?
Well, it's something that provides insight
on your query plan choice and your performance.
And before we go on, let's step back just a second.
When we talk about queries for the DP-203,
we are talking about Synapse Analytics.
So make sure you keep that service in mind.
And the query tore is just something that helps us
to get insight on the queries that we're running.
So the Query Store captures your queries
and your plans and your run time statistics.
And then from that, you can take a look
at time sections or windows, and you can get information
on the queries or the plans or the run time statistics.
So it's a very helpful tool set.
However, you need to make sure that you turn it on.
And you do that with that simple snippet there,
you're just going to alter database,
put your database in, and then set Query Store to on.
So make sure that you turn it on,
because it is not on by default.
And there's 3 different types
of stores, or kind of mini stores.
So think about a store within a store,
because that's kind of what is.
So within the Query Store, there's 3 little subsets.
There is the plan store,
the runtime stats store, and the wait stats store.
So the plan store is just for execution plan information.
The runtime stats
is going to be execution statistics information.
And then the wait stats is just going to give you
information about your wait times.
That's pretty much it.
So those are the 3 individual components
that make up the larger Query Store.
So now let's talk about some common scenarios
for using the Query Store,
or honestly for monitoring your queries.
The first is an audit history of queries.
So you might have regulatory requirements
or other things that you need to use.
You need to be able to audit your history.
Next, you want to identify top performing queries,
or poorly performing queries.
This is a great way to do that.
So you can kind of take a look at what's going on
with your queries and figure out for your databases
and your data in general, how accessible is that data?
So do we need to look at optimizing our queries,
building different partitions?
Do we need to look
at changing our database structure around?
This can give us insights into some of that.
Also we can determine how often a query executes.
So in addition to understanding
the top performing and poorly performing queries,
we can take a look for how often it's run.
So for instance, if we have a very poor performing query
but we run it once a quarter, eh,
maybe we don't care all that much.
If we run it every day,
then we probably care quite a bit more.
We can also analyze resource usage patterns.
So this is going to help us both in a cost savings
perspective. And it's also going to help us to understand
where there's bottlenecks.
And identify queries waiting on resources.
So there may be queries that are waiting on resources
that we need to bump up those resources
or we need to allocate when queries are being run
to more efficiently utilize the compute
and the the resources that are available to us.
Now, Query Store is not our only solution.
That's 1 of our solutions.
And the good thing about the Query Store,
at least from Microsoft's perspective,
is they say that it stores 30 days of data.
My question is, but is that good?
It really depends upon you.
And as we look at the 4 solutions,
you need to keep in mind that all of this
is going to be very dependent upon your specific scenario
and which one's the best for you.
So 30 days of data might be fantastic
or it might not be nearly enough.
The bad for that is it is limited analysis compared to DMVs.
And we're going to talk about that next,
but you can't get the depth that you can get
off of dynamic management views or DMVs.
So with DMVs,
you have an extremely extensive option for analysis.
And before I jump any further into the good
and the bad of dynamic management views,
you might not be familiar with DMVs.
Again, this is something that is run in SQL servers,
or Power BI, or there's several different places
that you can use dynamic management views,
but basically it's a query that gives you information
about model objects, or server operations, or server health.
So you can run queries to give you information
about what's going on in your environment,
and that is a dynamic management view.
So there are a ton of different queries that you can run
to get information about just about anything that you want.
The bad for this is you are limited
to the last 10,000 queries.
Now this may seem like a ton,
but it really depends again on your specific scenario.
And dynamic management views are not going to be as easy
for large scale operations.
If you're using large scale operations,
you're going to have an easier time using Log Analytics
or Azure Data Explorer.
Another alternative of course is going to be Log Analytics.
This is fantastic for log retention.
Hey, you want to look at 7 years of data?
You can do that,
because you can pull that information into Log Analytics,
store it in a blob storage and pull insights
or run queries against that for as long as you want.
It is a little bit more costly
and it uses the Kusto query language.
Again, is that bad?
It really depends on you and your team.
If you have developers or engineers
that understand Kusto query language, hey,
it's not really all that bad,
but just something to keep in mind for Log Analytics.
And then finally we have Azure Data Explorer.
This is one of the newer entries on the list,
and it provides an extremely extensive option for analysis.
So Azure Data Explorer is used
when you want your own analytics solution
or you want full control of your data sources.
So it's going to give you a much deeper insight
than some of these other solutions.
However, it uses KQL again, the Kusto query language,
and it is going to be a much more expensive solution
than any of these other options.
It really just comes down
to what you're trying to do with your data.
And it also depends on the rest of your environment.
So if Azure Data Explorer is something that you need
for quite a few different areas
then you might want to use it here as well.
So anyways, these are the 4 main choices that you have
when you look at analyzing queries in Synapse or in Azure.
So which one you use is going to be kind of dependent
upon your individual situation.
Couple of key points to remember.
1. Have a plan.
You do need to monitor queries in almost all cases
and you want to have a plan for how you're going to do that
and how long you need the data.
So make sure you have the basics down
from a business perspective as to what you need to be doing.
If you have the Query Store, hey,
don't forget to turn it on.
That's also going to be true for these other things.
Have you stood up Azure Data Explorer?
Have you turned on Log Analytics?
Those are the things that you need to look at
if you are going to monitor queries.
Finally, use the best tool for the job.
This is a very small dive
into some of the different solutions that you could use.
For the DP-203,
you need to be aware that those 4 options exist
and there is a path to monitoring queries in Azure.
Hope this has been helpful.
I'll see you in the next lesson.