Update Statistics With FULLSCAN and MAXDOP in SQL Server

To update statistics with FULLSCAN in parallel, add the MAXDOP option to the UPDATE STATISTICS statement. It lets one statement use more processors than your instance setting allows.

Why a FULLSCAN Update Crawls

Statistics tell the optimizer how many rows to expect. For a critical table, a sampled update isn’t always accurate enough. So the job uses UPDATE STATISTICS with FULLSCAN, which reads every row. On a big table that takes time. A common trap makes it worse. The instance or database has a low maximum degree of parallelism, such as 2 or 4. The workload needs it. The statistics job inherits that limit and crawls.

That is a catch-22. The setting protects the server, and the same setting slows the job. The way out is the MAXDOP option on the statement. It overrides the instance setting for that one statement, so the rest of the server keeps its limit. The option is available from SQL Server 2016 SP2 and SQL Server 2017 CU3.

Build a Table to Measure

The demo database holds one table of 3 million orders. It has a primary key, an index on CustomerCode and a plain column named OrderTotal with no index. The script needs SQL Server 2022 or later and compatibility level 160 or higher for GENERATE_SERIES. It creates a database named StatsScanSpeedDemo for this post only. Loading takes a few seconds.

IF DB_ID(N'StatsScanSpeedDemo') IS NULL CREATE DATABASE StatsScanSpeedDemo;
GO
USE StatsScanSpeedDemo;
GO
SET NOCOUNT ON;
DROP TABLE IF EXISTS dbo.SeedOrders;
CREATE TABLE dbo.SeedOrders (OrderID int NOT NULL PRIMARY KEY, CustomerCode nvarchar(20) NOT NULL, OrderTotal decimal(10,2) NOT NULL);
INSERT INTO dbo.SeedOrders (OrderID, CustomerCode, OrderTotal)
SELECT value, N'CUST-' + CAST(ABS(CHECKSUM(value)) % 90000 AS nvarchar(10)), ABS(CHECKSUM(value, 7)) % 50000 / 100.0
FROM GENERATE_SERIES(1, 3000000);
CREATE INDEX IX_SeedOrders_CustomerCode ON dbo.SeedOrders (CustomerCode);
CREATE STATISTICS ST_SeedOrders_OrderTotal ON dbo.SeedOrders (OrderTotal) WITH FULLSCAN;

First read the instance setting, so you know the baseline. Then check what a FULLSCAN statistic records. The column rows_sampled equals rows when every row was read.

SELECT name, value_in_use FROM sys.configurations WHERE name = N'max degree of parallelism';

SELECT s.name AS StatisticName, sp.rows, sp.rows_sampled
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID(N'dbo.SeedOrders') AND s.name IN (N'IX_SeedOrders_CustomerCode', N'ST_SeedOrders_OrderTotal');

The test instance allows 2 processors per statement. Both statistics read all 3 million rows. Now time the update. SET STATISTICS TIME prints CPU time and elapsed time for each statement. When CPU time is clearly above elapsed time, the statement ran in parallel.

Update Statistics With FULLSCAN and Compare MAXDOP

The first pair of statements updates the statistic on the indexed column. The second group updates the statistic on OrderTotal three ways: MAXDOP 1, no option at all, and MAXDOP 8. With no option, the instance setting of 2 applies.

SET STATISTICS TIME ON;
UPDATE STATISTICS dbo.SeedOrders IX_SeedOrders_CustomerCode WITH FULLSCAN, MAXDOP = 1;
UPDATE STATISTICS dbo.SeedOrders IX_SeedOrders_CustomerCode WITH FULLSCAN, MAXDOP = 8;
UPDATE STATISTICS dbo.SeedOrders ST_SeedOrders_OrderTotal WITH FULLSCAN, MAXDOP = 1;
UPDATE STATISTICS dbo.SeedOrders ST_SeedOrders_OrderTotal WITH FULLSCAN;
UPDATE STATISTICS dbo.SeedOrders ST_SeedOrders_OrderTotal WITH FULLSCAN, MAXDOP = 8;
SET STATISTICS TIME OFF;

The times come from one run, and they move a little each time. The pattern doesn’t. The statistic on the indexed column didn’t speed up with MAXDOP = 8. CPU time stayed close to elapsed time in both runs. The update didn’t use extra processors here. The index already holds the values in order. That is one reason to test your own statistics, because this test doesn’t prove why.

The statistic on OrderTotal behaved differently. With MAXDOP = 1, elapsed time and CPU time were close to each other. With MAXDOP = 8, CPU time rose above elapsed time, which marks a parallel plan. Elapsed time fell by a large factor. The option with no setting landed in between, because the instance limit of 2 applied. MAXDOP = 8 went past the instance limit, as the documentation says it can.

Update All Statistics of a Table

The same option works when you update every statistic on a table at once. Leave out the statistic name. This is the form a nightly job uses.

SET STATISTICS TIME ON;
UPDATE STATISTICS dbo.SeedOrders WITH FULLSCAN, MAXDOP = 1;
UPDATE STATISTICS dbo.SeedOrders WITH FULLSCAN, MAXDOP = 8;
SET STATISTICS TIME OFF;

The gain is smaller than for OrderTotal alone. The table holds three statistics, and the statistic on the indexed column doesn’t speed up.

Choose the Number

The case behind this tip used a 64-processor server with MAXDOP at 2 for the database. The fix was MAXDOP = 8 on the statistics statement, and the update ran faster. There was no need to use all 64 processors. A moderate number gets most of the gain and leaves room for the workload.

Pick the number from your core count and your load. Don’t go above the number of processors. Run a large FULLSCAN update in a quiet period, because several threads compete with your queries. Check the result with SET STATISTICS TIME, as above. If CPU time doesn’t rise above elapsed time, the update isn’t running in parallel, and a higher number won’t help.

Does the Default Already Do This?

A common question is whether MAXDOP is 0 by default, and whether SQL Server then picks the right number itself. The instance default is 0. Many instances change it, and this test instance uses 2. With the setting at 0, the update can already use every processor. The option helps when the setting is low.

You could argue that a better way to update statistics with FULLSCAN faster is to raise the instance setting. That moves the limit for every query, and your workload didn’t want that. The statement option changes one job and nothing else. I prefer the narrow change.

What to Remember

To update statistics with FULLSCAN faster, add MAXDOP to the statement on SQL Server 2016 SP2, 2017 CU3 or later. Expect the gain on statistics for columns without an index. A statistic on an indexed column can run no faster.

Measure with SET STATISTICS TIME before you trust a setting. When you finish the demo, run the cleanup script to drop the database.

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

A parallel limit is not a rule for every job, it is a default you can override for one statement.

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