>> Resumable indexes add a powerful capability which
can make DBA's life easier in SQL Server 2017,
2019, and in Azure SQL.
Tune into this episode of Data Exposed: MVP Edition to learn more.
[MUSIC]
>> Hi, I'm Anna Hoffman and welcome to
this episode of Data Exposed: MVP Edition.
Today I'm joined by John Morehouse,
who is a Consultant at Denny Cherry and Associates Consulting.
Today, we're going to talk about resumable indexes.
This is something I don't know a ton about
so I think it would be great if you
could start by just explaining,
what is this and why should I care about it?
>> Sure, absolutely.
In SQL Server 2017,
Microsoft introduced a concept that allowed us to
rebuild an index online and be resumable.
What that basically means is that we can
actually pause that process,
and then resume it at a different point in time.
SQL Server 2019, they actually extended
that to allow us to do that same process,
you start it and pause it,
but we can also add on some extra settings where we can
actually throttle the CPU utilization with a MAXDOP hint.
That really helps us as data
professionals have one, in my opinion,
a better work-life balance,
because now I can plan for
that activity versus having to do it all in one big fell swoop.
>> Awesome. We were chatting before
this and you said that sometimes or before,
this was something you might have to do on a Saturday,
very late at night or something like that.
>> Most definitely. With customers today,
at least in my experience as
a Consultant and being in the field for a number of years,
clients and customers are not deleting as much data as
they used to because storage is more readily available.
With the introduction of the Cloud and Azure,
all offerings that the Cloud brings,
storage is just as cheap.
That's always the phrase relates here.
>> Got it.
>> So with customers having
more data in larger volumes of data in tables,
building and rebuilding indexes can be a time-consuming process.
So before this feature was introduced,
DBAs in general would have to plan around that to,
we have to take an outage,
because that process could maximize
CPU throughput and cause performance issues to the applications.
This now allows us to have
a better work-life balance in my opinion, as I said,
and better granular control
as to when that process actually happens,
and I can do it in a way that
doesn't hinder application performance.
So it's done in a more controlled manner.
>> Got it, awesome. That makes sense.
I think I now have a baseline of how
this thing works and what it's for.
Now, from an implementation standpoint,
is it very difficult to set up?
>> That's a great question. Because it's
an online operation, it is enterprise-only.
If you're on-premises, you have to
have Enterprise Edition of SQL Server.
Now thankfully, with Azure SQL,
you get that out of the straight out of the box.
If you're in Azure SQL already,
you've already got that capability already there.
It's baked into the product.
So that's a really cool feature about Azure SQL.
It's really easy to set up.
It's really easy to configure.
Its just a syntactically change
in the alter or the create statement for the index.
It's really easy to implement and to control.
>> Awesome, that's great to know
that it's available in Enterprise,
but it's also available in all flavors of the Azure SQL.
That's awesome.
Now, before we get
into a demo because I know you have something ready.
My last question is, are there any gotchas that we
should really be aware of when we look at this?
>> Yeah. The one,
and I'm not sure I would classify it as a gotcha.
It might be more of a feature. I'm not sure.
With the ability to alter and control the MAXDOP,
which is the maximum degree of
parallelism configuration for the process,
when I first looked at this feature,
I was really happy about it because if you think
about it from a DBA perspective and maintenance,
if I had a really large table,
I had to do index maintenance on during the week,
during the normal 8:00am to 5:00pm.
Those are the prime work time
that my application is really busy.
I could limit that and throttle that process to
be one or two CPU cores.
Then on the weekends, if the weekend is an off peak cycle,
I could up that MAXDOP value and really chunk away at
that process because I'm
not worried about hindering application performance.
I've got the CPU resources
available to focus on my process at that point in time.
That doesn't actually happen that way.
You can actually specify a higher MAXDOP value,
and I'm going to show this in the demo,
but in reality under the scenes,
SQL Server doesn't actually take that value.
It only can take what you originally start the process with.
If you start with a MAXDOP of two,
and you try to set a new MAXDOP value or
resume that process with a MAXDOP value of,
say four, it will take the four,
and then the command will succeed,
but it doesn't actually use four, it uses two.
>> Okay.
>> That's kind of a gotcha but not really it works,
but in my mind, I was like, oh cool,
that's a really great way to really
granularly control when things happen.
It doesn't quite work that way.
>> Yeah. Maybe some feedback we can take back to the team.
>> Sure.
>> Cool. Let's see.
I know you wanted to show us.
>> Sure.
>> Let's see what you've got..
>> Time for a demo. What I've got here is
a local instance of SQL Server 2019.
We can see that I've got WorldWideImporters,
which is a sample database offered by Microsoft.
It's in GitHub if you want to go and download it.
In this database, I've got a couple tables.
In this case, I've actually got
a table ColdRoomTemperatures_Archive, this table right here.
In this case, it's actually a temporal table.
I've actually exploded it to be pretty large.
I want to drop an index on it if it does this,
just make sure it's gone, just for sanity sake.
But I want to prove that it's pretty big,
at least big in terms of my local laptop.
We can see that it's only got
a single clustered index on the table,
but it is just almost just shy of
37 million rows to 36.6 million rows.
So it's decent size.
Really, I needed it a larger size of
data sample just so that the demo would actually run slower.
Otherwise, it would finish pretty quickly because of
local SSDs and eight cores on my laptop.
I'm going to create a new non-clustered index.
In this case, it's on that temporal table.
I'm going to specify that it is online,
but since because it's an online operation,
it does have to have that syntax there,
otherwise it'll fail.
So it goes in conjunction with the resumable syntax.
I want to tell SQL Server that
this process is going to be resumable,
and then I could potentially
pause it later in life if I so desire.
In this case, I don't want to use
all six or eight cores that I've got in my laptop
so I'm going to throttle this down to
two cores with that MAXDOP sync.
Now, there is an additional setting here that I don't have shown,
it's a duration setting.
I can actually tell SQL server, hey,
run this process for
a maximum duration of however many minutes I specify.
So if I wanted to say 30 minutes, for example,
the index process or
the create process would only run for 30 minutes,
and it would automatically pause itself, which is really cool.
I think it's a really cool feature
and it really gives data professionals
a really granular control on how this process works.
Finally, I had a 30-minute window in production.
I could use that 30-minute window and be
safe in knowing that the process is actually going to pause,
and I don't have to worry about it.
So I want to kick this off.
I'm going to go over to flip a different session.
I'm going to pause it. We're going to see that it paused.
It came out in 2017,
but Microsoft actually introduced a new
DMV syst.index resumable operations.
So I can actually query a DMV and see what's
resumable operations I might have in flight at that point in time.
Looking at this results, we can see that yeah,
there's the Index Name that I'm creating,
here's the actual text I issued,
so this also helps from a DBA perspective because
if another DBA kicked it off and I don't know about it,
I can actually go query and see
exactly what the command was that was issued.
I can see what max.value was used,
so I can see whether or not we're throttling.
I can see what state it's in. Is it running?
Is it paused? What is it? When it started?
How far along is it? Is it close to being
done and/or how many pages are going to be built into the index?
I can see some of this information.
So this is really handy.
We know that it's paused,
so I'm actually going to go back.
In this case, I'm going to try
to resume the operation with a lower MAXDOP.
Maybe my system is being overloaded and I need
the resume it but I want to do it even more throttled or gated
and I want to see if that it's going to work.
Now unfortunately, it doesn't.
Because execution plan has already been generated and set,
I can't lower that value.
What if I went higher? Maybe it's now the weekend and I
want to try to escalate or accelerate that process,
can I increase that value?
Now, we'll see that it's actually running
and it should finish here in a little bit if I flip back over.
Now, we can see that it finished.
While it actually did complete,
there was like gosh that I
mentioned is that it doesn't really work,
we can't just increase that value
which I thought would be really cool to do.
It's still does complete.
Now, again, this really allows me as a DBA to have
more granular control over when I do the index operations.
Again, 2017, it was just a rebuilt, that's great.
But if you had to put in a new index,
create a new index,
you still had to take
potentially an outage to do that depending on what it was doing.
With this in 2019, in Azure SQL,
we now have the ability to resume an index operation for
Create which again is
a great thing for DBAs because it's all about me,
the DBA, that's what I want.
That's a really great feature that I really like.
Some people don't know about it yet still.
The other cool thing or something to note about is
if you start an index create process,
you can't just kill it.
If I were to start this over again,
start the process, I'm going to pause it.
Because there's a value in this DMV already,
I can't just stop
that process and I'm limited to what I can actually do
to that underlying table
because that process has already been started,
I have to abort it.
So there's actually a new command called abort.
You actually have to tell SQL, abort that process.
Until you do, it's going to sit there.
Another beauty about this feature is that it's totally durable.
If I had to reboot my box in
the middle of the night for some strange reason,
that process is still there and
SQL Server will pick up wherever it left off.
That also means that it's durable
for things like availability groups.
If I had a fail-over in the middle of this,
anything that's building in
the background will be
automatically transferred over to your secondaries,
and so you can just pick up where
you left off on your secondaries.
>> Awesome. Thanks, John.
That's a really interesting capability that we've added.
It seems like in 2017,
then we enhanced it in 2019,
and you get it out the gate with Azure SQL.
Really awesome technology.
Thanks for showing it to us and
explaining it and making it a little bit easier.
I realized I never asked,
what do you do at Denny Cherry and Associates?
>> That's a really great question.
We at Denny and Cherry Associate,
the small boutique of SQL Server consultants,
nerds. I'm a nerd.
But we do a lot of really cool things with SQL Server.
We do a lot of work in Azure,
whether it's Azure SQL DB or SQL Server on
Azure Virtual Machine or managed instances.
We also do a lot of BI work.
We help clients make their data ecosystems better and faster,
whether that's enhancing their on-premises platform
or migrating them from on-premises to
Azure SQL to help them really enhance
and migrate their data platform to a new and better ecosystem.
>> Awesome. When you move
people to 2019 or to Azure, or even 2017,
is this technology one of the things that you're
seeing is greatly increasing and impacting your customers?
>> What I'm seeing a lot of is yes,
they want to get to the new version stuff.
This is one of those features that hasn't quite,
I think, got out there a lot.
I'm really interested in it because it gives me
more control over my work-life balance,
as well as gives me more flexibility with
my clients when I can do maintenance work.
That's a better relationship that I have with my clients.
If they're on 2019, I can
utilize this to make sure one, I get the work done.
In theory, indexes help performance,
indexes are magical and make things better.
I've yet to have a client tell me
that they want their system to be slower.
If I can make their world better
and do so in a way that is one, controlled,
and two, slightly better work-life balance for
myself and my family, then that's great.
What I'm seeing is to be able to at least teach
the clients or let them know about these features.
To me, this is one of the selling features.
If I was a DBA, not a Consultant,
and somebody told me about this feature,
I would want to go there not only because it's
a better way to do things for production standpoint,
but it gives me the DBA better work-life balance,
and that's important for me.
So I was thrilled when Microsoft released this in 2019.
I thought it was a great feature add [inaudible]
>> Awesome. Cool. Well, thanks so much, John.
Like I said, this is really interesting and like you said,
this is still a relatively new thing,
hopefully, more people now know about it.
We'll include some links to
Denny Cherry and Associates Consulting as well as
some links in our documentation so you all can learn more.
Thanks so much, John, for being on today.
>> Thanks for having me. I really appreciate it being there.
>> If you like this video,
please like this video, subscribe to our channel,
and leave us a comment, and let us know what you're
doing with these noble indexes.
Thanks for joining us and see you next time on Data Exposed.
[MUSIC]