Sql-server – Online rebuild consuming lot of log space

clustered-indexsql serversql-server-2016

why the online rebuild on a primary key clustered index in taking almost equal to the space of non clustered index size which is untouched.

Details:

We have a table with primary key clustered index (bigint type) of
index size 7GB.
We have another non-clustered filtered index on same table (varchar(36) type) of index size nearly 1.5TB.
Online rebuild on primary key clustered index is consuming almost 1.6TB of transaction log size.
Options used for rebuild – (DATA_COMPRESSION = PAGE, ONLINE = ON, SORT_IN_TEMPDB = ON)
Another observation, the previous index was created without compression, not sure if that is what causing such a huge growth.
Could someone please shed some internals about it?

Best Answer

The clustered index is the table. It includes all columns. It is (basically) impossible for the clustered index to be smaller than any one non-clustered index. You probably looked at only the clustered key column size or only the non-leaf level when you read that 7GB size.

So if you rebuild the clustered index, then you rebuild all columns - including the compression setting. Rebuild of the clustered index in the end copies the data to a new location and after its done, it removes the data from the old location.

If you were on 2017, you could do resumable index rebuild, which allow you to pause the rebuild, empty the log and then resume the rebuild again.

Related Solutions

Sql-server – Different results rebuilding an index online and offline

This is by no means a full answer but may move things along a bit if you were to try something similar and report your results.

I couldn't reproduce them. With the following test table

CREATE TABLE [dbo].[Table]
(
Col BIGINT
)

CREATE NONCLUSTERED INDEX IX ON [dbo].[Table](Col)

INSERT INTO [dbo].[Table]
SELECT top 12000 ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM master..spt_values v1, master..spt_values v2

And multiple runs of the following script

USE FragTest;

DECLARE @DbccPage TABLE (
  ParentObject VARCHAR(255),
  Object       VARCHAR(255),
  Field        VARCHAR(255),
  VALUE        VARCHAR(255))

DECLARE @sp_index_info TABLE (
  PageFID         TINYINT,
  PagePID         INT,
  IAMFID          TINYINT,
  IAMPID          INT,
  ObjectID        INT,
  IndexID         TINYINT,
  PartitionNumber TINYINT,
  PartitionID     BIGINT,
  iam_chain_type  VARCHAR(30),
  PageType        TINYINT,
  IndexLevel      TINYINT,
  NextPageFID     TINYINT,
  NextPagePID     INT,
  PrevPageFID     TINYINT,
  PrevPagePID     INT,
  PRIMARY KEY (PageFID, PagePID));

DECLARE @I INT = 0

WHILE @I < 2
  BEGIN
      DECLARE @Online VARCHAR(3) = CASE
          WHEN @I = 0 THEN 'OFF'
          ELSE 'ON'
        END

      EXEC('ALTER INDEX [IX] ON [dbo].[Table]
REBUILD WITH
(
    PAD_INDEX  = OFF, 
    STATISTICS_NORECOMPUTE  = OFF, 
    ALLOW_ROW_LOCKS  = ON, 
    ALLOW_PAGE_LOCKS  = ON, 
    ONLINE = ' + @Online + ', 
    SORT_IN_TEMPDB = ON
);')

      INSERT INTO @sp_index_info
      EXEC ('DBCC IND ( FragTest, ''[dbo].[Table]'', 2)' );

      ; WITH T
           AS (SELECT *,
                      PagePID - ROW_NUMBER() OVER (PARTITION BY PageType, IndexLevel ORDER BY PagePID) AS Grp
               FROM   @sp_index_info)
      SELECT PageType,
             MIN(PagePID) AS StartPID,
             MAX(PagePID) AS EndPID,
             COUNT(*)     AS [count],
             IndexLevel
      FROM   T
      GROUP  BY Grp,
                PageType,
                IndexLevel
      ORDER  BY PageType DESC,
                StartPID

      DECLARE @DynSQL NVARCHAR(4000)

      SELECT @DynSQL = N'DBCC PAGE (FragTest, ' + LTRIM(PageFID) + ',' + LTRIM(PagePID) + ',3) WITH TABLERESULTS'
      FROM   @sp_index_info
      WHERE  PageType = 10

      INSERT INTO @DbccPage
      EXEC(@DynSQL)

      SELECT VALUE AS SinglePageAllocations
      FROM   @DbccPage
      WHERE  VALUE <> '(0:0)'
             AND Object LIKE '%IAM: Single Page Allocations%'

      SELECT avg_page_space_used_in_percent,
             avg_fragmentation_in_percent,
             fragment_count,
             page_count,
             @Online                                                   AS [Online],
             (SELECT COUNT(*)
              FROM   @DbccPage
              WHERE  VALUE <> '(0:0)'
                     AND Object LIKE '%IAM: Single Page Allocations%') AS SinglePageAllocations
      FROM   sys.dm_db_index_physical_stats(db_id(), object_id('[dbo].[Table]'), 2, NULL, 'DETAILED')
      WHERE  index_level = 0

      DELETE FROM @sp_index_info

      DELETE FROM @DbccPage

      SET @I = @I + 1
  END

I consistently got results like

Online = OFF

PageType StartPID    EndPID      count       IndexLevel
-------- ----------- ----------- ----------- ----------
10       119         119         1           NULL
2        2328        2351        24          0
2        2352        2352        1           1
2        2384        2392        9           0


SinglePageAllocations
----------------------

(0 row(s) affected)


avg_page_space_used_in_percent avg_fragmentation_in_percent fragment_count       page_count           Online SinglePageAllocations
------------------------------ ---------------------------- -------------------- -------------------- ------ ---------------------
98.8139362490734               0                            2                    33                   OFF    0

Online = ON

PageType StartPID    EndPID      count       IndexLevel
-------- ----------- ----------- ----------- ----------
10       115         115         1           NULL
2        114         114         1           0
2        118         118         1           1
2        2416        2449        34          0



SinglePageAllocations
-----------------------
(1:114)
(1:118)


avg_page_space_used_in_percent avg_fragmentation_in_percent fragment_count       page_count           Online SinglePageAllocations
------------------------------ ---------------------------- -------------------- -------------------- ------ ---------------------
97.4019644180875               2.85714285714286             2                    35                   ON     2

At least in the test I did the differences between the two balanced out fragmentation wise (though similarly to your test I did find that rebuilding the index online led to a higher page count.).

I found that the Online = OFF version always used uniform extents and had zero single page allocations whereas the Online = ON always seemed to put the index root page and first index leaf page in mixed extents.

Putting the first index leaf page in a mixed extent and the rest in contiguous uniform extents causes a fragment count of 2.

The Online = OFF version avoids the fragment caused by the lone index leaf page but the contiguity of the leaf pages is broken by the index root page that shares the same extents and this too has a fragment count of 2.

I was running my test on a newly created database with 1 GB of free space and no concurrent activity. Perhaps the Online = OFF version is more vulnerable to concurrent allocations causing it to be given non contiguous uniform extents.

Sql-server – Disk Space Considerations for Rebuilding a large Clustered Primary Key Index

The clustered index "is" the table. Rebuilding the index will require enough space to duplicate each existing non-deleted row, page-by-page. Since you don't have Enterprise Edition, you do not have the luxury of rebuilding the index online. This means the table will not be accessible during the rebuild operation.

You'd likely be better off rebuilding by creating a duplicate empty table on a different drive that has plenty of free space with room for foreseeable growth, moving data row-by-row in key order, then renaming the old and new tables. For instance if the original table is Table1 and the new copy is Table2, rename Table1 as Table1-old then rename Table2 as Table1.