One of the cool features introduced in SQL Server 2014 was the ability to perform an online index rebuild of a single partition. Prior to SQL Server 2014, the only way to rebuild indexes for a partitioned table was to do it on all of the partitions (or, simply put, rebuild the entire index on the table similar to how you would do it if the table wasn’t partitioned.) If you wanted to rebuild only one partition, the only option was to do it offline. In fact, if you rebuild an index on a partitioned table, compare the syntax and result with using the ALL keyword (rebuilding index on all partitions) versus just one of the partitions, you get a slightly confusing error message (the error message below is from a SQL Server 2008 R2 instance.)
ALTER INDEX [PK_Order_Details]
ON [dbo].[Order Details]
REBUILD PARTITION = ALL
WITH (ONLINE= ON);
GO
Command(s) completed successfully.
ALTER INDEX [PK_Order_Details]
ON [dbo].[Order Details]
REBUILD PARTITION = 1
WITH (ONLINE= ON);
GO
Msg 155, Level 15, State 1, Line 4
'ONLINE' is not a recognized ALTER INDEX REBUILD PARTITION option.
It may seem that, from the error message, the ONLINE option is not a valid keyword which may prompt you to use ONLINE=OFF because ONLINE=ON worked with REBUILD PARTITION = ALL. It turns out that you just can’t do a single partition index rebuild while keeping the partition online.
New Feature? Check
There have been several blog posts and articles written about this feature, most of which focus on increased availability of the partitioned table during index operations. For one, if you’re partitioning your table, it means that the table is very large. Making sure that the table is accessible during normal operations factor into your database availability. But that’s already a given if you simply look at the name of the feature – Partition-Level ONLINE Index Operation. There’s a reason that the marketing folks used the word ONLINE.
But beyond the benefit of keeping the partitioned table online during index maintenance, I wanted to focus more on it’s overall impact to high availability and disaster recovery. If this table is mission-critical, I would assume that there is some form of HA/DR features like Availability Groups, Log Shipping, Failover Clustering, Replication, etc. implemented with it. I’ll refer back to my blog post on Data Types and How They Affect HA/DR to explain further.
Online index operations are fully logged from SQL Server 2008 and higher versions.This behavior is described in Microsoft KB article 2407439. This means that when you rebuild an index, the database engine needs to track all of the activities involved in the index operation to make sure that it is kept consistent should anything happen to the database during this process (imagine that your server accidentally rebooted while running an index operation.) This also means that rebuilding an index for a very large partitioned table would generate a very large amount or transaction log records. Having the ability to perform online index operations on specific partitions not only allows us to keep the table available, it also reduces the amount of transaction log records generated while doing so. How cool is that?
Measuring and Comparing Transaction Log Records Generated
For testing, I *stole* the scripts used in this article SQL Server Partitioned Tables with Multiple Filegroups for High Availability. Use the Northwind database and run the script below to setup the test environment.
USE Northwind
GO
–Add the first filegroup
ALTER DATABASE Northwind
ADD FILEGROUP NorthwindOrderDetailsDataPartition1
GO
–Add a database file to the first filegroup
ALTER DATABASE Northwind
ADD FILE
( NAME = N’NorthwindOrderDetailsDataPartition1′
, FILENAME = N’C:\DBFiles\NorthwindOrderDetailsDataPartition1.ndf’
, SIZE = 10
, MAXSIZE = 120
, FILEGROWTH = 10)
TO FILEGROUP NorthwindOrderDetailsDataPartition1
GO
–Add the second filegroup
ALTER DATABASE Northwind
ADD FILEGROUP NorthwindOrderDetailsDataPartition2
GO
–Add a database file to the second filegroup
ALTER DATABASE Northwind
ADD FILE
( NAME = N’NorthwindOrderDetailsDataPartition2′
, FILENAME = N’C:\DBFiles\NorthwindOrderDetailsDataPartition2.ndf’
, SIZE = 10
, MAXSIZE = 120
, FILEGROWTH = 10)
TO FILEGROUP NorthwindOrderDetailsDataPartition2
GO
–Add the third filegroup
ALTER DATABASE Northwind
ADD FILEGROUP NorthwindOrderDetailsDataPartition3
GO
–Add a database file to the third filegroup
ALTER DATABASE Northwind
ADD FILE
( NAME = N’NorthwindOrderDetailsDataPartition3′
, FILENAME = N’C:\DBFiles\NorthwindOrderDetailsDataPartition3.ndf’
, SIZE = 10
, MAXSIZE = 120
, FILEGROWTH = 10)
TO FILEGROUP NorthwindOrderDetailsDataPartition3
GO
–Add the fourth filegroup
ALTER DATABASE Northwind
ADD FILEGROUP NorthwindOrderDetailsDataPartition4
GO
–Add a database file to the fourth filegroup
ALTER DATABASE [Northwind]
ADD FILE
( NAME = N’NorthwindOrderDetailsDataPartition4′
, FILENAME = N’C:\DBFiles\NorthwindOrderDetailsDataPartition4.ndf’
, SIZE = 10
, MAXSIZE = 120
, FILEGROWTH = 10)
TO FILEGROUP NorthwindOrderDetailsDataPartition4
GO
–Create the partition function for the partitioned table
CREATE PARTITION FUNCTION OrderDetails4Partitions_PFN(INT)
AS
RANGE RIGHT FOR VALUES (10000,10500,10750)
GO
–Create the partition scheme to map the partition function to the filegroups
CREATE PARTITION SCHEME [OrderDetails4Partitions_PS]
AS
PARTITION [OrderDetails4Partitions_PFN] TO
(NorthwindOrderDetailsDataPartition1, NorthwindOrderDetailsDataPartition2,
NorthwindOrderDetailsDataPartition3, NorthwindOrderDetailsDataPartition4 )
GO
–Rebuild the index to partition the table
IF EXISTS (SELECT * FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[Order Details]’)
AND name = N’PK_Order_Details’)
ALTER TABLE [dbo].[Order Details] DROP CONSTRAINT [PK_Order_Details]
GO
ALTER TABLE [dbo].[Order Details]
ADD CONSTRAINT [PK_Order_Details] PRIMARY KEY CLUSTERED
(
[OrderID] ASC,
[ProductID] ASC
)WITH (ONLINE = OFF) ON [OrderDetails4Partitions_PS] (OrderID)
–we pass the partition scheme name and the partitioning column
GO
Once you’ve setup the database for testing, let’s start using the partition-level online index operation and measure the amount of transaction log records generated in the process. The code sample below displays the amount of transaction log records generated by rebuilding the index on all partitions on the table, similar to rebuilding the index on a non-partitioned table. I’m only rolling back the transaction so I can repeat the process. You certainly don’t want to do this on a production system. Note, also, that the Northwind sample database is a very small database.
BEGIN TRAN;
USE Northwind
GO
ALTER INDEX [PK_Order_Details]
ON [dbo].[Order Details]
REBUILD PARTITION = ALL
WITH (ONLINE= ON);
GO
SELECT [database_transaction_log_bytes_used]
FROM sys.dm_tran_database_transactions
WHERE [database_id] = DB_ID (N'Northwind');
GO
ROLLBACK TRAN
GO 
Rebuilding the clustered index PK_Order_Details on the entire table (or on all of the partitions) generated 32.39 KB. Let’s try rebuilding the index on one of the partitions.
BEGIN TRAN;
USE Northwind
GO
ALTER INDEX [PK_Order_Details]
ON [dbo].[Order Details]
REBUILD PARTITION = 1
WITH (ONLINE= ON);
GO
SELECT [database_transaction_log_bytes_used]
FROM sys.dm_tran_database_transactions
WHERE [database_id] = DB_ID (N'Northwind');
GO
ROLLBACK TRAN
GO
By simply choosing to rebuild the index of only a portion of the table, we’ve not only increased the availability of the table by performing the index operation online but also reduced the amount of transaction log records generated. Imagine having to deal with a very large table with several partitions. You’re literally saving a large amount of transaction log records generated that will get sent to the Availability Group replicas, stored on backups, read during the recovery phase, and so on.
Try It Out
If you already have a partitioned table and running on SQL Server 2014, modify your existing index maintenance scripts by specifying which partitions to perform maintenance on. You can always check which partitions are highly fragmented and focus your index maintenance efforts on them. You can measure the before-and-after effects of the changes you made by looking at the size of the transaction log backups. You’re definitely going to increase your availability and save some storage along the way.
Please note: I reserve the right to delete comments that are offensive or off-topic.