2

I have a task in hand to move rebuild a big table to move LOB pages to a different filegroup on SQL Server 2017 Enterprise Edition.

I was testing the scripts in a proof-of-concept environment, and I can see the total CREATE INDEX .. DROP_EXISTING=ON takes about ~ 6 hours.

CREATE UNIQUE CLUSTERED INDEX [PK_TABLE1]
ON [dbo].[TABLE1] ([Id] ASC)
 WITH (DROP_EXISTING = ON , FILLFACTOR = 100, PAD_INDEX = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, IGNORE_DUP_KEY = OFF, DATA_COMPRESSION = NONE, STATISTICS_NORECOMPUTE = OFF, ONLINE = ON, MAXDOP=2) 
ON PS_MOVE_HELPER_D59E24BC73414AA8A5FB2E5D8F93C3D8([Id] );

CREATE UNIQUE CLUSTERED INDEX [PK_TABLE1]
ON [dbo].[TABLE1] ([Id] ASC)
 WITH (DROP_EXISTING = ON , FILLFACTOR = 100, PAD_INDEX = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, IGNORE_DUP_KEY = OFF, DATA_COMPRESSION = NONE, STATISTICS_NORECOMPUTE = OFF, ONLINE = ON, MAXDOP=2) 
ON [LOB_DATA];

CREATE UNIQUE CLUSTERED INDEX [PK_TABLE1]
ON [dbo].[TABLE1] ([Id] ASC)
 WITH (DROP_EXISTING = ON , FILLFACTOR = 100, PAD_INDEX = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, IGNORE_DUP_KEY = OFF, DATA_COMPRESSION = NONE, STATISTICS_NORECOMPUTE = OFF, ONLINE = ON, MAXDOP=2) 
ON [ROW_DATA];
  1. Are the associated statistics are getting updated every time the Index rebuild is happening?
  2. Stats update usually gets triggered asynchronously after the Index rebuild is completed?
  3. Is the total duration of 6 hours includes the time taken to update statistics as well? or only after 6 hours when the index rebuild is completed it triggers the stats update asynchronously?
  4. Can I capture this stats update event using the auto_stats extended event and see how long it took?

1 Answer 1

9
  1. Yes, statistics are updated when the index is rebuilt.
  2. No, statistics are usually gathered as a side effect of populating the index*. The data stream is already sorted as needed so it makes sense to populate the statistics at the same time. To be clear, index population and statistics gathering occurs simultaneously using the same data stream and execution plan.
  3. Yes, the total time includes building the statistics, but this is a tiny overhead compared to rebuilding the index.
  4. No, statistics updated as a side effect of rebuilding an index do not trigger the auto_stats event. You can follow progress with the progress_report_online_index_operation extended event, but that doesn't show a separate statistic building event because there isn't one.

Example call stack during online index build
Example call stack during online index build

Your process is slow because you're doing heaps of resource-intensive work, not because of the statistics refresh. This can't really be avoided until Microsoft provide a way to support the operation you need directly (moving individual allocation units to a different filegroup).

The first rebuild to the helper partition scheme will be single-threaded and feature an unnecessary sort.

Rebuilding the index on the LOB filegroup is expensive, even though you're only doing it to remove the partitioning. SQL Server doesn't know what you're doing so just gets on with rebuilding the entire table as you asked.

The third rebuild is also expensive, but this process does get you where you need to go eventually, while keeping the table mostly online.

Speed ups

Perform the rebuilds with the database set to the BULK_LOGGED or SIMPLE recovery models to take advantage of minimal logging if possible.

You can avoid one rebuild and the single-threaded sort if you are able to use SWITCH with the source table:

  • Create a temporary partitioning scheme and empty function on PRIMARY.
  • Create a switch table matching the source but partitioned on the new scheme.
  • Begin a transaction
    • SWITCH the source table into the partitioned table. This should be an instantaneous metadata-only operation. Specify the WAIT_AT_LOW_PRIORITY option anyway.
    • Drop the original table.
    • Rename the switch table and its primary key to match the original.
  • Commit the transaction if the operations so far were successful.
  • Rebuild the clustered index online to move everything to the LOB_DATA filegroup.
    • The plan will use parallelism and avoid a sort.
    • The result is a non-partitioned table with all allocation units on the LOB_DATA filegroup.
  • Drop the temporary partitioning scheme and function.
  • Rebuild the clustered index online a second time to move the non-LOB allocation units to the ROW_DATA filegroup.

You now have the original table with LOB data on the LOB_DATA filegroup and everything else on the ROW_DATA filegroup.

Script

Demo to reorganise the Users table in the Stack Overflow sample database (create the filegroups first):

-- Temporary partitioning function and scheme
CREATE PARTITION FUNCTION PF (integer) AS RANGE FOR VALUES ();
CREATE PARTITION SCHEME PS AS PARTITION PF ALL TO ([PRIMARY]);

-- Switch table
CREATE TABLE [dbo].[Users_Switch]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [AboutMe] [nvarchar](max) NULL,
    [Age] [int] NULL,
    [CreationDate] [datetime] NOT NULL,
    [DisplayName] [nvarchar](40) NOT NULL,
    [DownVotes] [int] NOT NULL,
    [EmailHash] [nvarchar](40) NULL,
    [LastAccessDate] [datetime] NOT NULL,
    [Location] [nvarchar](100) NULL,
    [Reputation] [int] NOT NULL,
    [UpVotes] [int] NOT NULL,
    [Views] [int] NOT NULL,
    [WebsiteUrl] [nvarchar](200) NULL,
    [AccountId] [int] NULL,
    CONSTRAINT [PK_Users_Switch_Id] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
        ON PS (Id)
) ON PS (Id);

-- Optional, to match source table
EXECUTE sys.sp_tableoption
    @TableNamePattern = N'dbo.Users_Switch',
    @OptionName = 'large value types out of row',
    @OptionValue = 'on';

BEGIN TRY;
    BEGIN TRANSACTION;

    -- Switch
    ALTER TABLE dbo.Users
        SWITCH TO dbo.Users_Switch 
        PARTITION 1
        WITH
        (
            WAIT_AT_LOW_PRIORITY 
            ( 
                MAX_DURATION = 1 MINUTES, 
                ABORT_AFTER_WAIT = SELF
            )
        );

    -- Drop original
    DROP TABLE dbo.Users;

    -- Rename table
    EXECUTE sys.sp_rename 
        @objname = N'dbo.Users_Switch',
        @newname = N'Users',
        @objtype = 'OBJECT';

    -- Rename primary key
    EXECUTE sys.sp_rename 
        @objname = N'PK_Users_Switch_Id',
        @newname = N'PK_Users_Id',
        @objtype = 'OBJECT';

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

-- Move everything to LOB_DATA
-- Parallel, no sort
CREATE UNIQUE CLUSTERED INDEX [PK_Users_Id] 
ON dbo.Users (Id)
WITH (ONLINE = ON, DROP_EXISTING = ON)
ON LOB_DATA;

-- Drop temporary partitioning function and scheme
DROP PARTITION SCHEME PS;
DROP PARTITION FUNCTION PF;

-- Move non-LOB data to ROW_DATA
-- Also parallel, no sort
CREATE UNIQUE CLUSTERED INDEX [PK_Users_Id] 
ON dbo.Users (Id)
WITH (ONLINE = ON, DROP_EXISTING = ON)
ON ROW_DATA;

-- Done

To reset the Users table back to everything being on PRIMARY:

IF EXISTS
(
    SELECT * 
    FROM sys.partitions AS P
    JOIN sys.allocation_units AS AU 
        ON P.hobt_id = AU.container_id
    JOIN sys.filegroups AS FG 
        ON FG.data_space_id = AU.data_space_id
    WHERE
        P.[object_id] = OBJECT_ID(N'dbo.Users', 'U')
        AND FG.[name] != N'PRIMARY'
)
BEGIN
    -- Temporary partitioning function and scheme
    CREATE PARTITION FUNCTION PF (integer) AS RANGE FOR VALUES ();
    CREATE PARTITION SCHEME PS AS PARTITION PF ALL TO ([PRIMARY]);

    -- Move everything to PRIMARY
    -- Single-threaded, sort
    CREATE UNIQUE CLUSTERED INDEX [PK_Users_Id] 
    ON dbo.Users (Id)
    WITH (ONLINE = ON, DROP_EXISTING = ON)
    ON PS (Id);

    -- Make table non-partitioned
    CREATE UNIQUE CLUSTERED INDEX [PK_Users_Id] 
    ON dbo.Users (Id)
    WITH (ONLINE = ON, DROP_EXISTING = ON)
    ON [PRIMARY];

    -- Drop temporary partitioning function and scheme
    DROP PARTITION SCHEME PS;
    DROP PARTITION FUNCTION PF;
END;

* The first rebuild to a partitioned table from non-partitioned is an example of an exception to this rule. This index build might cause a separate statistics refresh, but only if SQL Server considers the existing statistics out of date. I've not been able to reproduce this so far, so it remains only a possibility.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.