Pausing index rebuilds in SQL Server 2017

Last week Microsoft confirmed that the name of SQL vNext will indeed be SQL Server 2017. I was looking through the documentation for more info on Adaptive Query Processing as I wanted to write a post on adaptive joins but then I saw: –

To see the new Adaptive Join operator in Graphical Showplan, a new version of SQL Server Management Studio is required and will be released shortly.

Source

Ok, I’ll wait! So I went back to the list of new features in SQL 2017 and something else caught my eye. The ability to pause and then resume online index rebuilds.

Sounds pretty cool, let’s see it in action. Here’s the setup: –

(SQL Server 2017 can be downloaded from here btw)

USE [master];
GO

CREATE DATABASE [Test]
GO

ALTER DATABASE [Test] SET COMPATIBILITY_LEVEL = 140
GO

That’s the database created and put into the correct compatibility level (just to be sure). Now let’s create a table to test with: –

USE [Test];
GO

CREATE TABLE [TestTable]
(PKID INT IDENTITY(1,1) CONSTRAINT PK_Test PRIMARY KEY CLUSTERED,
 ColA VARCHAR(10),
 ColB VARCHAR(10),
 ColC VARCHAR(10),
 ColD DATETIME)


SET NOCOUNT ON;
INSERT INTO [TestTable]
(ColA,ColB,ColC,ColD)
VALUES
(REPLICATE('A',10),REPLICATE('B',10),REPLICATE('C',10),GETUTCDATE())
GO 100000

OK, now let’s rebuild the clustered index that we’ve created on the table: –

ALTER INDEX [PK_Test] ON [TestTable] REBUILD WITH (ONLINE=ON,RESUMABLE=ON);
GO

N.B. – notice the new option RESUMABLE=ON

Whilst that’s running, open a new connection and run: –

USE [Test];
GO

ALTER INDEX [PK_Test] ON [TestTable] PAUSE;
GO

The session that running the rebuild should now have stopped with the rather disconcerting error: –

Looks pretty worrying imho! But not to stress, jump back to your other connection and run: –

ALTER INDEX [PK_Test] ON [TestTable] RESUME;
GO

Hmm, but how do I tell that this has worked? Well, if you have sp_whoisactive on your instance you can verify that the query is re-running: –

Pretty cool, huh? Full information on this can be found here: – https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql

I think this is very useful but we do need to be careful. The documentation says that pausing an online index rebuild for a long time may affect query performance and disk utilisation. This is due to the newly rebuild index being created side-by-side to the original one so we’ll need to watch out for that.

Thanks for reading!

Monday Coffee 2017-04-24

Back after a couple of busy weeks, kinda looking forward to getting back into the old work routine (ah, well maybe 🙂 )

Last week was a big week, I had my first article published on SQLShack.com and there were a couple of small events going on called Docker Con 2017 and Microsoft Data Amp.

Docker announced that all the open-source projects are being consolidated under one name Moby. Not too sure what this means for the future of the project as Docker (the company) now want to focus on Enterprise (paid-for) solutions. Hopefully this is a good thing but time will tell.

The biggest announcement from Microsoft was that we now know that the next version of SQL Server will be called SQL Server 2017. Lots of new features to get stuck into but the one that caught my eye was Adaptive Query Processing.

There’s an excellent video here in which Joe Sack takes us through what AQP is and how it works. I definitely recommend that you give the video a watch.

I jokingly said on twitter that this feature is liable to put me out of a job. Obviously I don’t think that, the way I look at features like these is that they are another tool in the belt of the DBA. It will allow us to be freed up from routine tasks and focus on more in-depth work.

Plus, it’s not going to get things right all the time, is it?

Have a good week!

Thursday Reading 2017-04-13

Morning all, it’s been a busy week so I haven’t had a chance to write a post. Instead I’ve seen some great posts written about SQLBits 2017 so I thought I’d link to three of my favourites:-

Why Volunteer at SQLBits ?
Rob talks about his experiences volunteering at SQLBits and how you can get involved. The photos are hilarious!

Attending SQLBits for the first time
Great video by Adam Sexton of pretty much everything that went on at SQLBits!

State of the SQL Nation and the Microsoft Engineering Model
Victoria Holt talks about the points mentioned at the 15 mins Q & A session with Conor Cunningham and Simon Sabin at SQLBits this year

Have a good weekend!

Monday Coffee 2017-04-10

So SQLBits is over and I have to say that I really enjoyed attending on the Saturday, hopefully next year I’ll be able to go for the whole event.

Highlights for me included chatting to the RedGate people about their new product SQL Clone, attending Denny Cherry’s “What not to do with SQL Server” session and of course, presenting my session.

I’m still pretty new to presenting and SQLBits is a little different to SQL Saturdays! Having a microphone and a light shining on you is a bit distracting at first but I found that 10 minutes into my presentation I didn’t notice anymore.

Overall I thought my session went well, I covered everything that I wanted to and had some time at the end which allowed the attendees to ask a few questions, most of which I managed to answer (reasonably well 🙂 )

One thing I did notice, about half way through my session, was that I was enjoying it. Previously when I’ve spoken I’ve been pretty nervous so have kinda had the mentality of…let’s just get this done…so I hope the fact that I enjoyed it came across.

So all in all, pretty chuffed about how it went and I can’t wait to do more.

Have a good week!

Friday Reading 2017-04-07

As this is being published I’m on my way up to Dublin airport to head on over to the UK for SQLBits. I’ve never been before and it’s always being mentioned as people’s favourite SQL event so I’m really looking forward to it.

Anyway, this week I’ve been reading…

How to Talk Yourself out of your New Year’s Blogging Resolution… One Day At A Time
Tongue in cheek article about getting started blogging. There’s one great line… “One of the best ways to learn more is to risk being accidentally stupid on the internet.”

Among Giants
Monica Rathbun talks about how she became an MVP and what you can do for someone you think deserves to be awarded.

Is this database being used?
Nice post on the various different ways to see if a database is being used

Inside the Hunt for Russia’s Most Notorious Hacker
Wired’s article on the investigation into a notorious hacker group. Very interesting.

Have a good weekend and if you’re at SQLBits, I’m presenting on Saturday morning in Dome 8 @ 09:30, come and say hello! 🙂

Connecting a powershell session to a container

One of the questions that I was asked at SQL Saturday Iceland was “how can I view the filesystem within a container?”.

This is a great question as one of the things that people find off-putting about containers is their opaqueness. It’s not obvious where everything lives within the container or how we can view the files within it.

Thankfully there’s a simple docker command that allows us to open a powershell session within a container, that command is docker exec.

Let’s run through an example. Firstly I’m going to spin up a new container running an instance of SQL Server: –

docker run -d -p 15777:1433 --env ACCEPT_EULA=Y --env sa_password=Testing11@@ --name testcontainer microsoft/mssql-server-windows

Then verify that the container is running: –

docker ps

Now, to open up the powershell session inside run: –

docker exec -it testcontainer powershell

N.B. – the -i flag keeps the powershell session open, otherwise it will immediately exit. The -t flag creates a pseudo terminal, you don’t have to include it tbh but the examples I’ve seen online always include it.

Ok, we know have a powershell session within the container and we can execute commands as normal, for example: –

get-psdrive

This will allow us to explore what the filesystem is within the container and run powershell commands as needed. In the screenshot above it’s interesting to see the space available/used on the C: drive. Have a dig around within your containers and let me know if you find anything interesting 🙂

Thanks for reading!

Monday Coffee 2017-04-03

Last week Pass announced changes to how speakers are going to be selected for their annual conference (you can read the announcement here.

There’s been a bit of a debate about the changes but as far as I can tell, these seem to be for the better (right?).

Inviting speakers with a proven track record of delivering high-quality presentations can only be a good thing and guaranteeing that a (albeit) small number of new speakers will be there will hopefully prevent presenting at Pass Summit becoming only available to a certain circle of people.

I’ve only been to the event once and I have to say, I found quite a few of the sessions there to be below-par. Don’t get me wrong, I also saw a few great sessions but if these changes improve the overall quality then I say that they are a good thing. Of course, it’s all about the execution so only time will tell if these make the expected improvements but I think Pass are doing this right.

They’ve asked for feedback here and have organised two online meetings so that we, as the community, have the chance to speak directly to them and voice any concerns that we may have.

Have a good week.