none
UPDATE STATISTICS statement and MAXDOP hint

    Question

  • I am doing some testing in relation to the "parallelization" of gathering statistics in SQL Server.  I understand that since SQL Server 2005, gathering statistics can be done in parallel implicitly by SQL Server, meaning that the user cannot explicitly specify that option.  Based on my research, the general opinion out there is that running the statement UPDATE STATISTICS with either the FULLSCAN option or the option SAMPLE 100 PERCENT will result in SQL Server gathering the statistics in parallel.  I have yet to see this statement directly from Microsoft, it has just been in other websites and forums.

    This last statement is what I am having difficulty proving. I have access to 3 different SQL Server environments (SQL Server versions 2005, 2008, and 2012).  The databases on the 2008 and 2012 servers are the same databases.  The database on the 2005 is the same database from an object (table, index, procedure, etc..) level perspective but it has completely different data within the objects.

    From the testing that I have done, it seems that only SQL Server 2012 is actually gathering statistics in parallel.  I can see this from SQL Profiler trace...

    SELECT StatMan([SC0], [SC1], [SC2]) FROM (SELECT TOP 100 PERCENT [COLUMN_NAME] AS [SC0], [SOME OTHER COLUMN NAME] AS [SC1], [SOME OTHER COLUMN NAME] AS [SC2] FROM [dbo].[TABLE NAME] WITH (READUNCOMMITTED)  ORDER BY [SC0], [SC1], [SC2] ) AS _MS_UPDSTATS_TBL  OPTION (MAXDOP 16)

    I issue the exact same UPDATE statement for the exact same table in SQL Server 2008 (please note, that is the same database copy), and the result is...

    SELECT StatMan([SC0], [SC1], [SC2]) FROM (SELECT TOP 100 PERCENT [COLUMN_NAME] AS [SC0], [SOME OTHER COLUMN NAME] AS [SC1], [SOME OTHER COLUMN NAME] AS [SC2] FROM [dbo].[TABLE NAME] WITH (READUNCOMMITTED)  ORDER BY [SC0], [SC1], [SC2] ) AS _MS_UPDSTATS_TBL

    The same result comes from the SQL Server 2005 environment as well.  I then went on try other tables in the SQL 2005 and 2008 environments and no matter what I passed as a table (or index), SQL Server would not use the hint OPTION (MAXDOP x).  I tried "WITH FULLSCAN" and "WITH SAMPLE 100 PERCENT" and it didn't matter.  I adjusted the Max Degree of Parallelism setting for the instance and it also had no affect.

    These environments have little to no activity on them whatsoever.  I tried several other things as well, but they all resulted in the same behavior.

    Sorry for the long winded description, but I wanted to make sure the issue was understood clearly.  My question is, what criteria is SQL Server using to decide on whether or not to use the MAXDOP hint with the UPDATE STATISTICS statement and how has that criteria changed between SQL 2005, SQL 2008, and SQL 2012?  

    Thanks!


    • Edited by MayanMurad Friday, September 27, 2013 3:46 PM
    Friday, September 27, 2013 3:39 PM

Answers

  • Thanks, but I think I may have found my answer.  I am posting in case anyone else has the same question.

    After further testing, it appears as though in SQL Server 2005 and SQL Server 2008, the number of parallel threads used when gathering statistics at a 100% sample size (or with fullscan) is left up to what the instance has as a value for the "max degree of parallelism" setting.  This is why you will not see the OPTION (MAXDOP x) hint in these versions.  In SQL Server 2012, I believe Microsoft is overriding the instance level value of "max degree of parallelism" and in order to do so, it explicitly places the hint at the end of the query.  That is why I see the hint in SQL Server 2012 and I don't see the hint in SQL Server 2005 and 2008.

    I was able to prove this by taking one SQL 2008 environment and gathering statistics at 100% sample rate with "max degree of parallelism=0" and with "max degree of parallelism=1".  The timings for the two jobs were different.  The test with "max degree of parallelism=0" ran much faster. There were still no explicit hints (like in SQL Server 2012), so I was not able to show it in that sense, but I did run multiple tests and the results were the same each time.  These servers are test servers so there is little to no activity on them.

    In all versions from SQL Server 2005 - 2012, you will always see the hint OPTION (MAXDOP 1) for statistics queries that use a sample size less than 100% and that is to ensure that statistics gathered at lower sample sizes are single-threaded. 

    In my original post, I stated that "I adjusted the Max Degree of Parallelism setting for the instance and it also had no affect." This was from the perspective of the actual SQL trace and not response times.  I was testing to see whether the hint showed up or not depending on what "max degree of parallelism" was set to.  This still remains true as the hints passed are not affected by what value you have for "max degree of parallelism".  

    Hope this helps.

    Thursday, October 03, 2013 4:05 PM

All replies

  • Hi MayanMurad,
     
    Thank you for your question. 
     
    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 
     
    Thank you for your understanding and support.


    Allen Li
    TechNet Community Support

    Thursday, October 03, 2013 8:27 AM
  • Thanks, but I think I may have found my answer.  I am posting in case anyone else has the same question.

    After further testing, it appears as though in SQL Server 2005 and SQL Server 2008, the number of parallel threads used when gathering statistics at a 100% sample size (or with fullscan) is left up to what the instance has as a value for the "max degree of parallelism" setting.  This is why you will not see the OPTION (MAXDOP x) hint in these versions.  In SQL Server 2012, I believe Microsoft is overriding the instance level value of "max degree of parallelism" and in order to do so, it explicitly places the hint at the end of the query.  That is why I see the hint in SQL Server 2012 and I don't see the hint in SQL Server 2005 and 2008.

    I was able to prove this by taking one SQL 2008 environment and gathering statistics at 100% sample rate with "max degree of parallelism=0" and with "max degree of parallelism=1".  The timings for the two jobs were different.  The test with "max degree of parallelism=0" ran much faster. There were still no explicit hints (like in SQL Server 2012), so I was not able to show it in that sense, but I did run multiple tests and the results were the same each time.  These servers are test servers so there is little to no activity on them.

    In all versions from SQL Server 2005 - 2012, you will always see the hint OPTION (MAXDOP 1) for statistics queries that use a sample size less than 100% and that is to ensure that statistics gathered at lower sample sizes are single-threaded. 

    In my original post, I stated that "I adjusted the Max Degree of Parallelism setting for the instance and it also had no affect." This was from the perspective of the actual SQL trace and not response times.  I was testing to see whether the hint showed up or not depending on what "max degree of parallelism" was set to.  This still remains true as the hints passed are not affected by what value you have for "max degree of parallelism".  

    Hope this helps.

    Thursday, October 03, 2013 4:05 PM