Till SQL Server 2012, if we want to rebuild a partition table online, the only option available was to rebuild all the partitions of the table. If we want to rebuild only one partition, the option was to do it offline. Not being able to perform online partition rebuilds means application teams have to ultimately decide between index fragmentation or data being un-available while indexes are rebuilt, neither are consistent with an expectation of 24/7 enterprise data platform. Offline rebuild could result in much longer maintenance operations as partition grows. If we attempt to rebuild one partition online, we would be welcomed with below message

Msg 155, Level 15, State 1, Line 4
‘ONLINE’ is not a recognized ALTER INDEX REBUILD PARTITION option.

In SQL Server 2014, online single partition index operations are supported. The name of the feature “Single Partition Online Index Rebuild “ is mouthful, so some of you may prefer to use SPOIR in your day to day talk. The name of the feature itself is an explanation and you would have understood what it does. Let’s look at few highlights of this feature.

• Better control on online rebuild by choosing one or more partitions.
• This would keep the table accessible when rebuild is going on. As explained in earlier blog, short terms locks are taken at beginning and end of index rebuild.
• We can combine SPOIR with Managed Lock Priority (refer Blog # 13, 14, 15 of this series)
• Due to all of the above, the availability of the table would increase.
• CPU, Memory consumption would also reduce due to single partition rebuild.

Here is the syntax as per Books online: ALTER TABLE and ALTER INDEX

Let’s see it in action now.

To have some sample data, we can have download AdventureWorks sample database from: http://msftdbprodsamples.codeplex.com/downloads/get/417885

I have restored it in SQL Server 2014 and named as AdventureWorks2014. I would be playing around with [AdventureWorks2014].[Production].[TransactionHistoryArchive] table which has transactions from 2005-05-17 to 2007-08-31. I have created yearly partition.

Use AdventureWorks2014;
GO

CREATE partition FUNCTION [YearlyDate](datetime) AS range RIGHT FOR VALUES ( 
N'2005-05-01T00:00:00', N'2006-05-01T00:00:00',  N'2007-05-01T00:00:00'); 
GO

CREATE partition scheme [TransactionDatePS] AS partition [YearlyDate] TO ( 
[PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]);
GO


CREATE TABLE Production.TransactionHistoryArchive_SPOIR(
    TransactionID int NOT NULL,
    ProductID int NOT NULL,
    ReferenceOrderID int NOT NULL,
    ReferenceOrderLineID int NOT NULL, 
    TransactionDate datetime NOT NULL,
    TransactionType nchar(1) NOT NULL,
    Quantity int NOT NULL,
    ActualCost money NOT NULL,
    ModifiedDate datetime NOT NULL)
ON TransactionDatePS(TransactionDate);
GO

Create Clustered Index idx_TransactionID on Production.TransactionHistoryArchive_SPOIR (TransactionID)
go

Let’s populate partitioned table TransactionHistoryArchive_SPOIR from TransactionHistoryArchive

INSERT INTO Production.TransactionHistoryArchive_SPOIR
SELECT * FROM Production.TransactionHistoryArchive
-- (89253 row(s) affected)

Let’s check how much space is used per page. Since we just created clustered index, all pages would be almost full.

SELECT partition_number, 
       index_id, 
       avg_page_space_used_in_percent, 
       page_count 
FROM   sys.Dm_db_index_physical_stats(Db_id(), 
Object_id('AdventureWorks2014.Production.TransactionHistoryArchive_SPOIR' ), NULL, NULL, 'DETAILED') 

Let’s delete alternate rows. in 2nd partition.

Delete from Production.TransactionHistoryArchive_SPOIR
where TransactionDate >= '2005-05-01T00:00:00' and TransactionDate < '2006-05-01T00:00:00'
and TransactionID % 2 =  1
go
-- (10755 row(s) affected)

Once we delete, we should see TransactionID as 2, 4, 6.. (even numbers, due to delete)

Let’s rebuild only Partition # 2 online.

ALTER INDEX idx_TransactionID on Production.TransactionHistoryArchive_SPOIR
REBUILD PARTITION = 2 WITH (ONLINE = ON)

and let’s check the output again.

This means online index rebuild of single partition is working as expected. In my example I don’t have fragmentation because all pages are next to each other. That’s why I have not shown avg_fragmentation_in_percent column.

Go Do: If you want to learn, download EVAL version and read other new feature of SQL Server 2014 here.