Category Archives: Maintenance

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!

DBCC CLONEDATABASE in SQL Server 2014

~

In SQL Server 2014 SP2 an interesting new DBCC command was included, DBCC CLONEDATABASE

This command creates a “clone” of a specified user (not supported for the system databases) database that contains all objects and statistics of the specified database. Hmm, could be useful, but, how does it work? Let’s have a look.

First create a database: –

USE [master];
GO

CREATE DATABASE [Test];
GO

And then create a test table: –

USE [TEST];
GO

CREATE TABLE dbo.TestTable 
(PK_ID	   INT IDENTITY(1,1),
 ColA	   VARCHAR(10),
 ColB	   VARCHAR(10),
 ColC	   VARCHAR(10),
 CreatedDate DATE,
 CONSTRAINT [PK_ID] PRIMARY KEY (PK_ID));
GO

Insert some data and then make sure stats have been generated: –

INSERT INTO dbo.TestTable
(ColA,ColB,ColC,CreatedDate)
VALUES
(REPLICATE('A',10),REPLICATE('B',10),REPLICATE('C',10),GETUTCDATE());
GO 100000

EXEC sp_updatestats;
GO

Now we can run the DBCC CLONEDATABASE command: –

DBCC CLONEDATABASE ('test','testclone');
GO

And verify that a read only copy of the database has been generated: –

dbcc clonedatabase

So, let’s have a look at the data in the new database: –

SELECT TOP 1000 [PK_ID]
      ,[ColA]
      ,[ColB]
      ,[ColC]
      ,[CreatedDate]
  FROM [testclone].[dbo].[TestTable];
GO

No data! Ok, so let’s have a look at the stats: –

USE [testclone];
GO

EXEC sp_spaceused 'dbo.testtable';
GO


DBCC SHOW_STATISTICS(N'testtable',PK_ID);
GO

dbcc clonedatabase stats
There’s the stats, SQL thinks that there’s 1000 rows in the table, pretty cool.

What we’ve ended up with is a read only database with no data but the objects and stats of the target database.

First thing, I’d be doing is backing that clone up and bringing it down to my local instance. Want to see how code will execute against production but don’t want to touch that prod environment? Here’s your answer.

@Microsoft, can we have this for other versions of SQL please?

SQL Server 2016 – Perform Volume Maintenance

~

One of the server settings that I always enable when configuring a new instance of SQL is database instant file initialisation

In case you don’t know what this is, it is a setting that can be granted to the service account that the SQL database engine runs under that prevents SQL Server from “zeroing out” new space when a data file is created or expanded.

This improves the performance of CREATE/ALTER DATABASE statements, RESTORE statements and AUTOGROWTH operations. A full detailed article to how and why can be found here.

It’s cool to see that you can now enable this when installing SQL Server: –

SQL2016 Perform Volume Maintenance

I can’t think of any downside to having this enabled (off the top of my head, there’s probably one or two) and it’s good to see that Microsoft know that most people enable it so adding it as an option in the installer is great imho.

Bug fixes in SQL Server

~

SQL Server 2016 CU1 has been released and one thing I noticed was: –

FIX: Canceling a backup task crashes SQL Server 2014 or 2016

That’s pretty nasty, when I originally clicked on the link I was expecting to see detailed a pretty precise set of circumstances in which that bug can occur but no no, apparently not. Cancelling any backup task can lead to this happening.

This lead me to thinking about going to RTM versions of SQL when they’re released, there’s always a debate of whether to wait for the first service pack or not. Often people (like myself) are keen to upgrade to take advantage of new features that are available and will push for the upgrade.

Actually I tell a lie, I’ve always installed new versions of SQL Server on my laptop as soon as they’re available but I’ve been firmly in the “wait ’til the first service pack is released before deploying to production” brigade since SQL Server 2012.

The reason for this is that in a previous position, when SQL Server 2012 was released the devs were very keen to implement it immediately for a new project. I did halfheartedly object but in the end it was deployed (I even remember someone telling me, “yeah but it’s been in CTP for aaaaaages, they’ll have picked up most of the bugs”, ha ha ha).

Well, we had no end of problems and it wasn’t until SP2 was released and we patched our instance that things improved. KBs like this one really put me against using RTM versions and stuff like this made me even more cautious.

So when Microsoft announced that they were changing their patching policy and recommending that CUs should be proactively installed as they are released, I wasn’t particularly happy. Should I have been? Surely testing the CUs to the same level as the SPs is a good thing?

Being the pessimist that I am, all I saw with this was just more ways to introduce risk to my systems. If I’m not suffering any issues why would I install all CUs released instead of waiting for the SP?

And to be honest that’s what I’m going to do, unless I’m seeing a specific issue I’m going to wait for the SP and not bother with the CUs. Unless, and I’ve seen this mentioned, Microsoft do away with the SPs and just release CUs periodically.

Then I guess it’ll be a matter of installing on my dev/staging systems and test, test, test.

Setting up powershell remote sessions

~

Hey guys, differing from usual this is a quick post on setting up powershell remote sessions. I know you can remotely connect to powershell sessions using the Server Manager that comes with Windows Remote Administration Tools but it’s a bit of a clicky process and I like to eliminate using the mouse as much as possible.

Disclaimer! I’m not a scripter, there are probably much better ways of doing this but I’ll show you the way I set it up and how to fix any errors you may come across.

So here goes, first thing to do is check that the Windows Remote Management service (WinRM) is running, so open a powershell session (as an admin) and run:-

Get-Service WinRM

If it’s not running, start the service:-

Start-Service WinRM

And now enable powershell remoting:-

Enable-PSRemoting -force

Next thing to do is check your trusted hosts lists, so run:-

cd wsman::localhost
cd Client
dir

N.B. – I’m going to the Client via two cd commands as going straight to wsman::localhost\Client sometimes generates an error (not sure why TBH). If you get errors, try each command separately.

If the host you want to remote to isn’t in the list, run the following (changing the RemoteComputer1,RemoteComputer2 to your servers): –

winrm s winrm/config/client '@{TrustedHosts="RemoteComputer1,RemoteComputer2"}'

Btw, if you see this error:-
WinRM Error

Restart the WinRM service: –

Stop-Service WinRm
Start-Service WinRm

Cool, so now we can connect to a remote powershell session on the servers entered into the list. Save this simple script somewhere: –

Param (
    [string]$Server,
    [string]$Credential
      )

Enter-PSSession -ComputerName $Server -Credential $Credential

Which can be called by a .BAT file, passing in the parameters:-

powershell -NoExit -ExecutionPolicy Bypass -File 'PATH TO THE PS1 File' -Server YOURSERVER -Credential YOURLOGIN

N.B. – Use the -Credential parameter if your server is in a different domain (like a Staging or QA environment).

This .BAT file can be called via a cool program like Launchy and voila, a remote powershell session!