Add MAXDOP parameter to Update Stats - by Javier Villegas

Status : 

 


60
0
Sign in
to vote
ID 628971 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 12/6/2010 11:45:39 AM
Access Restriction Public

Description

I'd like to have an extra parameter in UPDATE STATS command to be able to specify the desired MAXDOP value.
Sign in to post a comment.
Posted by Laurent Pasquier on 2/19/2016 at 2:23 AM
MAXDOP hint on update statistics statement would be very interesting.

Our serveur (80 logical cores, 2To RAM, 6 * 24To LUN full SSD).
For example, on a table with 1392625705 rows in clustered columnStore ARCHIVE compress with month partitionning i have the following results :

> UPDATE STATISTICS dbo.FactEvenementsPages(ST_DateEnr) WITH FULLSCAN, INCREMENTAL=ON; => Complete in 59 seconds -> maxDop = 16
> UPDATE STATISTICS dbo.FactEvenementsPages(ST_DateEnr) WITH INCREMENTAL=ON; => Complete in 569 seconds -> maxDop = 1

The maxDop is selected automatically by sql server engine StatMan instruction

Détails:

> UPDATE STATISTICS dbo.FactEvenementsPages(ST_DateEnr) WITH FULLSCAN, INCREMENTAL=ON
give the folowing statement :
SELECT StatMan([SC0]) FROM (SELECT TOP 100 PERCENT [DateEnr] AS [SC0] FROM [dbo].[FactEvenementsPages] WITH (READUNCOMMITTED) WHERE $PARTITION.fn_PartMonthly([DateEnr]) = 14 ORDER BY [SC0] ) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 16)

AND

> UPDATE STATISTICS dbo.FactEvenementsPages(ST_DateEnr) WITH INCREMENTAL=ON
give the folowing statement :

SELECT StatMan([SC0], [SB0000]) FROM
(SELECT TOP 100 PERCENT [SC0], step_direction([SC0]) over (order by NULL) AS [SB0000] FROM (SELECT [DateEnr] AS [SC0] FROM [dbo].[FactEvenementsPages] TABLESAMPLE SYSTEM (5.796610e+001 PERCENT) WITH (READUNCOMMITTED) WHERE $PARTITION.fn_PartMonthly([DateEnr]) = 14) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SB0000] ) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 1)

Only update stats with FULLSCAN are maxdop.
Posted by AaronBertrand on 11/12/2015 at 1:32 PM
Javier, can you update this item to explicitly call out the need for control over parallelism used by auto stats as well?
Posted by Mike Lawell on 12/11/2014 at 2:17 PM
And actually, it seems to vary on what MAXDOP is used...

SELECT StatMan([SC0], [SC1], [SC2], [SC3], [SC4], [SC5], [SC6], [SC7], [SC8], [SC9], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], [SC1], [SC2], [SC3], [SC4], [SC5], [SC6], [SC7], [SC8], [SC9], step_direction([SC0]) over (order by NULL) AS [SB0000] FROM (SELECT [Merchant] AS [SC0], [Terminal] AS [SC1], [TransactionTypeId] AS [SC2], [ResponseCodeId] AS [SC3], [Reversed] AS [SC4], [Settled] AS [SC5], [Voided] AS [SC6], [Acquirer] AS [SC7], [AuthorizationId] AS [SC8], [TransactionId] AS [SC9] FROM [dbo].[Transactions] TABLESAMPLE SYSTEM (2.000000e+001 PERCENT) WITH (READUNCOMMITTED) ) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SC1], [SC2], [SC3], [SC4], [SC5], [SC6], [SC7], [SC8], [SC9], [SB0000] ) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 1)
Posted by Mike Lawell on 12/11/2014 at 2:04 PM
Ditto what others are saying... I would have expected this to be implemented in SQL Server 2014. Especially since it is obviously using the maxdop setting for the parallelism. We should be able to control the degrees of parallelism from command line as we can with an index rebuild.

There are two use cases for this, 1) want to use more DOP than specified in MAXDOP to burn through it quickly 2) want to limit the impact on other processes running by limiting it (and yes, I know we can control this through resource governor).
Posted by Rob Volk1 on 8/12/2011 at 11:22 AM
Definitely a good suggestion. It's frustrating to have UPDATE STATISTICS decrease performance due to CXPACKET waits and thread exhaustion. Sorry guys, Resource Governor may be a solution, but Enterprise Edition is not.

Also, this should also add a database-level setting for auto-update statistics maxdop.
Posted by Jon Morisi on 4/18/2011 at 2:41 PM
+1 for maxdop hint for update statistics.
Posted by Eric [MSFT] on 12/15/2010 at 4:32 PM
Ok. Thanks again. We'll consider this for a future release.

Best regards,
Eric
Posted by Javier Villegas on 12/15/2010 at 10:17 AM
Resource Governor can help for this issue. But I still think that having the MAXDOP option for UPDATE STATS will be very helpful

Regards
Javier Villegas
Posted by Eric [MSFT] on 12/9/2010 at 5:32 PM
Did you try to use resource governor to solve this problem? That could be done in Enterprise Edition and higher.

Thanks,
Eric
Posted by Eric [MSFT] on 12/9/2010 at 5:32 PM
Did you try to use resource governor to solve this problem? That could be done in Enterprise Edition and higher.

Thanks,
Eric
Posted by Eric [MSFT] on 12/9/2010 at 5:32 PM
Did you try to use resource governor to solve this problem? That could be done in Enterprise Edition and higher.

Thanks,
Eric
Posted by Javier Villegas on 12/9/2010 at 1:12 PM
The configured MAXDOP on our production server is 4. We perform update stats with FULL SCAN as part of our weekly maintenance process (currently taking ~ 9 hours) so I'd like to speed up the process by using MAXDOP = 0 just for this maintenance process.

Regards
Javier
Posted by Eric [MSFT] on 12/9/2010 at 11:27 AM
Thanks for your feedback. You can accomplish the same thing with SQL Server resource governor. Can you use that to solve your problem?

Can you elaborate on why you want this? What is the problem scenario? Do you want to speed up stats gathering or do you want to make it use less resources to interfere less with other work? Your example used the default, sampled approach to gathering statistics, which is usually pretty fast already.

Best regards,
Eric Hanson
SQL Server Query Processing Team