Parallel Statistics Update

Question

In SQL Server 2008 or later, is UPDATE STATISTICS WITH FULLSCAN a single threaded operation or it can use parallelism? How about update statistics with default sampling - can it use parallelism? I don't see an option specifying MAXDOP with update stats.

Answer 1

Parallel statistics update has been available since SQL Server 2005. It is documented in the TechNet article, "Statistics Used by the Query Optimizer in Microsoft SQL Server 2005":

Where a full scan is performed (whether explicitly requested or not) the internal query generated for the data-gathering has the general form:

SELECT
    StatMan([SC0])
FROM 
(
    SELECT TOP 100 PERCENT
        [Column] AS [SC0]
    FROM [Table] WITH (READUNCOMMITTED)
    ORDER BY [SC0]
) AS _MS_UPDSTATS_TBL
OPTION (MAXDOP 16)

Notice the MAXDOP hint there (though there is no way for a user to specify the hint manually). Where sampled statistics are gathered, the internal query uses the TABLESAMPLE clause, which prevents parallelism. The engine also generates a MAXDOP 1 hint on the internal query, which is a bit redundant.

Answer 2

UPDATE STATISTICS does not have any sort of internal parallelism. It does not matter if you are running with either FULLSCAN or SAMPLING. Of course, you can run several UPDATE STATISTICS commands at once, each on a different connection, through multiple SQL Agent jobs or some other contrivance. Depending on your exact situation with hardware and data, you may find that simply reindexing the tables is faster than UPDATE STATISTICS with FULLSCAN and possibly a better option.