Add MAXDOP parameter to Update Stats - by Javier Villegas

Status : 

 


25
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 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 Microsoft 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 Microsoft 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 Microsoft 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 Microsoft 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 Microsoft 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