14

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.

Improve this question

3 Answers 3

Reset to default
22

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":

Article extract

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.

If you ever need to reduce the parallelism, the MAXDOP hint in the internal query can be overridden using Resource Governor (Enterprise only).

SQL Server 2016 adds parallel sampled statistics update.

Improve this answer
0
1

Starting from SQL Server 2016 SP2 you could use MAXDOP option.

Update adds support for MAXDOP option for CREATE STATISTICS and UPDATE STATISTICS statements:

This update adds support for the MAXDOP option for the CREATE STATISTICS and UPDATE STATISTICS statements in Microsoft SQL Server 2016 and 2017. This enables you to override the default setting for max degree of parallelism (MAXDOP) that's specified at the database or server level.

Note: The resulting degree of parallelism is capped by the workload group MAX_DOP setting if Resource Governor is used.

For example, assume that the Sales.SalesOrderDetail table is used and several statistics have already been created on it. In this case, the following script updates each statistic with a degree of parallelism equal to 8.

UPDATE STATISTICS Sales.SalesOrderDetail WITH MAXDOP = 8
Improve this answer
-3

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.

Improve this answer
6
  • I apologize for mark down but you have no idea what you are talking about. From this whitepaper msdn.microsoft.com/en-us/library/dd535534(v=sql.100).aspx "Parallel statistics gathering for fullscan: For statistics gathered with fullscan, creation of a single statistics object can be done in parallel for both nonpartitioned and partitioned tables." However I am not able see parallelism when I use UPDATE STATISTICS with full scan on 30 million rows table
    – SQL Learner
    Commented Jul 4, 2012 at 17:11
  • Also, update stat takes schema stability lock so if you run other update stats it will wait until earlier one completes.
    – SQL Learner
    Commented Jul 4, 2012 at 17:15
  • 4
    @SQLLearner: No offense, but regardless of the correctness of this answer, why are you asking this question if you've read that whitepaper? It gives you an answer to everything you've asked. If you're not seeing parallelism for your specific scenario, then please edit the question to state that, and any other relevent details.
    – Jon Seigel
    Commented Jul 4, 2012 at 18:58
  • Jon Seigel, found and read the whitepaper after posting the question however seraching the web still not sure whether update statis can use parallelism.
    – SQL Learner
    Commented Jul 5, 2012 at 0:45
  • 4
    You are right, with respect to the white paper and sql2008. I just read that white paper; the info in my head is out of date. I had many problems with either sql2005 or maybe even sql2000 with respect to reindexing, UPDATE STATISTICS, FULLSCAN vs. SAMPLE, time required for jobs and the stochastic quality of the data on the stats pages. I guess that all of that pain is water under the bridge now. I would say that you could run UPDATE STATS on different tables in parallel, but that seems like more trouble than it's worth and you may have too much I/O contention for it to be worthwhile.
    – Darin Strait
    Commented Jul 5, 2012 at 16:10

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.