Online index rebuilds

G’day,

I’m probably not the only one that has noticed that as the volume of data gets bigger in out databases, the time taken to preform maintenance increases.

This seems to be a simple fact of life for the DBA. But as our maintenance time increases, we need to be aware of if we are still able to meet out SLA’s.

For example, a database that too 20 minutes to restore 6 months ago may now be so full of data that it now takes 45 minutes to restore. If our SLA’s say 30 minutes then it was fine 6 months ago, but now we’ll definitely be breaking that contract. This probably means that your boss will not be too happy should you need to restore that database now.

Another thing that happens is that as data in tables gets bigger, so too do the indexes, and consequently the time to rebuild / reorganize will increase. In fact the time to rebuild / reorganize may be so long that doing ONLINE rebuilds may be your only option to maintain your SLA’s. For example, if you rebuild an index OFFLINE then any data associated with the index will be inaccessible for the period of the rebuild, possibly taking your downtime past any acceptable metric defined in your SLA’s.

To start with, you’ll either need the enterprise or datacenter version of SQL SERVER.

Assuming you’re got that, you’re going to have to be aware of certain criteria that exist around rebuilding indexes on line.

The table can’t contain a lob type, if it does then you’ll recieve the following error message.

Msg 2725, Level 16, State 2, Line 2
An online operation cannot be performed for index ‘PK_TESTLOB’ because the index contains column ‘FULLNAME’
of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, or large CLR type.
For a non-clustered index, the column could be an include column of the index.
For a clustered index, the column could be any column of the table.
If DROP_EXISTING is used, the column could be part of a new or old index.
The operation must be performed offline.
The Books Online page for Alter Index states exactly under what circumstances an ONLINE index rebuild will fail.
The following script can be used for demonstrating the above error message.
USE master
GO

-- Drop the database if it already exists
IF EXISTS (
SELECT name
FROM sys.databases
WHERE name = N'TestDB'
)
BEGIN
ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE TestDB;
END
GO

CREATE DATABASE TestDB
GO

USE TestDB;
GO

CREATE TABLE TestLOB
(
PK_ID INT IDENTITY(1,1) NOT NULL,
AGE INT NOT NULL,
FULLNAME VARCHAR(MAX) NOT NULL
CONSTRAINT PK_TESTLOB PRIMARY KEY CLUSTERED (PK_ID ASC)
);
INSERT INTO TestLOB(AGE, FULLNAME) VALUES(41,'Martin');
INSERT INTO TestLOB(AGE, FULLNAME) VALUES(39,'Suzanne');
INSERT INTO TestLOB(AGE, FULLNAME) VALUES(2,'Callum');
GO

ALTER INDEX PK_TESTLOB
ON TestLOB
REBUILD
WITH ( ONLINE = ON )
GO
Tags: ,

About Martin Catherall

Martin Catherall is Senior SQL consultant at SQL Down Under, based in Melbourne, Australia. Martin is also a Microsoft Data Platform MVP and Regional Mentor (RM) for the Professional Association for SQL Server. Prior to relocating to Australia he was extremely active in the Christchurch, New Zealand data community - founding the local SQL Server user group and organizing SQL Saturday Christchurch - which later became SQL Saturday South Island. He likes learning interesting stuff about processing and storing data and passing on his knowledge. In his spare time he likes to learn guitar and hang out with his two young sons Callum Glen and Robert Michael.