Single Partition online rebuild in SQL 2014

If you have a large database then you probably have table partitioning implemented. While I have already blogged a lot about partitioning one feature that is really useful in SQL 2014 is the ability to rebuild an individual partition online.

Previously for VLDB that contain partitions you either had to rebuild the index on the entire table ( causing massive CPU , Memory and IO overhead) or rebuild the latest partition offline ( causing the table to be unavailable and often these tables are critical to the business process). With the ability to build indexes online for single partitions we can save a massive amount of time in our regular service interval doing the more critical stuff. In fact I have seen implementations were rebuilding the index was put off till fragmentation was huge only to avoid performance issues on the base table.

In this blog I am going to cover a few different scenarios of how online single partition rebuilds works in our favour.

I have a table which is not partitioned i.e. a single partition with a clustered index on it. Barring any other I/O activity let’s see how long it takes to rebuild the clustered index for 7000 rows split across 9 partitions.

When running a simple select on the entire table

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

(7000 row(s) affected)

Table 'MOCK_DATA'. Scan count 9, logical reads 137, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 176 ms.

 

 

Now I create a clustered index on the partitioning key column id creating an aligned index.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

(7000 row(s) affected)

Table 'MOCK_DATA'. Scan count 9, logical reads 151, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 46 ms, elapsed time = 175 ms.

 

 

Now I insert an additional 1000 rows to the table with random ids causing fragmentation that requires me to rebuild the index

 

Rebuild all partitions with ONLINE ON

 

USE [madworks]

GO

ALTER INDEX [ClusteredIndex-20150115-234948] ON [dbo].[MOCK_DATA]

REBUILD PARTITION = ALL

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,

SORT_IN_TEMPDB = OFF,

ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GO

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

Table 'MOCK_DATA'. Scan count 9, logical reads 8371, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 63 ms, elapsed time = 917 ms.

 

 

RESET THE TABLE BACK TO ORIGINAL STATE AND RELOAD THE DATA ONCE MORE , CAUSING THE SAME LEVEL OF FRAGMENTATION AS BEFORE.

 

This time I choose to rebuild only partition Number 5 with 2100 rows and do it OFFLINE.

USE [madworks]

GO

ALTER INDEX [ClusteredIndex-20150115-235806] ON [dbo].[MOCK_DATA]

REBUILD PARTITION = 5

WITH (

SORT_IN_TEMPDB = OFF,

ONLINE = OFF)

GO

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

Table 'MOCK_DATA'. Scan count 1, logical reads 71, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 11 ms.

 

At this point it should be fairly obvious why rebuilding the individual partition is more useful for critical workloads. Since the time to rebuild the individual partition is always going to be less than then entire table. Now let’s see if it doing it online adds any benefit or does it slow down the process a little.

USE [madworks]

GO

ALTER INDEX [ClusteredIndex-20150116-000521] ON [dbo].[MOCK_DATA]

REBUILD PARTITION = 5

WITH (

SORT_IN_TEMPDB = OFF,

ONLINE = ON)

GO

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

Table 'MOCK_DATA'. Scan count 1, logical reads 1304, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 15 ms, elapsed time = 27 ms.

 

Notice the slightly higher execution time and the significantly higher Logical reads compared to offline rebuild. So while being able to rebuild the individual partition online is useful for critical workloads which need to be online as much as possible, it makes sense to do this when

 

  • Work load is critical
  • Partitions are smaller in proportion to the table.
  • Historical partitions do not need rebuilds
  • Concurrency is an issue for the table being rebuilt