DBREINDEX and MAXDOP: Use ALTER INDEX to Limit Parallelism

DBREINDEX and MAXDOP do not combine, because DBCC DBREINDEX has no MAXDOP option. The old command follows the server setting and nothing else. To limit the parallelism of an index rebuild, use ALTER INDEX … REBUILD WITH (MAXDOP = n). This post shows the errors and measures what each form does.

What MAXDOP Controls

MAXDOP is the maximum degree of parallelism: the number of processors one operation can use. SQL Server reads it from three places. The server setting max degree of parallelism applies to everything. A query hint overrides it for one query. An index option overrides it for one index operation. A rebuild of a large index is a good candidate for a limit. It can take every processor away from the users.

Another post covers why DBCC DBREINDEX is deprecated and how to replace it. See DBCC DBREINDEX: Replace It With ALTER INDEX in SQL Server. This one stays on the parallelism question.

Build a Table Big Enough to Go Parallel

A small index rebuilds on one thread, so a demo needs real size. The demo database is named ReindexMaxdopDemo, so run the script on a test server. It loads 2,000,000 rows into a table with a wide text column and creates an index on that column. The load takes about ten seconds. The last query reads the server setting.

IF DB_ID(N'ReindexMaxdopDemo') IS NULL CREATE DATABASE ReindexMaxdopDemo;
GO
USE ReindexMaxdopDemo;
GO
DROP TABLE IF EXISTS dbo.Parcels;
CREATE TABLE dbo.Parcels (ParcelID int NOT NULL PRIMARY KEY CLUSTERED, RouteCode char(80) NOT NULL, Weight decimal(9,2) NOT NULL);
GO
INSERT dbo.Parcels (ParcelID, RouteCode, Weight)
SELECT TOP (2000000) n, REPLICATE(CONVERT(char(32), HASHBYTES('MD5', CONVERT(varchar(12), n)), 2), 2), 1.5
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b CROSS JOIN sys.all_objects AS c) AS t
ORDER BY n;
CREATE INDEX IX_Parcels_Route ON dbo.Parcels (RouteCode);
GO
SELECT name, value_in_use FROM sys.configurations WHERE name = N'max degree of parallelism';

The test server has 16 processors and a server setting of 2. Your numbers will differ. The setting matters because it is what DBCC DBREINDEX falls back to.

DBCC DBREINDEX Refuses MAXDOP

Try to pass the option to the old command. The next script makes three attempts, each in its own batch. The first puts MAXDOP in the WITH list. The second adds it as a fourth argument. The third tries the ONLINE option, which the old command never had.

DBCC DBREINDEX (N'dbo.Parcels', IX_Parcels_Route, 90) WITH MAXDOP = 1;
GO
DBCC DBREINDEX (N'dbo.Parcels', IX_Parcels_Route, 90, MAXDOP = 1);
GO
DBCC DBREINDEX (N'dbo.Parcels', IX_Parcels_Route, 90) WITH ONLINE = ON;

All three fail. DBREINDEX and MAXDOP do not mix, because the old command accepts a table, an index and a fill factor. Its few options do not include MAXDOP. It cannot be told how many processors to use, and it cannot rebuild online either.

ALTER INDEX Accepts MAXDOP

The replacement takes the option inside the WITH list. The next script rebuilds the index with one processor. Then it rebuilds with four processors and a fill factor of 90 in the same statement. The last statement rebuilds every index on the table with a limit of two.

ALTER INDEX IX_Parcels_Route ON dbo.Parcels REBUILD WITH (MAXDOP = 1);

ALTER INDEX IX_Parcels_Route ON dbo.Parcels REBUILD WITH (MAXDOP = 4, FILLFACTOR = 90);

ALTER INDEX ALL ON dbo.Parcels REBUILD WITH (MAXDOP = 2);

The option beats the server setting in both directions. The second statement asked for 4 while the server setting is 2, and the rebuild used 4. A limit of 1 forces a serial rebuild on a server that allows more. Another option, MAXDOP = 0, does not mean the server setting. It means up to all processors, and the next section shows it.

Watch the Degree of Parallelism

Do not trust the option. Check what the rebuild used. The view sys.dm_exec_requests has a dop column for every running request. Open a second window and run the next script. It polls the view ten times a second for about twenty seconds. It reports the highest value it saw for a rebuild in the demo database. Then start a rebuild in the first window.

DECLARE @i int = 0, @seen int, @dop int = 0;
WHILE @i < 200
BEGIN
    SELECT @seen = MAX(r.dop)
    FROM sys.dm_exec_requests AS r
    WHERE r.database_id = DB_ID(N'ReindexMaxdopDemo')
      AND r.session_id <> @@SPID
      AND r.command IN (N'DBCC', N'ALTER INDEX');
    IF @seen > @dop SET @dop = @seen;
    WAITFOR DELAY '00:00:00.100';
    SET @i += 1;
END;
SELECT @dop AS HighestDopSeen;

The table shows the highest degree of parallelism seen for each statement on the test server. It also shows the elapsed time of two runs. A limit of 1 took about 2 seconds, and a limit of 4 took about 1 second.

MAXDOP = 0 used all 16 processors even though the server setting says 2. DBCC DBREINDEX used 2, the server setting, because it has no other source. The elapsed time of DBREINDEX is not compared, because it also applied a fill factor and did other work.

Choosing a Value

Pick the value from the load, not from the processor count. On a quiet maintenance window, a higher value finishes the rebuild sooner. On a busy server, a low value leaves processors for the users and stretches the rebuild. A rebuild at MAXDOP 1 holds its locks longer when it runs offline. Pair a low value with the ONLINE option where your edition allows it.

Resource Governor can still cap the degree of parallelism of the session that runs the rebuild. If the dop column stays lower than your option, look there before you look at the statement.

You could argue that this is a small detail, because the server setting already limits every query. That is true until the day a rebuild needs a different limit from the rest of the workload. The index option is the only place to say so, and DBCC DBREINDEX never offered it.

What to Remember

DBREINDEX and MAXDOP never worked together. The old command follows the server setting. The three attempts above fail with Msg 2532, Msg 2583 and Msg 156. Use ALTER INDEX with a MAXDOP option, and check the real degree of parallelism with sys.dm_exec_requests. Treat MAXDOP = 0 as all processors, not as the default.

When you finish testing, remove the example database.

USE master;
GO
IF DB_ID(N'ReindexMaxdopDemo') IS NOT NULL
BEGIN
    ALTER DATABASE ReindexMaxdopDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE ReindexMaxdopDemo;
END;

A rebuild is not a single speed, it is a number of processors you choose.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.