Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.