-: Moving right along through this section,
in this lesson.
We are going to talk about monitoring and updating
statistics about data across the system.
To start off with,
we're going to talk about what that actually means.
So we're going to take a look at that requirement,
talk through what that means
and we're going to use Synapse SQL
as we move through this lesson.
We're also going to about auto-create statistics,
what it is and how you use it.
Then we'll follow up by talking about your statistics
and how to know whether or not they are up to date,
but to get started let's jump in
and talk about what this actually means.
So what are statistics in Synapse SQL?
Well, as we look at your dedicated SQL pools,
the more that that SQL pool knows about your data
the faster it can execute queries.
And so what we're essentially doing,
is we are just collecting statistics
or information about your data,
and leveraging a SQL pool query optimizer
to take a look at the best way to present those queries.
So when we look at statistics in Synapse SQL
first, they're vital to query performance
and then following on that
having those statistics allow us to avoid guesses
and lead to better data distribution.
So why is that important?
Well, it comes down to cost-based analysis.
So in that SQL pool query optimizer
one of the things it looks at is cost.
And so it is going to examine the best way to run your query
so that it saves the most amount of money for you.
And with that, it's going to analyze several different plans
or various plans,
depending upon the query that you're running
and choose the best one.
Less processing time, more money in your pocket.
All right.
So how do we look at automatic creation of statistics?
Is it automatically on, do we have to turn it on?
Well, in this case, you do have to turn it on.
And so the first question you need to ask yourself is
is it on? So you can jump into SQL pool
and you can run this query,
SELECT name, is_auto_create_stats_on FROM sys.databases
and what that'll do is that'll actually spit out
and tell you whether it's on.
If it's not on, you're going to need to alter the database.
So alter the database and then your data warehouse name
that's where the, your stuff here is.
Then you're going to set auto-create statistics to on.
Once you've done that, any statement in this list.
So select, insert-select, create table as select,
update, delete, explain.
Any of those statements,
will automatically trigger the creation of statistics
in your data.
Don't forget to update.
So the best practice is to update your statistics
on date columns each day as new dates are added.
So here's the deal.
As you update and add data to your database
if you are not updating the statistics,
your data distribution is going to get out of whack
and that could potentially lead to your query optimizer
not actually optimizing your data the way that it should.
So it's important that you update your statistics
as you move through
and that you have some sort of scheme to do that.
So how do we do this?
Well, it's not extremely simple.
There's quite a few different ways that you can do it.
The good news is each way is actually pretty simple.
So let's take a look at one of them as an example.
So we could use create statistics.
We could define our column and define our table,
and then once we've done that,
we could just simply say in this case,
we're looking at a range of values.
So we're looking at updating statistics on rows 43
all the way up to 8,675,308.
So that's what we're looking at doing in this case
and there's actually a Microsoft doc
that is a fantastic resource for more examples
and I'm going to throw that in the link for this lesson.
So take a look at that if you want to.
I don't think that this is likely to be on the DP-203,
of course, I don't know,
but I would lean towards specific examples
are not going to be,
so I would not go out and memorize all of the examples.
Really, more important for the DP-203
is understanding what is actually happening here.
So if we were going to do update statistics,
how do we do that?
What service are we talking about?
What kinds of commands could you look at?
So something that involves CREATE STATISTICS, right?
That's the kind of thing that we would be looking at.
Key points to remember.
Don't forget to turn it on.
Yes, you do have to turn on statistics
in order for them to actually work in your SQL pools.
Next, know what triggers the creation of statistics.
So we talked about the six different statements
that we have so select insert,
select create table is select,
so basically anything that involves select
and then update, delete and explain
are automatically going to trigger
the creation of your statistics.
Again, assuming you've actually turned them on.
If you've got those 2 points down
you are good to go onto the next lesson.
All right, I'll see you there.