- Yes, statistics are updated when the index is rebuilt.
- 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.
- Yes, the total time includes building the statistics, but this is a tiny overhead compared to rebuilding the index.
- 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
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.
