Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 6 down vote accepted

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.

share|improve this answer
    
Mr. White it took you that long :-) Thanks. –  SQL Learner Mar 11 '13 at 15:49
1  
@SQLLearner Ha :) Actually, I didn't notice this question was so old when I answered it. Something magic somewhere bumps old unanswered questions from time to time - that must have just happened when I checked the active questions list. Anyway, all good. –  Paul White Mar 11 '13 at 17:24

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.

share|improve this answer
    
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 Jul 4 '12 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 Jul 4 '12 at 17:15
3  
@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 Jul 4 '12 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 Jul 5 '12 at 0:45
2  
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 Jul 5 '12 at 16:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.